Document 1951729.1.pdf
免费下载
2022/4/6 上午11:37
Document 1951729.1
https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=pmb96jkg2_140&id=1951729.1
1/3
Copyright (c) 2022, Oracle. All rights reserved. Oracle Confidential.
Very High Waits for 'reliable message' After Upgrade to 11.2.0.4 When Using Result Cache (Doc ID 1951729.1)
In this Document
Symptoms
Changes
Cause
Solution
References
APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.4 to 12.1.0.2 [Release 11.2 to 12.1]
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Cloud Schema Service - Version N/A and later
Information in this document applies to any platform.
SYMPTOMS
After upgrade to 11.2.0.4, high waits for Reliable message is seen in AWR reports:
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
Result cache is enabled.
Querying against gv$channel_waits reveal component responsible for high waits:
2022/4/6 上午11:37
Document 1951729.1
https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=pmb96jkg2_140&id=1951729.1
2/3
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
CHANGES
Upgraded to 11.2.0.4.
CAUSE
Issue is caused by bug associated with result cache handling which is fixed in Oracle 12c release 12.2:
Bug 19557279 - EXCESSIVELY HIGH RELIABLE MESSAGE <==closed as duplicate to following bug:
Bug 18416368 - HANG OBSERVED DURING DMLS
SOLUTION
1. Upgrade to a higher version where the issue is fixed i.e. Oracle 12c release 12.2 or the 12.1.0.2.0 Patchset
2. Apply patch 18416368 if available on your platform
3. To workaround the issue, disable result cache by setting the following parameter:
SQL> alter system set result_cache_max_size=0;
of 3
免费下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
评论