Direct Path Reads in Oracle Database 11gR2

Updated: 11/26

A new feature introduced in Oracle 11gR2 is the way the Direct Path Read operation is done.  A direct path read is where the data is read directly from the data files into the PGA rather than into the buffer cache in the SGA.  Direct Path reads have been around for a while when doing parallel operations; however, serial direct path reads are new in Oracle 11gR2.  The direct path read limits the effect of a tablescan on the buffer cache by reading directly into PGA memory.  The direct path read is a feature that benefits both standard hardware and the Exadata Storage Cell, which performs table scans with amazing efficiency.

The direct path read is available only when the Oracle optimizer chooses a full table scan.  At run time, a direct path read can be chosen based on internal criteria.  Unlike previous releases of Oracle where a serial table scan used the buffer cache, the direct path read does not use the buffer cache, or in the case of an Exadata, the cell flash cache.  This is done to avoid the problem where a large table scan can place a large amount of data in the buffer cache.  Although Oracle does this intelligently, it still can take a lot of space in the buffer cache.

The direct path read operation is asynchronous, thus the session does not necessarily wait for them to complete.  It is only when the session stalls waiting to make sure that all asynchronous reads/writes have taken place that you will notice the direct path read waits.

This feature is designed to help avoid the problem where a large table scan ejects all other data from the buffer cache.  The keep and recycle caches helped with this, but could be difficult to tune.  The decision to perform a direct path read is based on the size of the buffer cache, the size of the table and various statistics.

In general, the direct path read can be helpful, but in some cases, where the same data is constantly being re-read for application purposes, the direct path read can generate huge amounts of unnecessary reads, thus bogging down the I/O subsystem.  Because of the nature of the direct path wait events, this effect of the direct path read operation will often show up as an I/O wait, not a direct path wait.

So, in some cases, a table that is frequently accessed via a table scan might hit the threshold where the direct path read operation is met where it is inappropriate.  In these applications, the same table scan is run over and over again with no benefit of the buffer cache, thus causing excessive I/O operations.

I have also seen this be a problem with Exadata where a TABLE ACCESS STORAGE FULL is downgraded to cell single block reads, due to a chained row or fragmentation issue.  Since it is a table scan, the direct path read was chosen, but the storage cell was unable to do a cell smart scan and downgraded to the cell single block read, thus resulting in un-optimized, un-cached reads.

Page 2 – Direct Path Read Solution explained

By |2018-02-23T17:58:49+00:00November 12th, 2013|Performance Wire|Comments Off on Direct Path Reads in Oracle Database 11gR2