db file scattered read

The db file scattered read wait event has three parameters: file#, first block#, and block count. In Oracle Database 10 g , this wait event falls under the User I/O wait class. Keep the following key thoughts in mind when dealing with the db file scattered read wait event.

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.
Multiblock I/O requests are associated with full table scans and index fast full scans (FFS) operations.
The two important numbers to look for are the TIME_WAITED and AVERAGE_WAIT by individual sessions.
Significant db file scattered read wait time is most likely an application issue.

Common Causes, Diagnosis, and Actions

The db file scattered read wait event is much like the db file sequential read event. Instead of single-block read, this is multiblock read. The db file scattered read wait event is initiated by SQL statements (both user and recursive) that perform full scans operations against tables and indexes. Contrary to some teaching, full scans are not always bad; they are good when the SQL statement needs most of the rows in the object. Full scans on tables and indexes are normal and are not the main issue. You want to avoid having full scans on objects when SQL predicates can be better served by single-block reads. Following are some helpful tips for diagnosing and correcting db file scattered read waits.

Session-Level Diagnosis

When do multiblock reads become an issue? As with the db file sequential read event, you must also establish a guideline for your environment. The amount of time a process spends on the db file scattered read event is always a good indicator, bearing in mind the LOGON_TIME and the significance of the wait time in relation to other nonidle events. A high wait time is normally caused by inefficient SQL statements and therefore is most likely an application issue.

In addition to the V$SESSION_EVENT view, the V$SESSTAT view also has full table scans statistics of current sessions. However the time element is missing, so the V$SESSION_EVENT view is better. Following is an example query:

 select a.sid, b.name, a.value 
 from   v$sesstat a, v$statname b 
 where  a.statistic# = b.statistic# 
 and    a.value     <> 0 
 and    b.name = 'table scan blocks gotten' 
 order by 3,1; 
 
 SID NAME                             VALUE 
 ---- -------------------------- ---------- 
 111 table scan blocks gotten        59,535 
 8 table scan blocks gotten       567,454 
 164 table scan blocks gotten     5,978,579 
 158 table scan blocks gotten    14,798,247

You can minimize the db file scattered read waits the same way you do db file sequential read waits:

Optimize the SQL statement that initiated most of the waits. The goal is to minimize the number of physical and logical reads.
Reduce the average wait time.

As we discussed in the db file sequential read section, you must first find the SQL statement that is responsible for most of the waits, and this is challenging if you do not have a wait event data collector that monitors processes from start to finish. (The Active Session History [ASH] in Oracle Database 10 g automatically collects SQL execution information, and you can easily identify the offending SQL statement using the Enterprise Manager.) Once the SQL statement is identified, examine its execution plan. 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.

Prior to Oracle9 i Database, generating accurate explain plans for SQL statements could be a painstaking task, especially if your schema was not privileged to the objects accessed by the SQL statements. This task is now a breeze , however, beginning in Oracle9 i Database with the V$SQL_PLAN view. The following query extracts the execution plans of SQL statements that are currently performing full scans:

 set linesize 132 
 break on hash_value skip 1 dup 
 col child_number format 9999    heading 'CHILD' 
 col operation    format a55 
 col cost         format 99999 
 col kbytes       format 999999 
 col object       format a25 
 select hash_value, 
 child_number, 
 lpad(' ',2*depth)operation' 'optionsdecode(id, 0, substr(optimizer,1, 6)' Cost='to_char(cost)) operation, 
 object_name object, 
 cost, 
 cardinality, 
 round(bytes / 1024) kbytes 
 from   v$sql_plan 
 where  hash_value in (select  a.sql_hash_value 
 from    v$session a, v$session_wait b 
 where   a.sid   = b.sid 
 and     b.event = 'db file scattered read') 
 order by hash_value, child_number, id; 
 
 -- Not all the columns are shown due to space constraints. 
 HASH_VALUE CHILD OPERATION                           OBJECT 
 ---------- ----- ----------------------------------- ---------------- 
 138447424     0 DELETE STATEMENT CHOOSE Cost=5 
 138447424     0   DELETE 
 138447424     0     INDEX FAST FULL SCAN            HOLD_Q1_LIST_PK 
 
 4246069598     0 SELECT STATEMENT CHOOSE Cost=4075 
 4246069598     0   NESTED LOOPS ANTI 
 4246069598     0     MERGE JOIN ANTI 
 4246069598     0       SORT JOIN 
 4246069598     0         PARTITION RANGE ALL 
 4246069598     0           TABLE ACCESS FULL         SRV_TRX_REPOS 
 4246069598     0       SORT UNIQUE 
 4246069598     0         PARTITION RANGE ALL 
 4246069598     0           INDEX FAST FULL SCAN      Q3_PK 
 4246069598     0     PARTITION RANGE ALL 
 4246069598     0       INDEX FAST FULL SCAN          ERROR_QUEUE_PK

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. To determine which indexes have been dropped, you can compare the development, test, and production databases. Also, check when the LAST_DDL_TIME of the table was in the DBA_OBJECTS view. When an index is created or dropped, Oracle stamps the date and time in the LAST_DDL_TIME column. The ALTER TABLE MOVE command marks all indexes associated with the table as unusable. Certain partitioning operations can also cause indexes to be marked unusable. This includes adding a new partition or coalescing partitions in a hash-partitioned table; dropping a partition from a partitioned table or global partitioned index; modifying partition attributes; and merging, moving, splitting, or truncating table partitions. A direct load operation that fails also leaves indexes in an unusable state. This can easily be fixed by rebuilding the indexes.

There are initialization parameters that, when increased in value, can skew the optimizer toward full scans: DB_FILE_MULTIBLOCK_READ_COUNT (MBRC), HASH_AREA_SIZE, and OPTIMIZER_INDEX_COST_ADJ. Find out the values the sessions are running with and make appropriate adjustments to the parameters so they do not adversely affect application runtimes .

Yet another factor that can adversely affect the quality of execution plans and cause excessive I/Os is having inaccurate statistics. When the optimizer sees a table with statistics that say it has only a few rows, it will choose the full table scan access path . If the table has millions of rows in reality, the SQL statement is in for a rude awakening at execution time, especially if the table is an inner table of a nested loops join. Make sure all statistics are representative of the actual data. Check the LAST_ANALYZED date. If you suspect the statistics are stale, you can count the current number of rows in the table and compare it with the NUM_ROWS value. Alternatively, you can let Oracle decide if statistics need to be refreshed by executing the DBMS_STATS.GATHER_TABLE_STATS with the GATHER STALE option, but only if table monitoring is enabled for the table. Oracle considers the statistics stale when 10 percent of the rows are changed. Table monitoring is enabled by default in Oracle Database 10 g .

Note ‚

When analyzing tables or indexes with the COMPUTE option, Oracle normally performs full table scans. This will add to the db file scattered read statistics for the session (V$SESSION_EVENT) and instance (V$SYSTEM_EVENT).

System-Level Diagnosis

Query the V$SYSTEM_EVENT view in the order of TIME_WAITED and find the db file scattered read wait event. The columns of interest are the AVERAGE_WAIT and TIME_WAITED. Handling the db file scattered read waits on systemlevel is similar to handling the db file sequential read waits on system level, so you should refer to the ‚“System-Level Diagnosis ‚½ section of the db file sequential read wait event.

Why Does db file sequential read event Show Up
in a Full Table Scan Operation

If you trace or monitor a full table scan operation closely, you may find db file sequential read events sandwiched between db file scattered read events. This may or may not be a problem depending on the circumstance of the single-block read. Following are the four primary reasons why you see db file sequential read events in a full scan operation.

Extent boundary When the last set of blocks in an extent is only 1 block, Oracle fetches that block with a single-block read call. This is normally not a problem unless your extent size is too small. Following is an event 10046 trace file that shows db file sequential read events embedded in a full table scan operation. The table block size is 8K, the MBRC is 8 blocks, and the extent size is 72K (9 blocks). A full table scan against the table will result in many db file sequential read events if the table is large. If this is the case, the full table scan operation will complete faster if the table is rebuilt with a larger extent size.

 WAIT #1: nam='db file scattered read' ela= 470 p1=7 p2=18 p3=8 
 WAIT #1: nam='db file sequential read' ela= 79 p1=7 p2=26 p3=1 
 WAIT #1: nam='db file scattered read' ela= 459 p1=7 p2=27 p3=8 
 WAIT #1: nam='db file sequential read' ela= 82 p1=7 p2=35 p3=1 
 WAIT #1: nam='db file scattered read' ela= 466 p1=7 p2=36 p3=8 
 WAIT #1: nam='db file sequential read' ela= 79 p1=7 p2=44 p3=1 
 WAIT #1: nam='db file scattered read' ela= 460 p1=7 p2=45 p3=8 
 WAIT #1: nam='db file sequential read' ela= 60 p1=7 p2=53 p3=1 
 WAIT #1: nam='db file scattered read' ela= 779 p1=7 p2=54 p3=8 
 WAIT #1: nam='db file sequential read' ela= 78 p1=7 p2=62 p3=1 
 . . .

