Oracle10g AWRに関する検証 その2

<Oracle10g AWR(Automatic Workload Repository)に関する検証 その2>
~自動SQLチューニング機能編~
ペンネーム:ちょびひげ

前回は、AWR(Automatic Workload Repository)のディスク上の情報がパー
ティションで管理されていることを確認しました。ここでのディスク上の情
報とはOracleの再起動を行なっても残っている情報を指します。

今回はこの情報量を管理する方法とログ取得時のパフォーマンスを簡単に見
ていきたいと思います。

システムのパフォーマンスに困っている読者はずっと気になっていたかもし
れませんが、これほど多くのログ情報を取得してパフォーマンスには影響し
ないのでしょうか?検証環境(RACの2ノード構成) で単純に領域サイズ的な
面だけを見ると、SYSAUX表領域のデータファイルのサイズはインストール時
に比較して、約6倍の710MBにもなっています。

□SYSAUX表領域の作成時(インストール時)のサイズと現在のサイズを取得

SQL> select t.name, d.name
  2  , d.bytes/1024/1024 CURRENT_TIME_MB
  3  , d.create_bytes/1024/1024 CREATED_TIME_MB
  4  from v$tablespace t, v$datafile d
  5  where  t.ts#=d.ts#
  6* and t.name='SYSAUX'

NAME     NAME                                CURRENT_TIME_MB CREATED_TIME_MB
-------- ----------------------------------- --------------- ---------------
SYSAUX   +DG01/ora10/datafile/sysaux.261.1               710             120

負荷の高い(セッション数が多い、オブジェクト数が多い等)環境では更に
大きな領域が必要となります。

いずれにしても、710MBでも小規模の環境では、結構大きなサイズです。この
情報の取得の有無の制御は以下のいくつかの方法で可能になっています。

■メモリ上の統計情報自体の取得を制御する場合
パラメータの設定により統計情報の取得の有無が設定可能となっています。

□以下の初期化パラメータを設定します。

statistics_level
  BASIC   - 統計情報の取得をOFFにする
  TYPYCAL - 一般的に必要な一部の情報を取得
  ALL     - 取得可能な統計の全てを取得

■ディスク上の情報の取得(保持期間)を制御する場合
メモリ上の統計情報を取得している場合は、メモリ上の情報をディスクに書
き込む頻度が設定可能となっています。

□DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGSプロシージャを使用

・RETENTIONでデータの保持期限を指定
・ITERVALで統計の差分を取得する間隔を指定
※分単位

検証環境では統計の保持期限が7日間で取得間隔が60(1時間)です。

SQL> desc MODIFY_SNAPSHOT_SETTINGS
  PROCEDURE MODIFY_SNAPSHOT_SETTINGS
  引数名          タイプ      In/Out Default?
  --------------- ----------- ------ --------
  RETENTION       NUMBER      IN     DEFAULT
  INTERVAL        NUMBER      IN     DEFAULT
  DBID            NUMBER      IN     DEFAULT

□保持期限を3日、取得間隔を10分に設定する場合は、以下のコマンドを実行

SQL> exec dbms_workload_repository.modify_snapshot_settings(4320,10,3847444778);

4320 = 3[日]*24[時間]*60[分]

□情報を取得ない場合は、INTERVALを0に設定

SQL> exec dbms_workload_repository.modify_snapshot_settings(4320,0,3847444778);

□現在の設定をdba_hist_wr_controlより確認

SQL> select * from dba_hist_wr_control;

       DBID SNAP_INTERVAL                  RETENTION
----------- ------------------------------ ------------------------------
 3847444778 +40150 00:00:00.0              +00003 00:00:00.0
                      ↑                       ↑
                      0なので取得しない        3日分の情報を保持

以上の2つの設定を行なうことにより、システムの要件にあった頻度でログ情
報を取得して、後からログ情報の分析を行う事になります。

ちなみに一回の集計でどの程度の処理時間がかかるのでしょうか?SQLのトレ
ースを取得するコマンドで実行時間を計測してみました。実行時間は環境に
よってまちまちですが、殆ど負荷の無い検証環境で実行した場合でも17秒程
かかっています。ちなみにRAC構成ですのでノード数分の統計情報が作成され
ます。以下、計測結果です。

□SQLトレースの設定(ON)

SQL> alter session set events='10046 trace name context forever, level 12';

□手動でスナップショットを作成

SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT('TYPICAL');

□SQLトレースの設定(OFF)

SQL> alter session set events='10046 trace name context off';

□user_dump_destに作成されたトレースファイルを確認

~~ 実行時間の殆どがリカーシブコールで、その該当部分を抜粋 ~~

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count     cpu    elapsed   disk   query    current    rows
------- ------  ------ ---------- ------ ------- ----------  ------
Parse     2216    0.88       1.49     13    1504          6       0
Execute   5414    6.70      13.67    243   17166       7996    2928
Fetch     9183    0.78       2.06    103   18736         40   12423
------- ------  ------ ---------- ------ ------- ----------  ------
total    16813    8.36      17.24    359   37406       8042   15351
                             ↑【これが実行時間で約17秒】

Misses in library cache during parse: 257
Misses in library cache during execute: 175
  ~~ リカーシブコールの待ち時間 ~~

Elapsed times include waiting on following events:
  Event waited on                 Times   Max. Wait  Total Waited
  ----------------------------   Waited  ----------  ------------
  library cache lock                426        0.03          0.30
  library cache pin                 252        0.76          1.60
  row cache lock                   1695        0.11          0.78
  gc cr block 2-way                  94        0.00          0.10
  rdbms ipc reply                   104        0.01          0.05
  ksfd: async disk IO               367        0.00          0.00
  db file sequential read           318        0.00          0.02
  gc cr grant 2-way                  53        0.00          0.01
  gc current block 2-way            126        0.18          0.38
  control file sequential read       49        0.00          0.00
  enq: TM - contention               56        0.01          0.02
  gc current grant busy              51        0.00          0.01
【省略】

検証環境では思っていたほどの負荷では有りませんが、アクティビティの高
いシステムでは大分負荷が高くなる事が予想されます。

情報を取得することによるメリット、デメリットを考えてこのあたりの設定
を行なう必要がありそうです。

以上、最近白バイによる取締りが多い茅ヶ崎にて