direct path read和direct path write等待事件

direct path read等待事件

direct path read和direct path write （直接路径读/写）事件发生在绕过SGA缓冲区高速缓存，执行PGA的直接读或写时的等待。直接路径读写 表示排序不在内存中，而是在磁盘上，采用自动PGA调优，在很大程度上能解决此类问题。另外就是调优sql语句本身

#http://www.askmaclean.com/archives/direct-path-read.html

Sometimes related to sorting operations, check to see if occurring primarily in temp tablespaces. During Direct Path operations the data is asynchronously read from the database files. At some point 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). Direct path reads are generally used by Oracle when reading directly into PGA memory (as opposed to into the buffer cache). If asynchronous I/O is supported (and in use) then Oracle can submit I/O requests and continue processing. It can then pick up the results of the I/O request later and will wait on “direct path read” until the required I/O completes.

Solutions

1) Reading from temp tablespaces

If the database parameter workarea_size_policy = AUTO:

- Increase the database parameter ‘pga_aggregate_target’

Otherwise:

- Increase the database parameter ‘sort_area_size’.

Another possible solution is to cache the temp datafile at the O/S or Storage system level.

2) Reading application data

a. Ensure database parameter, DISK_ASYNC_IO is TRUE

b. Ensure no disks are I/O bound.

c. Ensure the OS asynchronous I/O is configured correctly.

direct path write等待事件

#http://www.askmaclean.com/archives/direct-path-write.html

Direct path writes allow a session to queue an I/O write request and continue processing while the OS handles the I/O. If the session needs to know if an outstanding write is complete then it waits on this wait event. This can happen because the session is out of free slots and just needs an empty buffer (it waits on the oldest I/O) or because it needs to ensure all writes are flushed.

During Direct Path operations, the data is asynchronously written to 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 write, no more slots are available to store outstanding load requests (a load request could consist of multiple I/Os).

Solutions

If the file indicates a temporary tablespace, then check for unexpected disk sort operations.

Ensure DISK_ASYNCH_IO is TRUE

Ensure the OS asynchronous I/O is configured correctly

Ensure no disks are I/O bound

Review the SQL statement to see if I/O can be reduced