セッションの10046トレースを有効化/無効化し、TKPROFで要約

コマンド

-- 1) Find the target session and its trace file:
SELECT s.sid, s.serial#, s.username, s.module, p.tracefile
FROM   v$session s
  JOIN v$process p ON p.addr = s.paddr
WHERE  s.username = 'CUSTOMER_APP';

-- 2) Enable trace (waits + binds = equivalent of level 12):
BEGIN
  DBMS_MONITOR.SESSION_TRACE_ENABLE(
    session_id => <sid>,
    serial_num => <serial#>,
    waits      => TRUE,
    binds      => TRUE,
    plan_stat  => 'ALL_EXECUTIONS');
END;
/
-- Verify: SELECT sql_trace, sql_trace_waits, sql_trace_binds FROM v$session WHERE sid = <sid>;

-- 3) Disable once the problem operation completes:
EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => <sid>, serial_num => <serial#>);

-- Alternatives:
-- Your own session: ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
-- Single SQL_ID, all sessions: ALTER SYSTEM SET EVENTS 'sql_trace[SQL: <sql_id>] wait=true, bind=true';
--   disable: ALTER SYSTEM SET EVENTS 'sql_trace[SQL: <sql_id>] off';
-- For a connection pool: DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE / SERV_MOD_ACT_TRACE_ENABLE

-- 4) Summarize with TKPROF on the OS (most expensive SQL first):
-- $ tkprof /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_12345.trc /tmp/ora_12345.prf \
--     sys=no sort=exeela,fchela,prsela waits=yes aggregate=yes

概要

拡張SQLトレース(10046)は、セッションが実行するすべてのSQLのparse/execute/fetch時間、待機イベント、バインド値をトレースファイルに書き込む。TKPROFはこれをSQLごとに集計した読みやすいレポートに変換する。

使用する場面

AWR/ASHでは不十分で、単一のユーザー操作がどこで時間を費やしているかをエンドツーエンドで証明する必要があるとき。アプリケーションとデータベース間のラウンドトリップ(SQL*Net)の問題に。

注意

ライセンス不要。トレースファイルは急速に肥大化し、対象セッションにオーバーヘッドを加える。短時間にとどめ、必ず無効化する(DBA_ENABLED_TRACESで残存を確認する)。binds=TRUEは機密データ(個人番号、カード番号など)をトレースファイルに書き込む。ALTER SESSION SET EVENTSにはALTER SESSION権限が必要(ない場合はORA-01031)。TKPROFはデフォルトでバインド値を表示しないため、元の.trcファイルを保持する。