direct path read

The direct path read wait event has three parameters: file#, first block#, and block count. In Oracle Database 10 g , this wait event falls under the User I/O wait class. Keep the following key thoughts in mind when dealing with the direct path read wait event.

These are waits that are associated with direct read operations. An Oracle direct read operation reads data directly into the session ‚ s PGA (Program Global Area), bypassing the SGA. The data in the PGA is not shared with other sessions.
Direct reads may be performed in synchronous or asynchronous mode, depending on the platform and the value of the DISK_ASYNC_IO parameter. The systemwide direct path read wait event statistics can be very misleading when asynchronous I/O is used.
A significant number of direct path read waits is most likely an application issue.

Common Causes, Diagnosis, and Actions

The direct path read waits are driven by SQL statements that perform direct read operations from the temporary or regular tablespaces.

SQL statements with functions that require sorts, such as ORDER BY, GROUP BY, UNION, DISTINCT, and ROLLUP, write sort runs to the temporary tablespace when the input size is larger than the work area in the PGA. The sort runs in the temporary tablespace are subsequently read and merged to provide the final result. An Oracle session waits on the direct path read wait event while the sort runs are being read. SQL statements that employ the merge join, either through a hint or as decided by the optimizer, also require sort.

SQL statements that employ the hash join, either as directed by a hint or the optimizer, flush the hash partitions that don ‚ t fit in memory to the temporary tablespace. The hash partitions in the temporary tablespace are read back into the memory to be probed for rows that match the SQL predicates. An Oracle session waits on the direct path read wait event while the hash partitions are being read.

SQL statements that employ parallel scans also contribute to the systemwide direct path read waits. In a parallel execution, the direct path read waits are associated with the query slaves and not the parent query. The session that runs the parent query waits mostly on the PX Deq: Execute Reply wait event (in Oracle8 i Database and above).

Note ‚

As of Oracle 8.1.7, there is a separate direct read wait event for LOB segments: direct path read (lob). This wait event applies to LOBs that are stored as NOCACHE. When LOBs are stored as CACHE, reads and writes go through the buffer cache and waits show up as db file sequential read .

Session-Level Diagnosis

It is highly unlikely that the direct path read wait event will show up as the leading bottleneck within a session even though it may actually be it. The reasons are as follows :

The way Oracle accounts for the waits, as discussed in Chapter 3.
Sessions that perform parallel processing using parallel query do not wait on the direct path read wait event, which normally represents the bulk of the time. The direct path read waits are associated with the query slaves ‚ sessions that scan the tables. The only time the direct path read wait event shows up within a parent session is when the parent session itself has read activities against the temporary tablespace. This is driven by SQL functions, such as ORDER BY, GROUP BY, and DISTINCT, or hash partitions that spill to disk but not parallel scans.

Therefore you shouldn ‚ t evaluate direct path read waits based on the TOTAL_WAITS or TIME_WAITED in the V$SESSION_EVENT view. Instead, you can find current sessions that perform a lot of direct read operations from the V$SESSTAT view using the following query. The physical reads direct is comprised of the direct reads that are originated by the parent session itself as well as the sum of all direct reads that are originated by the query slaves that the parent session employs. The direct reads that are initiated by query slaves are only reflected in the parent session when the slaves complete their jobs. The downside to this approach is that there is no time element.

 select a.name, b.sid, b.value, 
 round((sysdate - c.logon_time) * 24) hours_connected 
 from   v$statname a, v$sesstat b, v$session c 
 where  b.sid        = c.sid 
 and    a.statistic# = b.statistic# 
 and    b.value      > 0 
 and    a.name       = 'physical reads direct' 
 order by b.value; 
 
 NAME                       SID      VALUE HOURS_CONNECTED 
 ------------------------- ---- ---------- --------------- 
 physical reads direct        2         41             980 
 physical reads direct        4         41             980 
 physical reads direct        5     445186             980

