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.
The following steps will demonstrate how to tune latches:
Connect to the database as
SYSDBA:Investigate system events related to latches:
SELECT EVENT, TIME_WAITED, TOTAL_WAITS FROM V$SYSTEM_EVENT WHERE EVENT LIKE '%latch%';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;
Query information about immediate latch requests:
COL NAME FOR A40 SELECT * FROM ( SELECT NAME, IMMEDIATE_GETS, IMMEDIATE_MISSES FROM V$LATCH ORDER BY IMMEDIATE_GETS DESC ) WHERE ROWNUM < 11;