Home > Articles > Data > Oracle

Managing Contention for Oracle Latches

  • Print
  • + Share This
  • 💬 Discuss
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.

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.

  • + Share This
  • 🔖 Save To Your Account

Discussions

comments powered by Disqus

Related Resources

There are currently no related titles. Please check back later.