データベースコンサルタントのノウハウちょい見せ私が使うSQL文の捕まえ方＆実行計画取得方法(後編)

私が使うSQL文の捕まえ方＆実行計画取得方法(後編)

前回に引き続いてSQL文の捕まえ方＆実行計画取得方法です。
その０から３は、前編に載せています。

●その４　statspackから捕まえる
　注：snapのレベルは6以上がお勧めですからね！
　　　例：execute statspack.snap(i_snap_level=>6);

　この方法は、過去のある時点のSQLの実行計画を確認するために
　便利な方法です。「昨日遅かった」とか、「前土曜日のバッチが
　遅かった」といった調査において力を発揮します。

　statspackやAWR(有償オプション)によって、過去のある時期の
　DB全体の稼動状況を確認できます。これはよく知られているかと
　思います。
　そのレポート上で(たしか9i以降から)SQLについても負荷が
　高いものなどを確認することができます。

　さらに個別のSQLについての詳細情報も各SQLごとに出力させる
　ことができます。例えば、statspackであれば、SQL*Plus上で、
　@?/rdbms/admin/sprepsql と入力し、
　表示されるガイドに従って、SQLのレポートを出力します。
　snapのレベルがレベル6以上であれば、”当時”の実行計画が
　見られるはずです。

　なお、statspackでは、情報取得の閾値が設けられており、
　デフォルトのままだと、軽いSQLは記録されません。
　テストなどにおいて、できるだけ多くのSQLを記録したい場合には
　snapのオプションで閾値の変更をしましょう。
　方法はマニュアルで確認してください。
　なお、記録されるSQLの数が増える代わりに、情報取得が重くなります。

●その５　SQL*Plusで実行して捕まえる

　開発などにおいては、いくつものSQLをSQL*Plusで実行しながら、
　逐次実行計画を確認することもあります。また、本番で流れている
　SQLが判明したとして、それを手軽に試してみたいこともあるでしょう。
　ヒント句を追加したSQLの実行計画を手軽に確認したいこともあるでしょう。

　SQL*PLUSを２つ立ち上げます。片方を「SQL実行用」として、もう片方を「SQL分析用」として使用します。

手順概要は次のとおりです。
・１ SQL実行側のSQL*PLUSで「SQL実行側その１」を実行して、セッションIDを取得
・２ SQL分析側のSQL*PLUSで「SQL分析側その１」を実行して、分析の準備をする
・３ SQL実行側のSQL*PLUSで「SQL実行側その２」の形式で、実際のSQL文を実行する
・４ SQL分析側のSQL*PLUSで「SQL分析側その２」と「SQL分析側その３」を実行して、
　　情報を確認する

※手順１と２は初回のみです。2回目以降は、手順３と４を繰り返すだけです。

・SQL実行側その１
まず、事前準備としてセッションのIDを調べておきます。
SELECT SYS_CONTEXT('USERENV','SESSIONID') AUDSID from dual;

・SQL実行側その２
セッションのIDを調べた方のSQL＊PlusでSQLを実行します。バインド変数を
用いたSQLの場合でも、次のようにバインド変数を指定することができます。
variable a number
execute :a := xxxxxxx
実行したいSQL文を実行

・SQL分析側その１
分析側のSQL*Plus上で、さきほど調べたセッションのIDを変数にセットします。
variable v_AUDSID number
execute :v_AUDSID := &AUDSID

・SQL分析側その２
さきほど実行した分析対象SQLの実行計画を表示させます。
select PREV_HASH_VALUE HASH_VALUE, PREV_SQL_ADDR SQL_ADDR from v$session
where AUDSID = :v_AUDSID;
column id format 999 newline
column operation format a20
column options format a15
column object_name format a22 trunc
column optimizer format a3 trunc
select id
, lpad (' ', depth) || operation operation
, options
, object_name
, optimizer
, cost
from v$sql_plan, v$session
where hash_value = PREV_HASH_VALUE
and address = PREV_SQL_ADDR
and AUDSID = :v_AUDSID
start with id = 0
connect by
(prior id = parent_id
and prior hash_value = hash_value
and prior child_number = child_number
)
order siblings by id, position;

