SQLチューニングの基盤となる統計情報：Oracle SQLチューニング講座（5）（2/4 ページ）

[亀田明裕，株式会社アゲハ]

EVENT 10046でのSQLトレースの取得

　ドキュメントには記載されていませんが、EVENTと呼ばれるデバッグ用の機能により、SQLトレースで取得できる情報に加えて、より詳細な情報を取得することも可能です。EVENT 10046には表3のようなレベルがあり、通常のSQLトレースによって作成されるトレースファイルには含まれない、バインド変数の値や待機イベントを確認できます。通常のSQLトレースに比べ、非常に多くの情報がファイルに出力されるため、ディスクの空き容量に十分注意してください。

　なお、EVENTはOracleの内部動作変更や、デバッグなどに使用されるものであり、正式にサポートされるものではありません。使用する場合は、自己責任において使用してください。

レベル	内容
LEVEL 1	SQL_TRACE機能と同等
LEVEL 4	LEVEL 1の情報に追加して、バインド変数情報が出力される
LEVEL 8	LEVEL 1の情報に追加して、待機イベント情報が出力される
LEVEL 12	LEVEL 1の情報に追加して、バインド変数情報、待機イベント情報が出力される
表3　EVENT 10046のレベル

SQL> ALTER SESSION SET EVENTS '10046 trace name context  ⇒forever, level 12'; SQL> +++ チューニング対象SQLの実行 +++ SQL> ALTER SESSION SET EVENTS '10046 trace name context  ⇒off';

EVENT 10046の使用例 （注：⇒記号は表示の都合で折り返していることを表します）

　デフォルトのSQLトレースには含まれないバインド変数の値を確認する場合、LEVEL 4もしくはLEVEL 12で取得したSQLトレースをTKPROFユーティリティで整形せずに、直接参照します。以下の例は、LEVEL4でバインド変数を確認した結果です。

PARSING IN CURSOR #2 len=57 dep=1 uid=35 oct=3 lid=35 ⇒tim=1071814928612333 hv=1198499521 ad='58cdd7d0' SELECT * FROM orders WHERE o_orderkey=:b2 AND o_clerk=:b1 END OF STMT PARSE #2:c=10000,e=8105,p=0,cr=2,cu=0,mis=1,r=0,dep=1,og=0, ⇒tim=1071814928612320 BINDS #2:  bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=13   ⇒oacfl2=1 size=24 offset=0    bfp=405e2a4c bln=22 avl=02 flg=05    value=1  bind 1: dty=1 mxl=2000(1000) mal=00 scl=00 pre=00 acfl2=1  ⇒oacflg=13 size=2000 offset=0    bfp=405e2224 bln=2000 avl=15 flg=05    value="Clerk#000000951" EXEC #2:c=0,e=1637,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4, tim=1071814928614269 FETCH #2:c=1720000,e=1933973,p=23248,cr=24063,cu=0,mis=0, ⇒r=1,dep=1,og=4,tim=1071814930548284

リスト1　EVENT 10046 LEVEL 4の出力例（クリックすると別ウィンドウで表示します） （注：⇒記号は表示の都合で折り返していることを表します）

　今回のSQLは、「CURSOR #2」で実行されていることから、バインド変数の値は、その直後にある「BINDS #2」になります。ここでは、「value=1」「value="Clerk#000000951"」が指定されていたことが確認できます。

別セッションのSQLトレース、EVENT 10046のSQLトレース取得手順

　先ほどは自セッションのSQLトレースを取得する方法を説明しましたが、アプリケーションを変更できない場合や、すでに実行中のアプリケーションのSQLトレースを取得したい場合も多いかと思います。そのような場合、ほかのセッションに対して、SQLトレースやEVENT10046をセットすることも可能です。

　ただし、取得できるのは、トレース取得開始時点以降の情報となります。そのため、すでに実行中であったSQLに関しては、読み取りブロック数などの値が不正確となりますので、注意してください。以下に別セッションのSQLトレースを取得する例を示します。

1. sysdba権限を持つユーザーでログインする

$ sqlplus '/ as sysdba' SQL>

2. トレース取得対象セッションのSID、SERIAL#をV$SESSION動的パフォーマンスビューで確認する（クリックすると別ウィンドウで表示します）

SQL> SELECT sid,serial#,username,program,machine,status, ⇒last_call_et FROM v$session   2  WHERE username='SCOTT';   SID    SERIAL# USERNAME     PROGRAM                      ⇒      MACHINE      STATUS   LAST_CALL_ET ----- ---------- ------------ ------------------------------ ⇒--- ------------ -------- ------------    10         12 SCOTT        sqlplus@linux006 (TNS V1-V3)  ⇒    linux006     INACTIVE            2

（注：⇒記号は表示の都合で折り返していることを表します）

　ここでは、SCOTTユーザーを指定しています。

3. SQLトレースを設定する

SQL> EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(10,12, ⇒TRUE);

（注：⇒記号は表示の都合で折り返していることを表します）

　ここでは、SID、SERIAL#、TRUEを指定して、上記のプロシージャを実行します。

4. SQLトレースの設定を解除する

SQL> EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(10,12, ⇒FALSE);

（注：⇒記号は表示の都合で折り返していることを表します）

　なおトレースファイルは、自セッションのSQLトレースを取得したときと同じディレクトリ下に作成されます。

　別セッションに対して、EVENT 10046のSQLトレースを設定する場合には、手順の3.と4.で以下のプロシージャを実行します。

3. EVENT 10046をLEVEL 12に設定する

SQL> EXECUTE DBMS_SYSTEM.SET_EV(10,12,10046,12,'');

4. EVENT 10046の設定を解除する

SQL> EXECUTE DBMS_SYSTEM.SET_EV(10,12,10046,0,'');