Previous | Table of Contents | Next

Page 639

In willing to wait mode, if the process fails to acquire the latch on the first try, it will wait and try again. If the system has multiple CPUs, this unsuccessful process will start spinning on the latch and try to acquire it. The number of times the process spins on the latch is defined by the init.ora parameter spin_count. With every spin, it will try to acquire the latch; if it does not, it spins again. This goes on until the number of spins reaches the spin_count value. The process will then go to sleep for a specified amount of time, wake up again, and repeat the previous sequence of steps, as shown in Figure 25.1.

FIG. 25.1
Process spinning
on a latch.


Analyzing Views Related to Latches

This section discusses data dictionary views, which can be used to obtain more information about latches on the system. Only the important columns in these views are discussed here. v$latch and v$latchholder are two very important views associated with latches.

Page 640

v$latch: This view contains all important statistics related to the performance of various latches on the system. Table 25.4 gives the description of important columns of view v$latch.

v$latchholder: If the system is currently having latch contention problems, this view can be used to determine which session is currently holding the latch.

Table 25.4v$latch
Column Name Description
immediate_gets Number of requests for the latch that were successful in obtaining the latch immediately (immediate mode only).
immediate_misses Number of requests for the latch that were not successful in obtaining the latch immediately (immediate mode only).
gets Number of successful requests for latches that were willing to wait for the latch.
misses Number of unsuccessful requests for latches that were willing to wait for the latch.
sleeps Number of times a process that was willing to wait for the latch requested the latch, and when it did not get it, had to sleep.
spin_gets Number of times the latch was obtained without sleeping but just spinning.
Sleep1-10 Every time a process sleeps, the value in this column is incremented—for example, if a process had to sleep three times before it acquired the latch, the sleep3 column would be incremented by 1.


Checking for Latch Contention

If the system is facing latch contention problems, the scripts Listing 25.5 and Listing 25.6 can be useful to find out which latch is badly hit. All scripts query the v$latch table to find the latch details. Shown next is the structure of the v$latch.

ADDR                                     RAW(4)
LATCH#                                   NUMBER
LEVEL#                                   NUMBER
NAME                                     VARCHAR2(64)
GETS                                     NUMBER
MISSES                                   NUMBER
SLEEPS                                   NUMBER
IMMEDIATE_GETS                           NUMBER
IMMEDIATE_MISSES                         NUMBER
WAITERS_WOKEN                            NUMBER
WAITS_HOLDING_LATCH                      NUMBER

Page 641


SPIN_GETS                                NUMBER
SLEEP1                                   NUMBER
SLEEP2                                   NUMBER
SLEEP3                                   NUMBER
SLEEP4                                   NUMBER
SLEEP5                                   NUMBER
SLEEP6                                   NUMBER
SLEEP7                                   NUMBER
SLEEP8                                   NUMBER
SLEEP9                                   NUMBER
SLEEP10                                  NUMBER
SLEEP11                                  NUMBER
Listing 25.5Script to Identify Latch Performance on the System
The following are statements issued in SQL*PLUS prior to issuing the query
on v$latch:

col name heading "Name" format a20 col pid heading "HSid" format a3 col gets heading "Gets" format 999999990 col misses heading "Miss" format 99990 col im_gets heading "ImG" format 99999990 col im_misses heading "ImM" format 999990 col sleeps heading "Sleeps" format 99990
select  n.name name, h.pid pid , l.gets gets , l.misses misses,l.immediate_gets im_gets, l.immediate_misses im_misses, l.sleeps sleeps from v$latchname n , v$latchholder h , v$latch l where l.latch# = n.latch#
and l.addr = h.laddr(+)
Name HSid Gets Miss ImG ImM Sleeps -------------------- ---------- ---------- ------ -------- ------ ------ cached attr list 0 0 0 0 0 modify parameter val 931 0 0 0 0 ues messages 736117 44 0 0 80 enqueue hash chains 1257100 145 0 0 163 trace latch 0 0 0 0 0 cache buffers lru 197351761 2092 66288289 20919 4020 chains cache buffer handles 2348 0 0 0 0 multiblock read obje 2139864 700 1 0 900 cts cache protection lat 0 0 0 0 0 ch shared pool 1261081 207 0 0 208 library cache 7867103 11803 376 0 11854 redo allocation 1306657 501 0 0 760 redo copy 34 0 463290 71 47

Page 642

In this output, the HSID column indicates the session id of the session currently holding the latch. If the number of misses as a percentage of the gets is more than 1 percent, the latch needs some attention.

Using the v$session wait view is another way to check whether a session hung for a long time is waiting for a latch. The following query can be used:

Listing 25.6Using v$session_wait to Identify Latch Contention
select  event,  p1text,p1  ,p2text,p2 ,seq#, wait_ time,  state

from v$session_wait

where    sid  = `&&1'


and event = `latch free';


EVENT            P1TEXT      P1 P2TEXT      P2      SEQ# WAIT_TIME   STATE

-------------- --------  ------ --------  ---- --------- --------- --------


latch free      address       3 number      11      1181         0 WAITING

If the wait_time column has a value of 0, the session is currently waiting for the latch.

The P2 column indicates the number of the latch. The name of the latch can be found by using v$latchname:

Select name
From v$latchname
Where latch# = 11;
cache buffers chains

Tuning Some Important Latches

Of all the latches shown in Listing 25.7, a few are important and proper steps can be taken to tune them. The latches that will be covered in this section are cache buffers lru chain, redo allocation, redo copy, and library cache.

Listing 25.7 shows all the latches possible on the database.

Listing 25.7Listing of All Latches
LATCH#             NAME

---------- --------------------

     0     latch wait list
     1     process allocation
     2     session allocation
     3     session switching
     4     session idle bit
     5     cached attr list
     6     modify parameter values
     7     messages
     8     enqueues
     9     enqueue hash chains
    10     trace latch

Previous | Table of Contents | Next