latch(zt)

WHAT ARE LATCHES AND WHAT CAUSES LATCH CONTENTION

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


2. Latches vs Enqueues

Enqueues are another type of locking mechanism used in Oracle.
An enqueue is a more sophisticated mechanism which permits several concurrent
processes to have varying degree of sharing of "known" resources. Any object
which can be concurrently used, can be protected with enqueues. A good example
is of locks on tables. We allow varying levels of sharing on tables e.g.
two processes can lock a table in share mode or in share update mode etc.
One difference is that the enqueue is obtained using an OS specific
locking mechanism. An enqueue 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.
Another difference between latches and enqueues is that
in latches there is no ordered queue of waiters like in enqueues. Latch
waiters may either use timers to wakeup and retry or spin (only in
multiprocessors). Since all waiters are concurrently retrying (depending on
the scheduler), anyone might get the latch and conceivably the first one to
try might be the last one to get.

3. When do we need to obtain a latch?

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. Each latch protects a different
set of data, identified by the name of the latch.
Oracle uses atomic instructions like "test and set" for operating on latches.
Processes waiting to execute a part of code for which a latch has
already been obtained by some other process will wait until the
latch is released. Examples are redo allocation latches, copy
latches, archive control latch etc. The basic idea is to block concurrent
access to shared data structures. Since the instructions to
set and free latches are atomic, the OS guarantees that only one process gets
it. Since it is only one instruction, it is quite fast. Latches are held
for short periods of time and provide a mechanism for cleanup in case
a holder dies abnormally while holding it. This cleaning is done using
the services of PMON.


4. Latches request modes?

Latches request can be made in two modes: "willing-to-wait" or "no wait". Normally,
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. If one is not available, instead of waiting, another one is requested. Only
when all fail does the server process have to wait.
Examples of "willing-to-wait" latches are: shared pool and library cache latches
A example of "no wait" latches is the redo copy latch.


5. What causes latch contention?
If a required latch is busy, the process requesting it spins, tries again
and if still not available, spins again. The loop is repeated up to a maximum
number of times determined by the initialization parameter _SPIN_COUNT.
If after this entire loop, the latch is still not available, the process must yield
the CPU and go to sleep. Initially is sleeps for one centisecond. This time is
doubled in every subsequent sleep.
This causes a slowdown to occur and results in additional CPU usage,
until a latch is available. 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.


6. How to identify contention for internal latches?


Relevant data dictionary views to query
---------------------------------------------

V$LATCH
V$LATCHHOLDER
V$LATCHNAME
Each row in the V$LATCH table contains statistics for a different type
of latch. The columns of the table reflect activity for different types
of latch requests. The distinction between these types of requests is
whether the requesting process continues to request a latch if it
is unavailable:

willing-to-wait If the latch requested with a willing-to-wait
request is not available, the requesting process
waits a short time and requests the latch again.
The process continues waiting and requesting until
the latch is available.

no wait If the latch requested with an immediate request is
not available, the requesting process does not
wait, but continues processing.

V$LATCHNAME key information:
---------------------------------------
GETS Number of successful willing-to-wait requests for
a latch.

MISSES Number of times an initial willing-to-wait request
was unsuccessful.

SLEEPS Number of times a process waited a requested a latch
after an initial wiling-to-wait request.


IMMEDIATE_GETS Number of successful immediate requests for each latch.

IMMEDIATE_MISSES Number of unsuccessful immediate requests for each latch.


Calculating latch hit ratio
----------------------------

To get the Hit ratio for latches apply the following formula:

"willing-to-wait" Hit Ratio=(GETS-MISSES)/GETS
"no wait" Hit Ratio=(IMMEDIATE_GETS-IMMEDIATE_MISSES)/IMMEDIATE_GETS

This number should be close to 1. If not, tune according to the latch name


7. Useful SQL scripts to get latch information


/*
** Display System-wide latch statistics.
*/
column name format A32 truncate heading "LATCH NAME"
column pid heading "HOLDER PID"
select c.name,a.addr,a.gets,a.misses,a.sleeps,
a.immediate_gets,a.immediate_misses,b.pid
from v$latch a, v$latchholder b, v$latchname c
where a.addr = b.laddr(+)
and a.latch# = c.latch#
order by a.latch#;
/*
** Given a latch address, find out the latch name.
*/
column name format a64 heading 'Name'
select name from v$latchname a, v$latch b
where b.addr = '&addr'
and b.latch#=a.latch#;


