Note:
This event signifies that the user process is reading buffers into the SGA buffer cache and is waiting for a physical I/O call to return. A db file scattered read issues a scatter-read to read the data into multiple discontinuous memory locations. A scattered read is usually a multiblock read. It can occur for a fast full scan (of an index) in addition to a full table scan.
The db file scattered read wait event identifies that a full table scan is occurring. When performing a full table scan into the buffer cache, the blocks read are read into memory locations that are not physically adjacent to each other. Such reads are called scattered read calls, because the blocks are scattered throughout memory. This is why the corresponding wait event is called ‘db file scattered read’. Multiblock (up to DB_FILE_MULTIBLOCK_READ_COUNT blocks) reads due to full table scans into the buffer cache show up as waits for ‘db file scattered read’.
DB_FILE_SCATTERED_READ COUNT ON DATABASE:
set pagesize 5000
set lines 185
set long 5000
col username for a15
col osuser for a15
col program for a20
col “LOGON_TIME” for a23
col status for a8
col machine for a15
col SQL_TEXT for a90
col EVENT for a50
col P1TEXT for a10
col P2TEXT for a10
col P3TEXT for a10
col p1 for 9999999999999
col p2 for 9999999999999
col p3 for 9999999999999
col “LAST_CALL_HRS” for 99999.999
col STATE for a12
select event,count(event) “DB_FILE_SCATTERED_READ_COUNT” from v$session_wait having count(event)>= 1 and event like ‘%scattered%’ group by event;
DB_FILE_SCATTERED_READ SESSIONS DETAIL:
col event for a25
select s.sid,username,osuser,program,machine,status,to_char(logon_time,’DD-MON-YYYY HH24:MI:SS’) “LOGON_TIME”,last_call_et/3600 “LAST_CALL_HRS”,sw.event from
v$session s,v$session_wait sw where s.sid=sw.sid and sw.event like ‘%scattered%’;
DB_FILE_SCATTERED_READ_WAIT_DETAIL:
select sid,EVENT,P1TEXT,P1,P2TEXT,P2,P3TEXT,P3,WAIT_TIME,SECONDS_IN_WAIT,STATE from v$session_wait where event like ‘%scattered%’;
SQL_TEXT OF DB_FILE_SCATTERED_READ SESSIONS:
select sw.sid,username,sql_text “SQL_TEXT”,sw.event from v$session s,v$session_wait sw,v$sqltext sq where s.sid=sw.sid and sq.address = s.sql_address and sw.event like ‘%scattered%’ order by sw.sid,piece;
USE THE BELOW SQL_FILE TO IDENTIFY THE SEGMENT:
set linesize 150
set pagesize 5000
col owner for a15
col segment_name for a30
SELECT owner,segment_name,segment_type FROM dba_extents WHERE file_id=&file AND &block_id BETWEEN block_id AND block_id + blocks -1 ;
