AWR报告中Top 10 Foreground Events存在”reliable message”等待事件的处理办法
操作系统版本:hp-unix b.11.31
数据库版本:11.2.0.4 rac
(一) 问题概要
(1)在awr报告的top 10 foreground events中发现reliable message占用了较高的db time,如下:
top 10 foreground events by total wait time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
total wait % db
event waits time avg(ms) time wait class
------------------------------ ------------ ---- ------- ------ ----------
reliable message 34,293,326 1430 42 64.1 other
db cpu 265 11.9
enq: tx - row lock contention 2,556,859 114k 45 5.1 application
px deq: signal ack rsg 102,595 101k 985 4.5 other
log file sync 6,458,803 86.5 13 3.9 commit
cursor: pin s wait on x 184,770 34.8 189 1.6 concurrent
(2)result cache被使用
sql> show parameter result_cache_max_size name type value ------------------------------------ ----------- ------------------------------ result_cache_max_size big integer 5248k
(3)查询给gv$chanel_waits显示高等待的组件
select channel, sum(wait_count) sum_wait_count from gv$channel_waits group by channel order by sum(wait_count) desc; channel wait count ------------------------------------------------------ -------------- result cache: channel 307718423 kxfp control signal channel 97080 rbr channel 60807 mmon remote action broadcast channel 28256 obj broadcast channel 14736 kill job broadcast - broadcast channel 367 parameters to cluster db instances - broadcast channel 15 broker iq result 5 service operations - broadcast channel 4 quiesce channel 2
(二)原因
如果满足上面的3个条件,那么可以确定该等待事件由result cache处理相关的bug_19557279造成,该问题在oracle 12.2版本中修复。
(三)解决方案
在11g中如何解决,oracle提供了3种方案:
1.更新数据库版本到12cr2;
2.应用补丁18416368;
3.禁用结果缓存,需要重启数据库实例生效
sql> alter system set result_cache_max_size=0;
参考文档:mos上文档id :1951729.1
【完】