梦想有多远

This 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.

The LGWR background process waits for this event while it is copying redo
records from the memory Log Buffer cache to the current redo group’s member
logfiles on disk.

Asynchronous I/O will be used if available to make the write parallel, otherwise
these writes will be done sequentially one member after the other.
However, LGWR has to wait until the I/Os to all member logfiles are complete
before the wait is completed.
Hence, the factor that determines the length of this wait is the speed with
which the I/O subsystem can perform the writes to the logfile members.

To reduce the time waited for this event, one approach is to reduce the amount
of redo generated by the database:

o Make use of UNRECOVERABLE/NOLOGGING options.

o Reduce the number of redo group members to the minimum necessary to ensure
not all members can be lost at the same time.

o Do not leave tablespaces in BACKUP mode for longer than necessary.

o Only use the minimal level of Supplemental Logging required to achieve
the required functionality e.g. in LogMiner, Logical Standby or Streams.

Another approach is to tune the I/O itself:

o Place redo group members on storage locations so that parallel
writes do not contend with each other.

o Do not use RAID-5 for redo logfiles.

o Use Raw Devices for redo logfiles.

o Use faster disks for redo logfiles.

o If archiving is being used setup redo storage so that writes for the current
redo group members do not contend with reads for the group(s) currently being
archived.