梦想有多远

The Oracle RDBMS makes use of different types of locking mechanisms.
They are mainly latches, enqueues, distributed locks and global locks
(used in parallel instance implementations).

This bulletin focuses on latches. It attempts to give a clear understanding
of how latches are implemented in the Oracle RDBMS and what causes latch
contention. The information provided can be used in tuning the various
kinds of latches discussed.

1. What is a latch?

Latches are low level serialization mechanisms used to protect shared
data structures in the SGA. The implementation of latches is operating
system dependent, particularly in regard to whether a process will wait
for a latch and for how long.
A latch is a type of a lock that can be very quickly acquired and freed.
Latches are typically used to prevent more than one process from
executing the same piece of code at a given time. Associated with each
latch is a cleanup procedure that will be called if a process dies while
holding the latch. Latches have an associated level that is used to
prevent deadlocks. Once a process acquires a latch at a certain level it
cannot subsequently acquire a latch at a level that is equal to or less
than that level (unless it acquires it nowait).
(全文…)

‘log file switch (checkpoint incomplete)’
————————————————————

This Wait Event occurs when Checkpointing activities are not occuring
quickly enough.

For guidelines on tuning Checkpoint operations please refer to:

‘log switch/archive’
‘log file switch (archiving needed)’
————————————————————

These Wait Events occur when archiving is enabled and indicate that archiving
is not performing fast enough.

For guidelines on tuning archiving operations please refer to:

Note 45042.1 Archiver Best Practices

These Wait Events occur during I/O to one or all copies of the controlfile.

Frequency of Controlfile access is governed by activities such as Redo Logfile
switching and Checkpointing. Therefore it can only be influenced indirectly
by tuning these activities.

‘control file parallel write’
————————————————————

This occurs when a server process is updating all copies of the controlfile.
If it is significant, check for bottlenecks on the I/O paths (controllers,
physical disks) of all of the copies of the controlfile.

Possible solutions:

o Reduce the number of controlfile copies to the minimum that ensures
that not all copies can be lost at the same time.

o Use Asynchronous I/O if available on your platform.

o Move the controlfile copies to less saturated storage locations.

‘control file sequential read’
‘control file single write’
————————————————————

These occur on I/O to a single copy of the controlfile.
If they are significant find out whether the waits are on particular copy
of the controlfile and if so whether its I/O path is saturated.

The following query can be used to find which controlfile is being accessed.
It has to be run when the problem is occuring:

select P1 from V$SESSION_WAIT
where EVENT like ‘control file%’ and STATUS=’WAITING’;

Possible solutions:

o Move the problematic controlfile copy to a less saturated storage location.

o Use Asynchronous I/O if available on your platform.

These occur when database processes perform special types of multiblock I/Os
between the disk and process PGA memory, thus bypassing the Buffer Cache.
Such I/Os may be performed both synchronously and asynchronously.

Examples where they may be used are:
o Sort I/Os when memory Sort areas are exhausted and temporary tablespaces
are used to perform the sort
o Parallel Execution (Query and DML)
o Readahead operations (buffer prefetching)
o Direct Load operations
o I/O to LOB segments (which are not cached in the Buffer Cache)

Due to the way in which time for these waits is recorded (it does not measure
the time taken to perform the I/O), their relative position in listings such
as Statspack’s “Top 5 Wait/Timed Events” cannot be used to evaluate their
true impact.

Guidelines for tuning:
o Usage of Asynchronous I/O is recommended where available.

o In Oracle8i, minimize the number of I/O requests by setting the
DB_FILE_DIRECT_IO_COUNT instance parameter so that

DB_BLOCK_SIZE x DB_FILE_DIRECT_IO_COUNT = max_io_size of system

In Oracle8i the default for this is 64 blocks.

(In Oracle9i, it is replaced by _DB_FILE_DIRECT_IO_COUNT which governs
the size of direct I/Os in BYTES (not blocks). The default is 1Mb but
will be sized down if the max_io_size of the system is smaller.)

Note 47324.1 Init.ora Parameter “DB_FILE_DIRECT_IO_COUNT” Reference Note

o Tune memory Sort areas so that disk I/O for Sorting is minimized:
In 9i and above use Automated SQL Execution Memory Management.
In 8i tune the various Sort areas manually.

Note 147806.1 Oracle9i New Feature: Automated SQL Execution Memory Management
Note 109907.1 How to Determine an Optimal SORT_AREA_SIZE

o For LOB segments, store them on filesystems where an Operating System File
Buffer Cache can provide some memory caching.

o Identify sessions performing direct I/Os by querying V$SESSION_EVENT
for these Wait Events or V$SESSTAT for statistics
‘physical reads direct’, ‘physical reads direct (lob)’,
‘physical writes direct’ & ‘physical writes direct (lob)’
and tune their SQL statements.

o Identify datafiles on bottlenecked disk storage and move elsewhere
using V$FILESTAT or Statspack’s “File IO Statistics” section.

This Wait Event is used when Oracle performs in parallel reads from multiple
datafiles to non-contiguous buffers in memory (PGA or Buffer Cache).
This is done during recovery operations or when buffer prefetching is being
used as an optimization i.e. instead of performing multiple single-block reads.

If this wait is an important component of Wait Time, follow the same guidelines
as ‘db file sequential read’.