Home > Performance > Direct path reads and serial table scans in 11g

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

About these ads
Categories: Performance
  1. 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. July 20, 2009 at 2:00 pm

    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.

  3. July 20, 2009 at 2:08 pm

    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

  4. 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.
    hth,
    Martin

    • July 21, 2009 at 4:12 am

      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

  1. July 21, 2009 at 3:38 am
  2. July 27, 2009 at 2:28 pm
  3. June 27, 2011 at 4:33 pm
  4. August 15, 2011 at 7:56 pm
  5. September 26, 2011 at 12:32 pm

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 47 other followers

%d bloggers like this: