direct path read

The direct path read wait event is related to direct read operations that read data into the session’s PGA.

1. Find out the name of the object the session is reading from using the P1 and P2 values supplied the CURRENT_EVENT cursor:

select segment_name, partition_name

from   dba_extents

where   between block_id and (block_id + blocks - 1)

and    file_id    = ;

2. You can get a rough idea of what the session is doing from the type of database file from which it reads. If the file is a TEMPFILE, then you know the session is reading temporary segments that it previously created through direct path write operations. However, if it is a data file, it is probably the parallel query slave at work. The following query determines the database file name using the P1 value supplied by the CURRENT_EVENT cursor:

select name

from   v$datafile

where  file# = 

union all

select a.name

from   v$tempfile a, v$parameter b

where  b.name = 'db_files'

and    a.file# + b.value = ;

3. If the session is reading from the temporary tablespace, find out what type of segment it is as this can also give you an idea of what the session is doing. For example, if the segment is SORT, then you know the sort batch is larger than your SORT_AREA_SIZE (or work area size), and you may have a SQL statement that employs one or more aggregate functions or the merge-join operation. If the segment is HASH, then you know your HASH_AREA_SIZE is too small for the SQL statement that employs the hash-join operation. However, this doesn’t mean you simply increase the sort and hash memory. You should first optimize the SQL statements. The following query reveals the type of segment that the session is reading using the SADDR (session address) and SERIAL# supplied by the CURRENT_EVENT cursor:

4.           select distinct decode(ktssosegt, 1,'SORT', 2,'HASH', 

3,'DATA', 4,'INDEX', 5,'LOB_DATA', 6,'LOB_INDEX', 'UNDEFINED')

from   sys.x$ktsso

where  inst_id  = userenv('instance')

and    ktssoses = 

and    ktssosno = ;

5. Obtain the SQL statement that is associated with the direct path read event using the hash value supplied by the CURRENT_EVENT cursor:

select hash_value, address, piece, sql_text

from   v$sqltext 

where  hash_value = 

order by piece;