Vishal Gupta's Blog

Direct Path Reads – 11g Changed Behaviour

Posted by Vishal Gupta on Aug 19, 2011

In 10g, serial full table scans for “large” tables used to always go through cache (by default). If table was small it was placed at most recently used (MRU) end of the buffer cache. If table is large it is placed at least recently used (LRU) end of the buffer cache.

In 11g, full table scan do not always go through the buffer cache. Decision to read via direct path or through cache is based on the size of the table, buffer cache and various other stats. Table is considered to be small or large based value of _small_table_threshold internal parameter.  Default value of this parameter is 2% of buffer cache size and is specified in blocks. This means any object (table) smaller than 2% of the buffer cache will be read via the buffer cache and not using direct path read. And tables larger than 2% of the buffer cache are read via direct path read and not via buffer cache. With AMM (Automatic Memory Management) or ASMM (Automatic Shared Memory Management), buffer cache could drop to a lower value if memory is falling short for shared pool. In such a case after restart of instance, _small_table_threshold parameter would become even lower due to decreased buffer cache.

By enabling event 10949 at either session or system/instance level one can disable the autotuning of direct path reads for full table scans. It means full table scan on every table whether small or large, will go via buffer cache. This could flush out the already cached cached objects. I would strongly advise against setting this for all the databases in our standard build.

[/source]SQL> alter system set events '10949 trace name context forever' scope=spfile;

$ oerr ora 10949 10949, 00000, "Disable autotune direct path read for full table scan"
 // *Cause:
 // *Action: Disable autotune direct path read for serial full table scan.

If you are doing full table scans on the same LARGE table too often, then I would suggest to tune the query or create an index on the table. You could also set the minimum value for db_cache_size, so that it does not fall below the minimum (normal workload) levels and small_table_threshold parameter value does not fall below desired threshold.

Related Oracle MOS Notes

Doc ID 793845.1 – High ‘direct path read’ waits in 11g

Doc ID 787373.1 – How does Oracle load data into the buffer cache for table scans ?

About these ads

8 Responses to “Direct Path Reads – 11g Changed Behaviour”

  1. Tim Hopkins said

    Hi Vishal,

    Have you tried event 10949 on an Exadata system? Serial direct path reads happened regardless of the event on Exadata when I last tried it. It did work as expected on non Exadata environments.

    Cheers,
    Tim

  2. Roel Hendriks said

    Nice one, Vishal. Thanks for sharing.

    Cheers,

    Roel Hendriks

  3. Igor Usoltsev said

    Thanks, Vishal

    Good sharing especially about the connection between Automatic [Shared] Memory Management usage and decreased _small_table_threshold value after instance restart

    We noticed the same behavior, which resulted in the significant increasing of some queries execution time, which used quick operations HASH JOIN / FULL TABLE SCAN of cached tables
    After db restart this tables “suddenly” became BIG tables (blocks > _small_table_threshold*5) and we got a lot of “direct path read” events and physical reads (statistics) on EVERY execution

    Igor

  4. Nice explanation of things. It means Oracle in one way is telling us to have more better indexes in place

    • Harmandeep,

      Oracle definitely wants you use index instead of full table scan, unless you are running your database on Exadata where in certain cases FTS can be even faster than indexes based access due to offloading of predicate filtering to Exadata storage layer (cell-offloading).

      Oracle is also trying to better utilize your cache. Instead of flushing the useful stuff in the cache with large FTS, its trying to protect your cached objects by self-tuning and intelligently deciding to cache small table FTS and not caching large table FTS.

      Vishal

      • Peter said

        Hi Vishal,

        Not still sure on whether this Direct Read Feature be disabled or kept as it is… whats your latest finding on this ?

        In case, big tables always go for Direct Reads and not cached, then every call to these big tables will be a performance issue in case these big tables are

        queried quite often. Isn’t Direct Physical Reads are costly than the read from cache. ?

        Thanks for great article !!
        Peter

  5. This blog post, “Direct Path Reads – 11g Changed Behaviour
    Vishal Gupta’s Blog” was in fact excellent. I’m producing out a reproduce to show my close friends. Thanks for the post,Bart

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

 
%d bloggers like this: