7. 使用v$session wait视图来鉴别闩竞争
select event, p1text, p1, p2text, p2, seq#, wait_time, state
from v$session_wait
where sid = '&&1'
and event = 'latch free';
8. 列举用于闩竞争的信息
ttitle center 'Latch Contention Report' skip 3
col name form a25
col gets form 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
order by gets + immediate_gets;
/
9. 检索闩睡眠率
col name form a18 trunc
col gets form 999,999,990
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 desc;
/
责任编辑：小草
您现在的位置： 