/*
** Display latch statistics by latch name.
*/
column name format a32 heading 'LATCH NAME'
column pid heading 'HOLDER PID'
select c.name,a.addr,a.gets,a.misses,a.sleeps,
a.immediate_gets,a.immediate_misses,b.pid
from v$latch a, v$latchholder b, v$latchname c
where a.addr = b.laddr(+) and a.latch# = c.latch#
and c.name like '&latch_name%' order by a.latch#;



8. List of all the latches


Oracle versions might differ in the latch# assigned to the existing latches.
The following query will help you to identify all latches and the number assigned.
column name format a40 heading 'LATCH NAME'
select latch#, name from v$latchname;

The following output is for Oracle Release 8.1.7:

LATCH# NAME
---------- ------------------
0 latch wait list
1 process allocation
2 session allocation
3 session switching
4 process group creation
5 session idle bit
6 longop free list
7 cached attr list
8 GDS latch
9 shared java pool
10 event group latch
11 messages
12 enqueues
13 enqueue hash chains
14 trace latch
15 KSFQ
16 X$KSFQP
17 i/o slave adaptor
18 ksfv messages
19 msg queue latch
20 done queue latch
21 session queue latch
22 direct msg latch
23 vecio buf des
24 ksfv subheap
25 resmgr group change latch
26 channel handle pool latch
27 channel operations parent latch
28 message pool operations parent latch
29 first spare latch
30 second spare latch
31 dlm process table freelist
32 process parent latch
33 dlm process hash list
34 dlm resource table freelist
35 dlm resource hash list
36 dlm resource scan list
37 dlm lock table freelist
38 dlm cr info freelist latch
39 dlm timeout list
40 dlm deadlock list
41 dlm statistic table latch
42 dlm synchronous data latch
43 dlm shared communication latch
44 dlm recovery domain table latch
45 dlm recovery domain record latch
46 dlm domain table latch
47 dlm domain lock latch
48 dlm domain lock table latch
49 dlm group table freelist
50 dlm group lock latch
51 dlm group lock table latch
52 name-service entry
53 name-service request queue
54 name-service pending queue
55 name-service namespace bucket
56 name-service memory objects
57 name-service request
58 dlm cr request queue latch
59 file number translation table
60 mostly latch-free SCN
61 batching SCNs
62 cache buffers lru chain
63 multiple dbwriter suspend
64 active checkpoint queue latch
65 checkpoint queue latch
66 cache buffers chains
67 cache buffer handles
68 multiblock read objects
69 cache protection latch
70 large memory latch
71 simulator lru latch
72 simulator hash latch
73 archive control
74 archive process latch
75 redo allocation
76 redo copy
77 redo writing
78 KCL instance latch
79 KCL lock element parent latch
80 KCL name table latch
81 KCL freelist latch
82 loader state object freelist
83 begin backup scn array
84 dml lock allocation
85 list of block allocation
86 transaction allocation
87 transaction branch allocation
88 sort extent pool
89 undo global data
90 ktm global data
91 parallel txn reco latch
92 intra txn parallel recovery
93 sequence cache
94 temp lob duration state obj allocation
95 row cache objects
96 dictionary lookup
97 cost function
98 user lock
99 comparison bit cache
100 instance information
101 policy information
102 global tx free list
103 global transaction
104 global tx hash mapping
105 shared pool
106 library cache
107 library cache load lock
108 Token Manager
109 Direct I/O Adaptor
110 cas latch
111 resmgr:runnable lists
112 resmgr:actses change state
113 resmgr:actses change group
114 resmgr:session queuing
115 resmgr:actses active list
116 resmgr:schema config
117 resmgr:gang list
118 resmgr:queued list
119 resmgr:running actses count
120 resmgr:method mem alloc latch
121 resmgrlan CPU method
122 resmgr:resource group CPU method
123 dispatcher configuration
124 session timer
125 parameter list
126 presentation list
127 address list
128 end-point list
129 virtual circuit buffers
130 virtual circuit queues
131 virtual circuits
132 ncodef allocation latch
133 NLS data objects
134 JOX SGA heap latch
135 job_queue_processes parameter latch
136 query server process
137 query server freelists
138 error message lists
139 process queue
140 process queue reference
141 parallel query stats
142 parallel query alloc buffer
143 temp table ageout allocation latch
144 hash table modification latch
145 constraint object allocation
146 device information
147 temporary table state object allocation
148 kwqit: protect wakeup time
149 AQ Propagation Scheduling Proc Table
150 AQ Propagation Scheduling System Load
151 fixed table rows for x$hs_session



