Chapter 20: Latches

---

Wait events were introduced in the previous chapter. A wait event is recorded when one thing is waiting for another. There are many different types of wait events. High latch free wait event rates indicate latch misses, showing possible problems with SGA memory buffers. High quantities of latch misses can seriously affect overall database performance; what is required is not being acquired.

20.1 What is a Latch?

Latches protect memory buffers much like locks protect table rows. When a latch is already held on an area of a buffer a latch free wait event occurs if another process attempts to access the same type of data and fails to do so. There are numerous types of latches for all the different types of memory buffer areas. Latches apply to items such as data in the database buffer cache, preventing multiple simultaneous changes to the same data.

Tip 

A latch cannot be tuned. Latch problems are indicative of other problems and can help to focus where significant performance problems may be occurring.

Note 

 Oracle Database 10 Grid   Oracle9i Database Release 2 (9.2) has 239 latches. Oracle Database 10g has 316 latches.

20.1.1 Latch Misses, Spins, and Sleeps

In the previous chapter we saw that a latch free wait event is generated when a latch is missed.

Note 

 Oracle Database 10 Grid   There are many more latch wait events where there were only two in Oracle9i Database.

What is a missed latch? When a latch is requested and the section of a buffer requested is "latched" (held by another process) then a latch free wait event is generated. Therefore, the second request for a latch already obtained by another process will wait for the required latch to be freed up for use. Where a lock on a table row simply waits for release of that table row by constant retrying for a lock, a latch miss will generate one of two types of action. The first type of action will place the latch in immediate mode. Immediate mode will cause the process requesting the latch to take an alternative action. The second type of action will place the latch request into willing to wait mode. Willing to wait mode causes the latch to repeatedly retry and spin. When the latch has spun a number of times, it will sleep for a period of time, reawakening to retry and potentially spin, and perhaps even sleep again. The number times a latch will retry acquiring the latch depends on a configuration parameter called SPIN_COUNT.

Tip 

The SPIN_COUNT parameter is no longer available in Oracle9i Database and Oracle Database 10g. SPIN_COUNT has become _SPIN_COUNT and is now a hidden configuration parameter. Hidden configuration parameters are hidden because Oracle Corporation prefers them not to be changed.

Spinning latches can consume enormous amounts of CPU time. When a latch spins it repeatedly requests a latch. This can affect performance drastically if many latches are spinning at the same time. The following query shows gets, misses, and a miss rate for all latches in my database. The redo copy latch is not an issue as it is a potentially immediate mode latch. Other miss rates are not too significant but there is still potential for problems.

COL latch FORMAT a28; COL gets FORMAT 99999990 HEADING "Gets"; COL misses FORMAT 99990 HEADING "Misses"; COL sleeps FORMAT 99990 HEADING "Sleep"; COL immediate_gets FORMAT 99999990 HEADING "IGet"; COL immediate_misses FORMAT 9990 HEADING "IMiss"; SELECT name "Latch", gets, misses    ,TO_CHAR(ROUND((misses/gets)*100,2),'99990.00') "Miss Rate"    ,immediate_gets, immediate_misses FROM v$latch WHERE gets!=0 AND misses! =0 ORDER BY 4 DESC;                                            Miss Latch                 Gets   Misses    Rate      IGet   IMiss ---------------   --------   ------   -----   -------   ----- redo copy             1620      243   15.00    240928    2138 cache buffers     lru chain       8022562     6651    0.08      2119       0 multiblock     read objects    6253965     2356    0.04        42       0 cache buffers     chains         34445102     9074    0.03   4957516     847 sequence cache       55793        7    0.01         0       0    library cache   2019837      177    0.01         0       0 session     allocation       500846       15    0.00         0       0 library     cache pin       1451385       15    0.00         0       0 library cache     pin allocation   332613        6    0.00         0       0 shared pool         757897        6    0.00         0       0 undo global data     80337        1    0.00         0       0 dml lock     allocation       441665        5    0.00         0       0 enqueues            286118        1    0.00         0       0 redo allocation     267146        8    0.00         0       0 simulator lru     latch            564509       19    0.00        48       0

Let's take a slightly different perspective in my Accounts schema database once again using the V$LATCH performance view. This time we focus on the total waiting time for each latch, more important than the number of misses.

