Oraclue
Oracle internals, debugging and undocumented features
Direct path reads and serial table scans in 11g
Posted by on July 17, 2009
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
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
Pingback: Disabling direct path read for the serial full table scan – 11g « Dion Cho – Oracle Performance Storyteller
Pingback: Blogroll Report 17/07/2009 – 24/07/2009 « Coskan’s Approach to Oracle
Pingback: Fun with _SERIAL_DIRECT_READ « ORAganism
Pingback: What happens, when you did not do a load test before release upgrades ? « Coskan’s Approach to Oracle
Pingback: Upgrade Argh « Oracle Scratchpad