SQL Script Monitor Latch Contention

Updated: 29-Mar-2002
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

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

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

Return to Index of SQL Scripts

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links