SQLトレースの取得方法まとめ（ケース別）

##処理を実行するユーザに接続
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'; 

##処理を実行するユーザに接続
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  /
　
##トリガーが利用可能（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
　　
##新規接続で調査対象の処理を実行（コネクションプーリングを使用している場合は注意）
　
##SQLトレースが取得できたらトリガーを使用不可に変更
　 
SQL> ALTER TRIGGER logon_sqltr DISABLE;

##管理ユーザで接続
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';

##管理ユーザで接続し、対象セッションを確認
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');

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

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