・SQL分析側その３
さきほど実行した分析対象SQLの各種統計情報を表示します。
select elapsed_time/executions/1000000 "elapsed_time(sec)",
cpu_time/executions/1000000 "cpu_time(sec)",
rows_processed/executions "rows_processed(rows)",
buffer_gets/executions "buffer_gets(blocks)",
disk_reads/executions "disk_reads(blocks)",
sorts/executions "sorts(times)",
first_load_time, executions
from v$sqlarea, v$session
where hash_value = PREV_HASH_VALUE
and address = PREV_SQL_ADDR
and AUDSID = :v_AUDSID;

SQLトレースを使用しなくても、このように手軽に確認することができます。

●その６　SQLトレースから確認

メジャーな方法であるSQLトレースです。そのため、方法は割愛します。注意しなければいけないのが、9i以降ではrow source operationと表示されている部分を確認することです。execution planは当時の実際の実行計画ではないかもしれないためです。この注意点は絶対忘れないようにしてください。

●その７　v$sessionとStatspackなどの併せワザ

最後が、過去のトラブルをもっとも良く分析できる(と私が思う)方法です。

ご存知の方もいらっしゃるかと思いますが、私はv$sessionを数秒おきに実行して、アクティブなセッションの情報を残しておく方法をお勧めしています(ミッションクリティカルシステムでは)。v$sessionのHASH_VALUEやSQL_IDやOLD_HASH_VALUE、ADDRESSなども記録します。

v$sessionを連続して記録することにより、どのアプリが、何時何分何秒に、どんな理由で、どのSQLが遅延したのかが、かなりの確率で判ります。ただし、v$sessionでは当時のSQLの情報が判りません。そこで、定期的に取得している「レベル６以上のstatspack」の登場です。v$sessionの情報から、SQLを特定するIDを調べ、上記sprepsqlで調べます。

これで、かなりの情報が得られるはずです。statspackのみでは、ある期間のレポートとなるため、SQLを特定できないことがあります。また、TOPいくつかのSQLしかレポートに表示されません。実は、statspackレポートに表示されていなくても、SQLの情報はDBに記録されていることは多いのです。そういったSQLを特定するためにもv$sessionを活用し、SQLを特定できたあとは、sprepsqlで情報を表示するというコンビネーションがとても有効です。

●おまけ

statspackを定期的にとっていると、情報がDBにどんどん溜まります。sppurge.sqlを使って、負荷の低く、かつ、スナップを取っていない時間に定期的に削除しましょう。削除処理は重いためです。

削除しても、stats$sqltext などの周辺テーブルの情報が増えていく場合、10g以降では、i_extended_purge パラメータを TRUE に設定して削除しましょう。9i以前では、サポートに問い合わせ(もしくはKROWN情報から調査)してみましょう。

長い時間(6秒以上)かかったSQL文を捕まえるビューv$session_longops や、v$sql_workarea という大きな作業領域(メモリなど)を使用したSQLを調査できるビューもあります。興味のある方は調べてみてください。

●告知させてください。

つい先日発売になったDBマガジン12月号の特集２を書きました。「１４のアンチパターンに学ぶデータベースの開発/運用」です。軽い気持ちで読める、よくあるダメなトラブルを紹介した20ページあまりの記事です。データが多いフルスキャンのダメさ、インデックスが多い場合のトラブルはどうなるか、正規化をしないトラブルはどうなるか、などが手軽に読めます。

書き手は、意外と読み手がどう思ったのか気にしているものですが、実は私も同じです。もしよかったら、読者はがきが、ブログにコメントください。続きを書くかどうかなどの参考にさせてもらいたいと思っています。