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 ?
Vishal Gupta's Blog 
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
sguinales said
Tim for Exadata try to turn off adaptative direct read by hidden parameters, _serial_direct_read to or _adaptive_direct_read.
Regards
Roel Hendriks said
Nice one, Vishal. Thanks for sharing.
Cheers,
Roel Hendriks
Vishal Gupta said
Roel, How are you? Long time no news from you.
Thanks for stopping by.
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
Harmandeep Singh said
Nice explanation of things. It means Oracle in one way is telling us to have more better indexes in place
Vishal Gupta said
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
http://yahoo.com said
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