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.
本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1277912