Cached blocks See explanation in the ‚“Why Does a Full Scan Operation Request Fewer Blocks than the MBRC ‚½ section. This is not a problem.
Chained or migrated rows It is a problem if you see many db file sequential read waits against a table when the execution plan of the SQL statement calls for a full table scan. This indicates the table has many chained or migrated rows. Oracle goes after each chained or migrated row with the single-block I/O call. Check the table ‚ s CHAIN_CNT in the DBA_TABLES view. Of course, the CHAIN_CNT is as of the LAST_ANALYZED date. Migrated rows can be corrected by reorganizing the table (export and import, or ALTER TABLE MOVE).
Index entry creation It is not a problem if you see many db file sequential read waits against an index when the execution plan of the SQL statement calls for a full table scan. In the following example, TABLE_A has an index and the db file sequential read waits were the result of reading index blocks into the SGA to be filled with data from TABLE_B. Notice the magnitude of the db file sequential read waits versus the db file scattered read in the statistics. This means you cannot always assume which bottlenecks you will see from looking at an execution plan. Most DBAs would expect to see a lot of db file scattered read events. Another point worth noting is that the db file sequential read wait event does apply to insert statements. The common misconception is that it only applies to update and delete statements.
```
 -- SQL statement 
 insert into table_A 
 select * from table_B; 
 
 -- Explain plan 
 LVL OPERATION              OBJECT 
 --- ---------------------- ------------------- 
 1  INSERT STATEMENT 
 2    TABLE ACCESS FULL   TABLE_B 
 
 -- Wait event statistics 
 SID EVENT                          TIME_WAITED 
 --- ------------------------------ ----------- 
 7 SQL*Net message from client              5 
 7 latch free                              11 
 7 log file switch completion             155 
 7 log buffer space                       205 
 7 log file sync                          467 
 7 db file scattered read               1,701 
 7 db file sequential read            185,682 
```

Why Does a Full Scan Operation Request Fewer Blocks than the MBRC?

If you monitor a full table scan operation closely by repeatedly querying the V$SESSION_WAIT view in quick successions or by tracing the session with trace event 10046, you may see some db file scattered read events that request fewer blocks than the MBRC. This irregularity is due to any of the following reasons:

The last set of blocks in an extent is less than the MBRC. If the MBRC is set to 8 and every extent has 10 blocks, Oracle will issue two multiblock read calls for each extent ‚ one read call for 8 blocks and the other read call for 2 blocks ‚ because the MBRC factor cannot span across extents.
One or more blocks in the multiblock read set is already in the buffer cache, so Oracle breaks the fetch into two or more reads, which may be comprised of a single or multiblock I/Os. For example, if the MBRC is 8 and blocks 3 and 7 are in the cache, Oracle will issue three read calls ‚ the first for blocks 1 and 2, the second for blocks 4 through 6, and the third for block 8. Since the third fetch is for a single database block, the wait event is db file sequential read . However, for the first two read calls, the wait event is db file scattered read because the number of blocks is greater than 1. Therefore, cached blocks can cause full table scans operations to perform more reads than required.

Setting the
DB_FILE_ MULTIBLOCK_ READ_ COUNT (MBRC)

As mentioned, a higher MBRC number can influence the optimizer to lean toward full scans. The right number for the database depends on the application (DSS or OLTP). Batch processes can benefit from a higher MBRC as it allows full scan operations to complete faster. If the database serves both batch and OLTP processes, you must find a balance. The default value of 8 is rather conservative.

