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

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

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

スポンサーサイト

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

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

※いただいたコメントを採用させていただきました。「 and a.sql_child_number = b.child_number」を追加しています。


今回は、私がよく使う、実行されたSQL文の捕まえ方、およびSQL文の実行計画取得方法をご紹介します。長いので前編と後編に分けます。今回は前編です。

まず、「v$sql_planから実行計画を表示する」方法を簡単に紹介し、その後、「実行されたSQL文の捕まえ方」を説明します。

●その0 v$sql_planから実行計画を表示する。
どの方法でも大体使える、v$ビューからの実行計画の表示方法です。

V$SQL_PLANの使い方(ハッシュ値とアドレスを指定するバージョン※)
※ハッシュ値とアドレスの解説は、あとで出てきます。
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
where hash_value = &hash_value
and address = '&address'
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;


V$SQL_PLANの使い方(SQL_IDを指定するバージョン※)
※SQL_IDの解説は、あとで出てきます。
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
where sql_id = '&sql_id'
start with id = 0
connect by
(prior id = parent_id
and prior sql_id = sql_id
and prior child_number = child_number
)
order siblings by id, position;



●その1 SQL文のテキストを指定して、v$sqlareaから捕まえる

この方法は、実行されたSQL文のテキストを知っている場合に使います。

"select 1 from test"が実行されたと仮定して、10g以降の場合は次のように入力します。
select SQL_ID from v$sqlarea where sql_text = 'select 1 from test';
9i以前の場合は、
select <ハッシュバリューなど(下記)> from v$sqlarea where sql_text = 'select 1 from test';
と入力します。

v$sqlareaなどで出てくる下記5つの列は、SQLを識別するためのものですが、各々意味があります。
SQL_ID ・・・比較的最近導入されたSQLを一意に識別するIDです。ハッシュ値よりも良いと思います。
HASH_VALUE ・・・古くから使われているIDです。10gから計算方法が変わりました。9iまででは、このHASH_VALUEを使って、statspackと突合せできます(10g以降は、HASH_VALUEではstatspackと突合せできません)。v$sqlareaなどでは、address列と併せてSQLを特定します。
OLD_HASH_VALUE   ・・・10gからできました。10g以降ではstatspackと突合せするときは、このOLD_HASH_VALUEを使用します。ハッシュ値のアルゴリズムが9iまでと同等であるため、OLDと呼ばれています。
PLAN_HASH_VALUE  ・・・実行計画のハッシュ値(ID)です。同じSQL文でも実行計画が異なることがあるため、このハッシュ値が必要なことがあります。
ADDRESS  ・・・HASH_VALUEと併せてSQLを特定します。

このあとは、「その0」で紹介した方法で、実行計画を表示します。

●その2 v$sessionから捕まえる。

これは今流れているSQLを(高い確率で※1)その場で捕まえるSQLです。今まさに遅延しているときなどに便利です
v$sessionのstatus列が、'ACTIVE'な場合、SQLを処理中もしくは
I/Oやロックなどを待機中がほとんどです。その条件に該当する
セッションが実行中のSQLを表示します。

ハッシュ値とアドレスを表示するバージョン
select a.sql_hash_value, a.sql_address, b.sql_fulltext
from v$session a,v$sql b
where a.sql_address = b.address
and a.sql_hash_value = b.hash_value
and a.sql_child_number = b.child_number
and a.username is not null
and a.status = 'ACTIVE'
order by a.username;


SQLIDを表示するバージョン(10g以降でお勧め)
select a.sql_id, b.sql_fulltext
from v$session a,v$sql b
where a.sql_id = b.sql_id
and a.sql_child_number = b.child_number
and a.username is not null
and a.status = 'ACTIVE'
order by a.username;

このあとは、「その0」で紹介した方法で、実行計画を表示します。

※1 where句にACTIVEという条件句を入れていますが、SQLの実行において、一時的にACTIVE以外のステータスになることがあります。そのため、100%ではありません。数回実行することで、ほぼ確実に捕まえることができるはずですし、私は困ったことはありません。
注:SQL文の出力が切れてしまう場合もあります。その場合はv$sqltextから見た方が早いかもしれません。

v$sqltextの例:
select sql_text
from v$sqltext
where sql_id = '&sql_id'
order by piece;

●その3 v$sqlareaから処理の重いものやI/Oの多いものを探す

過去のある程度の期間(※)に実行されたSQLの中から、
”悪玉”と私が呼んでいる、処理が重いSQLを抜き出す方法です。
少し前に実行されたSQL群の中から、重いものを抜き出せるため便利です。

※期間は、Oracleの共有プールから消えてしまうまでです。
 共有プールのサイズが大きければしばらく残っているでしょう(数日でも)。
 なお、インスタンス再起動、DDLやtruncate、共有プールのflushなどを
 行うと即座に消えてしまいます。このv$sqlareaに限らず、
 SQL文を表示するv$ビューは原則的に同じ動き(生存期間)です。

select sql_id,sql_text,
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
where <抽出条件(下記)>


抽出条件の例:
executions != 0 and elapsed_time/executions > 1000000;
executions != 0 and disk_reads/executions > 100;

このあとは、「その0」で紹介した方法で、実行計画を表示します。

