Tuning Database Locks &

Latches
Hamid R. Minoui
Fritz Companies Inc.
NoCOUG May 16, 2001

The Challenge of Tuning

Oracle performance tuning requires a
good understanding of all the components
of a database system and the way they
operate and interact.
This presentation addresses two types of
these components:

Database Locks and Latches

Need for locks and latches

To access shared resources concurrently by
other processes requiring access to the same
resources.
To protect the contents of database objects
while they are being modified or inspected
by other processes
To serialize access to SGA data structures

Locks & Latches

Oracle mechanisms for protecting and

managing SGA data structures and database
objects being accessed concurrently while
maintaining consistency and integrity

Differences between locks and

latches
Latches:

Provide only exclusive

access to protected
data structures
Request are not
queued, if a request
fails, process may try
later

Locks:

Allow serialized
access to some
resources
Requests for locks are
queued and serviced
in order

Locks & Latches

Latches:

Simple data structure

Protect resources that

are briefly needed
(LRU list)
Very efficient

Locks:

Complex data
structure that is further
protected by latch
Protect resources
needed for a longer
time (e.g. tables)
Less efficient

Categories of latches:
Solitary latches protecting one data
structure (majority of latches)
Multiple latches protecting different parts of
a single data structure (grouped in a child-parent

relationship)

Latches protect locks (type varies depending on

type of locks)

Modes of latches

An Oracle process can request a latch in

one of two modes:

Willing-to-Wait Mode

If the requested latch is not immediately available,

the process will wait.

Immediate Mode (no-wait mode)

Then process will not wait if the requested latch is

not available and it continues processing

Latch free wait (spin & sleep)

1- Active wait or spin

When an attempt to get a latch in a willing-towait mode fails, the process will spin and try
again

2- Sleep

If the number of attempts reaches the value of

SPIN_COUNT parameter, the process sleeps
Sleeping is more expensive than spinning

Wakeup Mechanisms

Timeout

The operating system signals (wakes up) the

process when a set alarm is triggered

Latch wait posting

The next process to free the required latch will

wake up the process waiting for the latch
Initiated by the requesting process before going
to sleep by putting itself in a latch wait list

Benefit & cost of wait posting

Benefit:

The process is woken up as soon as the latch is freed

Cost:
Requires protecting a latch wait list data structure
by yet another latch, namely latch wait list latch
When used extensively, it can result in a
secondary latch contention

Latch Contention

Latch contention has a significant impact on

performance when:

Enough latches are not available

A latch is held for a relatively long time

Latch contention can be resolved by increasing

specific init.ora parameters associated with latches
To detect latch contention latch statistics should be
examined

Dynamic Performance Views for

latches
Oracle collects statistics for the activity of
all latches and stores them in the dynamic
performance view V$LATCH.
Latch statistics can be used to find
performance problems associated latch
contentions.

V$LATCH

Each row contains statistics for a specific type of

