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

时间:2021-02-17 21:43:40

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

【完】