AWR收集緩慢、掛起的幾種常見情況分析

AWR （ Automatic Workload Repository ）作為對資料庫效能診斷的工具，採集與效能相關的統計資料，根據這些統計資料中的效能指標，以跟蹤潛在的問題。若因某些情況導致相關資料無法收集，就會對資料庫效能診斷大打折扣。

 

以下列舉 AWR 收集緩慢、掛起或缺失常見的幾種情況：

• STATISTICS_LEVEL  引數不為 ALL 或 TYPICAL

• SYSAUX 表空間不足

• 系統資源 I/O 、 CPU 使用率過高

• MMON/MMNL 程式異常

• 相關 FIXED TABLE 統計資訊不準確

 

1、STATISTICS_LEVEL  引數不為 ALL 或 TYPICAL

初始化引數 STATISTICS_LEVEL ， AWR 的採集資訊受到引數 STATISTICS_LEVEL 的影響。這個引數有三個值：

BASIC ： AWR 統計資訊的關閉，只收集少量的資料庫統計資訊。

TYPICAL ：預設值，只有部分的統計收集，都是需要監控 oracle 資料庫的典型行為。

ALL ：所有可能的統計都被捕捉，並且有作業系統的一些資訊，這個級別的捕捉用的較少，比如要更多的 sql 診斷資訊。

一般不會隨便修改該引數，都使用預設值 TYPICAL ，所以這種情況下導致 AWR 無法收集統計資料的很少的。

 

2、SYSAUX 表空間不足

AWR 採集的統計資料都以 WRM$_*  和  WRH$_* 的格式命名的表儲存在 SYSAUX 表空間上（ M  代表後設資料 (metadata) ，而 H  代表歷史資料  (historical) ）。可透過 @?/rdbms/admin/awrinfo.sql 或 x$kewrtb 查詢相關的表資訊。雖然 因 SYSAUX 表空間不足導致 AWR 無法生成是個低階問題 ，但是有一種情況需要注意，因為 BUG 等導致 ASH/AWR 的基表資料無法清理。如：

SQL> select * from dba_hist_wr_control;      DBID SNAP_INTERVAL        RETENTION            TOPNSQL 
---------- -------------------- -------------------- ---------262389084 +00000 01:00:00.0    +00007 00:00:00.0    DEFAULT

正常的每小時產生一個 SNAPSHOT ，保留 7 天。但一些基表如 WRH$_ACTIVE_SESSION_HISTORY 因為某些原因沒有根據 sys.wrm$_wr_control 的設定進行清理。 SNAPSHOT 快照的保留就會超過 7 天，這時會導致 SYSAUX 被撐爆，以及收集 AWR 報告很慢的情況。具體解決辦法 2 個：

1）alter session set “_swrf_test_action”=72;

2) 手工刪除過期無用的快照：

exec dbms_workload_repository.drop_snapshot_range(low_snap_id => xxx, high_snap_id => xxxx, dbid => 262389084);

MOS 文件：

WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy (Doc ID  )

 

3、 系統資源 I/O 、 CPU 使用率過高

當系統負載很高時，許多使用者程式都在爭用資源， AWR 報告的收集需要消耗系統主機的效能，當 awr 報告的收集時間超過 15 分鐘，若這個時候資料庫處於相當繁忙的狀態，   資料庫為了保證業務的正常執行，就自動把 AWR 功能的增強 。這種情況基本如下：

 

alert.log 中會出現如下告警資訊：

Suspending MMON slave action xxx for 82800 seconds

 

或者 mmon trc 中出現如下的告警資訊：

Unable to schedule a MMON slave at: Auto Flush Main 1
  Slave action has been temporarily suspended    - Slave action had prior policy violations.
  Unknown return code: 101

 --可根據參考：If the system is so over-loaded that it takes over 15 minutes to gather statistics or other MMON tasks, this error is expected.It is a functionality enhancement in 11g, as it prevents MMON from locking 
resources those other processes might be waiting for. In 10g , mmon slaves are allowed to run indefinitely.

從日誌看，存在大量的 ， 這是 11g 功能的增強，當系統處於 overload 狀態時， MMON 程式收集統計資訊超過 15 分鐘，則會終止該任務，  10g 會無限延期。所以系統資源不足也會導致 AWR 統計資訊無法正常收集。

 

為什麼是 15 分鐘？請參考 MOS 文件：

Troubleshooting ORA-12751 "cpu time or run time policy violation" Errors ( 文件  ID 761298.1)

Troubleshooting: Missing Automatic Workload Repository (AWR) Snapshots and Other Collection Issues ( 文件  ID 1301503.1)

 