9. List of latches that are of most concern to a DBA


BUFFER CACHE LATCHES
=====================
Cache buffers chains latch:
...........................

This latch is needed when user processes try to scan
the SGA for database cache buffers.


Cache buffers LRU chain latch:
..............................

This latch is needed when user processes try to scan
the LRU (least recently used) chain containing all the dirty blocks
in the buffer cache.


REDOLOG BUFFER LATCHES
========================

Redo allocation latch:
......................

This latch controls the allocation of space for redo
entries in the redo log buffer. There is one redo allocation
latch per instance.


Redo copy latch:
................

This latch is used to write redo records into the redolog buffer.

SHARED POOL LATCHES
====================

Row cache objects latch:
........................

This latch comes into play when user processes are attempting to
access the cached data dictionary values.


Library cache latch
..............................
It serialize access to the objects in the library cache. Every time a SQL statements, PL/SQL
blocks or a stored objects (Procedures, packages, functions, triggers) is executed this latch
is acquired.

Shared pool latch
............................
This latch protects the allocation of memory in the library cache. There is just one latch to
the entire database.

10. How can we reduce contention for internal latches?


We can reduce contention for these latches and tune them by adjusting
certain init.ora parameters.
Cache buffers chain latch:
..........................

Contention in this latch might be related with the Buffer cache size, but it might be present
due to a "hot block" (meaning a block highly accessed). Before of incrementing the parameter
DB_BLOCK_BUFFER check that specific blocks are not causing the contention avoiding
memory wasting.

Cache buffers LRU chain latch:
..............................

Multiple Buffer pools and adjusting the parameter DB_BLOCK_LRU_LATCHES to have multiple
LRU latches will help on reducing latch contention.

Redo Allocation Latch:
......................

Contention for this latch in Oracle7 can be reduced by decreasing the value of
LOG_SMALL_ENTRY_MAX_SIZE on multi-cpu systems to force the use of the
redo copy latch. In Oracle8i this parameter is obsolete, so you need to consider
to increase the size of the LOG_BUFFER or reduce the load of the log buffer using
NOLOGGING features when possible.


Redo copy latch:
................

This latch is waited for on both single and multi-cpu systems.
On multi-cpu systems, contention can be reduced by increasing the
value of LOG_SIMULTANEOUS_COPIES (Hidden in Oracle8i) and/or increasing
LOG_ENTRY_PREBUILD_THRESHOLD (undocumented in Oracle7).


Row cache objects latch:
........................

In order to reduce contention for this latch, we need to
tune the data dictionary cache. In Oracle7 this basically means
increasing the size of the shared pool (SHARED_POOL_SIZE)
as the dictionary cache is a part of the shared pool.

Library cache and Shared pool latches
..............................
The first resource to reduce contention on this latch is to ensure that the application
is reusing as much as possible SQL statement representation. If the application is already
tuned the SHARED_POOL_SIZE can be increased. Be aware that if the application is not
using appropriately the library cache the contention might be worst with a larger structure
to be handled.




11. Tuning _SPIN_COUNT (_LATCH_SPIN_COUNT in Oracle7)


SPIN_COUNT controls how many times the process will re-try to obtain
the latch before backing off and going to sleep. This basically
means the process is in a tight CPU loop continually trying to get
the latch for SPIN_COUNT attempts.

On a single CPU system if an Oracle process tries to acquire a latch
but it is held by someone else the process will release the CPU and go
to sleep for a short period before trying again.

However, on a multi processor system (SMP) it is possible that the
process holding the latch is running on one of the other CPUs
and so will potentially release the latch in the next few instructions
(latches are usually held for only very short periods of time).

Performance can be adjusted by changing the value of SPIN_COUNT.
If a high value is used, the latch will be attained sooner than if
you use a low value. However, you may use more CPU time spinning to
get the latch if you use a high value for SPIN_COUNT.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/756652/viewspace-442640/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/756652/viewspace-442640/