db file scattered read
星期三, 九月 24th, 2008This is another very common Wait Event.
It occurs when Oracle performs multiblock reads from disk into non-contiguous
(’scattered’) buffers in the Buffer Cache. Such reads are issued for up to
DB_FILE_MULTIBLOCK_READ_COUNT blocks at a time.
These typically happen for Full Table Scans and for Fast Full Index scans.
If this Wait Event is a significant portion of Wait Time then a number of
approaches are possible:
o Find which SQL statements perform Full Table or Fast Full Index scans and
tune them to make sure these scans are necessary and not the result of a
suboptimal plan.
- 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.
o 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
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 that
is equal to the maximum I/O size that can be performed efficiently divided by
the standard block size.
o 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, sometimes
it may help to use Multiple Buffer Pools and place such segments in the KEEP
pool. For more information please refer to
Note 76374.1 Multiple Buffer Pools
o 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.
o 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.
