AWR、ASH、ADDM和顧問程序

一、自動工作負荷知識庫(AWR)

Oracle收集大量有關性能和活動的統計信息，這些信息在內存中累積，并定期寫入到稱之為自動工作負荷知識庫(AWR)的表中。AWR作為數據庫SYSAUX表空間的一組表和其他對象而存在，并存在于SYSMAN模式中。

統計信息的收集級別由實例參數statistics_level控制，該參數可以設置為BASIC、TYPICAL(默認)、ALL：

show parameter statistics_level;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

statistics_level string TYPICAL

TYPICAL收集正常調整所需的所有統計信息，同時不會收集對性能有不利影響的統計信息。BASIC幾乎禁用收集統計信息，并且不存在可評估的性能優勢。ALL級別會收集與SQL語句執行相關的極其詳細的統計信息，如果進行高級的SQL語句調整，可以使用該級別，但在收集統計信息時對性能稍有影響。

統計信息在SGA內存的數據結構中累積，定期(默認每小時一次)寫入磁盤，也就是寫入AWR，這稱為一次AWR快照。AWR快照的采樣和將統計信息寫入磁盤的操作由后臺進程MMON(可管理性監視器進程)完成。11g默認快照會保留存儲8天，10g默認保留7天。

可以通過視圖v$sysaux_occupants查看sysaux表空間中駐留的組件，可以查看AWR占用的空間大小：

col occupant_name for a30

select occupant_name, occupant_desc, space_usage_kbytes from v$sysaux_occupants where occupant_name='SM/AWR';

OCCUPANT_NAME OCCUPANT_DESC SPACE_USAGE_KBYTES

------------------------------ ---------------------------------------------------------------- ------------------

SM/AWR Server Manageability - Automatic Workload Repository 101120

AWR收集的信息通過一系列視圖展現出來，可以查詢這些視圖獲得數據庫的信息采樣，這些視圖以dba_hist_打頭。而這些視圖的底層表大致有幾類：

WRM$打頭的表存儲AWR的元數據;

WRH$打頭的表存儲采樣快照的歷史數據;

WRI$打頭的表存儲同數據庫建議功能相關的數據;

WRR$打頭的表代表的是Oracle 11g新功能Workload Capture以及Workload Replay的相關信息。

AWR的歷史數據表主要通過分區表形式存儲在SYSAUX表空間中，可以通過dba_tab_partitions視圖進行查詢。

當MMON進程保存AWR快照時，它會根據統計信息自動生成大量的指標。而創建基準必須由DBA完成。基準是快照的一對或多對，將一直保存到專門刪除為止。可以比較從基準派生的指標與當前活動級別派生的指標，從而幫助確定活動和行為中的更改。可以為特定事件和普通運行創建基準。

Database Control在執行操作時需要調用PL/SQL包DBMS_WORKLOAD_REPOSITORY中的過程，這些過程可以調整快照的頻率和持久性，生成即席快照，創建和操作基準，并生成任何兩個快照之間的活動報告。

創建即席快照：

exec dbms_workload_repository.create_snapshot;

設置快照的保留時間和收集的時間間隔(單位分鐘)，保存30天，每半小時收集一次：

exec dbms_workload_repository.modify_snapshot_settings(retention => 43200, interval => 30);

查看快照收集的時間間隔(默認1小時)、保留時間(11g默認8天，10g默認7天)：

col snap_interval for a30

col retention for a30

select dbid, snap_interval, retention from dba_hist_wr_control;

DBID SNAP_INTERVAL RETENTION

---------- ------------------------------ ------------------------------

2001528686 +00000 01:00:00.0 +00008 00:00:00.0

可以為某個運行良好的時段創建基線，以便和其它報告做對比，基線報告不會因過期而刪除：

exec dbms_workload_repository.create_baseline(start_snap_id=>487, end_snap_id=>488, baseline_name=>'FridayPM');

查看AWR基線：

col baseline_name for a30

select dbid,

baseline_id,

baseline_name,

start_snap_id,

to_char(start_snap_time, 'yyyy-mm-dd hh34:mi:ss') start_snap_time,

end_snap_id,

to_char(end_snap_time, 'yyyy-mm-dd hh34:mi:ss') end_snap_time,

creation_time

from dba_hist_baseline;

DBID BASELINE_ID BASELINE_NAME START_SNAP_ID START_SNAP_TIME END_SNAP_ID END_SNAP_TIME CREATION_TIME

