操作系統版本: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
【完】