Databases Reference
In-Depth Information
CHAPTER 10
■ ■ ■
X$BH and Latch Contention
T
he X$ fixed table
X$BH
is partially documented in
Oracle Database Performance Tuning Guide
10g Release 2
. This chapter provides additional information on
X$BH
. Contrary to what you might
expect, I will show that scrutinizing undocumented X$ tables such as
X$BH
is not necessarily the
royal road to optimum system performance.
A latch is a low-level locking mechanism used by the ORACLE DBMS to protect memory
structures. The wait event
latch free
is used to account for the wait time a process incurs when
it attempts to get a latch, and the latch is unavailable on the first attempt. In Oracle10
g
, there
are several dedicated latch-related wait events for latches, which are usually affected by conten-
tion. For those events, the name of the latch appears in the name of the wait event. The wait
events
latch: library cache
or
latch: cache buffers chains
may serve as examples. The additional
wait events in Oracle10
g
dispel the requirement to find out which latch a generic
latch free
wait
pertains to. In Oracle9
i
,
V$SESSION_WAIT.P2
contains the latch number waited for. The latch
number corresponds to
V$LATCH.LATCH#
. By joining
V$SESSION_WAIT
and
V$LATCH
, the latch
name in
V$LATCH.NAME
may be retrieved.
Cache buffers chains latches are used to protect a buffer list in the buffer cache. These latches
are used when searching for, adding, and removing a buffer from the buffer list. Contention on
this latch usually indicates that there is contention for the blocks protected by certain latches.
Contention may be detected by looking at the column
MISSES
of the fixed view
V$LATCH_CHILDREN
.
The following query identifies child latches with the highest miss count:
SQL> SELECT name, addr, latch#, child#, misses, sleeps
FROM v$latch_children
WHERE misses > 10000
ORDER BY misses;
NAME ADDR LATCH# CHILD# MISSES SLEEPS
-------------------- -------- ------ ------ ------ ------
cache buffers chains 697ACFD8 122 190 11909 125
session idle bit 699B2E6C 7 2 13442 75
library cache pin 68BC3C90 216 2 30764 79
library cache 68BC3B58 214 2 178658 288
The cache buffers chains child latch with address 697ACFD8 is among the latches with the
highest miss count. Counters in
V$LATCH_CHILDREN
are since instance startup. I chose the pred-
icate
MISSES
>
10000
simply because this restricted the result of the query to four rows. Of course,
you should look at figures from an interval where a performance problem was observed, not
figures since instance startup.
105
