db file scattered read - Oracle Wait Event

 Part:1

The db file scatter read 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 scattered read to read the data into multiple discontinuous memory locations of buffer cache.

A scattered read is usually a multi block read. It can occur for a fast full scan (of an index) in addition to a full table scan.

• Multi Block Read
•  Full Table Scan 
•  Index Fast Full Scan

Example

select * from emp;

algorythm
search buffer cache for block by rowid, 
if fail, read block off  disk via file# and block#  and # of blocks

Note: "scattered" means blocks are scattered throughout the buffer cache (even though they are laid out sequential on disk)

db_file_multiblock_read_count set the target block read size 

Part:2

Scattered reads and full-table scans

Full table scans are not necessarily a detriment to performance and is a fastest way to access the table rows. The CBO choices to perform FTS depends on OPQ, db_block_size and clustering_factor, estimated % of rows determined by the query and other factors. Once of CBO chooses to perform FTS, The speed of performing FTS (SOFTS) depends on internal and external factors.

1. The number of CPUs on the system
2. The setting for Oracle Parallel Query (parallel hints, alter table)
3. Table partitioning
4. The speed of the disk I/O subsystem

With all the above factors it may be impossible to find best setting for optimizer_index_cost_adj parameter. In the real world the decision to perform FTS depends on below factors.

1. Free blocks available in database buffer cache.
2. Free space in temp as if query having order by clause
3. Current demands on CPU.

Hence, it follows that the optimizer_index_cost_adj should change frequently, as the load changes on the server.

Part:3

db file scattered read

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 scattered read to read the data into multiple discontinuous memory locations of buffer cache.

A scattered read is usually a multi block 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 scan is occurring. 

When performing a full 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 scans into the buffer cache show up as waits for 'db file scattered read'

Part:4

DB file scattered read wait
DB file scattered read wait event identifies that full table scans or index fast full scans are occurring. DB file scattered read means to read the data into multiple discontinuous memory location. Scattered read is the blocks are scattered throughout memory.

Explanation
SQL Statement cause full table scan, then read with DB_FILE_MULTIBLOCK_READ_COUNT consecutive blocks at a time and scatters them into buffers in the buffer cache. It cause large no of blocks have to be read into the buffer cache,
Server process has to search for a large no. of free/usable blocks in buffer cache which leads to wait included in DB file scattered read wait.

Solution to Reduce the Wait Event:

1. Ensure the value of the DB_FILE_MULTIBLOCK_READ_COUNT parameter is correctly set.

DB_FILE_MULIBLOCK_READ_COUNT = [largest read size] / db_block_size

2. Placing tables in the keep buffer pool as appropriate to avoid aging out.
3. Use the Parameter optimizer_index_cost_adj.
4. Add indexes to proper tune the SQL statement which cause less I/O operation.
5. Gather latest statistics and check it reduce I/O in execution plan for SQL queries.
6. Create materialized view for avoid extra reads.

Part:5

db file scattered reads

Possible Causes : 
· The Oracle session has requested and is waiting for multiple contiguous database blocks (up to DB_FILE_MULTIBLOCK_READ_COUNT)  to be  read into the SGA from disk.
· Full Table scans

· Fast Full Index Scans

Actions : 
· Optimize multi-block I/O by setting the parameter DB_FILE_MULTIBLOCK_READ_COUNT

· Partition pruning to reduce number of blocks visited

· Consider the usage of multiple buffer pools and cache frequently used indexes/tables in the KEEP pool
· Optimize the SQL statement that initiated most of the waits. The goal is to minimize the number of physical
and logical reads.
· Should the statement access the data by a full table scan or index FFS? Would an index range or unique scan
 be more efficient? Does the query use the right driving table?
· Are the SQL predicates appropriate for hash or merge join?
· If full scans are appropriate, can  parallel query improve the response time?
· The objective is to reduce the demands for both the logical and physical I/Os, and this is best
achieved through SQL and application tuning.
· Make sure all statistics are representative of the actual data. Check the LAST_ANALYZED date

Remarks:
· If an application that has been running fine for a while suddenly clocks a lot of time on the db file scattered read event and there hasn’t been a code change, you might want to check to see if one or more indexes has been dropped or become unusable.
· Or  whether the stats has been stale.

Part:6

db file scattered read

 

One: db file scattered read description

Two: db file scattered read solution

Three: db file scattered read reproduction process

Four: db file scattered read official documents

 

One: db file scattered read description

When Oracle performs Full Table Scan (FTS ) and Index Fast Full Scan ( Index Fast Full Scan ), in order to ensure performance, try to read multiple blocks at once, which is called Multi Block I/O .
Every time Multi Block I/O is executed, it will wait for the end of the physical I/O, and wait for the db file scattered read event at this time .

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 scattered 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 scan is occurring. When performing a full 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 scans into the buffer cache show up as waits for 'db file scattered read'.

