标签归档：result_cache_max_size

reliable message等待

发表于 2018 年 1 月 27 日由惜分飞

在一次给客户的rac poc测试中，发现大量reliable message等待，严重影响性能
数据库版本
11.2.0.4+最新psu(180116)

SYS@xffdb1>select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

Elapsed: 00:00:00.00
SYS@xffdb1>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[oracle@xifenfei01 ~]$ opatch lspatches
26609929;OCW Patch Set Update : 11.2.0.4.170814 (26609929)
26925576;Database Patch Set Update : 11.2.0.4.180116 (26925576)

OPatch succeeded.

监控em发现大量other等待

查询发现大量reliable message等待

SYS@xffdb1>@event

         INST_ID USERNAME                       EVENT                                                      COUNT(*)
---------------- ------------------------------ -------------------------------------------------- ----------------
               1 SOE                            reliable message                                                 16
               1 SOE                            gc current request                                                4
               1 SOE                            gc cr request                                                     5
               1 SOE                            db file sequential read                                          17
               1 SYS                            db file scattered read                                            1
               2 SOE                            library cache: mutex X                                            2
               2 SOE                            latch: ges resource hash list                                     2
               2 SOE                            gc current request                                                1
               2 SOE                            gc cr request                                                     4
               2 SOE                            db file sequential read                                          12
               2                                db file parallel write                                            1
               2 SOE                            SQL*Net message to client                                         1

12 rows selected.

进一步查询发现大量Result Cache: Channel

SYS@xffdb1>SELECT CHANNEL,
  2    SUM(wait_count) sum_wait_count
  3  FROM GV$CHANNEL_WAITS
  4  GROUP BY CHANNEL
  5  ORDER BY SUM(wait_count) DESC;

CHANNEL                                                            SUM_WAIT_COUNT
---------------------------------------------------------------- ----------------
Result Cache: Channel                                                   379367698
RBR channel                                                                128968
kxfp control signal channel                                                115091
MMON remote action broadcast channel                                         4646
obj broadcast channel                                                         395
LCK0 ksbxic channel                                                            17
parameters to cluster db instances - broadcast channel                          4
service operations - broadcast channel                                          3
kill job broadcast - broadcast channel                                          2

查看result_cache_max_size参数

SYS@xffdb1>show parameter result_cache_max_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_size                big integer 335552K

调整result_cache_max_size参数

SYS@xffdb1>alter system set result_cache_max_size=0;

System altered.

SYS@xffdb1>show parameter result_cache_max_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_size                big integer 0

重启数据库，再次测试，发现相关等待消失，系统也更加稳定

SYS@xffdb1>@event

         INST_ID USERNAME                       EVENT                                                      COUNT(*)
---------------- ------------------------------ -------------------------------------------------- ----------------
               1 SOE                            library cache: mutex X                                            1
               1 SOE                            gc current request                                                3
               1 DBSNMP                         gc cr request                                                     1
               1 SOE                            gc cr request                                                     7
               1 SOE                            db file sequential read                                          18
               1                                db file parallel write                                            1
               2 SOE                            library cache: mutex X                                            2
               2 SOE                            latch free                                                        1
               2 SOE                            gc current request                                                4
               2 SOE                            gc cr request                                                     4
               2 SOE                            db file sequential read                                          12
               2                                db file parallel write                                            3

12 rows selected.