DevelopOracle

Oracle - 실행 계획 분석

Oracle 실행 계획 분석

오라클 쿼리의 실행 계획을 확인하고 분석하는 다양한 방법을 다룹니다.

실행 계획은 오라클 옵티마이저가 SQL 문을 실행하기 위해 선택한 작업 순서와 방법입니다. 효과적인 쿼리 튜닝을 위해서는 실행 계획을 정확히 읽고 분석할 수 있어야 합니다.

EXPLAIN PLAN 사용

-- 실행 계획 생성
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE department_id = 10;

-- 실행 계획 확인
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 상세 실행 계획 확인
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ALLSTATS LAST'));

AUTOTRACE 활용

-- AUTOTRACE 활성화
SET AUTOTRACE ON EXPLAIN;
SET AUTOTRACE ON STATISTICS;
SET AUTOTRACE TRACEONLY;

-- 쿼리 실행
SELECT * FROM employees WHERE salary > 10000;

-- AUTOTRACE 비활성화
SET AUTOTRACE OFF;

SQL 모니터링

-- SQL 모니터링 활성화 (11g 이상)
SELECT /*+ MONITOR */ * FROM large_table WHERE condition = 'value';

-- SQL 모니터 레포트 확인
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
  sql_id => 'sql_id_here',
  type => 'TEXT'
) FROM DUAL;

운영 환경에서 실행된 쿼리의 실행 계획 조회

운영 중인 환경에서는 여러 쿼리가 동시에 실행되므로, 특정 쿼리의 실행 계획을 정확히 추출하는 것이 중요합니다.

방법 1: SQL_ID를 이용한 조회 (가장 추천)

-- 1단계: 실행할 쿼리에 고유 식별자 추가
SELECT /* MY_UNIQUE_QUERY_20260106 */ *
FROM employees
WHERE department_id = 10;

-- 2단계: SQL_ID 찾기
SELECT sql_id, sql_text, executions, last_active_time
FROM v$sql
WHERE sql_text LIKE '%MY_UNIQUE_QUERY_20260106%'
  AND sql_text NOT LIKE '%v$sql%';  -- 이 조회문 자체 제외

-- 3단계: SQL_ID로 실제 실행 계획 조회
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id_here', NULL, 'ALLSTATS LAST'));

-- 실행 통계까지 포함된 상세 계획
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id_here', NULL, 'ADVANCED ALLSTATS LAST'));

방법 2: GATHER_PLAN_STATISTICS 힌트 사용

-- 실행 계획 수집 힌트 추가하여 쿼리 실행
SELECT /*+ GATHER_PLAN_STATISTICS MY_QUERY_001 */ *
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 10000;

-- SQL_ID 찾기
SELECT sql_id, child_number, sql_text
FROM v$sql
WHERE sql_text LIKE '%MY_QUERY_001%'
  AND sql_text NOT LIKE '%v$sql%';

-- 실제 실행 통계가 포함된 계획 조회
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('발견된_sql_id', NULL, 'ALLSTATS LAST'));

방법 3: 세션 레벨 추적

-- 현재 세션에서만 통계 수집 활성화
ALTER SESSION SET STATISTICS_LEVEL = ALL;

-- 쿼리 실행
SELECT * FROM employees WHERE department_id = 10;

-- 마지막 실행된 쿼리의 SQL_ID 조회
SELECT prev_sql_id, prev_child_number
FROM v$session
WHERE sid = (SELECT sid FROM v$mystat WHERE ROWNUM = 1);

-- 실행 계획 조회
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

-- 통계 수집 원복
ALTER SESSION SET STATISTICS_LEVEL = TYPICAL;

방법 4: SQL 해시값으로 추적

-- 쿼리 텍스트의 해시값 미리 계산
SELECT DBMS_UTILITY.GET_SQL_HASH('
SELECT * FROM employees WHERE department_id = 10
', NULL, 1) as sql_hash FROM DUAL;

-- 쿼리 실행 후 해시값으로 찾기
SELECT sql_id, sql_text, executions, last_active_time
FROM v$sql
WHERE hash_value = '위에서_계산한_해시값';

-- 실행 계획 조회
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id', NULL, 'ALLSTATS LAST'));

방법 5: AWR에서 과거 실행 계획 조회

-- AWR에 저장된 SQL 찾기
SELECT sql_id, snap_id, plan_hash_value, executions_delta
FROM dba_hist_sqlstat
WHERE sql_id = 'your_sql_id'
ORDER BY snap_id DESC;

-- AWR에서 실행 계획 조회
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('sql_id', 'plan_hash_value'));

실전 팁

-- 실시간 실행 중인 쿼리 모니터링
SELECT s.sid, s.serial#, s.username, s.sql_id,
       q.sql_text, s.last_call_et, s.status
FROM v$session s
JOIN v$sql q ON s.sql_id = q.sql_id
WHERE s.username IS NOT NULL
  AND s.status = 'ACTIVE'
ORDER BY s.last_call_et DESC;

-- 특정 세션의 현재 실행 중인 SQL 계획
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
  (SELECT sql_id FROM v$session WHERE sid = '특정_sid'),
  NULL,
  'ALLSTATS LAST'
));

-- SQL_ID로 모든 child cursor의 계획 조회
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id', NULL, 'ALLSTATS LAST'));

-- SQL Baseline이 있는 경우 계획 조회
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(
  sql_handle => 'sql_handle',
  plan_name => NULL,
  format => 'TYPICAL'
));

주의사항

  • ALLSTATS LAST는 마지막 실행 통계를 보여줌 (정확한 실행 결과)
  • GATHER_PLAN_STATISTICS 힌트는 약간의 오버헤드 발생
  • STATISTICS_LEVEL=ALL은 시스템 전체에 영향을 주므로 세션 레벨에서만 사용
  • 운영 환경에서는 고유한 주석을 사용하여 SQL을 식별하는 것이 가장 안전
  • Shared Pool에서 aging out 되기 전에 빠르게 조회해야 함

관련 문서

Share