Similar to  db file sequential read, except that the session is reading multiple data blocks.

Two: db file scattered read solution

1 SQL optimization

If it is caused by some SQL, such as inaccurate statistics, no indexes or inefficient indexes, etc., you can reduce db file scattered read by optimizing SQL;

2 partition table

You can consider optimizing the full table scan into the area scan;

3 Increase BUFFER CACHE

If db file scattered read appears very frequently and the Buffer HIT is low, you can consider increasing the buffer cache;

4 Use faster storage;

select name, parameter1, parameter2, parameter3   from v$event_name

 where name = 'db file scattered read';

 

---View the session containing db file scattered read waiting events;

SELECT sid, total_waits, time_waited   FROM v$session_event

 WHERE event = 'db file scattered read'    and total_waits > 0

 ORDER BY 2 desc;

select sid, event, p1, p2, p3, wait_class   from v$session_wait

 where event = 'db file scattered read';

 

Check the following V$SESSION_WAIT parameter columns:

P1: The absolute file number

P2: The block being read

P3: The number of blocks (should be greater than 1)

select owner, segment_name, segment_type   from dba_extents

 where file_id = 6 and 37475 between block_id and block_id + blocks - 1;

 

SELECT row_wait_obj# FROM V$SESSION WHERE EVENT = 'db file scattered read';

 

SELECT owner, object_name, subobject_name, object_type

  FROM DBA_OBJECTS  WHERE data_object_id = '88605';

 

select v.last_call_et, v.username, v.sid, sql.sql_text,  sql.sql_id,        sql.disk_reads, v.event  from v$session v, v$sql sql where v.sql_address = sql.address and v.last_call_et > 0 and v.status = 'ACTIVE'    and v.username is not null;

select * from table(dbms_xplan.display_cursor('d24df9xbujb75'));

 

---SELECT SQL_ADDRESS, SQL_HASH_VALUE

  FROM V$SESSION

 WHERE EVENT LIKE 'db file%read';

Three: db file scattered read reproduction process

Index fast full scan (Index Fast Full Scan)

SQL>create tablespace chenjch_tbs datafile '/u01/app/oracle/oradata/orcl/chenjch_tbs01a.dbf' size 10M autoextend on maxsize 1G;

SQL> create user chenjch identified by a default tablespace chenjch_tbs;

SQL> grant connect,resource,dba to chenjch;

SQL> create table t1 as select * from dba_objects;

SQL> insert into t1 select * from t1;

SQL> commit;

SQL> insert into t1 select * from t1;

SQL> commit;

SQL> insert into t1 select * from t1;

SQL> commit;

......

SQL> create index i_t1_001 on t1(object_id,object_name,object_type);

SQL> 

begin

  dbms_workload_repository.create_snapshot();

end;

/

SQL> alter system flush buffer_cache;

SQL> alter session set tracefile_identifier='10046';

SQL> alter session set events '10046 trace name context forever, level 12';

SQL>  select  /*+ index_ffs(t1  i_t1_001) */   object_id, object_name, object_type from t1 where object_id = '20';

SQL> alter session set events '10046 trace name context off';

SQL> 

begin 

  dbms_workload_repository.create_snapshot();

  end;

/

SQL> 

select distinct (m.sid), p.pid, p.tracefile

  from v$mystat m, v$session s, v$process p

 where m.sid = s.sid

   and s.paddr = p.addr;

---/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_57032_10046.trc

[oracle@dip~]$tkprof /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_57032_10046.trc /home/oracle/10046_3.trc

 

 

 

 

Full Table Scan (FTS)

Oracle 11g has new changes in the full table scan of large tables. According to information such as the size of the table and the size of the cache, it is determined whether to bypass the SGA and directly read data from the disk.

And 10g reads all data through the cache.

Oracle 11g believes that the use of direct path reads for large tables and full tables may be faster and more efficient than the data file scattered reads in 10g. Therefore, most of what we see are direct path read waiting events. . Oracle 11g provides us with the right to choose, which can be controlled by an implicit parameter, which is " _serial_direct_read ". The default value of this parameter is auto

SQL> create tablespace chenjch_tbs datafile '/u01/app/oracle/oradata/orcl/chenjch_tbs01a.dbf' size 10M autoextend on maxsize 1G;

SQL> create user chenjch identified by a default tablespace chenjch_tbs;

SQL> grant connect,resource,dba to chenjch;

SQL> create table t1 as select * from dba_objects;

SQL> insert into t1 select * from t1;

SQL> commit;

SQL> insert into t1 select * from t1;

SQL> commit;

SQL> insert into t1 select * from t1;

SQL> commit;

......

SQL> 