If full scans are the best way to go, you want SQL statements to scan the objects with the maximum value supported by your system. Why waste time with a smaller value? You should find out what the maximum value is and dynamically apply this value to processes that perform large full scans.

There is a limit on MBRCs. It depends on several factors, including sstiomax , DB_BLOCK_SIZE, and DB_BLOCK_BUFFERS. The sstiomax is an Oracle internal limit, which limits the amount of data that can be transferred in a single I/O of a read or write operation. The value is internally set in the Oracle code and varies with the Oracle version. The limit is 128K in earlier versions of Oracle and 1MB beginning in version 8. The product of DB_BLOCK_SIZE and MBRC cannot exceed the port-specific definition for sstiomax . MBRC must also be smaller than DB_BLOCK_BUFFERS / 4. Furthermore, MBRC is subject to hardware limits such as the Solaris maxphys and file system maxcontig values. Does this sound like too much to you? It is! The good news is there is a shortcut to finding the limit for your platform. You can set the MBRC to a ridiculously high number for your session, as shown next , and let Oracle figure out what the system can handle. You then simply run a query that scans a table and monitor the progress from another session by querying the V$SESSION_WAIT view in quick successions. The maximum P3 value of the db file scattered read events that belong to the first session is the MBRC limit for your platform. Alternatively, you can monitor the full table scans with trace event 10046. This maximum value is not meant to be set at the database level; rather, it may be applied at the session level to speed up full scans when that is the best way to go.

 alter session set db_file_multiblock_read_count = 1000; 
 select /*+ full(a) */ count(*) from big_table a; 
 
 -- This following is an excerpt from the 10046 trace file. 
 -- It shows that the largest MBRC the system can bear is 128 blocks. 
 WAIT #1: nam='db file scattered read' ela= 17946 p1=6 p2=56617 p3=128 
 WAIT #1: nam='db file scattered read' ela= 21055 p1=6 p2=56745 p3=128 
 WAIT #1: nam='db file scattered read' ela= 17628 p1=6 p2=56873 p3=128 
 WAIT #1: nam='db file scattered read' ela= 29881 p1=6 p2=57001 p3=128 
 WAIT #1: nam='db file scattered read' ela= 33220 p1=6 p2=57129 p3=128 
 WAIT #1: nam='db file scattered read' ela= 33986 p1=6 p2=57257 p3=96 
 WAIT #1: nam='db file scattered read' ela= 46372 p1=6 p2=65577 p3=128 
 WAIT #1: nam='db file scattered read' ela= 33770 p1=6 p2=65705 p3=128 
 WAIT #1: nam='db file scattered read' ela= 41750 p1=6 p2=65833 p3=128 
 WAIT #1: nam='db file scattered read' ela= 34914 p1=6 p2=65961 p3=128 
 WAIT #1: nam='db file scattered read' ela= 33326 p1=6 p2=66089 p3=128

Why Physical I/Os Are Expensive

Whenever most DBAs hear that physical I/Os are costly, they immediately train their thoughts toward the physical disks and I/O subsystem. Yes, the storage layer is the slowest component, but that is only half of the story. The other half is about the stuff that goes on inside Oracle when blocks are being read into the SGA.

There are numerous operations that have to take place. For brevity ‚ s sake, among them, the foreground process must first scan the free buffer list. If a free buffer is not found when the maximum scan limit is reached, the foreground process posts the DBWR process to make free buffers. Then the foreground process has to retry for the free buffer. Once it finds a free buffer, it unlinks it from the free lists chain and relinks the buffer in at the top of the LRU (Least Recently Used) or the midpoint insertion of the LRU (depending on the version). Then the pointers for the buffer header must be adjusted accordingly . There are at least two sets of pointers, and each change requires a latch get. The header structure of the block must also be initialized and updated. The bits in the block header must also be set and changed during the allocation of the buffer, the read of the block into the buffer, and the completion of a read in order to prevent other processes from using the block while it is influx.

Therefore the best way to combat the db file sequential read and db file scattered read waits is to reduce the demand for both the logical and physical I/Os. This can be best achieved through application and SQL tuning. Now that you have been informed how expensive physical I/Os are, you should also know that logical I/Os are not that cheap either. We will discuss this in Chapter 6.

db file scattered read