Dear Readers,
In this article, we will see Direct Path ReadWait Event.
Direct Path Read Wait event:
The Direct Path Read Temp wait events are an access path in which multiple Oracle blocks are read directly to the Oracle process memory without being read into the buffer cache in the Shared Global Area (SGA). in Direct Path Read Temp the data is read from temporary tablespaces. This event is usually caused by a sort operation that cannot be completed in memory and requires storage access. It is common to encounter the Direct Path Read Temp event during parallel query execution.
Similarly, Direct Path Write Temp wait events are an access path in which multiple Oracle blocks are written directly to the temporary files by the shadow Oracle process.
Reason :
Direct path read temp waits occur when a session reads buffers from disk directly into the PGA.
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 hasn’t been read from disk, it issues a wait call and updates the statistics • The number of waits is not always the same as the number of read requests
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.
Solution:
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;
Check the following:
Sorts to Disk
To check for high disk sorts:
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
Full Table Scans
If tables are defined with a high degree of parallelism, the optimizer leans towards using full table scans with parallel slaves:
Hash Area Size
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.
Thank you for giving your valuable time to read the above information.
Follow us on
Website : www.ktexperts.com
Facebook Page : KTexperts
Linkedin Page : KT EXPERTS
Follow Me On
Linkedin :Rakesh Monga
Loading...
