近日有一套客户为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: 修改隐藏参数需谨慎，如有需要联系我。