Apart from finding the sessions that perform a lot of direct reads, you must also find out where the sessions are reading from (temporary tablespace or data files), the SQL statements that initiate the waits, and the type of segments being read. The following query gives you the answers. Sessions that read from the temporary tablespace may be reading sort or hash segments. Sessions that read from data files normally belong to parallel query slaves.

 select a.event, 
 a.sid, 
 c.sql_hash_value hash_value, 
 decode(d.ktssosegt, 
 1,'SORT', 2,'HASH',    3,'DATA', 
 4,'INDEX',5,'LOB_DATA',6,'LOB_INDEX', 
 null) as segment_type, 
 b.tablespace_name, 
 b.file_name 
 from   v$session_wait a, dba_data_files b, v$session c, x$ktsso d 
 where  c.saddr      = d.ktssoses(+) 
 and    c.serial#    = d.ktssosno(+) 
 and    d.inst_id(+) = userenv('instance') 
 and    a.sid        = c.sid 
 and    a.p1         = b.file_id 
 and    a.event      = 'direct path read' 
 union all 
 select a.event, 
 a.sid, 
 d.sql_hash_value hash_value, 
 decode(e.ktssosegt, 
 1,'SORT', 2,'HASH',    3,'DATA', 
 4,'INDEX',5,'LOB_DATA',6,'LOB_INDEX', 
 null) as segment_type, 
 b.tablespace_name, 
 b.file_name 
 from   v$session_wait a, dba_temp_files b, v$parameter c, 
 v$session d, x$ktsso e 
 where  d.saddr      = e.ktssoses(+) 
 and    d.serial#    = e.ktssosno(+) 
 and    e.inst_id(+) = userenv('instance') 
 and    a.sid        = d.sid 
 and    b.file_id    = a.p1 - c.value 
 and    c.name       = 'db_files' 
 and    a.event      = 'direct path read' 
 order by 1,2; 
 
 -- File name output is edited to fit page. 
 EVENT              SID HASH_VALUE SEGMENT TABLESPACE_N FILE_NAME 
 ------------------ --- ---------- ------- ------------ ----------------- 
 direct path read     8  511952958 SORT    TEMP_BATCH   temp_batch_01.dbf 
 direct path read     9 3138787393         ORDERS       orders_01.dbf 
 direct path read    11 3138787393         ORDERS       orders_01.dbf 
 direct path read    12 3138787393         ORDERS       orders_01.dbf 
 direct path read    14 3138787393         ORDERS       orders_01.dbf

If you catch a session reading sort segments from the temporary tablespace, this indicates the SORT_AREA_SIZE (or work area size if you use the PGA_AGGREGATE_TARGET in Oracle9 i Database) is not large enough to accommodate a cache sort (or in memory sort). This is fine. It is unrealistic to expect all SQL statements to perform cache sorts. However, you should avoid multipass sorts because they create a lot of I/Os to the temporary tablespace and are very slow. How can you tell if a SQL statement is doing a multipass sort? Well, it is not that easy in versions prior to Oracle9 i Database. You have to trace the session with the event 10032 and examine the trace file. However, beginning in Oracle9 i Database, you can simply query the V$SQL_WORKAREA or V$SQL_WORKAREA_ACTIVE views with the SQL hash value that performs the sort. For a more in-depth discussion on sort, please review the ‚“If Your Memory Serves You Right ‚½ white paper from the International Oracle Users Group (IOUG) 2004 conference proceedings at www.ioug.org.

The goal of tuning in this case is to minimize the number of sorts as a whole and, more specifically , disk sorts. Increasing the SORT_AREA_SIZE (or PGA_AGGREGATE_ TARGET) may help reduce the number of disk sorts, but that ‚ s usually the workaround rather than the cure, unless your SORT_AREA_SIZE is unreasonably small to begin with. You should examine the application and the SQL statements to see if sorts are really necessary. Applications have the tendency to abuse the DISTINCT and UNION functions. 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. Make sure the optimizer selects the right driving table. Check to see if the composite index ‚ s columns can be rearranged to match the ORDER BY clause to avoid sort entirely. Also, consider automating the SQL work areas using PGA_AGGREGATE_TARGET in Oracle9 i Database. Statistically, the automatic memory management delivers a higher percentage of cache sorts.

