High 'direct path read' waits in 11g

Symptoms : **************

After migrating an 11g database from a standalone to a 4-node RAC, a noticeable
increase of 'direct path read' waits were observed at times.


Here are the Cache sizes and Top 5 events.


waits

Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 3,232M 3,616M Std Block Size: 8K
Shared Pool Size: 6,736M 6,400M Log Buffer: 8,824K



Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU 13,916 42.1
direct path read 1,637,344 13,359 8 40.4 User I/O
db file sequential read 47,132 1,111 24 3.4 User I/O
DFS lock handle 301,278 1,028 3 3.1 Other
db file parallel read 14,724 554 38 1.7 User I/O





Changes : ******************************************

Migrated from a standalone database to a 4-node RAC.
Moved from Unix file system storage to ASM.

Using Automatic Shared Memory Management (ASMM).
The setting of db_cache_size in spfile/pfile is low compared to normal workload requirements.



Cause : **********************************

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.



Solution : ******************************************

When using Automatic Shared Memory Management (ASMM) and with buffer cache low limit set at a low end compared to the normal workload requirements and usually after startup.

11g might choose to do serial direct path read scans for large tables that do not fit in the SGA.


When ASMM increases the buffer cache due to increased demand, 11g might not again do serial direct path read scans for these same large tables.

If you like to avoid this from happening, you should note the buffer cache and share pool requirements for a normal workload and set the low limits of buffer cache and shared pool in spfile/pfile close to these normal workload values.

db_cache_size
shared_pool_size




References
BUG:8214594 - PERFORMANCE PROBLEMS ON ORACLE 11G - HIGH 'DIRECT PATH READ' WAITS