Understanding the Oracle Direct Path Read Temp Wait Event

Dean Richards

Senior DBA

Confio Software

8/27/2013 1

About Direct Path Read Temp

• Closely related to direct path write temp • Direct path read temp waits occur when a session

reads buffers from disk directly into the PGA – If I/O subsystem doesn’t support asynchronous I/O

• Each wait corresponds to a physical read request

– If I/O subsystem supports asynchronous I/O: • 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

8/27/2013 2

About Direct Path Write Temp

• Direct path write temp (and direct path write) waits occur when a session writes buffers directly from the PGA

• The process waits on this event for the write call to complete • Operations that perform direct path writes include:

– When a sort goes to disk – Parallel DML operations – Direct-path INSERTs – Parallel create table as select – Some LOB operations

• If I/O subsystem supports asynchronous I/O – The number of waits is not always the same as the number of write

calls – The session will wait if it has processed the buffers in the PGA and

can’t continue work until and I/O request complete

8/27/2013 3

Diagnosing Direct Path Read Temp

• Check the v$session_wait view: SELECT p1 “file#”, p2 “block#”, p3 “class#”

FROM v$session_wait

WHERE event = ‘direct path read temp’;

where

– p1 represents the file_ID for the read call

– p2 represents the start block_ID for the read call

– p3 is the number of blocks in the read call

• Check 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

;

8/27/2013 4

Understand Causes of Direct Path Read Temp

• High disk sorts – When sorts are too large to fit in memory, they get sent to disk

• Parallel slaves – When parallel slaves are used for scanning data or parallel DML are used to

create and populate objects

• Direct path loads – When the direct path API is used to pass data to the load engine in the server

• Server process ahead of I/O – If the server process is processing buffers faster than the I/O system can return

THEM, this can indicate an overloaded I/O system

• Data warehouse sorts – These sorts may always go to disk, which can lead 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

8/27/2013 5

Tuning for Direct Path Read Temp

• Direct path read temp waits often occur:

– When the PGA can’t support the size of the sorts

• Direct path read, direct path write temp, direct path write waits can occur:

– Parallel operations, direct path inserts or overloaded I/O

8/27/2013 6

Check for High Disk Sorts

• 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

8/27/2013 7

If High Disk Sorts Confirmed…

• If high disk sorts are confirmed, use pga_aggregate_target: – Total work area cannot exceed 200 MB RAM

• This is 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.

– No task may use more than 200 megabytes for sorting or hash joins.

• Note that Oracle does not recommend changing hidden parameters, but the result can be beneficial for certain environments.

8/27/2013 8

Tune the Sorts

• Find the cause of the sorts:

– Find the session experiencing the waits; look at the SQL statement currently being run.

• Query V$TEMPSEG_USAGE to find the SQL statement generating the sort

• Query V$SESSTAT for the session to determine the size of the sort

• Is it possible to reduce the sorting by tuning the SQL statement?

8/27/2013 9

If Full Table Scans Are the Cause, Consider Disk Striping

• If tables are defined with a high degree of parallelism, the optimizer leans towards using full table scans with parallel slaves:

– Locate the object being loaded

– Consider using disk striping or Automatic Storage Management (ASM) which can stripe for you

8/27/2013 10

Optimize for Parallel DML

• Check the I/O distribution across the disks

• Make sure I/O is configured for the parallelism being used. V

• erify that the parameter DISK_ASYNCH_IO is set to TRUE.

8/27/2013 11

Learn More About Oracle Performance Tuning

• Go to confio.com/logicalread • Free performance tuning tips for Oracle,

including:

– Tips on tuning for specific wait types and response time analysis

– Indexing strategies that can improve performance

– Much more, from database professionals, industry experts and Oracle ACEs

8/27/2013 12

Finally, Confio Ignite Can Help, Too

• Confio Ignite can help:

– Identify queries with large amounts of waits and many other database issues

– Install in minutes

– Find issues in 5 clicks or less

Download Ignite trial at

www.confio.com/freetrial

8/27/2013 13