This is another post in the series where we would be sharing the brief about Access Paths chosen by the optimizer and the situations where it would prefer to choose that Access Path.
In Direct Path Read, the server process reads the data block from disk directly into process-private memory i.e. PGA bypassing the SGA.
In case of asynchronous I/O, Server process can submit the I/O requests and continue processing further and can then pick up the results of the I/O request later waiting on “direct path read” wait event until the required I/O completes. In cases where asynchronous I/O is not being used, then the I/O requests gets blocked until completed and is not reflected as a wait at the time the I/O is issued. Then the session returns later to pick up the completed I/O data and then shows as a wait on “direct path read” even though this wait will return immediately.
Following are the situation where Optimizer would choose Direct Path Reads:-
- Execution of a CREATE TABLE AS SELECT statement.
- Execution of an ALTER REBUILD or ALTER MOVE statement.
- Reads from a temporary tablespace.
- Parallel queries.
- Reads from a LOB segment.