Oracle10g AWRに関する検証 その２
＜Oracle10g AWR(Automatic Workload Repository)に関する検証 その２＞
～自動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で統計の差分を取得する間隔を指定
※分単位
検証環境では統計の保持期限が７日間で取得間隔が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 【省略】
検証環境では思っていたほどの負荷では有りませんが、アクティビティの高
いシステムでは大分負荷が高くなる事が予想されます。
情報を取得することによるメリット、デメリットを考えてこのあたりの設定
を行なう必要がありそうです。
以上、最近白バイによる取締りが多い茅ヶ崎にて