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.

 

db_file_sequential_read

 

 

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

 733 views last month,  3 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply