DB File Sequential Read Wait event in Oracle

I will explain DB File Sequential Read Wait event in Oracle in this post.

 

 

DB File Sequential Read in Oracle

DB File Sequential Read Wait event shows a wait for a foreground process while doing a sequential read from the database. The I/O is generally issued as a single I/O request to the OS; the wait blocks until the I/O request completes.

This event occurs when a user tries to perform a Physical I/O while waiting for sequential reads from the Buffer cache. This type of situation usually occurs when the data on the table is accessed by using index, not full table scan, as a result of single block reading.

 

 

 

If this event occurs,  possible reasons are wrong index usage, index fragmentation, excessive I/O traffic on specific disks. To Solve this problem, Query should use Right index and fragmented indexes should be defragmented with Rebuild Index operation.

 

When you encounter this wait event, which appears very frequently in AWR and ADDM reports, we cannot always say that there is a problem. However, if this wait event takes place, if the database have ‘Enqueue’ and Latch Free and they are spending too much time, then database should be monitored

 

 

 

 

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 = 'db file sequential 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 Wait events in Oracle.

Oracle Wait Events and Their Solutions in Oracle Database

 

 

 

 

Do you want to learn Oracle Database Performance Tuning detailed, then Click this link.

Performance Tuning and SQL Tuning Tutorial in the Oracle Database