Managing Contention for Oracle Latches
Managing Contention for Oracle Latches
By Megh Thakkar
Internally, Oracle uses various types of structures, the access of which is controlled using a variety of mechanisms, including latches. In this article, Oracle expert Megh Thakkar discusses how contention for Oracle latches can be minimized.
Internally, Oracle uses various types of structures. Access to these structures is controlled using a variety of mechanisms:
-
Latches
-
Enqueues
-
Distributed locks
-
Global locks (used in parallel instance implementations)
This article discusses how contention for Oracle latches can be minimized.
Latches control the access to internal data structures and thereby provide a way to protect them. If a process can't obtain a latch immediately, it spins while waiting for the latch. Spinning processes should be minimized because they can lead to additional CPU use and a slowing of the system.
There are various types of latches, which are commonly referenced by the data structure to which they control access. Table 1 lists the most important latches that you should be concerned with.
Several data dictionary views can be helpful in identifying latch contention:
-
v$latch
-
v$latchholder
-
v$latchname
The following queries can provide useful information about latches:
- This query provides the name of the latch by using the latch address:
svrmgr> SELECT name 2> FROM v$latchname ln, v$latch l 3> WHERE l.addr = '&addr' 4> AND l.latch# = ln.latch# ;
- This query provides system-wide latch statistics:
svrmgr> SELECT ln.name, l.addr, l.gets, l.misses, l.sleeps, 2> l.immediate_gets, l.immediate_misses, lh.pid 3> FROM v$latch l , v$latchholder lh , v$latchname ln 4> WHERE l.addr = lh.laddr (+) 5> AND l.latch# = ln.latch# ORDER BY l.latch# ;
-
This query provides statistics for any latch 'Z':
svrmgr> SELECT ln.name, l.addr, l.gets, l.misses, l.sleeps, 2> l.immediate_gets, l.immediate_misses, lh.pid 3> FROM v$latch l , v$latchholder lh , v$latchname ln 4> WHERE l.addr = lh.laddr (+) 5> AND l.latch# = ln.latch# 6> AND ln.name like '%Z%' 7> ORDER BY l.latch# ;
If either of the following is true for a latch, it indicates contention:
-
The ratio of MISSES to GETS exceeds 1 percent.
-
The ratio of IMMEDIATE_MISSES to the sum of IMMEDIATE_MISSES and IMMEDIATE_GETS exceeds 1 percent.
Table 1: Important Latches That Can Cause Contention
|
Latch Number |
Name Number |
Latch |
Name |
|
0 |
Latch wait list |
22 |
Sequence cache |
|
1 |
Process allocation |
23 |
Sequence cache entry |
|
2 |
Session allocation |
24 |
Row cache objects |
|
3 |
Session switching |
25 |
Cost function |
|
4 |
Session idle bit |
26 |
User lock |
|
5 |
Messages |
27 |
Global transaction mapping table |
|
6 |
Enqueues |
28 |
Global transaction |
|
7 |
Trace latch |
29 |
Shared pool |
|
8 |
Cache buffers chain |
30 |
Library cache |
|
9 |
Cache buffers LRU chain |
31 |
Library cache pin |
|
10 |
Cache buffer handles |
32 |
Library cache load lock |
|
11 |
Multiblock read objects |
33 |
Virtual circuit buffers |
|
12 |
Cache protection latch |
34 |
Virtual circuit queues |
|
13 |
System commit number |
35 |
Virtual circuits |
|
14 |
Archive control |
36 |
Query server process |
|
15 |
Redo allocation |
37 |
Query server freelists |
|
16 |
Redo copy |
38 |
Error message lists |
|
17 |
Instance latch |
39 |
Process queue |
|
18 |
Lock element parent latch |
40 |
Process queue reference |
|
19 |
DML lock allocation |
41 |
Parallel query stats |
|
20 |
Transaction allocation |
||
|
21 |
Undo global data |
The following recommendations can help reduce latch contention:
-
The cache buffers chains latch is needed when the SGA is scanned for database cache buffers. Contention for this latch can be reduced by increasing the DB_BLOCK_BUFFERS init.ora parameter.
-
The cache buffers LRU chain latch is needed when the LRU chain containing all the dirty blocks in the buffer cache is scanned. Contention for this latch can be reduced by increasing the DB_BLOCK_BUFFERS and DB_BLOCK_WRITE_BATCH init.ora parameters.
-
The row cache objects latch is needed when the cached data dictionary values are being accessed. Contention for this latch can be reduced by increasing the SHARED_POOL_SIZE init.ora parameter.
-
Minimize contention for library cache latches by using the following guidelines:
-
Minimize the fragmentation of the shared pool.
-
Increase the use of shared SQL statements, and thereby decrease the reloads. Identify the SQL statements that are receiving many parse calls with the following query:
svrmgr> SELECT sql_text, parse_calls, executions
2> FROM v$sqlarea
3> WHERE parse_calls > 100
4> AND executions < 2*parse_calls; -
Then, try to use sharable SQL wherever possible.
-
Set the CURSOR_SPACE_FOR_TIME init.ora parameter to TRUE to keep shared SQL areas pinned in the shared pool. This prevents them from aging out of the pool as long as an open cursor references them, which results in faster execution. However, be sure that your shared pool is large enough to hold all the cursors.
-
Use fully qualified table names.
-
The redo allocation latch controls the allocation of space for redo entries in the redo log buffer. Before an Oracle process can allocate space in the redo log buffer, it must obtain this latch. Contention for the redo allocation latch can be minimized by decreasing the value of the LOG_SMALL_ENTRY_MAX_SIZE init.ora parameter because this parameter determines the number and size of redo entries copied on the redo allocation latch.
-
-
The redo allocation latch is released as soon as space is allocated. However, the copy is then performed under the redo copy latch. On multiple CPUs, the LOG_SIMULTANEOUS_COPIES parameter determines the number of redo copy latches. Contention for redo copy latches can be reduced by increasing the value of LOG_SIMULTANEOUS_COPIES because having multiple redo copy latches enables multiple processes to concurrently write the redo entries. The init.ora parameter LOG_ENTRY_PREBUILD_THRESHOLD (default value is 0) places a limit on the size of redo entries so that those redo entries that are smaller than this threshold are prebuilt before requesting the redo copy latch. Therefore, increasing LOG_ENTRY_PREBUILD_THRESHOLD can reduce the contention for the redo copy latch.
In general, you don't have control over which latch is used or when it is used. However, by setting certain init.ora parameters, you can optimize the use of these latches, resulting in improved system performance.
About the Author
Megh Thakkar is the Director of Database Technologies at Quest Software in Australia. Previously, he worked as a technical specialist at Oracle Corporation. He holds a master's degree in computer science and a bachelor's degree in electronics engineering. Megh also holds several industry vendor certifications, including OCP, MCSE, Novell Certified ECNE, and SCO Unix ACE, and he is a Lotus Notes Certified Consultant. He is a frequent presenter at Oracle OpenWorld and various international Oracle User Groups.
Megh is the author of E-commerce Applications Using Oracle8i and Java from Scratch and SAMS Teach Yourself Oracle8i on Windows NT in 24 Hours. He has also co-authored several books, including Migrating to Oracle8i, Special Edition Using Oracle8/8i, Oracle8 Server Unleashed, C++ Unleashed, COBOL Unleashed, Oracle Certified DBA, and Using Oracle8. Megh is a renowned Oracle specialist who has performed Oracle development, consulting, support, and DBA functions worldwide over the past 10 years.