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

Oracle RDBMSなどのオラクル製品や各種インフラ技術(OS、ストレージ、ネットワーク)といった話題を取り上げます。著者は小田圭二、「門外不出のOracle現場ワザ」、「絵で見てわかるOracleの仕組み」、「絵で見てわかるOS/ストレージ/ネットワーク」などの著作もあります

ホーム > スポンサー広告 > 私が使うSQL文の捕まえ方&実行計画取得方法(後編)ホーム > DBA > 私が使うSQL文の捕まえ方&実行計画取得方法(後編)

スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。
[ --/--/-- --:-- ] スポンサー広告 | トラックバック(-) | コメント(-)

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

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

●その4 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の数が増える代わりに、情報取得が重くなります。

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

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

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

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

※手順1と2は初回のみです。2回目以降は、手順3と4を繰り返すだけです。

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

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

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

・SQL分析側 その2
さきほど実行した分析対象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分析側 その3
さきほど実行した分析対象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トレースを使用しなくても、このように手軽に確認することができます。

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

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

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

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

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

v$sessionを連続して記録することにより、どのアプリが、何時何分何秒に、どんな理由で、どのSQLが遅延したのかが、かなりの確率で判ります。ただし、v$sessionでは当時のSQLの情報が判りません。そこで、定期的に取得している「レベル6以上の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月号の特集2を書きました。「14のアンチパターンに学ぶ データベースの開発/運用」です。軽い気持ちで読める、よくあるダメなトラブルを紹介した20ページあまりの記事です。データが多いフルスキャンのダメさ、インデックスが多い場合のトラブルはどうなるか、正規化をしないトラブルはどうなるか、などが手軽に読めます。

書き手は、意外と読み手がどう思ったのか気にしているものですが、実は私も同じです。もしよかったら、読者はがきが、ブログにコメントください。続きを書くかどうかなどの参考にさせてもらいたいと思っています。
スポンサーサイト
[ 2008/10/27 00:39 ] DBA | TB(0) | CM(0)
コメントの投稿













管理者にだけ表示を許可する
プロフィール

odakeiji

Author:odakeiji
小田圭二 日本オラクルのテクノロジーソリューションコンサルティング統括本部においてデータベースのコンサルタントをしている。今までのキャリアでは、社内教育部隊で、データベースやOS、ネットワークを教える経験を5年ほど積んだり、コンサル部門で主にDB(インフラ含む)のコンサルを10年程度経験した。また、コンサルタントとして、主に大規模ミッションクリティカルシステムを担当。社内では”火消し”とも呼ばれ、システムトラブルの火消しをいくつも担当していたこともある。
ポリシーは、「OracleもOS上で動くアプリケーションにすぎない。だから、OS、ストレージ、ネットワークを学ぶべき。アーキテクチャから考えろ」。
スキル面の興味は、アーキテクチャ、DBA、インフラ技術、教育、コンサル手法など。
本ブログのポリシーは「週に1回、DBAやインフラ担当者の役に立つ記事を書きたい」です(守れるだけ、守りたい・・・・)
なお、本ブログにおいて示されている見解は、私自身の見解であって、オラクルの見解を必ずしも反映したものではありません。ご了承ください。

私の主な著書の紹介です。もしよかったら、お役立てください。他にもオライリーなどがあります

●「絵で見てわかるOS/ストレージ/ネットワーク データベースはこう使っている」小田圭二 著
私のポリシーである”DBMSもOSの動くアプリケーションに過ぎない”に基づいて、OSとDBMSの関係、ストレージとDBMSの関係、ネットワークとDBMSの関係、を解説した珍しい書籍です。DBを学んでひと段落したら、DB使いもインフラ全体を意識しなければなりませんが、そのような人にお勧めです。企業ユーザー向けのIT本としては、2008年度翔泳社No1だとか(最後は出版社談)。

●「絵で見てわかるOracleの仕組み」 小田圭二 著
教育に携わる者としての私の思い「丸暗記するな。アーキテクチャを知るべき。絵で説明すべき」を具体化した、Oracleの入門書です。Oracle初心者向きですが、Oracleの基礎となる部分の動きを解説しているため、バージョンに依存せずに何年先でも使えます。逆に、本書の内容を理解せずに、ひたすら丸暗記すると応用力が身につきません。この本を読むだけで何かできるようになるわけではありませんが、アーキテクチャを身につけて、本当の技術力を身につける第一歩として欲しいと思っています。

●「44のアンチパターンに学ぶDBシステム」 小田圭二 著
本書は、企業のDBシステムの設計/構築から運用管理、プロジェクト管理までの各フェーズにおけるトラブル(失敗)事例について、アンチパターン(べからず集)とその回避策/防止策として解説するものです。チェックリストとして使っていただいても構いません。分かっていてもアンチパターンは避けられないことも多いものです(政治とか)。そういう方には、同じ仲間は多いのだなと再認識していただくための一服の清涼剤としていただければと思います。

●「門外不出のOracle現場ワザ」 小田圭二 他 著
一番最初に出た本です。結構とがった内容を扱っています。
・パフォーマンス分析の考え方(私の担当)
・性能テストや障害テストの仕方、設計の注意点(主に私が執筆)
・コストベースオプティマイザ(10gベース)のアーキテクチャ
・コネクションプーリング
最新のOracleの内容は含んでいませんが、今でも性能の考え方やオプティマイザの考え方は使えるはずです。オプティマイザをここまで解説している本を私は知りません。

●「続・門外不出のOracle現場ワザ」 小田圭二 他 著
「続」の名前の通り、次に出た本です。ちょっと尖り過ぎたかもしれません^^; でも本当に使う内容を選んだつもりです。一流になりたい・他の人と差をつけたい人にお勧めでしょうか。
・性能の良いSQLの書き方
・文字化けの仕組み
・障害(特に性能やハング)の分析・対応方法(私が執筆)
・障害をリアルタイムに分析・対処する方法(私が執筆)
・オプティマイザの使い方ノウハウ
・アップグレードのノウハウ

●「データベース」小田圭二 他 著
私にしては堅い本です。なんせ、共同執筆者が大御所の國友義久先生です。階層型DBMS、ネットワーク型DBMS、リレーショナル型DBMS、XMLDBMS、OO(オブジェクト指向)DBMS、DBMSの持つ機能、DBMSのセキュリティ、データベースの著作権、監査、モデリング、正規化といった内容を網羅しており、深い記述は無いものの、DB技術全体を抑えるのに向いている一冊です。ある程度技術力がついたエンジニアの方が、DB全体を振り返りたい(勉強したい)というときの最初の1冊としてお勧めです。



上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。