O'Reilly logo

Oracle Database 11gR2 Performance Tuning Cookbook by Ciro Fiorillo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ( SELECT NAME, ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required