Note ‚

Be careful when switching from UNION to UNION ALL as this can produce different results depending on the data. The UNION operator returns only distinct rows that appear in either result, while the UNION ALL operator returns all rows.

Note ‚

By default, the HASH_AREA_SIZE is twice the SORT_AREA_SIZE. A larger HASH_AREA_SIZE will influence the optimizer toward the hash joins (full table scan) rather than nested loops operation.

Similarly, if you catch a session reading hash segments from the temporary tablespace, all that tells you is that the HASH_AREA_SIZE (or work area size in case of the PGA_AGGREGATE_TARGET in Oracle9 i Database) is not big enough to accommodate the hash table in memory. The solution is similar to the one just mentioned: cure it from the application and SQL tuning before adjusting the HASH_AREA_SIZE (or PGA_AGGREGATE_TARGET). Unless, of course, your HASH_AREA_SIZE is too small to begin with.

If you discover that the direct reads belong to parallel query slaves, you should verify if parallel scans are appropriate for the parent SQL statement and that the degree of parallelism is right. Make sure the query slaves do not saturate your CPUs or disks. Identifying the parent SQL statement can be a bit tricky because the hash value of the parent statement is not the same as the hash value of child statements that the query slaves execute. It was even trickier before the V$PX_SESSION view was introduced in Oracle 8.1.5. Following are two examples, one for versions prior to 8.1.5 and the other for version 8.1.5 and greater, that can help you identify the parent SQL statements when parallel queries are involved:

 -- For versions prior to 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 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; 
 
 STMT_L   AUDSID  SID SERIAL# USERNAME OSUSER  PROCESS HASH_VALUE SQL_ADDR 
 ------ -------- ---- ------- -------- ------- ------- ---------- -------- 
 PARENT  3086501   20     779 INTREPID cdh8455 16537   3663187692 A0938E54 
 CHILD   3086501   12     841 INTREPID cdh8455 16544    817802256 A092E1CC 
 CHILD   3086501   14    2241 INTREPID cdh8455 16546    817802256 A092E1CC 
 CHILD   3086501   17    3617 INTREPID cdh8455 16540    817802256 A092E1CC 
 CHILD   3086501   21     370 INTREPID cdh8455 16542    817802256 A092E1CC

The following query applies to version 8.1.5 and higher.

 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; 
 
 STMT_L SID SERIAL# USERNAME OSUSER  HASH_VALUE SQL_ADDR DEG REQ_DEG 
 ------ --- ------- -------- ------- ---------- -------- --- ------- 
 PARENT  20     779 INTREPID cdh8455 3663187692 A0938E54 
 CHILD   17    3617 INTREPID cdh8455  817802256 A092E1CC   4       4 
 CHILD   21     370 INTREPID cdh8455  817802256 A092E1CC   4       4 
 CHILD   12     841 INTREPID cdh8455  817802256 A092E1CC   4       4 
 CHILD   14    2241 INTREPID cdh8455  817802256 A092E1CC   4       4

Initialization Parameters of Interest

The DB_FILE_DIRECT_IO_COUNT initialization parameter can impact the direct path read performance. It sets the maximum I/O buffer size for direct reads and writes operations. Up to Oracle8 i Database, the default value on most platforms is 64 blocks. So if the DB_BLOCK_SIZE is 8K, the maximum I/O buffer size for direct reads and writes operations is 512K. This number is further subject to hardware limits.

The DB_FILE_DIRECT_IO_COUNT parameter is hidden in Oracle9 i Database, and the value is expressed in bytes instead of blocks. The default value in Oracle9 i Database is 1MB. The actual direct I/O size depends on your hardware configuration and limits.

You can discover the actual direct read I/O size in three ways:

