This content has been marked as final. Show 4 replies
The autotrace statistics do not prove that there was a direct path read in the first execution.
All they prove is a physical read.
Do a sql trace with waits / 10046 trace to check.
serialdirect_read does not seem to be part of the optimizer environment settings.
alter session set events 'sql_trace wait=true';
The SQL*Plus autotrace command displays a small set of the available session statistics, and, as Dom mentions, this small selection can't be used to directly confirm if direct path reads are being used. I believe the statistic you want is "physical reads direct". (Looking at wait events in a 10046 trace can also confirm the presence of direct path reads)
That having been said, the purpose of serialdirect_read is to adjust Oracle's preference to do multiblock reads via direct path rather than through the buffer cache. Note the key word "multiblock"; your query's 4 logical I/O's look suspiciously like a series of single-block I/Os in an index and table lookup. There's precious little to be gained by using direct path for single block reads.
For a much more detailed look at these concepts, I'd recommend Frits Hoogland's excellent presentation "about multiblock reads" which describes the logic in a much more detailed fashion: http://www.ukoug.org/what-we-offer/library/about-multiblock-reads/about-multiblock-reads.pdf
Although direct path read is not an optimizer decision it ,nevertheless, depends in some way on the decision made by the optimizer on the choice this one will made to read your segment.
was exepecting that during the 2nd execution there would still be physical reads going to the disk (because direct read does not cache to the buffer).
1. FULL TABLE SCAN
2. INDEX FAST FULL SCAN
So, I don't think that you will be direct path reading if your query is not using one of the above FULL segment operation. Because a FULL TABLE SCAN can be done either via
1. DIRECT PATH READ or
2. DB FILE SCATTERED READ
The decision to use one of the above read pathes depends on the information Oracle will find on the segment header block. This is why a DB FILE SEQUENTIAL read is first made(if not yet in the cache) against the segment header block to have information about the number of blocks to be read and the number of blocks below the high water mark. Based on this information and based on the fact that the CBO has decided to FULL scan the TABLE or the INDEX, direct path read (or db file scattered read) will be done.
Bottom line (in my opinion) is that direct path read is related to FULL TABLE SCAN and INDEX FAST FULL SCAN. Ensure first that your are using one of these operations before questioning why direct path read has not been used
Hope this helps
Edited by: Mohamed Houri on 11-juin-2012 5:08
I agree with Mohamed,
And if you really want to see the offload in action, remove the predicate and perhaps use some aggregate function.
It will do the trick.