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:

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:

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:

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/178357/viewspace-896951/，如需转载，请注明出处，否则将追究法律责任。