begin

  dbms_workload_repository.create_snapshot();

end;

/

SQL> alter system flush buffer_cache;

SQL> alter session set tracefile_identifier='10046';

SQL> alter session set events '10046 trace name context forever, level 12';

SQL>  select object_id, object_name, object_type from t1 where object_id = '20';

SQL> alter session set events '10046 trace name context off';

SQL> 

begin 

  dbms_workload_repository.create_snapshot();

  end;

/

SQL> 

select distinct (m.sid), p.pid, p.tracefile

  from v$mystat m, v$session s, v$process p

 where m.sid = s.sid

   and s.paddr = p.addr;

---/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_55600_10046.trc

[oracle@dip ~]$ cp /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_55600_10046.trc .

[oracle@dip ~]$ tkprof orcl_ora_55600_10046.trc 10046_1.trc

 

 

 

Turn off the direct path read special effect, waiting for the event to become db file scattered read during the full table scan

SQL> 

select a.ksppinm name, b.ksppstvl value, a.ksppdesc description

  from x$ksppi a, x$ksppcv b

 where a.indx = b.indx

   and a.ksppinm like '_serial_direct_read'; ---auto   

SQL>  alter session set "_serial_direct_read"=never;

SQL> 

begin

  dbms_workload_repository.create_snapshot();

end;

/

SQL> alter system flush buffer_cache;

SQL> alter session set tracefile_identifier='10046';

SQL> alter session set events '10046 trace name context forever, level 12';

SQL>  select object_id, object_name, object_type from t1 where object_id = '20';

SQL> alter session set events '10046 trace name context off';

SQL> 

begin 

  dbms_workload_repository.create_snapshot();

  end;

/

SQL> 

select distinct (m.sid), p.pid, p.tracefile

  from v$mystat m, v$session s, v$process p

 where m.sid = s.sid

   and s.paddr = p.addr;

---/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_55600_10046.trc

[oracle@dip ~]$ cp /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_55600_10046.trc 10046_2.trc

[oracle@dip ~]$ tkprof 10046_2.trc 10046_2a.trc

 

 

 

 

Four: db file scattered read official documents

db file scattered read

Similar to  db file sequential read, except that the session is reading multiple data blocks.

Wait Time: The wait time is the actual time it takes to do all of the I/Os

10.3.2  db file scattered read

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 scattered 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 scan is occurring. When performing a full 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 scans into the buffer cache show up as waits for 'db file scattered read'.

Check the following V$SESSION_WAIT parameter columns:

P1: The absolute file number

P2: The block being read

P3: The number of blocks (should be greater than 1)

10.3.2.2  Managing Excessive I/O

There are several ways to handle excessive I/O waits. In the order of effectiveness, these are as follows:

Reduce the I/O activity by SQL tuning.

Reduce the need to do I/O by managing the workload.

Gather system statistics with DBMS_STATS package, allowing the query optimizer to accurately cost possible access paths that use full scans.

Use Automatic Storage Management.

Add more disks to reduce the number of I/Os for each disk.

Alleviate I/O hot spots by redistributing I/O across existing disks.

The first course of action should be to find opportunities to reduce I/O. Examine the SQL statements being run by sessions waiting for these events and statements causing high physical I/Os from V$SQLAREA. Factors that can adversely affect the execution plans causing excessive I/O include the following:

Improperly optimized SQL

Missing indexes

High degree of parallelism for the table (skewing the optimizer toward scans)

Lack of accurate statistics for the optimizer

Setting the value for DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter too high which favors full scans

10.3.2.3  Inadequate I/O Distribution

Besides reducing I/O, also examine the I/O distribution of files across the disks. Is I/O distributed uniformly across the disks, or are there hot spots on some disks? Are the number of disks sufficient to meet the I/O needs of the database?

See the total I/O operations (reads and writes) by the database, and compare those with the number of disks used. Remember to include the I/O activity of LGWR and ARCH processes.

10.3.2.4  Finding the SQL Statement executed by Sessions Waiting for I/O

Use the following query to determine, at a point in time, which sessions are waiting for I/O:

SELECT SQL_ADDRESS, SQL_HASH_VALUE

  FROM V$SESSION

 WHERE EVENT LIKE 'db file%read';  

10.3.2.5  Finding the Object Requiring I/O

To determine the possible causes, first query V$SESSION to identify the value of ROW_WAIT_OBJ# when the session waits for db file scatteredread. For example:

SELECT row_wait_obj# FROM V$SESSION  WHERE EVENT = 'db file scattered read';

To identify the object and object type contended for, query DBA_OBJECTS using the value for ROW_WAIT_OBJ# that is returned from V$SESSION. For example:

SELECT owner, object_name, subobject_name, object_type   FROM DBA_OBJECTS

 WHERE data_object_id = &row_wait_obj;