Automatic Workload Repository (AWR)
AWR (Automatic Workload Management) はOracle Database のパフォーマンス情報を自動的に蓄積する機能で、Oracle Database 10g から提供されています。それ以前のバージョンでは STASPACK と呼ばれる機能を使用していました。AWR は STATSPACK と同様にインスタンスの特定時点のパフォーマンス情報をスナップショットとして保存し、２つの時点の差分からレポートを作成することができます。
この機能を利用するためには、Diagnostics Packライセンスが必要で、初期化パラメーターcontrol_management_pack_accessにDIAGNOSTICSまたは、DIAGNOSTIC+TUNING（デフォルト値）を指定する必要があります。
SQL> SHOW PARAMETER control_management_pack_access
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access string DIAGNOSTIC+TUNING
スナップショットで取得する情報の変更
スナップショットは、特定時点のインスタンス上のパフォーマンス指標の集合体です。どのような値を取得するかは、初期化パラメーター statistics_level で決まります。
デフォルト値は TYPICAL で、ほとんどの場合この値を使用します。BASICに指定するとAWRスナップショットは取得されなくなります。ALL に指定すると時刻に関するオペレーティング・システムの統計と、実行計画統計が追加されます。
スナップショットは自動的に作成されますが、手動でも作成することができます。DBMS_WORKLOAD_REPOSITORY パッケージの CREATE_SNAPSHOT ファンクション／プロシージャを実行します。
flush_level パラメーターには、スナップショットのレベルを指定することができます。デフォルト値は BESTFIT です。
下記の例では ALL を指定してスナップショットを手動で作成しています。
SQL> EXEC dbms_workload_repository.create_snapshot(flush_level=>'ALL');
PL/SQL procedure successfully completed.
スナップショットの一覧は、DBA_HIST_SNAPSHOT ビューを検索することで確認できます。このビューには SNAP_LEVEL 列が存在し、スナップショットのレベルを確認できます。DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOTプロシージャのflush_levelパラメーターで指定した文字列と、DBA_HIST_SNAPSHOTビューのSNAP_LEVEL 列の対比は以下の通りです。
| flush_level パラメーター | snap_level 列の値 | 備考 |
|---|---|---|
| BESTFIT | statistics_levelに依存 | デフォルト |
| LITE | 11 | |
| TYPICAL | 1 | |
| ALL | 2 |
スナップショットの設定変更
スナップショットの変更はDBMS_WORKLOAD_REPOSITORYパッケージのMODIFY_SNAPSHOT_SETTINGSプロシージャを実行します。変更したい項目のパラメーター名を指定して値を変更します。
パラメーターを省略した場合は NULL 値とみなされ現状の値が維持されます。
現在のセッティングを確認するためにはDBA_HIST_WR_CONTROLビューを検索します。
SQL> SELECT * FROM DBA_HIST_WR_CONTROL;
DBID SNAP_INTERVAL RETENTION TOPNSQL CON_ID SRC_DBID SRC_DBNAME
---------- ------------------------- ------------------------- ---------- ---------- ---------- --------------------------------
4103976400 +00000 00:30:00.0 +00060 00:00:00.0 DEFAULT 0 4103976400
保存期間
AWRスナップショットの保存期間のデフォルト値は retention パラメーターを分単位で指定します。最小値は1,440（1日）で、最長は52,560,000（100年）です。
マニュアル には「ZEROを指定した場合、スナップショットは永久に保持されます。」と書かれていますが、文字列 ZERO ではなく、数字の 0 を指定します。
指定する値はSYSTEM_MOVING_WINDOWベースラインよりも長い時間に指定する必要があります。それよりも短い時間を指定するとエラー ORA-13541 が発生します。
SQL> EXEC dbms_workload_repository.modify_snapshot_settings (retention=>1440);
BEGIN dbms_workload_repository.modify_snapshot_settings (retention=>1440); END;
*
ERROR at line 1:
ORA-13541: system moving window baseline size (691200) greater than retention
(86400)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 215
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 263
ORA-06512: at line 1
下記の例ではスナップショットの保存期間を60日に指定しています。
SQL> EXEC dbms_workload_repository.modify_snapshot_settings (retention=>86400);
PL/SQL procedure successfully completed.
取得間隔
AWRスナップショットのデフォルトは取得間隔は1時間（=60分）です。この間隔を変更するためには、interval パラメーターに間隔を分単位で指定します。
指定できる値は 10（MIN_INTERVAL）～52,560,000（MAX_INTERVAL=１年）です。
下記の例では30分間隔でスナップショットを取得するように指定しています。
SQL> EXEC dbms_workload_repository.modify_snapshot_settings (interval=>30);
PL/SQL procedure successfully completed.
SQL出力数
負荷が高いSQL文（Top N SQL）の取得数を指定する場合にはtopnsqlパラメーターを変更します。数値では30～50,000の値を指定できます。文字列で指定する場合は以下の値を指定できます。
| 指定値 | 説明 | 備考 |
|---|---|---|
| DEFAULT | 初期化パラメーターstatistics_levelに合わせて変化します | デフォルト値 |
| MAXIMUM | カーソル・キャッシュ内の全SQLを取得します | |
| 数字 | 特定の値を指定します |
下記の例ではTop N SQL の個数をデフォルト値に設定しています。
SQL> EXEC dbms_workload_repository.modify_snapshot_settings (topnsql=>'DEFAULT');
PL/SQL procedure successfully completed.
レポートの変更
AWRレポートに出力される要素の個数を変更するためにはDBMS_WORKLOAD_REPOSITORYパッケージのAWR_SET_REPORT_THRESHOLDSプロシージャを実行します。変更したい値のパラメーター名に値を指定します。
| パラメーター名 | 説明（一部マニュアルからコピー） |
|---|---|
| top_n_events | 最も影響が大きい待機イベント数 |
| top_n_files | 最もアクティブなデータファイル数 |
| top_n_segments | 最もアクティブなセグメント数 |
| top_n_services | 最もアクティブなサービス数 |
| top_n_sql | 最も負荷が高いSQL文の数 |
| top_n_sql_max | SQL文のアクティビティがtop_sql_pctで指定されたものより大きい場合のSQL数 |
| top_sql_pct | top_n_sqlとtop_n_sql_maxの間のSQL文の重要度しきい値 |
| shmem_threshold | 共有メモリーのしきい値 |
| versions_threshold | 実行計画のバージョン数の低しきい値 |
| top_n_disks | 最大I/Oのセル・ディスクの数 |
| outlier_pct | Exadataセクションの外れ値を表示する前の最大容量のパーセント |
| outlier_cpu_pct | 外れ値を表示するためのCPUの平均パーセントのしきい値 |
以下の例では、イベントの出力数を変更しています。
SQL> EXEC dbms_workload_repository.awr_set_report_thresholds(top_n_events=>20);
PL/SQL procedure successfully completed.