I will explain Direct path read Wait event in Oracle in this post.
Direct Path Read in Oracle
This event occurs when Oracle Instance query data from the Datafiles asynchronously and puts this data into PGA instead of Buffer Cache in SGA.
The session is waiting for a direct read to complete. A direct read is a physical I/O from a data file that bypasses the buffer cache and reads the data block directly into process-private memory.
If asynchronous I/O is supported (and in use), then Oracle can submit I/O requests and continue processing. Oracle can then pick up the results of the I/O request later and wait on “direct path read” until the required I/O completes.
If asynchronous I/O is not being used, then the I/O requests block until completed but these do not show as waits at the time the I/O is issued. The session returns later to pick up the completed I/O data but can then show a wait on “direct path read” even though this wait will return immediately.
This type of event usually occurs during the use of Temporary ( Temp ) Tablespace in the Sorting operations, during the creation of Lob segments, and when multiple sessions Full table scan in parallel.
In order to solve this problem, the memory should be increased, parallel operations should not be done unless required, and pay attention to Lob segments reads.
In DSS type systems, or during heavy batch periods, waits on “direct path read” are normal. However, if the waits are significant on an OLTP style system, there may be a problem.
You can:
- Examine the V$SESSION_EVENT view to identify sessions with high numbers of waits
- Examine the V$SESSTAT view to identify sessions with high “physical reads direct” (statistic only present in newer Oracle releases)
- Examine the V$FILESTAT view to see where the I/O is occurring
- Examine the V$SQLAREA view for statements with SORTS and high DISK_READS (which may or may not be due to direct reads)
- Determine whether the file indicates a temporary tablespace check for unexpected disk sort operations.
- Ensure that the DISK_ASYNCH_IO parameter is set to TRUE. This is unlikely to reduce wait times from the wait event timings but may reduce sessions elapsed times (as synchronous direct I/O is not accounted for in wait event timings).
- Ensure the OS asynchronous I/O is configured correctly.
- Check for I/O heavy sessions and SQL and see if the amount of I/O can be reduced.
- Ensure no disks are I/O bound.
You can find the sql statements that are waiting on db file sequential read wait event using the following script.
select v.sql_text,v.sql_fulltext,sub.* from gv$sql v,
(select sample_time,s.sql_id sql_id, session_state, blocking_session,
owner||'.'||object_name||':'||nvl(subobject_name,'-') obj_name,s.program,s.module,s.machine
from dba_hist_active_sess_history s, dba_objects o
where sample_time between
to_date('02/08/2021 07:30:02','DD/MM/YYYY HH24:MI:SS')
and
to_date('02/08/2021 15:10:02','DD/MM/YYYY HH24:MI:SS')
and event = 'direct path read'
and o.data_object_id = s.current_obj#
order by 1 desc) sub where sub.sql_id=v.sql_id;
Read the following post to learn more details about DB File Sequential and Scattered Read Wait event in Oracle.
Do you want to learn Oracle Database Performance Tuning detailed, then Click this link.
Performance Tuning and SQL Tuning Tutorial in the Oracle Database