tech_memo / ORACLE / sql_trace

tech_memo/ORACLE

SQLトレース出力方法 †

• 参考
  • https://www.ashisuto.co.jp/db_blog/article/20160630_sqltrace.html

セッションに対してトレース出力を設定 †

• V$SESSION等からSIDとSERIAL#を取得し、それを指定する

  EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 19, serial_num => 2689, waits => true, binds => true, plan_stat=>'ALL_EXECUTIONS'); 

• 上記を実行すると、V$SESSIONのSQL_TRACE列の値がENABLEDになる

ALTER SESSIONでトレース出力設定 †

• 要ALTER SESSION権限

  ALTER SESSION SET TIMED_STATISTICS=TRUE;
  ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED;
  ALTER SESSION SET TRACEFILE_IDENTIFIER='SQLTRACE';
  ALTER SESSION SET EVENTS 'sql_trace wait=true,bind=true,plan_stat=adaptive'; -- ★

  • ★でエラーになった。未調査
• トレース出力解除

  ALTER SESSION SET EVENTS 'sql_trace off'; 

SQL_IDを指定してトレース出力 †

• 管理ユーザで実行

  ALTER SYSTEM SET EVENTS 'sql_trace [sql:f4bp1uungguxb] wait=true,bind=true,plan_stat=adaptive';

• 設定解除

  ALTER SYSTEM SET EVENTS 'sql_trace [sql:f4bp1uungguxb] off';

スキーマログイン時に設定 †

• 要ALTER SESSION権限
• ログイントリガーを作成する

  CREATE OR REPLACE TRIGGER logon_sqltr AFTER LOGON ON SCHEMA
     BEGIN
      EXECUTE IMMEDIATE 'ALTER SESSION SET TIMED_STATISTICS=TRUE';
      EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''sql_trace wait=true,bind=true,plan_stat=adaptive'' ';
      EXECUTE IMMEDIATE 'ALTER SESSION SET TRACEFILE_IDENTIFIER=''SQLTRACE'' ';
      EXECUTE IMMEDIATE 'ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED';
     END;
     /

• ログオフトリガー作成

  CREATE OR REPLACE TRIGGER logoff_sqltr BEFORE LOGOFF ON SCHEMA
     BEGIN
      EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT OFF'' ';
     END;
     /

• トレース取得後は、トリガーを無効化する

  ALTER TRIGGER logon_sqltr DISABLE
  ALTER TRIGGER logoff_sqltr DISABLE

SQLトレース出力場所 †

• alertログと同じ場所
• 以下のクエリでも確認可能

  select value from v$diag_info where name = 'Diag Trace';

TKPROFによるSQLトレースファイルの整形 †

• tkprofコマンドで整形

  # $ORACLE_HOME/bin/tkprof <SQLトレースファイル> <出力ファイル名>