Direct path reads and serial table scans in 11g
To answer comment by Frits about Doug Burns observation of using full table scans that resulted in direct path reads.
Here is explanation from Oracle itself:
There have been changes in 11g in the heuristics to choose between direct path reads or reads through buffer cache for serial table scans.
In 10g, serial table scans for “large” tables used to go through cache (by default) which is not the case anymore. In 11g, this decision to read via direct path or through cache is based on the size of the table, buffer cache size and various other stats.
Direct path reads are faster than scattered reads and have less impact on other processes because they avoid latches.
References: High ‘direct path read’ waits in 11g Note: 793845.1
Comments
10 Responses to “Direct path reads and serial table scans in 11g”Trackbacks
Check out what others are saying...-
[...] Miladin Madrakovic -Direct path reads and serial table scans in 11g [...]
-
[...] amount of attention since 11g was released. Doug Burns, Dion Cho (and here), Kerry Osborne and Miladin Modrakovic have all written on the subject. Tanel Põder also covered it in his presentation at the UKOUG [...]
-
[...] parallel, and no parallel hinted code. Fortunately, enough people have written about serial direct path reads in 11g to give us a clue about what’s going on, so let’s take a look at the [...]
Alex Faktulin reported some oservations about these ‘various other stats’ in sime of his blog-posts.
Maybe http://afatkulin.blogspot.com/2009/01/11g-adaptive-direct-path-reads-what-is.html is a good start
Miladin
Plz Do not believe Oracle’s Note: 793845.1
It’s not Hueristic Rules.
CBO choose the way which go direct or not.
Hueristic Rule something like RBO?
Have you ever heard using RBO with various other stats?
there must have some formular to get the cost.
PLZ remember!
“Very very very simple formular with stats to get the cost” is not heuristic.
Hi,
I know.I do not trust anything until I try.Did not have a chance to test this yet ( extremely busy these days ).As you know Oracle own the code and they can change “anything” at any point in time especially CBO behavior.
Thanks for reading my post.
Miladin
first of all, it’s not the CBO. It’s an underlying logic (check a 10053 event, you will see no difference).
In 11gR1 it’s really a ‘simple’ logic. You can check for “smallness logic”, or at least _small_table_threshold.
Note:787373.1 – How does Oracle load data into the buffer cache for table scans? might be of some interrest for you.
hth,
Martin
Martin
you should know that 10053 Event just show us 3 things below.
First Query Transformation with or
without cost
ex) Heuristic Rule Or Cost based Query transformation
Second Access Method with cost
ex) using index or full table scan
Third Join Method with cost
ex) Using Nested loop join or Hash Join
“go direct or not” is nothing more to do with 3 things now.
10053 Event keep on changing to catch up with the latest Enhanced feature.
PLZ remember another 3 things
1.smallness logic to get cost is not heuristic.
2.Only CBO use various stats to get cost.
RBO use Heuristic rules without stats
3.Oracle 11g introduce “cost based analysis of direct i/o access”
find and read Oracle patents file.
after that you will agree with me