---------- ----------- ------------------------------ ------------- ------------------- ----------- ------------------- -------------------

2001528686 1 baseline_214_215 214 2017-02-04 15:00:36 215 2017-02-04 15:41:41 2017-02-04 16:49:19

2001528686 0 SYSTEM_MOVING_WINDOW 155 2017-01-27 18:00:18 216 2017-02-04 17:00:03 2016-07-23 10:05:47

查詢歷史快照：

select dbid,

instance_number,

snap_id,

to_char(begin_interval_time, 'yyyy-mm-dd hh34:mi:ss') begin_interval_time

from dba_hist_snapshot

order by begin_interval_time desc;

DBID INSTANCE_NUMBER SNAP_ID BEGIN_INTERVAL_TIME

---------- --------------- ---------- -------------------

1903404692 1 31179 2017-02-04 15:00:22

1903404692 1 31178 2017-02-04 14:00:09

1903404692 1 31177 2017-02-04 13:00:56

1903404692 1 31176 2017-02-04 12:00:43

1903404692 1 31175 2017-02-04 11:00:30

1903404692 1 31174 2017-02-04 10:00:17

1903404692 1 31173 2017-02-04 09:00:04

1903404692 1 31172 2017-02-04 08:00:51

1903404692 1 31171 2017-02-04 07:00:38

可以看到快照默認每小時保存一次。

查詢AWR歷史快照的數量及其涉及的時間范圍：

select dbid,

instance_number,

to_char(min(begin_interval_time), 'yyyy-mm-dd hh34:mi:ss') begin_time,

min(snap_id) begin_id,

to_char(max(begin_interval_time), 'yyyy-mm-dd hh34:mi:ss') end_time,

max(snap_id) end_id,

count(snap_id) amount

from dba_hist_snapshot

group by dbid, instance_number;

DBID INSTANCE_NUMBER BEGIN_TIME BEGIN_ID END_TIME END_ID AMOUNT

---------- --------------- ------------------- ---------- ------------------- ---------- ----------

1903404692 1 2017-01-27 22:00:21 30994 2017-02-04 15:00:22 31179 186

刪除基線，連同其快照一并刪除：

exec dbms_workload_repository.drop_baseline(baseline_name => 'FridayPM', cascade => true);

刪除快照：

exec dbms_workload_repository.drop_snapshot_range(low_snap_id => 487, high_snap_id => 488);

命令行生成AWR報告：

@?\rdbms\admin\awrrpt.sql

指定要顯示最近幾天的快照、選取用來分析的前后兩個快照的ID、生成的AWR報告文件格式(默認為html)、報告的路徑和文件名，默認的報告文件生成路徑就是執行SQL*Plus所在的當前路徑。

awrrpt.sql腳本實際上是調用了DBMS_WORKLOAD_REPOSITORY包來生成報表的，這個包中主要有兩個函數用于生成報表：

DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT，用于生成TEXT格式報表;

DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML，用于生成HTML格式報表。

例如，通過以下查詢方式也可以生成AWR報告，參數分別為數據庫ID、實例編號、起始快照ID和結束快照ID：

select * from table(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(2896903393, 1, 230, 232));

還可以生成兩個時段的AWR比較報告，便于對比兩個不同時段的性能差異：

@?\rdbms\admin\awrddrpt.sql

Oracle允許將AWR數據導出并遷移到其他數據庫便于以后分析。dbms_swrf_internal.awr_extract可以用來導出數據，awrextr.sql腳本就是用來完成該工作的，而導入工作可以通過dbms_swrf_internal.awr_load和dbms_swrf_internal.move_to_awr過程來完成，awrload.sql腳本用于完成該工作。

另外還有一個關于AWR數據存儲和分布情況的報告，顯示的是AWR自身的使用信息，包括快照信息、SYSAUX空間使用、AWR組件、ASH等信息：

@?\rdbms\admin\awrinfo.sql

還可以生成指定SQL語句的AWR報告，執行時需要提供SQL ID：

@?\rdbms\admin\awrsqrpi.sql

二、活動會話歷史(ASH)

作為AWR的補充，還有一個ASH(Active Session History)，即活動會話歷史。ASH以V$SESSION為基礎，每秒采樣一次，記錄活動會話等待的歷史事件。不活動的會話不會采樣，采樣工作由新引入的后臺進程MMNL來完成，并在ASH Buffers寫滿之后，由該進程將ASH信息篩選過濾后通過直接路徑插入寫出到磁盤，從而最小化對數據庫性能的影響。

