Database Support Blog

  • 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®、Java、MySQL及びNetSuiteは、Oracle、その子会社及び関連会社の米国及びその他の国における登録商標です。
 ・Amazon Web Services、AWS、Powered by AWS ロゴ、[およびかかる資料で使用されるその他の AWS 商標] は、Amazon.com, Inc. またはその関連会社の商標です。
  文中の社名、商品名等は各社の商標または登録商標である場合があります。

関連している記事

  • Oracle Cloud
  • Oracle Database
2024.02.02

OCIにおけるOracle Database 11g R2、12g R1、12g R2の新規プロビジョニング終了とその影響

Oracle Databaseのバージョン11g R2、12g.R1、12g.R2は既にすべてのメーカーサポートが終了しています。OCIのBase Database Serviceでも2024年1月中旬ころから11g R2、12g R1、12g R2での新規プロビジョニングができなくなりました。

  • Oracle Database
  • その他
2023.12.21

【Oracle Database】サポートセンターでの生成AI(Glean)活用

アシストでは全社員にAIアシスタントGleanを導入しました。サポートセンターで2ヶ月間使ってみて感じた効果やメリットをお伝えします。

  • Oracle Database
  • Oracle Cloud
2023.12.15

ライセンスの観点から考えるOracle Cloudのススメ

オラクル社が提供しているクラウドサービス「Oracle Cloud」は、Oracle Databaseライセンス観点でも様々な効果があることはご存じでしょうか? ここでは「ライセンス」に焦点をあて、Oracle Cloudがおススメできるポイントを説明します。

ページの先頭へ戻る