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  387914.1 )

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

當系統負載很高時，許多用戶進程都在爭用資源， AWR 報告的收集需要消耗系統主機的性能，當 awr 報告的收集時間超過 15 分鐘，若這個時候數據庫處于相當繁忙的狀態，   數據庫為了保證業務的正常運行，就自動把 AWR 的功能關閉，減少系統的開銷，這是11g 功能的增強 。這種情況基本如下：

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

 --可根據https://community.oracle.com/thread/2153562參考：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.

從日志看，存在大量的 Slave action has been temporarily suspended ， 這是 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)