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 hh24:mi:ss') start_snap_time,

end_snap_id,

to_char(end_snap_time, 'yyyy-mm-dd hh24: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 hh24: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 hh24:mi:ss') begin_time,

min(snap_id) begin_id,

to_char(max(begin_interval_time), 'yyyy-mm-dd hh24: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統計資訊。