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 ?
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
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