Awr報告停止收集的解決方法

一、錯誤資訊

資料庫的alert日誌裡面顯示了以下報錯：

二、出現的問題平臺

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later  Information in this document applies to any platform.

該問題在oracle 11.2.0.3和以後的版本中都會出現；

三、症狀

• Manual snapshot generation is slow, often running for 30 minute or more.（手工執行awr報告的時候超過30分鐘）
• Alert log shows messages similar to:   

  'Suspending MMON slave action kewrmrfsa_ for 82800 seconds'

• AWR stops generating snapshots automatically.（awr報告停止自動執行）
• M00x trace file shows that the following SQL is taking a long time.

  UPDATE wrh$_datafile dfh SET (snap_id, filename, tsname) = (SELECT /*+ordered use_nl(f) index(f) index(ts) */ :lah_snap_id, v.name name,ts.name tsname FROM v$dbfile v, file$ f, ts$ ts WHERE f.file# = v.file# AND f.status$ = 2 AND f.ts# = ts.ts#   AND f.file# = dfh.file# AND (f.crscnbas + (f.crscnwrp * power(2,32))) = dfh.creation_change#) WHERE (file#, creation_change#) IN (SELECT f.file#, f.crscnbas + (f.crscnwrp * power(2,32))creation_change# FROM file$ f WHERE f.status$ = 2) AND dbid = :dbid AND snap_id < :snap_id

四、原因

The symptoms shown are caused by a particular UPDATE statement exceeding the time quota for the MMON slave task. This is fixed by:

Bug 16041961AWR SNAPSHOTS NOT BEING GENERATED AFTER UPGRADE TO 11.2.0.3

由一個特定的update語句執行的時間過長導致的；

五、解決方法

The preferred option is to apply the patch for  AWR SNAPSHOTS NOT BEING GENERATED AFTER UPGRADE TO 11.2.0.3

There is also a workaround of skipping the flushing of the problem table. That can be achieved by using the _AWR_DISABLED_FLUSH_TABLES parameter against the ''WRH$_DATAFILE' ' table:

SQL> alter system set "_awr_disabled_flush_tables"='WRH$_DATAFILE' ;

修改隱藏引數；

The impact of using the workaround is that AWR won't show newly added data files (that is, files added after the workaround was enabled), and any data files that were moved or renamed will show their old names. When a permanent fix/patch is applied, one can disable the workaround using the following command:

修改該引數會導致awr報告不能顯示新增加的資料檔案或者資料檔案修改名稱後還是顯示老的檔名，處理方法是把該隱藏引數恢復預設；

SQL> alter system set "_awr_disabled_flush_tables"='';

At that point, subsequent snapshots will run both the insert and the update of WRH$_DATAFILE.

六、該效能引數的設定

Awr報告的收集需要消耗主機的效能，當awr報告的收集時間超過30分鐘後，說明這個時候資料庫的效能已經很慢了，資料庫處於相當繁忙的狀態；

資料庫為了保證業務的正常執行，就自動把awr的功能關閉，減少系統的開銷；

從一個軟體的架構師和設計人員來說這樣是合理的。（以上純屬個人猜想如有錯誤，請指出）

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

本文作者：JOHN

ORACLE技術部落格：ORACLE 獵人筆記               資料庫技術群：367875324 （請備註ORACLE管理 ）  

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++