Oracle 关于latch_free事件

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%';