- This wait happens when a session is waiting for a multiblock IO to complete. This typically occurs during full table scans or index fast full scans.
Oracle reads up to DB_FILE_MULTIBLOCK_READ_COUNT consecutive blocks at a time and scatters them into buffers in the buffer cache.
- That means It occurs when Oracle performs multiblock reads from disk into non-contiguous('scattered') buffers in the Buffer Cache.
- How this is done depends on the platform and the release of Oracle you are running.
When Will Index FFS be used in preference to FTS?
From the Oracle8 Server Concepts manual:
1. The index must contain all the columns referenced in the query.
2. Index FFS is only available with Cost Based Optimizer (CBO) (Index hint forces CBO).
3. Index FFS can be hinted with /*+ INDEX_FFS(table index) */ .
From the Oracle8 Server Concepts manual:
1. The index must contain all the columns referenced in the query.
2. Index FFS is only available with Cost Based Optimizer (CBO) (Index hint forces CBO).
3. Index FFS can be hinted with /*+ INDEX_FFS(table index) */ .
Example
SQL> select /*+ INDEX_FFS (emp emp_ix) */ empno, deptno, ename from emp;
SQL> select /*+ INDEX_FFS (emp emp_ix) */ empno, deptno, ename from emp;
An Index FFS will scan all blocks in the index. The returned data is not sorted.
Index FFS can use multiblock I/O and can be parallelized just like a Full Table Scan.
If this Wait Event is a significant portion of Wait Time then a number of approaches are possible:-
Starting with Oracle9i the new view V$SQL_PLAN view can help:
(ignore data dictionary SQL in the output of these queries)
For Full Table scans:
select sql_text from v$sqltext t, v$sql_plan p
where t.hash_value=p.hash_value and p.operation='TABLE ACCESS'
and p.options='FULL'
order by p.hash_value, t.piece;
For Fast Full Index scans:
select sql_text from v$sqltext t, v$sql_plan p
where t.hash_value=p.hash_value and p.operation='INDEX'
and p.options='FULL SCAN'
order by p.hash_value, t.piece;
In Oracle8i a possible approach is to find sessions performing multiblock reads by querying V$SESSION_EVENT for this Wait Event and then SQL Tracing them. Alternatively, the Top SQL statements for Physical Reads can be investigated to see if their execution plans contain Full Table or Fast Full Index scans.
In cases where such multiblock scans occur from optimal execution plans it is possible to tune the size of multiblock I/Os issued by Oracle by setting the instance parameter DB_FILE_MULTIBLOCK_READ_COUNT so that
DB_BLOCK_SIZE x DB_FILE_MULTIBLOCK_READ_COUNT = max_io_size of system
How to resolve wait 'db file scattered read'?
- The goal of setting the db_file_multiblock_read_count parameter is that table scans are performed in fewer, larger I/Os. This is done by evaluating the number of blocks required to complete each table scan over time, then adjusting the parameter so that on average, most scans can be performed in one I/O.
- Starting with Oracle10g Release 2 the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter is now automatically tuned to use a default value when this parameter is not set explicitly. This default value corresponds to the maximum I/O size that can be performed efficiently.
- This value is platform-dependent and is 1MB for most platforms. Because the parameter is expressed in blocks, it will be set to a value thatis equal to the maximum I/O size that can be performed efficiently divided bythe standard block size.
- As blocks read using Full Table and Fast Full Index scans are placed on the least recently used end of the Buffer Cache replacement lists, sometimesit may help to use Multiple Buffer Pools and place such segments in the KEEPpool.
- Partitioning can also be used to reduce the amount of data to be scanned as Partition Pruning can restrict the scan to a subset of the segment's partitions.
- Finally, you can consider reducing the data held in the most frequently accessed segments (by moving older unneeded data out of the database) or moving these segments to new faster disks to reduce the response time on their I/Os.