MMNL是否將ASH信息寫出磁盤受到一個隱含參數的控制：_ash_disk_write_enable，默認為True。而MMNL對ASH信息寫出的比例則受另一個隱含參數控制：_ash_disk_filter_ratio，默認按10%的比例篩選過濾寫出。

ASH buffers的最小值為1MB，最大值不超過30MB，在SGA內存中記錄數據，期望值是記錄一小時的內容。可以看到這部分內存分配在共享池中：

select * from v$sgastat where name = 'ASH buffers';

POOL NAME BYTES

------------ ---------------------------------------- ----------

shared pool ASH buffers 16252928

ASH功能是否啟用，受一個內部隱藏參數的控制：_ash_enable，該參數為true時才能啟用，默認為true。

ASH的采樣時間同樣受一個內部隱藏參數的控制：_ash_sampling_interval，默認為1000毫秒即一秒鐘采樣一次。

ASH記錄的信息可以通過視圖v$active_session_history來訪問，對于每個活動session，默認每秒采樣一次，每次采樣會在這個視圖中記錄一行信息。該部分內容記錄在內存中，期望值是記錄一個小時的內容。該信息會被定期(每小時一次)刷新到AWR負載庫中，并默認保留一周。內部表wrh$_active_session_history是視圖v$active_session_history在AWR的存儲地。dba_hist_active_sess_history是wrh$_active_session_history和其他幾個視圖的聯合展現，通常可通過該視圖進行AWR歷史數據的訪問。

要生成ASH報告，可在命令行執行以下腳本：

@?\rdbms\admin\ashrpt.sql

ASH報告包括了TOP等待事件、TOP SQL、TOP Sessions等內容，清晰扼要，簡明易懂。但ASH內存記錄數據始終是有限的，為了保存歷史數據，我們需要AWR。

三、數據庫顧問框架

1、自動數據庫診斷監視器(ADDM)

Oracle數據庫預配置了一組顧問，在這些顧問中，首先涉及的是Automatic Database Diagnostic Monitor(自動數據庫診斷監視器，ADDM)。ADDM報告在保存AWR快照時自動生成，只要生成快照，MMON進程就會自動運行ADDM。自動生成的ADDM報告總是會包括當前快照與前一個快照之間的時間段，因此在默認情況下可以訪問每小時的ADDM報告。也可以手動的調用ADDM生成包括任意兩個快照之間時間段的報告。自動快照以及手動的收集快照都會觸發ADDM。

ADDM報告默認在30天后清除。ADDM報告以DB時間作為衡量指標，DB時間包括花費在事務計算上的CPU時間和事務等待上的時間，即DBTime = DB CPU + Waiting Time，ADDM的核心就是減少DBTime，提高數據庫系統的吞吐率。ADDM報告提示了系統存在的各類等待給數據庫造成的時間消耗，并提出一些建議，比較明確直觀。

命令行生成ADDM報告：

@?\rdbms\admin\addmrpt.sql

在這其中指定前后兩個快照的ID、生成的ADDM報告的路徑和文件名，報告是擴展名為LST的文本格式文件。

2、其它顧問程序

許多情況下，ADDM報告會建議運行一個或多個其他顧問。與ADDM相比，這些顧問能給出更準確的診斷信息和建議。這些顧問包括：

內存顧問

SQL訪問、調整和修復顧問

自動撤銷顧問

平均恢復時間顧問

數據恢復顧問

段顧問

3、自動維護作業

Oracle11g默認在數據庫創建后，將在AutoTask系統中配置三項任務。這三項自動任務是：

收集優化器統計信息

運行段顧問

運行SQL調整顧問

AotoTask在調度程序的維護窗口中運行，默認方式下，維護窗口從工作日的22點開始運行4個小時，而在周六和周日，從早上6點開始運行20個小時。調度程序與資源管理器相關聯，在維護窗口期間激活的資源管理器計劃默認確保分配給AutoTask作業的計算機資源不超過總量的25%，以避免對其他工作造成負面影響。維護窗口的時間范圍和最大資源使用量可以根據需要做調整。

要使任何AutoTask運行，必須將STATISTICS_LEVEL參數設置為TYPICAL(默認值)或ALL，設置為BASIC時是不會運行的。

段顧問任務依賴于通過日常運行的優化器統計收集任務構建的對象統計信息歷史。SQL調整顧問依賴于MMON進程收集的AWR統計信息。