direct path read temp等待事件

Often related to sorting operations, check to see if occurring primarily in temp tablespaces.

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

Ensure database parameter, DISK_ASYNC_IO is TRUE

Ensure no disks are I/O bound.

Ensure the OS asynchronous I/O is configured correctly.

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.