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.
