联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在一次给客户的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.
参考：Very High Waits for ‘reliable message’ After Upgrade to 11.2.0.4 When Using Result Cache (Doc ID 1951729.1)