We have been experiencing performance problems after upgrading to Oracle RDBMS 220.127.116.11 and 18.104.22.168 due to high Direct Path Read events. This post is a selection of MOS documents that could be relevant to our problems.
Direct path reads are generally used by Oracle when reading directly into PGA memory (as opposed to into the buffer cache). If asynchronous IO is supported (and in use) then Oracle can submit IO requests and continue processing. It can then pick up the results of the IO request later and will wait on “direct path read” until the required IO completes.
If asynchronous IO is not being used then the IO requests block until completed but these do not show as waits at the time the IO is issued. The session returns later to pick up the completed IO data but can then show a wait on “direct path read” even though this wait will return immediately.
Hence this wait event is very misleading as:
- The total number of waits does not reflect the number of IO requests
- The total time spent in “direct path read” does not always reflect the true wait time.
This style of read request is typically used for:
- Sort IO (when a sort does not fit in memory)
- Parallel Query slaves
- Readahead (where a process may issue an IO request for a block it expects to need in the near future)
One cause appears to be documented in Higher ‘direct path read’ Waits in 11g when Compared to 10g (Doc ID 793845.1):
In 10g, serial table scans for “large” tables go through the buffer cache (by default).
In 11g, there has a been a change in the rules that choose between using ‘direct path reads’ and reads through the buffer cache for serial (i.e. non-parallel) table scans. This decision is based on the size of the table, buffer cache size and various other statistics. Since Direct path reads are faster than scattered reads and have less impact on other processes because they avoid latches it is likely that they will be chosen for such reads in 11g and above.
The choice can vary over time for the same tables, for example, when using Automatic Shared Memory Management (ASMM) with the buffer cache low limit set low when 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 then change to go through the buffer cache for these same large tables.
In our experience, the problem appeared when we upgraded from Oracle RDBMS 22.214.171.124 to 126.96.36.199 or 188.8.131.52. The upgrade from Oracle RDBMS 10.2.0.4 to 184.108.40.206 did not encounter this problem. And Oracle RDBMS 220.127.116.11 seems to be worse than 18.104.22.168 in this regard.
However, AIX: Query Becomes Progressively Slower over Time, Waits On Direct Path Read (Doc ID 1568676.1) indicates a possible performance degradation due to the following:
The AIX fastpath feature is impacting the performance of the query. Beginning with AIX 6.1, the fsfastpath feature for file systems is enabled by default.
Will need to check for asynchronous I/O settings and for the following warnings in the database alert log:
Warning: lio_listio returned EAGAIN Performance degradation may be seen.
These are just random notes as I investigate the Direct Path Read problem.
Our immediate fix is to use the
KEEP BUFFER CACHE for the objects that are adversely affected by this wait event. This is the fix recommended by Jonathan Lewis when he discusses a Cache anomaly.