Oracle SQLチューニング講座（5）

SQLチューニングの基盤となる統計情報　Page 2

株式会社アゲハ
亀田 明裕
2004/10/21

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ユーティリティで整形せずに、直接参照します。以下の例は、LEVEL 4でバインド変数を確認した結果です。

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,'');

　こちらもSQLトレースと同じように忘れずに設定を解除します。

4. EVENT 10046の設定を解除する
SQL> EXECUTE DBMS_SYSTEM.SET_EV(10,12,10046,0,'');

（次ページへ続く）

2/4

　Index
連載 Oracle SQLチューニング講座（5）
SQLチューニングの基盤となる統計情報
  Page 1
・SQL詳細情報の取得
・SQLトレース、TKPROFユーティリティの使用方法
　−SQLトレース、TKPROFユーティリティの実行手順
Page 2
　−EVENT 10046でのSQLトレースの取得
　−別セッションのSQLトレース、EVENT 10046のSQLトレース取得手順
  Page 3
・トレースファイルの注目ポイント
　−SQL実行時の実行計画の見方
　−TKPROF実行時の実行計画の見方
　−SQLの分析
  Page 4
・SQL*PlusのAUTOTRACE機能
　−AUTOTRACE機能の設定方法
　−AUTOTRACE機能の使用方法
・V$SQL_PLANでの実行計画確認
　−V$SQL_PLANからの実行計画確認方法


連載　Oracle SQLチューニング講座

TechTargetジャパン

Database Expert フォーラム 新着記事

＠ITメールマガジン　新着情報やスタッフのコラムがメールで届きます（無料）

RSSフィード

＠IT 新着記事

キャリアアップ

- PR -
＠IT Sepcial
→ ＠IT Special ヘ

イベントカレンダー

PickUpイベント

- PR -
もっと見る
- PR -

お勧め求人情報

ホワイトペーパー（TechTargetジャパン）

＠IT Sepcial
→ ＠IT Special ヘ
ソリューションFLASH