Understanding direct path read waits

Question:  When I run by query, it takes a really long time and I see waits on direct path read temp and direct path read.  What causes the direct path read waits?

Answer:  You see direct path read waits only when you are doing a parallel full-scan.

Unplanned direct path reads commonly happen when you turn on parallelism on at the system or session level:

alter table xxx parallel degree 32;

By specifying a table or index with the parallel option, the SQL optimizer thinks that a parallel full scan will be cheaper than a index range scan.  In these cases you will see lots of direct path reads.

When Oracle performs a parallel full-table scan, the database blocks are read directly into the program global area (PGA), bypassing the data buffer RAM:

Direct path reads are parallel full-table scans

The direct path read wait occurs during Direct Path operations when the data is asynchronously read directly from the database files into the PGA, bypassing the SGA.

At some stage the session needs to make sure that all outstanding asynchronous I/O have been completed to disk. This can also happen if during a direct read no more slots are available to store outstanding load requests (a load request could consist of multiple I/Os).

The popular Ion tool is the easiest way to analyze Oracle direct path reads and writes, and Ion allows you to spot hidden disk I/O performance trends.  Ion is our favorite Oracle tuning tool, and the only 3rd party tool that we use. 

---