Oracle internals, debugging and undocumented features

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

10 responses to “Direct path reads and serial table scans in 11g

  1. Martin Berger July 17, 2009 at 8:09 pm

    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 😉

  2. extremedb July 20, 2009 at 2:00 pm

    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.

  3. oraclue July 20, 2009 at 2:08 pm


    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.


  4. Martin Berger July 20, 2009 at 6:15 pm

    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.

    • extremedb July 21, 2009 at 4:12 am

      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

  5. Pingback: Disabling direct path read for the serial full table scan – 11g « Dion Cho – Oracle Performance Storyteller

  6. Pingback: Blogroll Report 17/07/2009 – 24/07/2009 « Coskan’s Approach to Oracle

  7. Pingback: Fun with _SERIAL_DIRECT_READ « ORAganism

  8. Pingback: What happens, when you did not do a load test before release upgrades ? « Coskan’s Approach to Oracle

  9. Pingback: Upgrade Argh « Oracle Scratchpad

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: