Oracle Wait Events – direct path read

A direct path read I/O operation reads data into the session's PGA instead of the SGA. Direct read I/Os may be performed in synchronous or asynchronous mode, depending on the platform and the value of the DISK_ASYNC_IO parameter. An Oracle session waits for a direct read I/O request to complete on the direct path read wait event. Direct I/O is normally used when reading from sort segments. Parallel query slaves also use direct I/O when scanning a table or index. As of Oracle 8.1.7, there is a separate direct read I/O wait event for LOB segments – direct path read (lob).

The number of waits and time waited for this event may be somewhat misleading. If the DISK_ASYNC_IO is FALSE or the platform does not support asynchronous I/O, then all direct path read I/Os are synchronous and each wait corresponds to a physical read request. If the platform is capable of asynchronous I/O and the DISK_ASYNC_IO is TRUE, then the session may issue multiple direct read I/O requests and continue to process the blocks that are already cached in the PGA. The session only registers a wait on the direct path read event when it cannot continue processing because some blocks have not been read from disk. Therefore, the number of waits does not equal the read requests, and the wait time is deceiving, as the clock does not start when the read requests were issued. Due to these behaviors, this event seldom shows up as the main bottleneck in the V$SYSTEM_EVENT and V$SESSION_EVENT views.

V$SESSION_WAIT Parameters:

• P1=The absolute file# 
   
• P2=The starting block# 
   
• P3=Number of blocks read

If P1 is not in DBA_DATA_FILES or V$DATAFILE, and it is greater than the DB_FILES INIT.ORA parameter, then it is a TEMPFILE.

Common Causes and Actions

SQL statements that perform large disk sort operations are the primary cause of waits for this event. When sorts are too large to fit in memory, Oracle breaks it into multiple runs and writes the overflow to disk. Functions and operations such as DISTINCT, GROUP BY, ORDER BY, UNION, SORT MERGE, and HASH JOIN demand sorting of data. The DBA should examine the SQL statement and take note of the type of sort that is being performed (SORT, HASH, DATA, INDEX, LOB_DATA, or LOB_INDEX). V$SORT_USAGE view provides the SQL hash value and sort type. (Note: This view has a bug – the USER column always shows the username of the user you are logged in as. This is because USER is a reserved word. Oracle 9i fixes this by offering both USER and USERNAME columns.) Depending on the type of sort, examine the relevant INIT.ORA parameters, make sure they are not too small, and tune the SQL statement appropriately. The goal is to minimize the number of sorts as a whole and more specifically disk sorts. Whenever possible use UNION ALL instead of UNION, and where applicable use HASH JOIN instead of SORT MERGE, and NESTED LOOPS instead of HASH JOIN. Also make sure the plan selects the right driving table. INIT.ORA parameters of interest are SORT_AREA_SIZE, HASH_AREA_SIZE, and in 9i, WORK_AREA_SIZE_POLICY, and PGA_AGGREGATE_TARGET.

When large disk sort operations are expected, the DBA should also monitor the I/O throughput from the OS using sar -d, iostat –dxn or an equivalent process to see if there are I/O bottlenecks on the device where the TEMP tablespace files live, and take appropriate actions to remove hot spots. If the platform supports asynchronous I/O, the DBA should take advantage of the benefits of non-blocking I/O by setting the DISK_ASYNC_IO to TRUE. The DB_FILE_DIRECT_IO_COUNT parameter must also be set according to the maximum physical I/O size (maxphys) for the platform.

SQL statements that use parallel query slaves to scan tables or indexes are another common contributor to this wait event. The parallel query slaves read data directly into their PGAs and I/O waits are a "fact of life". Notice in this case, it is the parallel query slave sessions that suffer the direct path read wait and not the parent session. The parent session normally waits on the PX Deq: Execute Reply event (8i and above). The parent session's statement hash value is not the same as the hash value of the child statements that the parallel query slaves execute. For the purpose of tuning, the DBA has to locate and extract the parent statement that spawns the query slaves and verify that this is the best way to access data.

Use this query to identify parallel query parent/child statements for versions below 8.1.5. Note: This query is not able to differentiate parallel query statements that are executed by multiple SYS users as they all share a common AUDSID.

select decode(ownerid,2147483644,'PARENT','CHILD') stmt_level,
       audsid,
       sid,
       serial#,
       username,
       osuser,
       process,
       sql_hash_value,
       sql_address
from   v$session
where  type <> 'BACKGROUND'
and    audsid in (select audsid
                  from   v$session
                  group by audsid
                  having count(*) > 1)
order by audsid, stmt_level desc, sid, username, osuser;

Use this query for Oracle 8.1.5 and above.

select decode(a.qcserial#, null, 'PARENT', 'CHILD') stmt_level,
       a.sid,
       a.serial#,
       b.username,
       b.osuser,
       b.sql_hash_value,
       b.sql_address,
       a.degree,
       a.req_degree
from   v$px_session a, v$session b
where  a.sid = b.sid
order by a.qcsid, stmt_level desc;

The direct read I/O is also used when reading LOBs that are stored as NOCACHE. This is another source for direct I/O waits. When LOBs are stored as CACHE, reads and writes go through the Buffer Cache and read latency shows up as db file sequential read.

Diagnosis

The direct path read event is not likely to show up as the main bottleneck in the V$SYSTEM_EVENT or V$SESSION_EVENT views for the reasons explained above. Since the TOTAL_WAITS and TIME_WAITED err on the low side, they cover up the real significance of direct I/O in a session, and DBAs tend to overlook this symptom.

In Oracle 8.1.6 and up, the DBA can identify live sessions that read the most number of blocks in direct mode using the query below.

select a.name, b.sid, b.value
from   v$statname a, v$sesstat b
where  a.statistic# = b.statistic#
and    b.value      > 0
and    a.name       = 'physical reads direct'
order by b.value;

Considering the direct path read event is often a byproduct of sort and parallel query operations, the DBA should also take a look at its companion events, such as the db file scattered read, PX Deq: Table Q Normal, PX Deq: Execute Reply, and PX Deq: Execution Msg. If the direct path read event coexists with the db file scattered read event within a session, this usually discloses the fact that there are SQL plans that call for SORT MERGE or HASH JOIN operations, or one or more sort functions. If the direct path read event coexists with the parallel query related events within a session, this usually indicates there are parallel scan operations. Whatever the case, the cure for the direct path read waits is to find and improve the SQL statement appropriately, either by reducing the number of sorts or by employing a more efficient way to access the data.

When tracing a session with the Oracle event 10046, search the trace file for the statement with the most direct path read waits. Examine the P1 values. If most of the P1s point to the TEMP tablespace, this shows the statement performed disk sort operations. In this case, the goal of tuning is to reduce the number of sorts. If most of the P1s point to a tablespace other than the TEMP tablespace, this usually indicates parallel query operations. The object name can be obtained by querying the DBA_EXTENTS view using the P1 and P2 values. In this case, the DBA should verify that parallel query scans is the best way to access data from the object and there are no hot disks.

If it is not operationally possible to trace a session with the event 10046 (perhaps due to space constraints), the DBA may install a third party tool or develop a process that monitors the V$SESSION_WAIT view for the direct path read event at a fixed interval. Each time this event occurs, note the P1 and P2 values, as well as the SQL hash value from the V$SESSION view based on the SID. If P1 points to the TEMP tablespace, find out what kind of sorts are taking place from the X$KTSSO table (i.e. SORT, HASH, DATA, etc. X$KTSSO is the base table for the V$SORT_USAGE view.) If P1 is not a TEMP tablespace, then translate the P1 and P2 to the object name.

Use this query to get the SQL hash value and other user information when there is a direct path read wait:

select a.username,
       a.osuser,
       to_char(a.logon_time,'MMDD/HH24MISS') as logon_time,
       a.sid,
       to_char(sysdate,'MMDD/HH24MI') as sample_time,
       b.event,
       a.sql_hash_value
from   v$session a, v$session_wait b
where  a.sid   = b.sid
and    b.event = 'direct path read';

Use this query to determine the type of sort that is taking place (if any).

select b.sid,
       b.serial#,
       b.username,
       b.osuser,
       decode(a.ktssosegt,
              1,'SORT', 2,'HASH',    3,'DATA',
              4,'INDEX',5,'LOB_DATA',6,'LOB_INDEX',
              'UNDEFINED') as sort_type
from   x$ktsso a, v$session b
where  a.inst_id  = userenv('instance')
and    a.ktssoses = b.saddr
and    a.ktssosno = b.serial#
and    b.sid in (select sid
                 from   v$session_wait
                 where  event = 'direct path read');

Use this query to get the name of the object that is being scanned by parallel query slaves (if P1 is not a TEMPFILE).

select segment_name, partition_name, segment_type, tablespace_name
from   dba_extents a, v$session_wait b
where  b.p2 between a.block_id and (a.block_id + a.blocks - 1)
and    a.file_id  = b.p1
and    b.event    = 'direct path read';

Use this query to translate P1 into tablespace and file name (Oracle 8i and above).

select a.tablespace_name, a.file_name from dba_temp_files a, v$parameter b, v$session_wait c where a.file_id + b.value = c.p1 and b.name = 'db_files' and c.event = 'direct path read' union all select a.tablespace_name, a.file_name from dba_data_files a, v$session_wait b where a.file_id = b.p1 and b.event = 'direct path read';

Related INIT.ORA parameter

DB_FILE_DIRECT_IO_COUNT influences the direct path read/write I/O buffer size. The I/O buffer size is a product of this parameter and the DB_BLOCK_SIZE, and is limited by the hardware maximum physical I/O size. The default value on most platforms is 64, and a higher value can result in a larger PGA.

During Direct Path operations the data is asynchronously read from the database files. At some stage the session needs to make sure that all outstanding asynchronous I/O have been completed to disk. This can also happen if during a direct read no more slots are available to store outstanding load requests (a load request could consist of multiple I/Os).

Wait Time:

10 seconds. The session will be posted by the completing asynchronous I/O. It will never wait the entire 10 seconds. The session waits in a tight loop until all outstanding I/Os have completed.