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.
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$latchColumn 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 incrementedfor example, if a process had to sleep three times before it acquired the latch, the sleep3 column would be incremented by 1. |
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 NUMBERListing 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 Contentionselect 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
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 LatchesLATCH# 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