Gvlatch (gvlatch.sql)

What This Script Does

This script monitors latch contention across RAC instances, showing detailed statistics about latch waits, misses, and sleep patterns. Essential for diagnosing concurrency issues and system bottlenecks.

The Script

rem  gvlatch.sql
rem
set linesize 1000
set pagesize 300
rem
col inst_id format 99 heading "RAC|INST"
col addr format a16 heading "Addr"
col latch# format 99999 heading "Latch|Nbr"
col level# format 99999 heading "Level|Nbr"
col name format a50 heading "Latch Name"
col hash format 9999999999 heading "Hash"
col gets format 999,999,999,999
col misses format 999,999,999,999
col sleeps format 999,999,999,999
col immediate_gets format 999,999,999 heading "Immed|Gets"
col immediate_misses format 999,999,999 heading "Immed|Misses"
col waiters_woken format 999,999 heading "Wait|Woken"
col waits_holding_latch format 999,999 heading "Wait|Hold|Latch"
col spin_gets format 999,999 heading "Spin|Gets"
col sleep1 format 999 heading "S1"
col sleep2 format 999 heading "S2"
col sleep3 format 999 heading "S3"
col sleep4 format 999 heading "S4"
col sleep5 format 999 heading "S5"
col sleep6 format 999 heading "S6"
col sleep7 format 999 heading "S7"
col sleep8 format 999 heading "S8"
col sleep9 format 999 heading "S9"
col sleep10 format 999 heading "S10"
col sleep11 format 999 heading "S11"
col wait_time format 999,999,999,999,999 heading "Wait|Time"

rem
select INST_ID
,ADDR
,LATCH#
,LEVEL#
,NAME
,WAIT_TIME
,HASH
,GETS
,MISSES
,SLEEPS
,IMMEDIATE_GETS
,IMMEDIATE_MISSES
,WAITERS_WOKEN
,WAITS_HOLDING_LATCH
,SPIN_GETS
,SLEEP1
,SLEEP2
,SLEEP3
,SLEEP4
,SLEEP5
,SLEEP6
,SLEEP7
,SLEEP8
,SLEEP9
,SLEEP10
,SLEEP11
  from gv$latch l
 where inst_id like nvl('&inst_id','%')
   and wait_time <> 0
 order by wait_time desc, name, inst_id
/

Parameters

The script prompts for:

&RAC - instance ID to filter (use % for all)

Usage

-- Basic usage
@gvlatch.sql

-- When prompted, enter:
-- inst_id: RAC instance ID to filter (use % for all)

Sample Output

RAC                 Latch  Level                                                               Wait                                        Immed      Immed    Wait   Wait    Spin
INST Addr            Nbr    Nbr Latch Name                                         Time            Hash             Gets         Misses         Sleeps         Gets     Misses   Woken   Hold   Gets  S1  S2  S3  S4  S5  S6  S7  S8  S9 S10 S11
---- ---------------- ----- ----- -------------------------------------------------- -------------------- ----------- ---------------- ---------------- ---------------- ------------ ------------ ------- ------- ------- --- --- --- --- --- --- --- --- --- --- ---
   1 00000000600B8E98   336     7 cache buffers chains                                   45,678,901,234   123456789   987,654,321,098      12,345,678        1,234,567    456,789,012        2,345   1,234     567  12,345 850 250  85  40  15   5   2   1   0   0   0
   1 00000000600C1A20   157     9 shared pool                                           12,345,678,901   234567890   234,567,890,123       3,456,789          345,678    123,456,789          567     345     123   3,456 700 200  75  20   5   0   0   0   0   0   0
   2 00000000601B9F10   336     7 cache buffers chains                                   34,567,890,123   123456789   876,543,210,987      10,234,567          987,654    345,678,901        1,987     987     456  10,234 800 220  80  35  10   3   1   0   0   0   0
   1 00000000600D2B45   278     6 row cache objects                                       8,901,234,567   345678901    56,789,012,345       1,234,567          123,456     78,901,234          234     123      56   1,234 650 180  50  15   5   0   0   0   0   0   0