세션의 10046 trace 활성화/비활성화 및 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

하는 일

Extended SQL trace(10046)는 세션이 실행하는 모든 SQL의 parse/execute/fetch 시간, 대기 이벤트, bind 값을 trace 파일에 기록한다. TKPROF는 이를 SQL별로 집계한 읽기 쉬운 보고서로 변환한다.

사용 시점

AWR/ASH로 충분하지 않아 단일 사용자 작업이 시간을 어디에 쓰는지 end-to-end로 증명해야 할 때, 애플리케이션-데이터베이스 왕복(SQL*Net) 문제 분석 시.

주의

라이선스가 필요 없다. trace 파일은 빠르게 커지고 대상 세션에 오버헤드를 준다. 짧게 유지하고 반드시 비활성화한다(DBA_ENABLED_TRACES로 남은 것을 확인). binds=TRUE는 민감 데이터(주민등록번호, 카드 번호 등)를 trace 파일에 기록한다. ALTER SESSION SET EVENTS는 ALTER SESSION 권한이 필요하다(없으면 ORA-01031). TKPROF는 기본적으로 bind 값을 보여 주지 않으므로 원본 .trc 파일을 보관한다.