AWR のカスタマイズを試す（Oracle Database 18c）

# 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: 初期化パラメーターの確認
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: スナップショットの手動作成
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: AWR設定の確認
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年）です。
[マニュアル](https://docs.oracle.com/cd/E96517_01/arpls/DBMS_WORKLOAD_REPOSITORY.html#GUID-E2B46878-1BDB-4789-8A21-016A625530F1) には「ZEROを指定した場合、スナップショットは永久に保持されます。」と書かれていますが、文字列 ZERO ではなく、数字の 0 を指定します。
指定する値は SYSTEM_MOVING_WINDOW ベースラインよりも長い時間に指定する必要があります。それよりも短い時間を指定するとエラー ORA-13541 が発生します。

```SQL: 設定エラーの発生
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: 保存期間の設定
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: 取得間隔の設定
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: 出力数の設定
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: イベント数の設定
SQL> EXEC dbms_workload_repository.awr_set_report_thresholds(top_n_events=>20);

PL/SQL procedure successfully completed.
```