Databases Reference
In-Depth Information
The library cache latches are used when finding SQL statements in the library cache. High
wait time values for this type of latch requests are caused by unnecessary parses and not
using bind variables, as we will see in the next recipe.
Cache buffers (LRU) chain latches are used to protect data blocks and LRU lists in the buffer
cache. When we experience contention on these latches, we probably need to tune large full
table or index scans in our application to minimize them. Increasing the buffer cache size or
the number of
DBWn
processes may help if there is too much buffer cache activity. Contention
also indicates that a data block is heavily accessed. This can be identified by the following
query, where
'X'
is the address of the child latch; we can get this from the
V$LATCH_
CHILDREN
dynamic performance view:
SELECT * FROM X$BH
WHERE HLADDR = 'X'
We can then join the
DBA_EXTENTS
view to identify the segment, to which the heavily
accessed data block belongs.
The redo copy and redo allocation latches are used to write into the redo log buffer.
Experiencing contention on this latch, requests can be avoided from executing, when possible,
statements using the
NOLOGGING
option or increasing the
LOG_BUFFER
parameter.
See also
F
The
Analyzing
data
using
Statspack
report
recipe in
Chapter 1
F
T
Tuning
latches
in this chapter
F
Minimizing
latches
using
bind
variables
in this chapter
Minimizing latches using bind variables
In this recipe we will see how not using bind variables leads to latch contentions.
Getting ready
We will use the same package used in
Chapter
4
, where we have discussed using bind
variables in our application code, to compare the execution with and without the use of
bind variables.
