Troubleshooting Oracle 19c wait event latch free 39 “object stats modification”

by

近日，一位客户的Oracle 19c（19.18）环境中出现了一些查询堵塞等待较高的“latch free”情况。通过分析AWR报告中的ASH（Active Session History）数据，我们发现某些查询频繁等待“latch free”，并且p2值对应的latch号为39，latch名称为“object stats modification”。

“latch free”等待事件在Oracle 11g之后相对较少见到，通常我们会看到具体的latch名称。 “object stats modification” latches 又是一个较为罕见的等待事件。为了便于后续跟踪和分析，这里记录一下该问题及其相关细节。

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: 修改隐藏参数需谨慎，如有需要联系我。