About the direct path read temp wait event
When a session reads buffers from disk directly into the PGA, the wait is on direct path read temp. This is closely related to the direct path read wait. If the I/O subsystem doesn’t 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 overlaps read requests with processing the blocks already in the PGA. When the process attempts to access a block in the PGA that has not yet been read from disk, it issues a wait call and updates the statistics for this event. So, the number of waits is not always the same as the number of read requests.
Similarly, the direct path write temp wait event and direct path write wait event can occur when a process is writing buffers directly from PGA. The process will wait on this event for the write call to complete. Operations that could perform direct path writes include when a sort goes to disk, parallel DML operations, direct-path INSERTs, parallel create table as select, and some LOB operations. Like direct path reads, the number of waits is not the same as number of write calls issued if the I/O subsystem supports asynchronous writes. The session will wait if it has processed the buffers in the PGA and can’t continue work until an I/O request completes.
Diagnosing the direct path read temp wait event
When a session is waiting on this event, an entry will be seen in the v$session_wait system view giving more information on the blocks being waited for:SELECT p1 “file#”, p2 “block#”, p3 “class#” FROM v$session_wait WHERE event = ‘direct path read temp’;
In this case p1 represents the file_ID for the read call; p2 represents the start block_ID for the read call; while p3 is the number of blocks in the read call. Check the file_ID to see if it is for temp.SELECT relative_fno, owner, segment_name, segment_type FROM dba_extents WHERE file_id = &file AND &block BETWEEN block_id AND block_id + &blocks – 1;
Causes for the direct path read temp wait event To reduce the direct path read wait event and direct path read temp wait event:
- High disk sorts – If the sorts are too large to fit in memory and get sent to disk, this wait can occur.
- Parallel slaves – Parallel slaves are used for scanning data or parallel DML may be used to create and populate objects. These may lead to direct path read wait and direct path write wait respectively.
- Direct path loads – The direct path API is used to pass data to the load engine in the server and can cause the related direct path write wait.
- Server process ahead of I/O – The server process is processing buffers faster than the I/O system can return the buffers. This can indicate an overloaded I/O system
- Data Warehouse – Sorts in a data warehouse environment may always go to disk leading to high waits on direct path read temp and/or direct path write temp.
Hash area size – For query plans that call for a hash join, excessive I/O could result from having HASH_AREA_SIZE too small.
Tune away the direct path read temp
High disk sorts
The disk sort distribution can be checked:select a.instance_number,to_char(a.snap_time,’dd/mon/yyyy hh24:mi’) meas_date, b.value from stats$snapshot a, stats$sysstat b, v$statname c where c.name=’sorts (disk)’ and a.snap_time>sysdate-7 and c.statistic#=b.statistic# and b.snap_id=a.snap_id order by a.instance_number,a.snap_time
It is recommended to use pga_aggregate_target. This area is used both for sorts and the hash join area. If possible the PGA can be sized larger to fit more in-memory sorts reducing the disk sorts. It is important to remember that there are limitations of pga_aggregate_target: The total work area cannot exceed 200 megabytes of RAM because of the default setting for the hidden parameter_pga_max_size. No RAM sort may use more than 5% of pga_aggregate_target or _pga_max_size, whichever is smaller. This means that no task may use more than 200 megabytes for sorting or hash joins. Oracle does not recommend changing the hidden parameters, but for certain environments, the result can be beneficial.
To view the PGA and its distribution.Select * from v$pgastat; NAME VALUE —————————————- ———- aggregate PGA target parameter 419430400 aggregate PGA auto target 343517184 global memory bound 20971520 total PGA inuse 37789696 total PGA allocated 42677248 maximum PGA allocated 53294080 total freeable PGA memory 0 PGA memory freed back to OS 0 total PGA used for auto workareas 0 maximum PGA used for auto workareas 16384 total PGA used for manual workareas 0 maximum PGA used for manual workareas 0 over allocation count 0 bytes processed 26643456 extra bytes read/written 0 cache hit percentage 100
Tune the sorts
Find the session experiencing the waits and look at the SQL statement currently being run to see the cause of the sorts. Query V$TEMPSEG_USAGE to find the SQL statement generating the sort. Also query the statistics from V$SESSTAT for the session to determine the size of the sort. Determine whether it is possible to reduce the sorting by tuning the SQL statement.
Full table scans
If tables are defined with a high degree of parallelism, the optimizer leans towards using full table scans with parallel slaves. For a direct path read, locate the object being loaded. Consider using disk striping or Automatic Storage Management (ASM) which can stripe for you.
Parallel DML
Check the I/O distribution across the disks and make sure your I/O is configured for the parallelism being used. Verify that the parameter DISK_ASYNCH_IO is set to true.
Conclusion
The direct path read temp wait event is most often encountered when the PGA is not able to support the size of the sorts. The closely related wait events of direct path read, direct path write temp, and direct path write can occur due to parallel operations, direct path inserts and overloaded I/O. But tuning the PGA, the I/O placement, and SQL tuning can reduce or eliminate this wait.