슬로우 쿼리 추적과 진단 도구

SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       s.event,                       -- 무엇을 기다리는가
       s.wait_class,                  -- User I/O, Concurrency, Idle ...
       s.seconds_in_wait,
       s.sql_id,
       s.module,
       s.machine,
       q.sql_text
FROM   v$session s
LEFT JOIN v$sql q ON q.sql_id = s.sql_id
WHERE  s.status = 'ACTIVE'
AND    s.username IS NOT NULL        -- 백그라운드 프로세스 제외
ORDER BY s.seconds_in_wait DESC;

wait_class 가 Concurrency 면 락 경합이므로 Part 11로, User I/O 면 실제로 많이 읽고 있는 것이므로 쿼리 튜닝으로 갑니다. 이 한 줄이 방향을 가릅니다.

SELECT * FROM (
  SELECT sql_id,
         plan_hash_value,
         executions,
         ROUND(elapsed_time / 1000000, 1)                        AS total_sec,
         ROUND(elapsed_time / GREATEST(executions, 1) / 1000, 1) AS avg_ms,
         buffer_gets,
         ROUND(buffer_gets / GREATEST(executions, 1))            AS gets_per_exec,
         SUBSTR(sql_text, 1, 120)                                AS sql_head
  FROM   v$sql
  WHERE  executions > 0
  ORDER BY elapsed_time DESC
)
WHERE ROWNUM <= 20;

한 번에 오래 걸리는 쿼리보다, 조금 느린데 아주 자주 실행되는 쿼리가 시스템 전체에는 더 해롭습니다. total_sec 로 정렬하면 그런 쿼리가 잡힙니다. gets_per_exec 는 한 번 실행에 몇 블록을 읽는지로, 튜닝 전후 비교에 쓰기 좋습니다.

-- NONE 이면 유료 팩 기능이 막혀 있습니다
SHOW PARAMETER control_management_pack_access

-- 값: NONE / DIAGNOSTIC / DIAGNOSTIC+TUNING

라이선스가 없는 환경이라면 이 값을 NONE 으로 두는 것이 안전합니다. 실수로 실행하는 것 자체를 막아 줍니다. 설정 변경은 DBA와 협의합니다.

-- 내 세션을 추적
ALTER SESSION SET tracefile_identifier = 'my_trace';
EXEC DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => TRUE);

-- 문제 되는 작업 실행
-- ...

EXEC DBMS_SESSION.SESSION_TRACE_DISABLE;

-- 트레이스 파일 경로 확인
SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

waits => TRUE 는 무엇을 기다렸는지, binds => TRUE 는 바인드 변수에 실제로 어떤 값이 들어갔는지 기록합니다. 값이 몰린 컬럼 문제를 확인할 때 binds 가 결정적입니다.

# 다른 세션을 추적하려면 SID와 SERIAL# 로 지정 (SQL*Plus에서)
#   EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(123, 45678, TRUE, TRUE);

# 서버에서 트레이스 파일을 읽기 좋게 변환
tkprof my_trace_12345.trc report.txt sys=no sort=exeela

# sort=exeela : 실행 시간이 긴 순서로 정렬
# sys=no      : 내부 재귀 SQL 제외

변환된 리포트에서 볼 것은 세 가지입니다. 같은 SQL의 실행 횟수(예상보다 많으면 N+1 문제), disk 와 query 열의 블록 수, 그리고 대기 이벤트 목록입니다.

BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE(
    module_name => 'ORDER_BATCH',
    action_name => 'DAILY_SUMMARY'
  );
END;
/

-- 이후 V$SESSION 과 V$SQL 에서 출처가 보입니다
SELECT module, action, sql_id, event
FROM   v$session
WHERE  module IS NOT NULL;

Spring이라면 배치 잡 시작 시점이나 인터셉터에서 설정하면 됩니다. 커넥션 풀을 쓰면 반납 전에 초기화해 다음 사용자와 섞이지 않게 합니다.

SELECT /* OrderSummaryBatch.aggregate */
       customer_id, SUM(amount)
FROM   orders
WHERE  order_dt >= :from_dt
GROUP BY customer_id;

주석은 SQL 텍스트에 그대로 남아 V$SQL 에서 검색됩니다. 다만 주석이 다르면 다른 SQL로 취급되므로, 값이 아니라 고정된 출처 표시에만 씁니다. MyBatis XML의 각 쿼리에 붙여 두면 추적이 쉬워집니다.