4、MMON/MMNL 程式異常

Memory Monitor(MMON) ： MMON 主要用於 AWR ， ADDM ， MMON 會從 SGA 將統計結果寫到系統表中

The Memory Monitor Light (MMNL) ： mmon 程式主要是記憶體中 sql 資訊， ash 資訊的收集工作，如果這些資訊需要寫入到磁碟（即一些資料字典表）中，那麼就需要 MMNL 程式負責寫入

 

MMON 、 MMNL 和 Mnnn 這些程式用於填充自動工作負載儲存庫（ Automatic WorkloadRepository ， AWR ），這是 Oracle 10g 中新增的一個特性。 MMNL 程式會根據排程從 SGA 將統計結果重新整理輸出至資料庫表。 MMON 程式用於“自動檢測”資料庫效能問題，並實現新增的自調整特性。 Mnnn 程式類似於作業佇列的 Jnnn 或 Qnnn 程式； MMON 程式會請求這些從屬程式代表它完成工作。 由此可見， MMON 和 MMNL 程式異常是 AWR 不能自動收集的根本原因。

遇到 AWR 無法收集的情況可以根據文件（ ID 1301503.1 ）進行排查，檢查 mmon 和 mmnl 程式是否正常。

ps -ef|egrep "mmon|mmnl"  #檢視mmon和mmnl程式是否存oracle    32674      1  0 21:22 ?        00:00:01 ora_mmon_<sid>oracle    32676      1  0 21:22 ?        00:00:01 ora_mmnl_<sid>

這兩個程式是 oracle 的非核心程式，可以 kill 掉，它們會自動啟動程式，並且自動維護。若這兩個程式沒有問題，可以手動生成 AWR 看是否有效：

exec  dbms_workload_repository.create_snapshot();  然後再進一步診斷問題。

因為這兩個程式都非核心程式，所以很多文件都是說可 kill ，重新喚起這個程式，讓 AWR 可繼續生成。在 11.2.0.4 中可能會存在起不來的情況，原因根據 MOS 文件： AWR Snapshots Are Not Being Created Because MMON Is Not Being Respawned ( 文件  ID 2023652.1) 可知：

 

5、FIXED TABLE 統計資訊不準確

 

檢視 mmon  程式的 trace 檔案，出現以下報錯：

** KEWROCISTMTEXEC - encountered error: (ORA-12751: cpu time or
 run time policy violation)
  *** SQLSTR: total-len=295, dump-len=240,
      STR={insert into WRH$_SERVICE_STAT   (snap_id, dbid,
      instance_number,    service_name_hash, stat_id, value) select
      :snap_id, :dbid, :instance_number,   stat.service_name_hash,
      stat.stat_id, stat.value from  v$active_services asvc, v$service_st}
 DDE rules only execution for: ORA-12751

檢視該 SQL 為何執行緩慢，發現 v$service_stats 檢視數量很大。該檢視記錄的是最小的效能統計資料集，其中有個自動 service_name 來著 v$services ，它顯示關於服務的資訊。e xpdp 每次備份開始，都會新增一個 service name ，備份結束後會去掉該 service name ，該動作會記錄在 alert log 中，這個動作就會導致 v$service_stats  檢視出現很多 unknown 的記錄。

 

錯誤的執行計劃：

每次邏輯匯出時，會在 v$service_stats 檢視中增加 service_name=unknow 的記錄，導致 v$service_stats 檢視中累積儲存了大量 unknow service name 的記錄， AWR 快照生成過程中在執行上述 SQL 時，由於 fixed table 統計資訊不準確或者尚無統計資訊， oracle 選擇了效率較低的執行計劃， SQL 的執行消耗大量時間，導致 oracle 維護任務 cpu time policy violation ， AWR 快照生成中斷。

 

解決辦法是：手動收集 fixed table 的統計資訊（在 12 c 之前，固定的統計資料沒有自動收集，它是對所有 X$ 基表統計資訊的收集，這個收集是要相對比較長時間的，同時評估好收集之後對其它 SQL 語句執行的影響。如 V$SESSION 、 V$PROCESS 、 V$LOCK 等常用檢視相關 SQL 語句的執行計劃影響）

 

select table_name,num_rows,last_analyzed from dba_tab_statistics where last_analyzed is not null order by last_analyzed desc;
 
exec dbms_stats.gather_fixed_objects_stats(no_invalidate => false);

 

fixed table 的統計資訊 請參考文件：Fixed Objects Statistics (GATHER_FIXED_OBJECTS_STATS) Considerations (文件 ID 798257.1)