Here’s a summary of a recent posting on OTN:
I have two indexes (REFNO, REFTYPESEQNO) and (REFNO,TMSTAMP,REFTYPESEQNO). When I run the following query the optimizer uses the second index rather than the first index – which is an exact match for the predicates, unless I hint it otherwise:
In order to look how Exadata smartscans are different, first let’s have a peek the Oracle full segment/multiblock read evolution as short as possible:
a) Traditional multiblock reads, visible via the event ‘db file scattered read’
The essence is: Multiple adjacent blocks are read from disk, and put in the buffercache. Because every read is sequentially processed, IO latency is a performance penalty for every physical read. This works roughly this way: get a set of adjacent blocks from the segment header, fetch these blocks from disk, process these blocks, then get the next set of adjacent blocks, fetch these blocks from disk, process these blocks, etc.
SQL> !cat dbren.sql
SQL> !cat dbren.sql
I’ve just wasted quite a bit of time trying to figure out why one of my VirtualBox VMs wasn’t starting properly, so I thought I would share the experience here in case anyone else has a similar issue…
It all started because I decided to fire up an OL6 VM, that I hadn’t used for a little while. It started up fine and I figured I should probably patch the OS and the VirtualBox Guest Additions. The first thing I did was a “yum update” to bring it up to OL6.4 and the the latest UEK2. Once that was complete I did a reboot and that is where the issue started. The VM booted in the normal fashion, but then hung at this point.
Consider the following simple setup
SQL> create table t ( x int, b blob ); Table created. SQL> declare 2 bin blob; 3 begin 4 insert into t values (1, empty_blob()) 5 returning b into bin; 6 7 dbms_lob.writeappend(bin,10000,utl_raw.cast_to_raw(rpad('x',10000,'x'))); 8 commit; 9 end; 10 / PL/SQL procedure successfully completed. SQL> select ora_hash(b) from t; ORA_HASH(B) ----------- 3856858313 SQL> select ora_hash(b) from t; ORA_HASH(B) ----------- 3856858313 SQL> select ora_hash(b) from t; ORA_HASH(B) ----------- 3856858313
That all seems fine…but thats because I ran the ORA_HASH commands quickly one after the other… Now that 30 seconds has passed (as I type this into the blog)…lets run the same ORA_HASH again
A long time ago … a long long time ago (http://www.oracledba.co.uk/tips/merge_speed.htm) I wrote that MERGE works best over update and insert.
This still holds true, but its also apparent that MERGE seems to be optimized for larger sets of rows. When you take it down to single row operations, then don’t be SO hasty to recast your updates-and-inserts into merge commands.
From time to time I’ve looked at an AWR report and pointed out to the owner the difference in work load visible in the “SQL ordered by” sections of the report when they compare the summary figure with the sum of the work done by the individual statements. Often the summary will state that the captured SQL in the interval represents some percentage of the total workload in the high 80s to mid 90s – sometimes you might see a statement that the capture represents a really low percentage, perhaps in the 30s or 40s.