DB File Scattered Read Wait event in Oracle

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

 

DB File Scattered Read in Oracle

This wait event occurs getting multiblock of physical blocks that are not physically close to each other (neighbors) into buffer cache Scattered, or during a full scan to the buffer cache. So Db file scattered read is to read multiple blocks I/O during the fast full scan.

 

 

Small tables can be cached to avoid this wait event.

 

You can read the following post to learn DB File Sequential Read Wait event.

DB File Sequential Read Wait event in Oracle

 

 

 

 

DB File Scattered Read Wait event is the same type of event as “db file sequential read”, except that Oracle will read multiple data blocks. Multi-block reads are typically used on full table scans. The name “scattered read” refers to the fact that multiple blocks are read into database block buffers that are ‘scattered’ throughout memory.

 

This statement can be used to see which sessions may be worth tracing:

 

SELECT sid, total_waits, time_waited
  FROM v$session_event
 WHERE event='db file scattered read'
  and total_waits>0
 ORDER BY 3,2;


 

You can find the sql statements that are waiting on DB file Scattered 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 scattered 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

 

 

 1,025 views last month,  1 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