|
Category :
Database
Resources -> Database Administration
DB Version
:
Oracle 8i /
Oracle 9i
OS Details :
Sun Solaris9
Reference
: Oracle Metalink
LATCHES:
Latches
are low level serialization mechanisms used to protect shared data
structures in the SGA.
A latch is a type of a lock that
can be very quickly acquired and freed and are typically used to
prevent more than one process from executing the same piece of code at
a given time.
A process acquires a latch when
working with a structure in the SGA (System Global Area). It continues
to hold the latch for the period of time it works with the structure.
The latch is dropped when the process is finished with the structure.
Associated with each latch is a
cleanup procedure that will be called if a process dies while holding
the latch. Examples of latches include
redo allocation latches, redo
copy latches, archive control latch, cache buffers lru chain latch,
library cache pin latch and more. The V$LATCHNAME view lists all the
latches that are available and used by the database.
How do Latches work?
Latches request are made in
one of the two modes: "willing-to-wait" or "no wait". Mostly, latches
will be requested in "willing-to-wait" mode. A request in
"willing-to-wait" mode will loop, wait, and request again until the
latch is obtained. In "no wait" mode the process request the latch and
if one is not available, instead of waiting, another one is requested.
Only when all request fails does the server process have to wait. An
Example of "willing-to-wait" latch is the library cache latches which
is requested by oracle to put the optimized query plan for a query in
the library cache to be shared by other session. An example of a "no
wait" latches is the redo copy latch.
Latch Contention:
If a
latch that oracle requests is busy, the process requesting it spins
and keeps retrying it. The loop is repeated up to a maximum number of
times determined by the hidden parameter _SPIN_COUNT. If after this
entire loop, the latch is still not available, then the process
must yield the CPU and go to sleep. Initially is sleeps for one
centisecond. The time is doubled in every subsequent sleep which
causes a slowdowns to occur and results in additional CPU usage. The
CPU usage is a consequence of the "spinning" of the process.
"Spinning" means that the process continues to look for the
availability of the latch after certain intervals of time, during
which it sleeps.
Monitoring Latches:
V$LATCHNAME, V$LATCH and
V$LATCHHOLDER views are provided by oracle to provide us with various
details associated with latches. In addition, statspack reports can be
run against the database to give us information on all the database
latches and contention details. You can also use custom scripts like
the latch monitor script included here (latch_monitor)
to monitor latch contentions.
Enqueues:
An
enqueue is a
sophisticated locking mechanism that permits several concurrent
processes to share known resources to varying degrees.
Any object which can be concurrently used, can be protected with
enqueues. A good example is of locks on tables. Varying levels of
sharing are allowed on tables e.g. two processes can lock a table in
share mode or in share update mode among other modes. Enqueue is
obtained using an OS specific locking mechanism and allows the user to
store a value in the lock, i.e the mode in which we are requesting it.
The OS lock manager keeps track of the resources locked. If a process
cannot be granted the lock because it is incompatible with the mode
requested and the lock is requested with wait, the OS puts the
requesting process on a wait queue which is serviced in FIFO.
Enqueue Management:
Oracle
allocates the number of enqueues specified by the
ENQUEUE_RESOURCES
parameter. The default value of
ENQUEUE_RESOURCES is derived from
the SESSIONS
parameter and is usually adequate, as long as its value is greater
than DML_LOCKS
+ 20. For three or fewer sessions, the default value is the number of
database files + 20. For 4 to 10 sessions, the default value is the
number of database files + ((SESSIONS
- 3) * 5) + 20. For more than 10 sessions, it is the number of
database files + ((SESSIONS
- 10) * 2) + 55.
If you
explicitly set ENQUEUE_RESOURCES
to a value higher than DML_LOCKS
+ 20, then Oracle uses the value you provide. Oracle will
automatically allocate additional enqueues from the shared pool as
needed if the number specified by
ENQUEUE_RESOURCES is exceeded. You
can check resource usage by querying
V$RESOURCE_LIMIT.
V$LOCK in
conjunction with V$TRANSACTION and V$SESSION views can be used to get
detailed information on locks. In addition one can run the
catblock.sql script found under $ORACLE_HOME/rdbms/admin directory to
create additional views that provide us with extra information on
locks. |
