実行統計の取得方法について　SQLIDを取得～SQLを抽出～実行統計取得(バインド変数付きのSQLにも対応)

どうも。

「実行統計」

ってご存知ですか？

Oracleで性能が出ないーって時にとりあえず実行計画を調べるってことをすると思います。

簡単にいうと実行計画よりも実行統計の方が情報量が多いって感じです。

詳しいことは「実行統計　実行計画　違い」とでもググると出てくるので見てください。

チューニングに関しては、知識と経験とセンスが必要です。

自分はまだ３年目なのでその辺はあんまりなんですが、チューニングをしてもらうにあたって準備できることがあります。

それが、実行統計の取得です。

システム運用やテストをしていて

(1)画面などでこういった条件で検索した時が遅い

(2)バッチが遅い

(3)なんか全体的に遅い

など調査することがあると思います。

実行統計を取得するには、実際に流れているSQLを調べる必要があります。

という訳で、Oracleから実際に流れているSQLを抜き出して実行統計を取得するまでの手順を記載します。

※Oracle11gR2でやっている内容なので他のバージョンだと出来ないかもしれません

Contents

1 SQLIDの特定
2 SQLIDからSQLを抜き出す
- 2.1 バインド変数取得方法
3 実行統計の取得
4 おわりに

SQLIDの特定

兎にも角にもSQLIDを特定する必要があります。

画面からこういった条件で検索した時が遅いといった場合には、今流れているSQLをキャッチすることができます。

例えば、webアプリに問題の検索条件をセットし、検索ボタンを画面で押下した瞬間に以下のSQLを投げます。

流れ切ると引っ張ってこれないのでぱぱっと投げてください。

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;

もし、どういう条件で流したか分からないけど、なんか遅いといった場合にはここ最近流れたSQLから遅いものを抜き出すことができます。

各々の条件については「v$sql 格納されている内容」とでも入れてググッてください。

--処理の長いSQL
SELECT *
FROM (SELECT MODULE,
PARSING_SCHEMA_NAME ,
SQL_ID ,
dbms_lob.substr(sql_fulltext, 3000, 1),
dbms_lob.substr(sql_fulltext, 3000, 3001),
dbms_lob.substr(sql_fulltext, 3000, 6001),
dbms_lob.substr(sql_fulltext, 3000, 9001),
dbms_lob.substr(sql_fulltext, 3000, 12001),
address,
hash_value,
parse_calls,
executions,
buffer_gets,
disk_reads,
buffer_gets / executions buffer_per_run,
disk_reads / executions disk_per_run,
round(cpu_time / 1000000, 2) as cpu_sec,
TRUNC(round(elapsed_time / 1000000, 2) / executions) as ela_sec,
LAST_ACTIVE_TIME
FROM v$sql
WHERE executions > 0
AND PARSING_SCHEMA_NAME = 'スキーマ名'
--AND MODULE = 'JDBC Thin Client'
ORDER BY LAST_ACTIVE_TIME desc)
--ORDER BY ela_sec desc)
WHERE rownum <= 20;

で、無事にSQLIDを取得できればそのIDからSQL文を抜き出しますので、SQLの抽出に進みます。

SQLIDからSQLを抜き出す

SQLIDからSQLを抜き出すには以下のSQLを投げます。

--SQLIDからSQLを抜く
select s.PIECE,s.SQL_ID,s.HASH_VALUE,s.ADDRESS,s.SQL_TEXT
from V$SQLTEXT s
where s.SQL_ID = 'SQLIDをセットする'
order by PIECE

これでSQL_TEXTに流れていたSQLが抽出されます。

長いSQLの場合複数カラムに割れるので、一旦秀丸やサクラエディタなど正規表現で置換できるエディタに貼り付けます。

秀丸であればこの状態からCtrl+Rで置換ウィンドウを出して

正規表現にチェックを入れて、検索に「\n」と入力し置換側には何もセットせずに全置換をクリックします。

すると改行コードがとれて一本のSQLになります。

※SQL整形できるものであれば改行コードありでもいい感じにされると思いますのでご自由にしてください。

で、後々調べる時に楽になりますので抜き出したSQLの頭にコメントを入れておきます。

例　抜き出したSQLが

SELECT * from TABLE_A

であれば

SELECT /*調査対象*/ * from TABLE_A

みたいにします。

SQLが特定できればようやく実行統計の取得に移ります。

ただし、ここで取得したSQLがバインド変数付きの場合はバインド変数にセットされるであろう値を調べる必要があります。

手打ちでセットしてもいいのですが30個など多いと打つのも面倒です。

なので、Oracleからバインド変数を抜き出します。

バインド変数取得方法

バインド変数を取得するにはSQLIDが必要になります。

既に１番目でSQLIDは特定済みなので問題なしですね。

以下のSQLを投げます。

--バインド変数調べる
select name,value_STRING from v$sql_bind_capture where sql_id='SQLIDを入れます';

ただ、必ずその時流れたバインド変数が抜ける訳じゃないらしいので一応目視でチェックしてください。

で、そのバインド変数をSQLにセットする方法なんですが、「osqledit」というツールであれば複数行一気にペーストすることができます。

シェアウェアですが、送金してねウィンドウが上がるだけで無料でも使うことできますので導入をおすすめします。

個人的にこのツールは超おすすめですので購入も検討してください。

ただ、64bitだとinstanseClientの導入などが必要になります。

「64bit osqledit」とかでググッてください。

さて、これで流すSQLは完成しましたので実行統計を取得します。