Trace the Oracle session that performs direct reads operations using the trace event 10046 at level 8. The P3 parameter indicates the number of blocks read. Based on the following example, the direct path read I/O size is 64K since the block size is 8K. Alternatively, you can query the V$SESSION_WAIT view for the P3 value of the direct path read event.

 WAIT #1: nam='direct path read' ela= 4 p1=4 p2=86919 p3=8 
 WAIT #1: nam='direct path read' ela= 5 p1=4 p2=86927 p3=8 
 WAIT #1: nam='direct path read' ela= 10 p1=4 p2=86935 p3=8 
 WAIT #1: nam='direct path read' ela= 39 p1=4 p2=86943 p3=8 
 WAIT #1: nam='direct path read' ela= 5 p1=4 p2=86951 p3=8 
 WAIT #1: nam='direct path read' ela= 38 p1=4 p2=86959 p3=8 
 . . .

Trace the Unix session that performs direct reads or writes operations using the operating system trace facility such as truss , tusc , trace , or strace. The snippet of the truss report from an Oracle9 i Database reveals the direct I/O size is 65536 bytes or 64K:

 9218/6:         kaio(AIONOTIFY, -14712832)                      = 0 
 9218/1:         kaio(AIOWAIT, 0xFFBECE98)                       = 1 
 9218/1:         lwp_cond_signal(0xFEB7BFA0)                     = 0 
 9218/3:         pread64(256, "0602  9218/6: kaio(AIONOTIFY, -14712832) = 0 
 9218/1: kaio(AIOWAIT, 0xFFBECE98) = 1 
 9218/1: lwp_cond_signal(0xFEB7BFA0) = 0 
 9218/3: pread64(256, "0602\0\001\0 ~13C19AEE }".., 65536, 0x0FC26000) =  65536  
 9218/1: lwp_cond_signal(0xFEB69FA0) = 0 
 9218/4: pread64(256, "0602\0\001\0 ~1BC19AEFE7".., 65536, 0x0FC36000) =  65536  
  . . .  
  9218/6: kaio(AIONOTIFY, -14712832) = 0 
 9218/1: kaio(AIOWAIT, 0xFFBECE98) = 1 
 9218/1: lwp_cond_signal(0xFEB7BFA0) = 0 
 9218/3: pread64(256, "0602\0\001\0 ~13C19AEE }".., 65536, 0x0FC26000) =  65536  
 9218/1: lwp_cond_signal(0xFEB69FA0) = 0 
 9218/4: pread64(256, "0602\0\001\0 ~1BC19AEFE7".., 65536, 0x0FC36000) =  65536  
  . . .  
 1  9218/6: kaio(AIONOTIFY, -14712832) = 0 
 9218/1: kaio(AIOWAIT, 0xFFBECE98) = 1 
 9218/1: lwp_cond_signal(0xFEB7BFA0) = 0 
 9218/3: pread64(256, "0602\0\001\0 ~13C19AEE }".., 65536, 0x0FC26000) =  65536  
 9218/1: lwp_cond_signal(0xFEB69FA0) = 0 
 9218/4: pread64(256, "0602\0\001\0 ~1BC19AEFE7".., 65536, 0x0FC36000) =  65536  
  . . .  
 ~13C19AEE }".., 65536, 0x0FC26000) =  65536  
 9218/1:         lwp_cond_signal(0xFEB69FA0)                     = 0 
 9218/4:         pread64(256, "0602  9218/6: kaio(AIONOTIFY, -14712832) = 0 
 9218/1: kaio(AIOWAIT, 0xFFBECE98) = 1 
 9218/1: lwp_cond_signal(0xFEB7BFA0) = 0 
 9218/3: pread64(256, "0602\0\001\0 ~13C19AEE }".., 65536, 0x0FC26000) =  65536  
 9218/1: lwp_cond_signal(0xFEB69FA0) = 0 
 9218/4: pread64(256, "0602\0\001\0 ~1BC19AEFE7".., 65536, 0x0FC36000) =  65536  
  . . .  
  9218/6: kaio(AIONOTIFY, -14712832) = 0 
 9218/1: kaio(AIOWAIT, 0xFFBECE98) = 1 
 9218/1: lwp_cond_signal(0xFEB7BFA0) = 0 
 9218/3: pread64(256, "0602\0\001\0 ~13C19AEE }".., 65536, 0x0FC26000) =  65536  
 9218/1: lwp_cond_signal(0xFEB69FA0) = 0 
 9218/4: pread64(256, "0602\0\001\0 ~1BC19AEFE7".., 65536, 0x0FC36000) =  65536  
  . . .  
 1  9218/6: kaio(AIONOTIFY, -14712832) = 0 
 9218/1: kaio(AIOWAIT, 0xFFBECE98) = 1 
 9218/1: lwp_cond_signal(0xFEB7BFA0) = 0 
 9218/3: pread64(256, "0602\0\001\0 ~13C19AEE }".., 65536, 0x0FC26000) =  65536  
 9218/1: lwp_cond_signal(0xFEB69FA0) = 0 
 9218/4: pread64(256, "0602\0\001\0 ~1BC19AEFE7".., 65536, 0x0FC36000) =  65536  
  . . .  
 ~1BC19AEFE7".., 65536, 0x0FC36000) =  65536  
  . . .

Enable the debug information for the session that performs direct I/O operations using the trace event 10357 at level 1. Example: alter session set events '10357 trace name context forever, level 1'. The snippet of the trace file is provided here:

 Unix process pid: 4375, image: oracle@kccdeds73  (P000)  
 *** SESSION ID:(9.18) 2004-02-08 21:47:01.908 
 DBA Range Initialized: length is 1570, start dba is 0100602b 
 kcblin: lbs=fc86c1cc  flag=8  slot_cnt=32 slot_size=65536  state obj=24321224 
 kcblin: state objects are: Call=243a2210,Current Call=243a2210, Session=24321224 
 kdContigDbaDrCbk:starting from tsn 5 
 kdContigDbaDrCbk:starting from rdba 0100602b 
 kdContigDbaDrCbk:returning 1570 blocks 
 kcblrs:issuing read on slot : 0 
 kcbldio:lbs=fc86c1cc slt=fc86408c typ=0  async=1 afn=4  blk=602b  cnt=8  buf=fc87fe00 
 kcblrs:issuing read on slot : 1 
 kcbldio:lbs=fc86c1cc slt=fc864210 typ=0 async=1 afn=4 blk=6033 cnt=8 buf=fc89fe00 
 kcblcio: lbs=fc86c1cc slt=fc86408c type=0 afn=4 blk=602b cnt=8 buf=fc87fe00 
 . . .

In the preceding example, the trace file belongs to query slave #0 (P000). There are 32 I/O slots available for the direct read operation (slot_cnt=32). A slot is a unit of I/O, and each slot is 65536 bytes (slot_size=65536). Asynchronous I/O is enabled during the read operation (async=1). The query slave reads data file #4 (afn=4). The number of blocks read is 8 (cnt=8). Since the block size is 8K, this translates to 65536 bytes.

In this case, the direct I/O slot size prevents the process from achieving the full 1MB, which is the default limit of the _DB_FILE_DIRECT_IO_COUNT parameter. The slot size can be modified by event 10351. The number of slots can be modified by event 10353.

Caution ‚

The preceding information gives you a sense of the direct I/O throughput in your system. Don ‚ t simply change the default slot size or the number of direct I/O slots. You need to know your hardware limits before doing so. Besides, you should focus on optimizing the application and SQL statements first.

Lastly, in Oracle8 i Database, direct reads can also be enabled for serial scans with the _SERIAL_DIRECT_READ initialization parameter. Earlier releases may do the same by setting event 10355. Doing so will cause data from full scans to be read into the session ‚ s PGA and not shared with other sessions. This can sharply increase memory usage.