Database Support Blog

Database Support Blog>SQLトレースの取得方法まとめ(ケース別)

  • Oracle Database
2016.06.30

SQLトレースの取得方法まとめ(ケース別)

SQLトレース

「SQLの結果が返るのに時間がかかる」、「バッチ処理が遅くなった」など、弊社サポートセンターには処理パフォーマンスの低下に関するお問い合わせを年間で200件以上いただきます。しかし、その原因は実行計画の変化、I/O競合、ロック競合など様々考えられるため、特定は容易ではありません。

パフォーマンス低下の原因を調べるために、SQLトレースから調査をすることがあります。SQLトレースは実行したSQLの詳細な情報をトレースファイルに出力しますが、出力量が多いため、適切な方法で調査対象とするSQLの情報をピンポイントで取得する必要があります。

今回は複数あるSQLトレースの取得方法を、ケース別に紹介します。

SQLトレースとは

SQLトレース はSQLのパフォーマンス情報を文単位で出力します。各フェーズ(Parse/Execute/Fetch)でかかった時間や、実行計画、待機イベントの情報などが確認できるためパフォーマンス低下の調査に有用です。

トレースファイルに出力される情報(生トレース)は内部的なハッシュ値やタイムスタンプなどが出力されておりそのままでは見辛いものですが、ツール( TKPROF )を使用することで可読性の高い情報になります。

TKPROFで整形前のSQLトレース

▲TKPROFで整形前 のSQLトレース

TKPROFで整形後のSQLトレース

▲TKPROFで整形後 のSQLトレース

SQLトレースの取得方法4つ

SQL*Plusから実行可能な処理に対して取得(ALTER SESSION)

SQL*Plusからパフォーマンス低下が発生しているSQL文の実行が可能なケースでは、ALTER SESSIONでSQLトレースを取得します。処理を実行するユーザには"ALTER SESSION"権限が必要です。

##処理を実行するユーザに接続
SQL> conn scott/tiger
 
##SQLトレースを利用可能に変更(待機イベント、バインド変数、適応出力)
SQL> ALTER SESSION SET TIMED_STATISTICS=TRUE;
SQL> ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED;
SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER='SQLTRACE';
SQL> ALTER SESSION SET EVENTS 'sql_trace wait=true,bind=true,plan_stat=adaptive';
 
##調査対象の処理を実行
 
##SQLトレースの取得を解除
SQL> ALTER SESSION SET EVENTS 'sql_trace off'; 

アプリケーションからのみ実行可能/DBLINKを使用している処理に対して取得(ログオントリガー)

アプリケーションから対象のSQLを実行していてALTER SESSIONを埋め込むように改修ができない、あるいはDBLINKを使用していてリモートノードのSQLトレースを取得したいケースではログオントリガーを使用します。

こちらも処理を実行するユーザには"ALTER SESSION"権限が必要です。 この際、GRANT ALTER SESSION TO <ユーザ名>;で直接権限を与える必要があることに注意が必要です(ロール経由では不可)。権限が不足しているとトレースファイルには"ORA-01031:権限が不足しています"が出力され、SQLトレースは取得できません。

##処理を実行するユーザに接続
SQL> conn scott/tiger
 
##ログオン時にSQLトレースを取得するオントリガーを作成
SQL> CREATE OR REPLACE TRIGGER logon_sqltr AFTER LOGON ON SCHEMA
  2   BEGIN
  3   EXECUTE IMMEDIATE 'ALTER SESSION SET TIMED_STATISTICS=TRUE';
  4   EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''sql_trace wait=true,bind=true,plan_stat=adaptive'' ';
  5   EXECUTE IMMEDIATE 'ALTER SESSION SET TRACEFILE_IDENTIFIER=''SQLTRACE'' ';
  6   EXECUTE IMMEDIATE 'ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED';
  7  END;
  8  /
 
##ログオフ時にSQLトレースの取得を停止するトリガーを作成
SQL> CREATE OR REPLACE TRIGGER logoff_sqltr BEFORE LOGOFF ON SCHEMA
  2   BEGIN
  3   EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT OFF'' ';
  4  END;
  5  /
 
##トリガーが利用可能(ENABLE)なことををuser_triggersから確認
SQL> SELECT trigger_name,status FROM user_triggers
  2  WHERE trigger_name in ('LOGON_SQLTR','LOGOFF_SQLTR');
 
TRIGGER_NAME                   STATUS
------------------------------ -------
LOGON_SQLTR                    ENABLED
LOGOFF_SQLTR                   ENABLED
 
##新規接続で調査対象の処理を実行(コネクションプーリングを使用している場合は注意)
 
##SQLトレースが取得できたらトリガーを使用不可に変更
  
SQL> ALTER TRIGGER logon_sqltr DISABLE;
SQL> ALTER TRIGGER logoff_sqltr DISABLE;

特定のSQL_IDに対して取得(ALTER SYSTEM)

対象の処理のSQL_IDが判明している場合にはSQL_IDを指定してピンポイントに情報を取得できます。実行タイミングが不定な場合や、バッチ処理の一部が遅いなどのケースにはこの取得方法が適しています。

##管理ユーザで接続
SQL> conn /as sysdba
 
