Oracle Database 11gR2 Performance Tuning Cookbook

Tuning latches

In this recipe we will see what latches are, and how (and if) we can tune latches. We will discover that we don't tune latches, but we tune resources that can cause issues related to latches.

How to do it...

The following steps will demonstrate how to tune latches:

1. Connect to the database as SYSDBA:

   CONNECT / AS SYSDBA
   
   

2. Investigate system events related to latches:

   SELECT
     EVENT, TIME_WAITED, TOTAL_WAITS
   FROM V$SYSTEM_EVENT
   WHERE EVENT LIKE '%latch%';3
   

3. Query information about willing-to-wait latch requests:

   COL NAME FOR A20
   SELECT * FROM (
    SELECT
      NAME, GETS, MISSES, SLEEPS, SPIN_GETS, WAIT_TIME
    FROM V$LATCH
    ORDER BY GETS DESC
   )
   WHERE ROWNUM < 11;
   

4. Query information about immediate latch requests:

   COL NAME FOR A40
   SELECT * FROM ( ...