When writing posts on performance tuning I felt that there needs to be a my-version of description of Oracle Wait Events along with Access method and Process & Memory Architecture. Thus this series is to start the Oracle Wait events which are common in any Database Environment BUT has a lot of story.

Direct Path Read/Direct Path Read Temp – wait event

When a session reads buffers from disk directly into the PGA (opposed to SGA’s buffer cache), the wait is on this wait event.

If the I/O subsystem doesn’t support asynchronous I/Os, then each wait corresponds to a physical read request.

If the I/O subsystem supports asynchronous I/O, then the process overlaps read requests with processing the blocks that are already in the PGA. When the process attempts to access a block in the PGA that has not yet been read from disk, it then issues a wait call and updates the statistics for this event. Hence, the number of waits is not always the same as the number of read requests.

Causes for the direct path read/direct path read temp – wait event

• High disk sorts – If the sorts are too large to fit in memory and get sent to disk, this wait can occur.
• Parallel slaves – Parallel slaves are used for scanning data or parallel DML may be used to create and populate objects. These may lead to direct path read wait and direct path write wait respectively.
• Direct path loads – The direct path API is used to pass data to the load engine in the server and can cause the related direct path write wait.
• Server process ahead of I/O – The server process is processing buffers faster than the I/O system can return the buffers. This can indicate an overloaded I/O system.
• Data Warehouse – Sorts in a data warehouse environment may always go to disk leading to high waits on direct path read temp and/or direct path write temp.