Visual Explain 情報の取得(SQL パフォーマンス・モニターの結果から)

Visual Explain の情報は、いちいち画面で確認しないでも SQL を使ってプログラム的に取得することができます。

より効率的に SQL チューニングを進めていくのに助けになりそうなので、ちょっと紹介しておきます。


SQL をチューニングするような際、インデックスを追加する前後に毎回「実行および Explain」などで Visual Explain を起動して、そのSQL の実行時間を確認したりしていませんか?

↓の例で、右側のペインにある "Optimazation Time"、"Statement Open Time"、"Statement Fetch Time"、"Statement Close Time"のそれぞれが、この SQL(下側のペインに表示されています)の最適化/オープン/フェッチ/クローズの時間になります。
SQL を変更したり、インデックスを追加したりして、このそれぞれの時間がどう変わるかを見るわけですね。

V5R4/V6R1 から「SQL スクリプトの実行」のメニューから SQL パフォーマンス・モニターを起動し、その画面から実行した SQL をモニタリングすることができるようになっています。
(ちなみに以下の例は V6R1 での実行例です)

ここで開始した SQL パフォーマンス・モニターのモニタリングは、この「SQL スクリプトの実行」セッションで実行された SQL のみが(デフォルトでは)対象になります。

たとえば、バッチ的に、インデックス追加前に関連テーブルについての SQL をすべて実行してしまって、さらに同じ SQL を追加後にまたすべて実行する、というように実行すれば、いちいち毎回「実行および Explain」をするよりは効率的ですね。
インデックス追加前と後で SQL パフォーマンス・モニターを別にして、結果を別のテーブルに保管しておけば、後で見返すような時にも都合がよさそうです。

SQL パフォーマンス・モニターには任意の名前をつけられますが、保管先となる実際のテーブルの名前は↓のように、システムによってつけられた名前になっています。

このテーブルに対して SQL を実行することで、Visual Explain で見ることのできる、SQL の最適化/オープン/フェッチ/クローズの時間を取得することができるわけですね。

↑で見たテーブル名を FROM 句に指定します。

実行された SQL について、いろんな情報が記録されます。

記録された情報のタイプは QQRID というカラムの値から確認することができます。

行の QQRID が、1000 のものが「SQL 情報」、3010 は「ホスト変数と ODP 実装」、3014 は「照会情報」、3019 は「検索された行」になるので、それぞれを条件選択した表を自己結合してやることで、SQL 毎に情報を集約して見ることができるわけです。(QQRID が 3010 の行については、ホスト変数がない SQL の場合は存在しないことがありますので、外部結合を利用する必要があります)

/* V6R1 */

SELECT 
     T.QQUCNT as Statement_ID, 
     T.QQC182 as Statement_Name, 
     T.QQC181 as Cursor_Name, 
     T.QQC21 as Statement_Operation,   
     T.QQ1000L as Statement_Text_Long, 
     V.QQ1000 as Host_Variable_Values, 
     (Q.QQI5 * 1000) as Optimize_Microseconds, 
     CASE WHEN T.QQC21 = 'OP' THEN Microsecond(T.QQETIM - T.QQSTIM) ELSE NULL END as Open_Microseconds, 
     CASE WHEN T.QQC21 = 'FE' THEN Microsecond(T.QQETIM - T.QQSTIM) ELSE NULL END as Fetch_Microseconds,
     CASE WHEN T.QQC21 = 'CL' THEN Microsecond(T.QQETIM - T.QQSTIM) ELSE NULL END as Close_Microseconds 

FROM ((QGPL.QZG0000178 T LEFT OUTER JOIN QGPL.QZG0000178 V ON T.QQUCNT=V.QQUCNT AND T.QQRID=1000 AND V.QQRID=3010) 
                                    JOIN QGPL.QZG0000178 Q ON T.QQUCNT=Q.QQUCNT AND T.QQRID=1000 AND Q.QQRID=3014) 
                                    JOIN QGPL.QZG0000178 R ON T.QQUCNT=R.QQUCNT AND T.QQRID=1000 AND R.QQRID=3019 

WHERE T.QQC21 in ('OP' ,'FE', 'CL') AND R.QQI2 <> 0 

ORDER BY STATEMENT_ID, Host_Variable_Values, T.QQSTIM, T.QQC21 DESC;

実行結果は↓のようになります。

サンプルとして実行した SQL の Visual Explain です。

QQRID が 1000 の行では、QQC21 というカラムには「ステートメント操作」ということで"オープン"や"フェッチ"、"クローズ"といった SQL 実行の状況が記録されています。
その行に開始時間と終了時間が記録されているので、その"オープン"や"フェッチ"、"クローズ"のそれぞれの時間を↑のSQL では計算して出しています。

だいたい Visual Explain の該当部分と同じような時間になっているのがわかりますね。

両方ともフェッチの時間に 4000 マイクロ秒程度の差があります。これが何を示しているのかは正直よくわからないのですが、まぁ実用上問題ないのではないかと思います。

V5R4 での考慮点

V5R4 で実行してみたら、↓のようにフェッチの時間とクローズの時間が同じになってしまいました。

実際の Visual Explain 情報を見てみると、クローズ時間はあっていますが、フェッチの時間は …

結論から言うと、V5R4 では取ってくる先を変更する必要がありました。

CASE WHEN T.QQC21 = 'FE' THEN Microsecond(T.QQETIM - T.QQSTIM) ELSE NULL END as Fetch_Microseconds,

を↓のように変更すると、Visual Explain の情報とだいたい同じ結果が出てくるようになりました。

CASE WHEN T.QQC21 = 'FE' THEN (R.QQI2 * 1000) ELSE NULL END as Fetch_Microseconds,

いちおう全文を載せておきます。

/* V5R4 */

SELECT 
     T.QQUCNT as Statement_ID, 
     T.QQC182 as Statement_Name, 
     T.QQC181 as Cursor_Name, 
     T.QQC21 as Statement_Operation,   
     T.QQ1000L as Statement_Text_Long, 
     V.QQ1000 as Host_Variable_Values, 
     (Q.QQI5 * 1000) as Optimize_Microseconds, 
     CASE WHEN T.QQC21 = 'OP' THEN Microsecond(T.QQETIM - T.QQSTIM) ELSE NULL END as Open_Microseconds, 
     CASE WHEN T.QQC21 = 'FE' THEN (R.QQI2 * 1000) ELSE NULL END as Fetch_Microseconds,
     CASE WHEN T.QQC21 = 'CL' THEN Microsecond(T.QQETIM - T.QQSTIM) ELSE NULL END as Close_Microseconds 

FROM ((QGPL.QZG0000178 T LEFT OUTER JOIN QGPL.QZG0000178 V ON T.QQUCNT=V.QQUCNT AND T.QQRID=1000 AND V.QQRID=3010) 
                                    JOIN QGPL.QZG0000178 Q ON T.QQUCNT=Q.QQUCNT AND T.QQRID=1000 AND Q.QQRID=3014) 
                                    JOIN QGPL.QZG0000178 R ON T.QQUCNT=R.QQUCNT AND T.QQRID=1000 AND R.QQRID=3019 

WHERE T.QQC21 in ('OP' ,'FE', 'CL') AND R.QQI2 <> 0 

ORDER BY STATEMENT_ID, Host_Variable_Values, T.QQSTIM, T.QQC21 DESC;

[Top Pageに戻る]

Ads by TOK2