Oracle10g AWRに関する検証　その３

＜Oracle10g AWR(Automatic Workload Repository)に関する検証　その３＞
～自動SQLチューニング機能編～
ペンネーム：ちょびひげ

前回はOracleの自動管理機能を実現する為の統計情報（AWR – Automatic Wor
kload Repository）を制御する方法と、情報取得時の負荷を見ました。

今回から実際にSQLチューニング機能を使用してどのようにAWRの情報を取得
しているのかを見ていきたいと思います。

SQLチューニングには以下の2つのステップが考えられます。

(1)負荷の高いSQL文の取得
(2)自動SQLチューニング

上記のどちらもOracle 10gの機能を使用して行う事が可能になっていますが、
今回の検証では(1)の負荷の高いSQLの取得に関して、どのようにSQL文を取得
するのかを見ていきます。

負荷の高いSQL文の特定を行なう為には、v$active_session_historyを利用し
ます。

■v$active_session_historyとは？
文字通り、アクティブ(ACTIVE)なセッション(SESION)の履歴(HISTORY)が入っ
ています。セッション単位に「待ちイベント」や「待ち時間」、「SQLのID」
等の情報が入っており、Oracleが定期的（デフォルトでは1秒毎）にアクティ
ブなセッションのみを、記録しています。ここでのポイントは、以下の2点。

・1秒に１回取得
・アクティブなセッションの情報を取得

メモリ上にこれだけの情報があれば、何とか負荷の高いSQLの特定は出来そう
です。それではどのくらいの期間の情報がメモリ上に残っているのでしょう
か？ちょっとメモリ上に残っている情報の時間を見てみましょう。

□最も古いデータと最新のデータのサンプリング時間を取得

SQL> select max(SAMPLE_TIME) , min(SAMPLE_TIME) from v$active_session_history;

MAX(SAMPLE_TIME)           MIN(SAMPLE_TIME)
-------------------------- --------------------------
04-08-02 21:57:23.460      04-08-02 20:37:22.173

これを見ると20:37～21:57までなので約80分の情報を保持しています。ちな
みにSAMPLE_TIME列は9i から追加されたデータタイプで１秒以下の単位の情
報を保持しています。サンプリング間隔を制御する初期化パラメータ
(_ash_sampling_interval)のデフォルト値は1000ですが、これを100に変更す
ると、1秒間に10回のサンプリングが可能となります。

それでは、80分以上前のデータはどうなるかというと、前回まで見てきた通
り”dba_hist_active_sess_history”に書き込まれます。

書き込み（ディスクへフラッシュ）を行なっているSQLが気になります。実は
前回ディスクへのフラッシュを手動で実施して実行時間を測定した際に、ディ
スクへのフラッシュを行なうSQL文を取得することが出来ました。

□詳細は前回の内容を参照

SQL> alter session set events='10046 trace name context forever, level 12';
SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT('TYPICAL');
SQL> alter session set events='10046 trace name context off';

□上記でudump配下に出力されたトレースファイルより、メモリからディスク
にフラッッシュするSQL文を抽出

INSERT /*+ APPEND */ INTO WRH$_ACTIVE_SESSION_HISTORY
( snap_id, dbid, instance_number, sample_id,    sample_time
, session_id, session_serial#, user_id, sql_id, sql_child_number
, sql_plan_hash_value, service_hash,    session_type, sql_opcode
, qc_session_id, qc_instance_id,    current_obj#, current_file#
, current_block#, event_id, seq#, p1, p2, p3, wait_time, time_waited
, program, module, action, client_id )  (
SELECT
:snap_id
,:dbid
,:instance_number
                 ＜省略＞
FROM   x$ash a, (SELECT h.sample_addr, h.sample_id
                 FROM   x$kewash h
                 WHERE  ( (h.sample_id >= :begin_flushing) and
                          (h.sample_id <  :latest_sample_id) )
                  and (MOD(h.sample_id, :disk_filter_ratio) = 0)
                ) shdr      ↑↑↑↑↑↑↑↑↑【ここに注目！】
WHERE shdr.sample_addr = a.sample_addr
and shdr.sample_id   = a.sample_id)

v$active_session_historyの元表は以下の2つです。

・x$bh
・x$kewash

x$kewashはx$ash(ashはActive Session Historyの略)の情報を管理する表の
ようです。

■全ての情報はディスクに書きこまれない
INSERT文のFROM句の複文が興味深いですね。よく見るとSQLをみると、x$ash
の全てのデータをフラッシュするのではなく、sample_idをある数字
(:disk_filter_ratio)で割った余りが0のデータだけをWRH$_ACTIVE_SESSION_
HISTORYにINSERTしています。

この:disk_filter_ratioも初期化パラメータ(_ash_disk_filter_ratio)で設
定可能になっており、デフォルトでは10になっています。つまり、メモリ上
のデータの10分の1のデータしかディスクには書き込まれないわけです。

システム全体の傾向を知るには問題はありませんが、詳細な調査を行なうと
いうわけには行かないようです。情報はメモリ上にあるうちに取得しろ！と
言う事ですね。

以上、めがねのレンズとフレームの間に入ったねり梅が気になる茅ヶ崎にて

Oracle10g AWRに関する検証 その３