Top 60 Oracle Blogs

Recent comments

July 2013

Cursor Sharing

Here’s a couple of extracts from a trace file after I’ve set optimizer_dynamic_sampling to level 3. I’ve run two, very similar, SQL statements that both require dynamic sampling according to the rules for the parameter – but take a look at the different ways that sampling has happened, and ask yourself what’s going on:

Statement 1 produced this sampling code:

The Oracle ‘db file parallel read’ wait event

This is a small note describing how Oracle implemented the situation which is covered by the db file parallel read wait event. This events happens if Oracle knows it must read multiple blocks which are not adjacent (thus from different random files and locations), and cannot continue processing with the result of a single block. In other words: if it cannot process something after reading a single block (otherwise Oracle will read a single block visible by the wait ‘db file sequential read’).

This is how it shows up if you enable sql trace:

Oracle Database 12c Identity Columns and New Column DEFAULT Enhancements…

My mission to learn 12c continues. I’ve been looking at the Identity Column functionality and enhancements to column defaults.

Oracle Cloud Control 12c Release 3 Installation (EM12cR3)…

With all the excitement about Oracle Database 12c being released, you may have missed the release of Cloud Control 12c Release 3. It’s available for download from OTN. All the usual ports are available. This is the version you are going to need if you want to monitor 12c databases in your organisation.

I did a quick run through the Cloud Control 12c Release 3 installation yesterday on both OL5 and OL6. As I suspected, there is pretty much no change in the installation process compared to the previous release.

Enkitec Extreme Exadata Expo (E4)

Exadata Conference
Event date: 
Mon, 2013-08-05 - Tue, 2013-08-06

You're invited to E4 2013!

Enkitec Extreme Exadata Expo (E4) is the only conference with a focus on the Oracle Exadata platform. E4 will bring together a team of world-renowned Oracle Exadata experts who will present proven methods and techniques gained while working with mission critical applications in production environments.

The E4 speakers list features many OakTable members including Karl Arao, Martin Bach, Maria Colgan, Frits Hoogland, Karen Morton, Kerry Osborne, Tanel Poder and Gwen Shapira.

Extra huge database IOs, part 2

This is part 2 of a number of blogposts about huge Oracle database IO’s.
If you landed on this blogpost and did not read part 1, please read part 1 here.

In part 1 I showed how database IOs of a full table scan could be bigger than 1MB by increasing the db_file_multiblock_read_count parameter to a number beyond 1MB expressed in Oracle blocks. These bigger IOs only happen with direct path reads, not with buffered multiblock reads.

But how much bigger can these IOs be? In part 1 I showed Oracle IOs of 1020 blocks. Is that the limit? To investigate this, I created a much bigger table (table T2 in part 1 had a maximum extent size of 1024 blocks, which meant that the 1020 is the biggest IO possible from this table).

For the sake of this investigation I created a much bigger table to get larger extents:

12c – New SQL_ID Calculation

Updated 7/7/13: Well I’m a doofus! This is not a generic problem. It is a bug but only happens when using a specific new feature I was playing with on my 12.1 database (SQL Translation Framework). No need to worry about this unless using that feature. (thanks to Stefen for pointing this out) So you probably don’t need to read this at all. The comments might be worth looking at though. :)


Shoot! SQL_ID calculation is different between 11.2 and 12.1. This is a bummer because we’ve gotten used to being able to go back and forth between versions to verify plans after upgrading to 11g for example. It was also convenient to be able to track changes in performance statistics before and after an upgrade. Fortunately there is a work around. The old_hash_value column has been carried through to 12c. See here:

VirtualBox 4.2.16 Released…

Oracle VirtualBox 4.2.16 has been released. The downloads and changelog are in the usual places.

Happy upgrading!



VirtualBox 4.2.16 Released… was first posted on July 5, 2013 at 11:26 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Extra huge database IOs

It’s been a while since I presented the first incarnation of my ‘about multiblock reads’ presentation. When I did this at the UKOUG TEBS conference in Birmingham in 2011, Christian Antognini chaired my presentation. After my presentation Christian showed me it’s possible to set the parameter ‘db_file_multiblock_read_count’ higher than 1MB/db_block_size (which is 128 if your blocksize is 8kB), and you could benefit from it if your hardware is sufficient. In fact, Christian showed me AWR reports (could also be statspack reports, not sure) which showed the benefit.

My understanding of the parameter db_file_multiblock_read_count at the time was:

The maximum value is the operating system’s maximum I/O size expressed as Oracle blocks ((max I/O size)/DB_BLOCK_SIZE). If you set this parameter to a value greater than the maximum, Oracle uses the maximum.

Wrong Index

One of the sad things about trying to keep on top of Oracle is that there are so many little things that could go wrong and take a long time to identify. In part this is why I try to accumulate test cases for all the oddities and anomalies I come across as I travel around the world – if I’ve spent the time recreating a problem I’ll probably remember it the next time I see the symptoms.

Here’s a little threat that comes into play when a couple of events occur simultaneously, in this case: automatically selected indexes being rebuilt combined with an unfortunate choice of index definitions. Here’s a demonstration (running, 1MB uniform extents, 8KB block size, freelist management – first the symptoms, script, followed by results: