direct path read temp wait events

Direct read temp wait event is similar to direct path read wait event except difference
that data which is read from disk into the PGA bypassing the SGA comes from the temp data
files instead of permanent data files. So sort segments, hashes and bit maps etc from
temp files are read into PGA

SELECT p1 “file#”, p2 “block#”, p3 “class#”
 FROM v$session
 WHERE event = ‘direct path read temp’;

Note: since temp file numbers are found by adding 200(default value of db_files or whatever the value of db_files has been set, related this P1 information to v$tempfile accordingly.


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;

ASH Query to find direct path read temp waits:

SELECT dba_objects.object_name,
dba_objects.object_type,
ash.event,
p1,p2,p3,
round(SUM(active_session_history.wait_time + active_session_history.time_waited) / 1000000, 2) ttl_wait_time
FROM v$active_session_history ash, dba_objects
WHERE sample_time BETWEEN sysdate-60/1440 and sysdate
AND active_session_history.current_obj# = dba_objects.object_id
AND event LIKE 'direct path read temp%'
GROUP BY dba_objects.object_name, dba_objects.object_type, ash.event,p1,p2,p3
ORDER BY 7 DESC;

How to tune:

1. Individual sessions are not alloted PGA greater than least (5% of PGA_AGGREGATE_TARGET, _PGA_MAX_SIZE)  So set  _pga_max_size to value higher than 200M to large value and increase
PGA_AGGREGATE_PARAMETER

If using shared servers then increase sort_area_size and hash_area_size

2. Query v$PGA_TARGET_ADVICE and see PGA_CACHE_HIT_PERCENTAGE should be 100% also PGA_OVERALLOCATION must be 0.

If you query V$PGASTAT you should PGA should not be overallocated than target.

3. use indexes for sorting

4. Aoid sort operations

5. try use UNION ALL instead UNION

6. try avoid use sort merge join by using hint NO_USE_MERGE