ANBOB™

近日有一套客户为oracle 19c（19.18）的环境，从ASH中可以发现一些查询堵塞等待较高的”latch free”， p2值latch#=39, latch name为 “object stats modification”，latch free从11g后较为不常见，多数都是具体的latch name, 可见这是一个比较稀缺的latch，记录一下这个问题。

with latch_free as (
   select p2 
   from v$session_wait_history
   where event = 'latch free' 
)
select l.latch#,l.name,count(*) 
from latch_free lf
    ,v$latch l
where latch# =lf.p2
group by l.latch#,l.name;
/

or 
select *
from v$latchname
where latch# = <p2 value>

或p1查看latch address，对应v$latch_parent/v$latch_children.addr 列，

@dec <p1 value>
@la p1 hex value

V$LATCHHOLDER 查看latch holder.

or Poder's latchprofx script
@latchprofx sid,name,func,hmode % % 1000000

通过上面的方法不难定位是”object stats modification”，MOS中先确认是否有相关bug？ 不难定位到Encountered ‘Latch Free’ Wait Event, ‘Object Stats Modification’ Upgrade from 12.1 to 19c (Doc ID 2778826.1)

这个等待主要是在AWR刷新segment statistics时，在OTN上也有人提问这个问题， 这其实是12.1.0.2中引入的一个bug，因为未公开也没解决，一直持续到19c, 后期版本19c中可能会出相应的oneoff patch。 当前临时的解决方法是禁用object statistics

ALTER SYSTEM SET "_object statistics"=FALSE;

配置参数后会影响AWR中Segment statistics信息为空，但不影响日常业务.

Main Report

• Report Summary
• Wait Events Statistics
• SQL Statistics
• Instance Activity Statistics
• IO Stats
• Buffer Pool Statistics
• Advisory Statistics
• Wait Statistics
• Undo Statistics
• Latch Statistics
• Segment Statistics
• Dictionary Cache Statistics
• Library Cache Statistics
• Memory Statistics
• Replication Statistics (GoldenGate, XStream)
• Advanced Queuing
• Resource Limit Statistics
• Shared Server Statistics
• Initialization Parameters
• Active Session History (ASH) Report
• ADDM Reports

— Note: 修改隐藏参数需谨慎，如有需要联系我。

打赏

微信扫一扫，打赏作者吧～