direct path write

The direct path write wait event is related to direct write operations.

1. Ascertain the name of the object the session is writing to using the P1 and P2 values supplied by 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 it writes to. If the file is a TEMPFILE, then you know the SQL statement that the session is executing is creating temporary segments. But if it is a data file, then the session is performing a direct path load operation. 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 writing to the temporary tablespace, find out what type of segment it is. If the segment is SORT, then you know the sort batch is larger than the SORT_AREA_SIZE (or work area size) in memory and sort runs are being written to the temporary tablespace. 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 the HASH_AREA_SIZE is too small for the SQL statement that performs the hash-join operation. It is also possible for a SQL statement to create both SORT and HASH segments in the temporary tablespace. This is common when the execution plan contains both the hash and merge join, or a hash join with an aggregate function. The following query reveals the type of segment that the session is writing to 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 write event using the hash value supplied by the CURRENT_EVENT cursor:

6.           select hash_value, address, piece, sql_text

from   v$sqltext 

where  hash_value = 

order by piece;