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.
733 views last month, 3 views today