v$sqlareaには、その他にも optimizer_cost や direct_writes など
役に立つ情報が満載です。Oracleのリファレンスマニュアルで各列の意味を
調べることができるのでぜひ見てみてください。

長くなったので、今日はここら辺で。次回もSQLの捕まえ方と実行計画の表示の仕方の説明が続きます。
スポンサーサイト
[ 2008/10/23 21:13 ] DBA | TB(0) | CM(4)
のらくろさんのメッセージを代筆します

----------------ここから--------------------

はじめまして、のらくろ(ふ)と申します。
久しぶりに DBAの仕事に戻った関係で小田様の書籍を
最近いくつか購入させて頂きました。
「続~」、「絵で見る Oracle ~」「絵で見て分かる OS ~」を
行き帰りに楽しく読ませて頂いております。

「門外不出~」に記載されていた SQLとシェルの組み合わせを
何年か前に拝見してから非常に便利なので 10g でもと思い
マニュアル片手に SQLをカスタマイズして使用させて頂いていたのですが
ある事に気が付きましたのでコメントさせて頂きました。

> ●その2 v$sessionから捕まえる。
こちらの SQLですが、結合キーに a.sql_child_number = b.child_number も
指定された方が良いかと存じます。

# 指定されないのであれば v$sql の情報が sql_id 毎に集計されている(と思われる)
# v$sqlarea との結合で良いのではないでしょうか。

OTN のマニュアルにハッキリとした記述はありませんが
親カーソルID と 子カーソルとい記述がありますし、下記 SQLで
検索するとかなりの件数がヒットしますので
このキーがないと稀に同じセッションが複数行表示される事があるかと思います。

# そのような現象に出会い、マニュアルにあたりましたが
# ディクショナリのキー情報がなく、少し残念に思います。

SELECT SQL_ID, COUNT(*) FROM V$SQL GROUP BY SQL_ID HAVING COUNT (*) > 1;


以上です。

# 突然の長文失礼致しました m(__)m

----------------ここまで--------------------
[ 2009/07/30 11:29 ] [ 編集 ]
のらくろ様

返事が遅くなり、すみませんでした。

コメントありがとうございます。気がついてもコメントまではしてくれる人は
少ないので、大変助かります。たしかに、ご指摘の現象が起こる可能性があります。

元記事を直しました。ありがとうございました。

> > ●その2 v$sessionから捕まえる。
> こちらの SQLですが、結合キーに a.sql_child_number = b.child_number も
> 指定された方が良いかと存じます。
> # そのような現象に出会い、マニュアルにあたりましたが
> # ディクショナリのキー情報がなく、少し残念に思います。

親と子について多少解説すると、OracleはSQL(含むカーソルや実行計画)を出来る限り、
使いまわそうとします。しかし、変数や諸々の条件の違いにより、使いまわさないという
判断がされることがあり、そのタイミングで子を作ります。そのため、SQL_IDやhash_valueが
同じだけれども、実体が複数になるSQLがあります。
ご指摘の現象はこれに当たっているときに起きるはずです。おっしゃるとおり、記憶によると
v$sqlareaはサマリーした情報のはずで、v$sqlは詳細が出てくるはずなので、
v$sqlだと複数表示されるはずです。

ありがとうございました。また、何かあればコメントください。
[ 2009/07/30 11:31 ] [ 編集 ]
お世話になっております、のらくろと申します。

当方の不注意でメアド記載の投稿をして、
小田様のお手を煩わせてしまい
申し訳ございませんでした。

お忙しいところご対応頂きましてまことに
ありがとうございました。

------------------------------
> 親と子について多少解説すると、OracleはSQL(含むカーソルや実行計画)を出来る限り、
> 使いまわそうとします。しかし、変数や諸々の条件の違いにより、使いまわさないという
> 判断がされることがあり、そのタイミングで子を作ります。

なるほど....
9iR2 以降暫く離れていたため、10gR2 のマニュアルを見てかなり驚いたのですが
まだまだ理解すべきことは多いと感じました。

それにしても Oracle7 の頃に比べるとなんと便利になったことでしょうか。
当時は紙のマニュアルが普通で、ネットの情報もなかった(私は探せていませんでした)のに比べると
OTN の豊富なマニュアルをネットから参照出来るだけでも天国のようです (^^;;;;


すこしづつこちらのブログ記事を
読ませて頂いております。
もし何かお伝えできる事がございましたら
コメントさせて頂きたく存じます。

# Database リファレンスのディクショナリ情報に
# キー情報があればいいのにな、とは思います。


以上です。
[ 2009/07/30 17:27 ] [ 編集 ]
> それにしても Oracle7 の頃に比べるとなんと便利になったことでしょうか。
> 当時は紙のマニュアルが普通で、ネットの情報もなかった(私は探せていませんでした)のに比べると
> OTN の豊富なマニュアルをネットから参照出来るだけでも天国のようです (^^;;;;

たしかに昔はマニュアルが売り物でした。そのボリュームに圧倒されたものでした。
それに比べると便利になりました。情報もインターネットにいろいろ転がっていますし。

またコメントください。
[ 2009/08/01 23:27 ] [ 編集 ]
コメントの投稿













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

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ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。