latch.
Contains summary statistics for both non-parent
and parent latches grouped by latch number
(latch#).
Should be the first point of reference when
investigating a suspecting latch contention.

Understanding the V$LATCH

Statistics
V$LATCH contains information such as:

GETS-Number of successful willing-to-wait requests for a

latch
MISSES- Number of times a willing-to-wait process had to
spin on the first try
SPIN_GETS - Number of times a latch is obtained after
spinning at least once
SLEEPS- Number of times a willing-to-wait process slept
WAITERS_WOKEN- Number of times a wait was awakened

V$LATCH Statistics (2)

WAITS_HOLDING -Number of waits while holding a

different latch
IMMEDIATE_GETS - Number of times obtained without
a wait
IMMEDIATE_MISSES - Number of times failed to get
without a wait
For the entire iterations for a latch request no more than
one gets, misses and spin_gets is recorded

(gets-misses) : Number of times a latch was obtained

without spinning at all

V$LATCHNAME

Holds information about decoded latch names

for the latches shown in V$LATCH
The rows of this view have one-to-one
correspondence to the rows of V$LATCH

Latches willing to wait

Query that shows the number of processes that had to sleep, and the
number of times they had to sleep.

This query is run by UTLESTAT.

SELECT name latch_name, gets, misses,

round((gets-misses)/decode(gets,0,1,gets),3) hit_ratio,
sleeps,
round(sleeps/decode(misses,0,1,misses),3) sleeps/misses
from
stats$latches
where gets != 0
order by name;

Evaluating the result

Hit_ratio:
Sleeps/Misses:

The ratio of gets to misses: (gets-misses)/gets

The ratio of sleeps to misses: sleeps/misses

Any latches that have a hit ratio below .99 should be

investigated.

Sleeps/misses is > 1 means there were processes that had to

sleep more than once before getting the latch

Increasing the parameter _LATCH_SPIN_COUNT can

increase the amount of CPU time a process will burn before
trying to acquire a latch (tunable in Oracle7)

Latches not willing to wait

For not willing-to-wait latches, the query the

immediate_gets and immediate_misses columns of the
v$latch view. It shows the statistics for not willing to
wait latches.

This query is run by UTLESTAT.

SELECT name latch_name, immed_gets nowait_gets,

immed_misses nowait_misses,
round((immed_gets/immed_gets+immed_misses),3) nowait_hit_ratio,
from
stats$latches
where immed_gets + immed_misses != 0
order by name;

Evaluating the result

nowait_gets - Number of times a request for a not-willingto-wait latch was successful

nowait_misses - Number of times a request for a notwilling-to-wait latch failed
nowait_hit_ratio - The ratio of nowait_misses to
nowait_gets: (nowait_gets - nowait_misses) / nowait_gets.
Nowait_hit_ratio should be as close to 1 as possible

V$LATCHHOLDER

Contains information about the current latch

holders.
Used to find the process (PID) & session (SID)
of the process and session holding the latch
identified by name (NAME) and address of the
latch (LADDR) being held.
In conjunction it with V$SESSION reveals the
identity of the user and process holding the
latch

V$LATCH_CHILDREN
These views contain statistics about child
latches and parent latches for multiple
latches
Child latches with the same LATCH#
have the same parents
The CHILD# column identifies the child
latch for the same parent

V$LATCH_PARENT
Has the same columns found in
V$LATCH
The union of this view and
V$LACH_CHILDREN represents all
latches

V$LATCH_MISSES
Contains statistics about missed attempts to
acquire a latch
NWFAIL_COUNT - Number of times that a
no-wait (immediate) acquisition of the latch
failed
SLEEP_COUNT - Number of times that
acquisition attempts caused sleeps

Key Latches

Key latches impacting performance:

redo allocation
redo copy
cache buffers LRU
enqueues
row cache objects
library cache
shared pool

Latches using wait posting

By default latch-wait posting is enabled for the

library cache and shared pool latches
Wait posting can be entirely disabled by setting
_LATCH_WAIT_POSTING to 0 (default is 1)
Setting it to 2, enables it for all latches except for
cache buffers chains latch
Changing this parameter should be carefully
benchmarked
Disabling it can be beneficial where contention on
the library cache latch is severe

Sleeps Parameters

_MAX_EXPONENTIAL_SLEEP

The maximum duration of sleep (in seconds) under

an exponential back-off algorithm
default value is 2 second in Oracle8

_MAX_SLEEP_HOLDING_LATCH

The value to which maximum sleep time is

reduced, if the process is already holding other
latches
The default to 4 centiseconds

A sample query

To monitor the statistics for the redo allocation

latch and the redo copy latches:
SELECT name
Latch,
sum(gets)
WTW gets,
sum(misses)
WTW misses,
sum(immediate_gets)
Immediate gets,
sum(immediate_misses)
Immediate Misses
FROM v$latch
WHERE name IN (redo allocation, redo copy)
GROUP BY name

The redo allocation latch

Controls the allocation of space for redo entries in
the redo log buffer.
There is only one redo allocation latch to enforce the
sequential nature of the entries in the buffer.
Only after allocation, the user process may copy the
entry into the buffer (copying on the redo allocation
latch).
A process may only copy on the redo allocation latch
if the redo entry is smaller than a threshold size,
otherwise a redo copy latch is needed

The redo copy latch

Acquired before the allocation latch

Allocation latch is immediately released after acquisition
User process performs the copy under the copy latch,
and releases the copy
User process does not try to obtain the copy latch while
holding the allocation latch.
Redo copy latch is released after the redo entry copy
System with multiple CPUs may have multiple redo
copy latches for the redo log buffer

Tuning redo allocation latch

Goal:
Minimize the time that a process holds the latch
Achieved by:
Reduce the frequency of copying on the redo
allocation latch.
How ?

Decrease LOG_SMALL_ENTRY_MAX_SIZE parameter

value which is the threshold for number and size of redo
entries to copied to redo allocation latch.

Tuning redo copy latch

Goal:
Reduce contention on available copy latches
Achieved by:
Adding more redo copy latches
How ?
Set LOG_SIMULTANEOUS_COPIES up to twice
the number of CPUs

Cache buffer LRU latch

Controls buffers replacement in the buffer cache

Each LRU latch controls a set of buffers
Each latch should have at least 50 buffers in its set
Contention detected by querying v$latch,
v$session_event and v$system_event
Contention also exists if misses are higher than
3% in v$latch

Tuning LRU latch

Goal:
Reduce cache buffer LRU latch contention
Achieved by:
Having enough latches for the entire buffer cache.
How ?
Set the maximum number of desired LRU latch sets
with DB_BLOCK_LRU_LATCHES up to (number_of
CPUs)*2
Adjust DB_BLOCK_BUFFERS.

Enqueue latch
This latch is used to protect the enqueue data
structure
To tune:
Set ENQUEUE_RESOURCES to a value greater
than 10

Monitoring Wait Events

Wait events on any latch (latch free wait) are
recorded in WAIT and EVENT dynamic views:

V$SESSION_WAIT - Record events for which sessions are waiting

or just completed waiting (e.g. latch free wait)
V$SESSION_EVENTS - Record cumulative statistics events have
waited for each session (e.g. sessions latch free waits)
V$SYSTEM_EVENTS - Record cumulative wait statistics for all
sessions (e.g. latch free wait).
TIMED_STATISTICS must be enabled for the above statistics to
be recorded

v$session_wait for latch free wait

Wait parameters P1, P2 and P3 contain the following

values for latch free when the process is waiting on a
latch to be available

P1

Latch SGA address; corresponding with the ADDR

column of V$LATCH_PARENT & V$LATCH_CHILDREN

P2

Type of latch; corresponding with LATCH# column of

the V$LATCH family of views

P3

Number of times the process has slept trying to

acquire the latch

v$session_event &v$system_event

Symptoms of latch contention can be found in these

views
Updated when the process wakes up again indicating
the wait is over.
Sleep time is recorded
Consecutive sleeps during attempts to obtain a single
latch is recorded as separate waits
Latching statistics in the V$LATCH family are only
updated once the latch is acquired

Locks
Allow sessions to join a queue for a
resource that is not immediately available
To achieve consistency and integrity
Performed automatically by Oracle and
manually by users

Lock Usage

Transaction & Row-level locks

Buffer locks

Transactions imposing implicit locks on rows

In effect for the duration of the transaction
Short term block-level locks in force while
modifying blocks in cache

Data dictionary locks

Locks that protect data dictionary objects

Lock Modes

Applied to simple objects:

X - Exclusive
S - Shared
N- Null

Applied to compound objects:

SS - Sub-shared
SX- Sub-exclusive
SSX-Shared-sub exclusive

Enqueue Conversion
The operation of changing the mode of an
enqueue lock
Example:

1- Transaction T1 holds a lock on table TAB in SS

mode
2- T1 needs to update a row of TAB
3- Lock is converted to SX mode

ENQUEUE Locks

A sophisticated locking mechanism that uses fixed

arrays for the lock and the resource data structure
A request for a resource is queued
Permits several concurrent processes to share
known resources to varying levels
Can protect any object used concurrently
Many of Oracle locks

Enqueue Resources

The fixed array for enqueue resources is sized by

ENQUEUE_RESOURCES parameter.
Determines number of resources that can be
concurrently locked by the lock manager
Its default value is derived from SESSIONS
parameter
If set to a value greater than DML_LOCKS+20, the
provided value will be used
Increase if enqueues are exhausted

Enqueue Locks
A second fixed array used for enqueue
locking
Size set by _ENQUEUE_LOCK
Used by each session waiting for a lock or
holding a lock on a resource

Corresponding views
Each row in v$resource represents a locked
enqueue resource that is currently locked
All locks owned by enqueue state objects
are shown in v$enqueue_lock
All locks held by Oracle or locks and
outstanding requests for locks and latches
are recorded in v$lock

Enqueue wait
Occurs when an enqueue request or
conversion can not be granted at once
An enqueue wait event is recorded by the
blocked process in the v$session_wait view

Enqueue statistics

Enqueue statistics recorded in V$SYSSTAT

enqueue waits
enqueue requests
enqueue conversions
enqueue timeouts
enqueue deadlocks

Deadlock Detection

Automatically performed by Oracle

Initiated when an enqueue wait times out and if:
The resource type is deadlock sensitive
The lock state for the resource in unchanged

When a session holding a lock on a resource

is waiting for a resource that is held by the
current session in an incompatible mode

DML Locks

Guarantees integrity of data being access and

modified concurrently for the entire transaction
Prevent destructive interference of conflicting DML
and/or DDL operations occurring at the same time
Adds maintenance of locks conversion history
Locks are held during the entire transaction
Sessions with blocking transaction enqueue locks
always hold a DML lock as well

DML_LOCKS

DML_LOCKS - Max # of DML locks-one for each table

modified in a transaction. Equals the total number of locks
on tables currently references by all users.
If set to 0, DML locks are entirely disabled
V$LOCKED_OBJECTS reveals active slots
DISABLE TABLE LOCKS or ALTER TABLE can be
used to disable DML locks for particular tables
The free list data structure for DML locks is protected by
dml lock allocation latch

V$LOCK view
Records locks currently held as well as
outstanding requests for a lock or a latch
Key columns are:

ADDR: Memory address of object in locked state

SID: Id of session holding or requesting the lock
TYPE: type of user or system lock
ID1, ID2: Type dependent lock identifiers
LMODE, REQUEST: Mode the lock is held or requested

Example: Locked Users

If locking conflict are suspected, locked

users and the statement they are running can
be identified by the following query:

select b.username, b.serial#, d.id1, a.sql_text

from v$session b, v$lock d , v$sqltext a
where b.lockwait = d.kaddr
and a.address = b.sql_address
and a.hash_value = b.sql_hash_value

V$LOCKED_OBJECTS
Records information on all locks acquired
by all transactions including slot numbers
being used by locks
Used to obtain session information for
sessions holding DML locks on crucial
database objects

Views created by catblock.sql

DBA_LOCKS: Gathers various lock statistics

translated into an easier to understand format
DBA_WRITERS: Provides information on
sessions waiting for locks on specific resources
and sessions that have those resources blocked
DBA_BLOCKERS: Provides information on
which sessions are holding up others

Other lock utilities

Utllockt.sql provided by Oracle
The dbms_lock package
Oracle Enterprise Manager
Third-party tools

V$RESOURCE_LIMIT view
To monitor consumption of resources
Reveals number of used slots in the fixed
array of lock structures
Use it to adjust ENQUEUE_RESOURCE &
DML_LOCKS parameter settings

Other lock topics

Distributed transactions
The Lock Manager
LCKs processes
Global locks
Parallel cache management (PCM) locks

Resources

Oracle8i Internal Services for waits, latches, locks, and

memory by Steve Adams
Oracle Performance Tuning TIPS & TECHNIQUES by
Richard Niemiec
Oracle8i Tuning Manual
Oracle8i Reference Manual