Top 60 Oracle Blogs

Recent comments

November 2018

A Brief Look Inside Oracle's Autonomous Data Warehouse Cloud

This post is part of a series that discusses some common issues in data warehouses.
There is lots of documentation for Autonomous Data Warehouse Cloud (ADWC), in which I found this bold claim:

Index rebuild bug

I tweeted a reference yesterday to a 9 year old article about index rebuilds, and this led me on to look for an item that I thought I’d written on a related topic. I hadn’t written it (so there’s another item on my todo list) but I did discover a draft I’d written a few years ago about an unpleasant side effect relating to rebuilding subpartitions of local indexes on composite partitoned tables. It’s probably the case that no-one will notice they’re suffering from it because it’s a bit of an edge case – but you might want to review the things your system does.

Here’s the scenario: you have a large table that is composite partitioned with roughly 180 daily partitions and 512 subpartitions (per partition). For some strange reason you have a couple of local indexes on the table that have been declared unusable – hoping, perhaps, that no-one ever does anything that makes Oracle decide to rebuild all the unusable bits.

Bootstrapping a VM image in Oracle Cloud Infrastructure using cloud-init

At the time of writing Oracle’s Cloud Infrastructure as a Service (IaaS) offers 2 ways to connect block storage to virtual machines: paravirtualised and via iSCSI. There are important differences between the two so please read the documentation to understand all the implications. I need all the performance I can get with my systems so I’m going with iSCSI.

Data Warehouse Design: Engineered Systems Considerations

This post is part of a series that discusses some common issues in data warehouses.

On an engineered system, a key feature is that Bloom filters are pushed to storage cells during smart scan,  Additionally, a Bloom filter computed from a join column of a one table can be used against another table.  Storage index can skip I/O against the large fact table based on a Bloom filter calculated from a small dimension table (see Tanel Poder's Blog: Combining Bloom Filter Offloading and Storage Indexes on Exadata)
This shifts the balance away from Star Transformation, so you are far less likely to want to add bitmap indexes.

The phantom tablespace

(Cueing my deep baritone Morpheus voice…) What if I told you that you can reference non-existent tablespaces in your DDL?

OK, it sounds like a gimmick but there is a real issue that I’ll get to shortly. But first the gimmick Smile

I’ve created a partitioned table called “T” (I’ll pause here for your applause at my incredible imagination skills for table naming Smile) and to show you the complete DDL, I’ll extract it using the familiar DBMS_METADATA package.

Counting Rows

Here’s another little utility I use from time to time (usually for small tables) to check how many rows there are in each block of the table, and which blocks are used. It doesn’t do anything clever, just call routines in the dbms_rowid package for each rowid in the table:

Enhanced “validate” commands in Oracle’s Data Guard Broker 18c

If you are using an Oracle Database Enterprise Edition chances are that there is at least one environment in your estate making use of Data Guard. And if you are using Data Guard, why not use the broker? I have been using Data Guard broker for a long time now, and it has definitely improved a lot over the first releases, back in the day. I like it so much these days that I feel hard done by if I can’t make use of it. This is of course a matter of personal preference, and I might be exaggerating a little :)

One of the nice additions to the broker in Oracle 12.1 was the ability to validate a database before a role change. This is documented in the Data Guard broker documentation. I certainly don’t solely rely on the output of the command, I have my own checks I’m running that go over and above what a validate can do.

Oracle Adaptive Plan info in OTHER_XML

DBMS_XPLAN displays the operation ID with no gap, even for Adaptive Plans where the inactive operations are skipped. Did you ever wonder where the information of skipped rows is stored?

Here is a simple query (but please, remember that natural join is bad ;)

SQL> set feedback on sql_id
SQL> select * from dept natural join emp natural join bonus;
no rows selected
SQL_ID: 3q7fbwk91v4ra

The execution plan shows that the plan is adaptive:

Dump logfile

Here’s a little procedure I’ve been using since Oracle 8i to dump the contents of the current log file – I’ve mentioned it several times in the past but never published it, so I’ll be checking for references to it and linking to it.

The code hasn’t changed in a long time, although I did add a query to get the full tracefile name from v$process when that became available. There’s also an (optional) called to dbms_support.my_sid to pick up the SID of the current session that slid into the code when that package became available.

Log in to Ubuntu VMs in Oracle Cloud Infrastructure

When I learned that Oracle was providing Ubuntu images in Oracle Cloud Infrastructure (OCI) I was a bit surprised at first. After all, Oracle provides a great Enterprise Linux distribution in the form of Oracle Linux. As a Ubuntu fan I do of course appreciate the addition of Ubuntu to the list of supported distributions. In fact it doesn’t end there, have a look at the complete list of Oracle provided images to see what’s available.

Trying Ubuntu LTS

I wanted to give Ubuntu a spin on OCI and decided to start a small VM using the 16.04 LTS image. I have been using this release quite heavily in the past and have yet to make the transition to 18.04. Starting the 16.04 VM up was easily done using my terraform script. Immediately after the terraform prompt returned I faced a slight issue: I couldn’t log in: