There are two scripts in this document.
The report generated by the first of the two scripts in this document 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 report generated by the second script provides the ratios of various sleeps for the latches.
========
Script 1:
========
SET ECHO off
REM NAME: TFSLATCH.SQL
REM USAGE:”@path/tfslatch”
REM ————————————————————————
REM AUTHOR:
REM Virag Saksena, Craig A. Shallahamer, Oracle US
REM (c)1994 Oracle Corporation
REM ————————————————————————
REM Main text of script follows
ttitle -
center ‘Latch Contention Report’ skip 3
col name form A25
col gets form 999,999,999,999
col misses form 999.99
col spins form 999.99
col igets form 999,999,999
col imisses form 999.99
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
where gets<>0
order by gets DESC
/
NAME GETS MISSES SPINS IGETS IMISSES
————————- —————- ——- ——- ———— ——-
cache buffers chains 67,412,367,032 .01 96.73 38,287,986 .01
simulator hash latch 2,414,755,134 .00 99.89 0 .00
simulator lru latch 2,383,067,905 .07 99.89 30,085,656 .03
row cache objects 1,553,956,475 .15 99.98 119,197 .02
session idle bit 1,172,009,931 .00 99.24 0 .00
library cache 379,801,499 .08 79.42 9,335,054 11.54
shared pool 225,106,805 .19 83.69 0 .00
SQL memory manager workar 210,967,639 .00 98.48 0 .00
这几列：
name,gets
MISSES=misses*100/decode(gets,0,1,gets) 也就是miss的百分比，不要超过1%
========
Script2:
========
SET ECHO off
REM NAME: TFSLTSLP.SQL
REM USAGE:”@path/tfsltslp”
REM ————————————————————————
REM AUTHOR:
REM Virag Saksena, Craig A. Shallahamer, Oracle US
REM (c)1994 Oracle Corporation
REM ————————————————————————
REM Main text of script follows:
col name form A18 trunc
col gets form 999,999,999,999
col miss form 90.9
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) miss
,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 asc
/
spin sl01 sl02 sl03 sl04 sl05
NAME GETS MISS sl06 sl07 sl08 sl09 sl10 sl11
—————— —————- —– —— —– —– —– —– —–
simulator lru latc 2,384,112,093 0.1 99.9 0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 0.0
simulator hash lat 2,415,800,963 0.0 99.9 0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 0.0
cache buffers chai 67,442,373,821 0.0 96.7 0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 0.0