Oracle Database - “direct path read temp” and “direct path read” wait event
About
“direct path read temp” and “direct path read” event are wait events.
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 asynchronous I/Os is by the I/O subsystem:
- not supported, then each wait corresponds to a physical read request.
- supported, 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).
Check the following VSESSION_WAIT parameter columns:
- P1 - File_id for the read call
- P2 - Start block_id for the read call
- P3 - Number of blocks in the read call
Articles Related
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
The file_id shows if the reads are for an object in TEMP tablespace (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.
Sorts to Disk
Examine the SQL statement currently being run by the session experiencing waits to see what is causing the sorts. Query VTEMPSEG_USAGE to find the SQL statement that is generating the sort. Also query the statistics from VSESSTAT for the session to determine the size of the sort. See if it is possible to reduce the sorting by tuning the SQL statement. If WORKAREA_SIZE_POLICY is MANUAL, then consider increasing the SORT_AREA_SIZE for the system (if the sorts are not too big) or for individual processes. If WORKAREA_SIZE_POLICY is AUTO, then investigate whether to increase PGA_AGGREGATE_TARGET. See “PGA Memory Management”.
Full Table Scans
If tables are defined with a high degree of parallelism, then this could skew the optimizer to use full table scans with parallel slaves. Check the object being read into using the direct path reads. If the full table scans are a valid part of the workload, then ensure that the I/O subsystem is configured adequately for the degree of parallelism. Consider using disk striping through a material solution or software solutions such as Automatic Storage Management (ASM).
Hash Area Size
For query plans that call for a hash join, excessive I/O could result from having HASH_AREA_SIZE too small. If WORKAREA_SIZE_POLICY is MANUAL, then consider increasing the HASH_AREA_SIZE for the system or for individual processes. If WORKAREA_SIZE_POLICY is AUTO, then investigate whether to increase PGA_AGGREGATE_TARGET.