Random Notes on Direct Path Reads


We have been experiencing performance problems after upgrading to Oracle RDBMS 11.2.0.3 and 11.2.0.4 due to high Direct Path Read events. This post is a selection of MOS documents that could be relevant to our problems.

See WAITEVENT: “direct path read” Reference Note (Doc ID 50415.1):

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.

Emphasis Mine

In our experience, the problem appeared when we upgraded from Oracle RDBMS 11.2.0.2 to 11.2.0.3 or 11.2.0.4. The upgrade from Oracle RDBMS 10.2.0.4 to 11.2.0.2 did not encounter this problem. And Oracle RDBMS 11.2.0.4 seems to be worse than 11.2.0.3 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.

Advertisements

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