direct path read and direct path read temp
When a session is reading buffers from disk directly into the PGA (opposed to the buffer cache in SGA), it waits on this event. If the I/O subsystem does not support asynchronous I/Os, then each wait corresponds to a physical read request.
If the I/O subsystem supports asynchronous I/O, then the process is able to overlap issuing read requests with processing the blocks already existing in the PGA. When the process attempts to access a block in the PGA that has not yet been read from disk, it then issues a wait call and updates the statistics for this event. Hence, the number of waits is not necessarily the same as the number of read requests (unlike db file scattered read and db file sequential read).
Causes
This happens in the following situations:
•The sorts are too large to fit in memory and some of the sort data is written out directly to disk. This data is later read back in, using direct reads.
•Parallel slaves are used for scanning data.
•The server process is processing buffers faster than the I/O system can return the buffers. This can indicate an overloaded I/O system.
Actions
This wait event can be because of large sorts to disk or full table scans by parallel slaves. This is the biggest wait for large data warehouse sites. However, if the workload is not a DSS workload, then examine why this is happening.
In the above scenario we identified a query using parallel slaves performing multiple full scans on couple of tables having 20 million records each. If tables are defined with a high degree of parallelism, then this could skew the optimizer to use full table scans with parallel slaves and result into direct path read wait event on the top.
No comments:
Post a Comment