Oracle 关于latch_free事件
查找关于latch的隐含参数
column name format a42
column value format a24
select
x.ksppinm name,
y.ksppstvl value,
y.ksppstdf isdefault,
decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod,
decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj
from
sys.x$ksppi x,
sys.x$ksppcv y
where
x.inst_id = userenv('Instance') and
y.inst_id = userenv('Instance') and
x.indx = y.indx and x.ksppinm like '%latch%'
order by
translate(x.ksppinm, ' _', ' ');
1.
select sid,event from v$session_wait;
可以看到大量的latch free事件。
2。
P1－表示Latch地址，也就是进程正在等待的latch地址。
P2－表示Latch编号，对应于视图V$LATCHNAME中的latch#。
select *
from v$latchname
where latch# = number;
P3－表示为了获得该latch而尝试的次数。
3。
查看参数
select value from v$parameter where upper(name)='TIMED_STATISTICS';
将参数TIMED_STATISTICS设置为假。
TIMED_STATISTICS=false4。
Cache buffers chains latch:
Data buffer chains—热点块,找到misses>10000次的
select CHILD# "cCHILD",ADDR "sADDR", GETS "sGETS", MISSES "sMISSES", SLEEPS "sSLEEPS"
from v$latch_children
where name = 'cache buffers chains'
and misses>10000
order by 4, 1, 2, 3;--找到段的名称，千万别运行，老大的查询，相当慢
select /*+ RULE */
e.owner ||'.'|| e.segment_name segment_name,
e.extent_id extent#,
x.dbablk - e.block_id + 1 block#,x.tch,l.child#
from sys.v$latch_children l,sys.x$bh x,sys.dba_extents e
where
x.hladdr = 'SADDR' and
e.file_id = x.file# and
x.hladdr = l.addr and
x.dbablk between e.block_id and
e.block_id + e.blocks -1
order by x.tch desc ;--library cache latch的诊断
--查看latch信息：
select name,gets,misses,sleeps
from v$latch
where name like 'library%';
--查看latch操作系统进程号
select a.name,pid from v$latch a , V$latchholder b
where a.addr=b.laddr
and a.name = 'library cache%';
--查看
select count(*) number_of_waiters
from v$session_wait w, v$latch l
where w.wait_time = 0
and w.event = 'latch free'
and w.p2 = l.latch#
and l.name like 'library%';