COL latch FORMAT a28; COL misses FORMAT 99990 HEADING "Miss"; COL sleeps FORMAT 99990 HEADING "Sleep"; SELECT name "Latch", ROUND(wait_time/1000) "WaitTime"    ,ROUND((wait_time     /(SELECT SUM(wait_time) FROM v$latch))*100,0) "Percent"    ,misses, sleeps FROM v$latch WHERE wait_time! =0 ORDER BY 3 DESC; 

The picture is somewhat different. Library cache events have become more significant, the shared pool latch has become the least significant and the cache buffers chains latch has disappeared due to zero waiting time. The session allocation latch is interesting. This latch tells us about database connection activity. Continual connection and disconnection from the database can cause problems. There are many other latches used in Oracle Database, not shown here. This query shows significant latch activity in my database.

Latch                      WaitTime   Percent   Miss   Sleep ------------------------   --------   -------   ----   ----- cache buffers lru chain       10068        49   6653    7113 redo copy                      3693        18    243     297 library cache                  3520        17    177     207 multiblock read objects        2434        12   2357    2390 session allocation              569         3     15      16 library cache pin               108         1     15      18 enqueues                         10         0      1       1 library cache pin  allocation                       21         0      6       7 simulator lru latch              14         0     19      20 redo allocation                  35         0      8       9 dml lock allocation              50         0      5       7 undo global data                  0         0      1       1 sequence cache                   41         0      7       7 shared pool                      31         0      6       6 

This introduces potential latch issues in the Accounts schema. Let's take a small step sideways and briefly examine latch performance views.

20.1.2 Latch Performance Views

There are a number of Oracle Database performance views for examining latches and latch statistics, the most important of these being the V$LATCH and the V$LATCHHOLDER performance views.

• V$LATCH.   Aggregation of all latch statistics.

• V$LATCHNAME.   All possible latches in an Oracle database.

• V$LATCHHOLDER.   Contains real-time current latch information about sessions and processes holding latches. This view can be used in conjunction with other views such as V$SESSION, V$SESSION_WAIT, and V$PROCESS to examine latch problems as they occur.

• V$LATCH_MISSES.   Details about latch misses.

• V$LATCH_PARENT and V$LATCH_CHILDREN.   Child and parent details of latches.

20.1.3 Latches in Real Time

Now let's take a look at my database containing the active Accounts schema in real time. I use an anonymous PL/SQL procedure to poll latches in the database many times at the session level, as those latches occur, in order to get a useful result over a period of time.

Tip 

This type of processing can be done easily with Statspack and even more easily with the Oracle Database Wait Event Interface. The purpose here is to explain the details.

DROP TABLE statistics_latches; CREATE TABLE statistics_latches(event varchar2(64)       ,latch varchar2(64), address NUMBER, latch# NUMBER       ,sleeps NUMBER); BEGIN  FOR counter IN 1..100000 LOOP   INSERT INTO statistics_latches   -   --P1 is the latch address, P2 is the latch number, P3 is     the number   --of sleeps   --   SELECT sw.event AS event, l.name AS latch, sw.p1 AS address,     sw.p2 AS latch#, sw.p3 AS sleep   FROM v$session_wait sw, v$latchname l   WHERE sw.event = 'latch free' AND sw.p2 = l.latch#;   COMMIT;  END LOOP; END; /

Now running a query against the table created we can see that the database buffer cache and the library cache have potential latch free wait problems, amongst other potential latch performance possibilities.

SELECT latch "Latch", COUNT(latch) "Latch Waits",    SUM(sleeps) "Sleeps" FROM statistics_latches GROUP BY latch;     Latch                       Latch Waits   Sleep -------------------------   -----------   ----- cache buffers chains                 63       0 cache buffers lru chain              26       7 dml lock allocation                   1       0 enqueue hash chains                  11       0 library cache                         1       0 multiblock read objects               2       0 redo allocation                       1       0 redo copy                             6       0

Now we know a little about latches and potential latch problems in the Accounts schema. Let's examine what the most significant latches are in general for an Oracle installation, indicating potential latch problems and resulting performance issues.

I could make changes to configuration similar to those made in the previous chapter, attempting to both increase and decrease the occurrence of particular latches. At this point these actions are not necessary.

---