SQL Script

Monitor Latch Contention

Updated : 29-March-2000
Version : 1.0

Description

The first statement lists information critical to determining if a database instance is experiencing latch contention. Latch contention ratios should remain less than or equal to 1%. If a ratio column is greater than 1%, latch contention exists. The second script provides the ratios of various sleeps for the latches. The third detects latch contention in the db block buffer LRU.

Parameters

None.

SQL Source

col name form A25
col gets form 999,999,999
col misses form 999.99 Heading "Miss %"
col spins form 999.99 Heading "Spin %"
col igets form 999,999,999
col imisses form 999.99 "IMiss %"

spool monlatch.lst

select name
,gets
,misses*100/decode(gets,0,1,gets) misses
,spin_gets*100/decode(misses,0,1,misses) spins
, immediate_gets igets
,immediate_misses*100/decode(immediate_gets,0,1,immediate_gets) imisses
from v$latch
order by gets + immediate_gets
/

col name form A18 trunc
col gets form 999,999,990
col misses form 90.9 Heading "Miss %"
col cspins form A6 heading 'spin|sl06'
col csleep1 form A5 heading 'sl01|sl07'
col csleep2 form A5 heading 'sl02|sl08'
col csleep3 form A5 heading 'sl03|sl09'
col csleep4 form A5 heading 'sl04|sl10'
col csleep5 form A5 heading 'sl05|sl11'
col Interval form A12

set recsep off

select a.name
,a.gets gets
,a.misses*100/decode(a.gets,0,1,a.gets) misses
,to_char(a.spin_gets*100/decode(a.misses,0,1,a.misses),'990.9')||
to_char(a.sleep6*100/decode(a.misses,0,1
,a.misses),'90.9') cspins
,to_char(a.sleep1*100/decode(a.misses,0,1 ,a.misses),'90.9')||
to_char(a.sleep7*100/decode(a.misses,0,1
,a.misses),'90.9') csleep1
,to_char(a.sleep2*100/decode(a.misses,0,1 ,a.misses),'90.9')||
to_char(a.sleep8*100/decode(a.misses,0,1
,a.misses),'90.9') csleep2
,to_char(a.sleep3*100/decode(a.misses,0,1 ,a.misses),'90.9')||
to_char(a.sleep9*100/decode(a.misses,0,1
,a.misses),'90.9') csleep3
,to_char(a.sleep4*100/decode(a.misses,0,1 ,a.misses),'90.9')||
to_char(a.sleep10*100/decode(a.misses,0,1
,a.misses),'90.9') csleep4
,to_char(a.sleep5*100/decode(a.misses,0,1 ,a.misses),'90.9')||
to_char(a.sleep11*100/decode(a.misses,0,1
,a.misses),'90.9') csleep5
from v$latch a 
where a.misses <> 0
order by 2 desc
/ 

select child#
   , ROUND(sleeps/gets*100,2)
   , ROUND( ((1 - sleeps/gets) * 100),2) ratio
from	v$latch_children
where	name = 'cache buffers lru chain'
/          

spool off