##SQL_IDを指定してSQLトレースを取得するように設定
SQL> ALTER SYSTEM SET EVENTS 'sql_trace [sql:f4bp1uungguxb] wait=true,bind=true,plan_stat=adaptive';
 
##設定を解除
SQL> ALTER SYSTEM SET EVENTS 'sql_trace [sql:f4bp1uungguxb] off';

既存のセッション対して別セッションから取得(DBMS_MONITOR)

既存のセッションに対しては、ALTER SESSIONやログオントリガーといった方法は使用できません。 DBMS_MONITOR を使用して、別のセッションからSQLトレースを取得します。SIDとSERIAL#を指定する方法と、サービス名、モジュール名を指定する方法の2通りがあります。

##管理ユーザで接続し、対象セッションを確認
SQL> SELECT sid,serial#,module,service_name,sql_trace
  2  FROM v$session
  3  WHERE username='SCOTT';
 
 SID SERIAL# MODULE      SERVICE_NAME   SQL_TRACE
---- ------- ----------- -------------- ---------
  19    2689 Apache.exe  SYS$USERS      DISABLED
 
##SIDとSERIAL#を指定して設定
SQL> EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE( -
>     session_id => 19, -
>     serial_num => 2689, -
>     waits => true, -
>     binds => true, -
>     plan_stat=>'ALL_EXECUTIONS'); 
  
##設定されていることを確認
SQL> SELECT sid,serial#,module,service_name,sql_trace
  2  FROM v$session
  3  WHERE username='SCOTT';
 
 SID SERIAL# MODULE      SERVICE_NAME   SQL_TRACE
---- ------- ----------- -------------- ---------
  19    2689 Apache.exe  SYS$USERS      ENABLED
 
##SIDとSERIAL#を指定して解除
SQL> EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE( -
  2   session_id => 19, -
  3   serial_num => 2689);
 
##サービス名、モジュール名を指定して設定
SQL> EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(-
>     service_name  => 'SYS$USERS', -
>     module_name   => 'Apache.exe', -
>     waits         =>  true, -
>     binds         =>  true, -
>     plan_stat=>'ALL_EXECUTIONS'); 
 
##設定されていることを確認
SQL> SELECT primary_id, qualifier_id1, waits, binds ,plan_stats
  2  FROM dba_enabled_traces WHERE trace_type = 'SERVICE_MODULE';
 
PRIMARY_ID   QUALIFIER_ID1   WAITS  BINDS  PLAN_STATS
------------ --------------- ------ ------ ----------
SYS$USERS    Apache.exe      TRUE   TRUE   ALL_EXEC
 
##サービス名、モジュール名を指定して解除
SQL> EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE( -
  2   service_name  => 'SYS$USERS', -
  3   module_name   => 'Apache.exe');

SQLトレースの整形方法

SQLトレースは処理を実行したプロセスのトレースファイルに出力されます。取得の際にTRACEFILE_IDENTIFIER='SQLTRACE'を指定していた場合は、トレースファイルにタグが付いているので見つけやすくなります。トレースファイルに出力されたSQLトレースをTKPROFで整形します。

SQLトレースの整形

SQLトレースをサポートセンターに提供する場合には、整形前/整形後の両方をご送付ください。

まとめ

SQLトレースはパフォーマンス調査に非常に有用ですが、適切な方法で取得をしないと意図した情報を得られずに再設定→再発待ちとなり、原因特定までの時間が長引きます。

また、情報が取得できた場合には必ず設定を解除することも忘れないように注意しましょう。特にログオントリガーは有効にし続けてしまうと、後続セッションの処理でもSQLトレースが取得されてしまい、ディスクを圧迫するなどの問題を引き起こしかねません。

今回ご紹介したコマンドの例は12.1.0.1環境で動作を確認していますが、実行時に指定しているplan_stat=adaptive(11.2.0.2~利用可)などバージョンに依存するものもあります。特に、10gR2以前のバージョンでは紹介しているコマンドが使用できないケースが多いため、利用しているバージョンの「パフォーマンス・チューニング・ガイド」をご参照ください。

筆者情報

大野 高志

サービス事業部 サポートセンター

2007年にアシスト入社後、Oracle Databaseのサポート業務に従事、現在はサポートの傍ら、未解決のトラブルを一つでも多く減らせるよう、サポートセンターに蓄積されているノウハウを社内外に伝える活動を行っている。

関連している記事

  • Oracle Database
2016.09.21

再現ケースを簡単に作成!SQLテスト・ケース・ビルダーの使用方法

再現ケースに必要な情報を一括で容易に取得できる「SQLテスト・ケース・ビルダー」の使用方法を紹介します。

  • Oracle Database
2016.08.31

DataGuard構築後に使うSQLコマンドまとめ(逆引き)

DataGuard構築後に使うSQLコマンドを逆引きでまとめました。DataGuardを構築した方、これから構築する方は押さえておきましょう。トラブル発生時に焦らないように。

  • Oracle Database
2016.08.12

仮想列関連エラーORA-54032/ORA-54033の原因と対処方法

仮想列に関するエラー(ORA-54032、ORA-54033等)が発生した際の対処方法をご紹介します。Oracle Database 12cでは仮想列が自動で作成されるため、このようなエラーにつながるケースがあります。

カテゴリ一覧

アシストサポートセンターのご紹介 Oracle Database研修

ページの先頭へ戻る