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