欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

AWR报告中Top 10 Foreground Events存在”reliable message”等待事件的处理办法

程序员文章站 2022-06-01 07:56:37
操作系统版本: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 ......

操作系统版本: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

【完】