실행계획 읽기 실습

ORACLE SQL TUNING · HANDS-ON LAB 1

실행계획 읽기 실습

DBMS_XPLAN 포맷부터 오퍼레이션 해석, 병목 단계 찾기까지 — 10개 실습으로 익히는 실행계획

선수 학습 · SQL 튜닝 입문 4~5장커리큘럼 · 5주차기준 · Oracle 19c

1. 실습 준비

1.1 권한과 환경

-- DISPLAY_CURSOR에 필요한 권한 (DBA가 부여)
GRANT SELECT ON V_$SQL_PLAN              TO lab_user;
GRANT SELECT ON V_$SQL_PLAN_STATISTICS_ALL TO lab_user;
GRANT SELECT ON V_$SQL                   TO lab_user;
GRANT SELECT ON V_$SESSION               TO lab_user;
-- 또는 GRANT SELECT_CATALOG_ROLE TO lab_user;

-- SQL*Plus 출력 설정
SET LINESIZE 200 PAGESIZE 0 SERVEROUTPUT OFF

⚠ SERVEROUTPUT ON 상태에서는 SQL*Plus가 실행 후 DBMS_OUTPUT.GET_LINES를 호출하므로, DISPLAY_CURSOR(NULL, NULL)이 방금 실행한 SQL이 아닌 그 호출의 계획을 보여준다. 반드시 SERVEROUTPUT OFF.

1.2 실습 테이블

CREATE TABLE lab_cust NOLOGGING AS
SELECT 'C' || LPAD(LEVEL, 6, '0')                         AS cust_no,
       'CUST_' || LEVEL                                   AS cust_nm,
       DECODE(MOD(LEVEL, 10), 0, 'VIP', 'NORMAL')         AS grade_cd,
       DECODE(MOD(LEVEL, 5), 0, '서울', 1, '부산', 2, '대구', 3, '광주', '대전') AS region
  FROM dual CONNECT BY LEVEL <= 50000;

CREATE TABLE lab_order NOLOGGING AS
SELECT 'O' || LPAD(LEVEL, 10, '0')                        AS order_no,
       DATE '2024-01-01' + MOD(LEVEL, 1000)               AS order_dt,
       'C' || LPAD(MOD(LEVEL, 50000) + 1, 6, '0')          AS cust_no,
       DECODE(MOD(LEVEL, 100), 0, '90', 1, '30', '10')    AS stat_cd,
       ROUND(DBMS_RANDOM.VALUE(1000, 500000))              AS order_amt
  FROM dual CONNECT BY LEVEL <= 3000000;

ALTER TABLE lab_cust  ADD CONSTRAINT pk_lab_cust  PRIMARY KEY (cust_no);
ALTER TABLE lab_order ADD CONSTRAINT pk_lab_order PRIMARY KEY (order_no);
CREATE INDEX ix_lab_order_01 ON lab_order (cust_no, order_dt);
CREATE INDEX ix_lab_order_02 ON lab_order (order_dt);

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'LAB_CUST');
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'LAB_ORDER');

2. DBMS_XPLAN 완전 정복

2.1 함수 종류

함수	소스	용도
DISPLAY	PLAN_TABLE (EXPLAIN PLAN)	예상 계획 확인
DISPLAY_CURSOR	V$SQL_PLAN (Library Cache)	실제 실행된 계획 + 실측 통계
DISPLAY_AWR	DBA_HIST_SQL_PLAN	과거 계획 이력, 계획 변경 추적
DISPLAY_SQL_PLAN_BASELINE	SPM	고정된 Baseline 계획 확인
DISPLAY_SQLSET	SQL Tuning Set	STS에 저장된 계획

2.2 포맷 옵션

포맷	보여주는 것	언제
BASIC	Id, Operation, Name만	구조만 빠르게
TYPICAL (기본)	+ Rows, Bytes, Cost, Time, Predicate	일반 확인
ALL	+ Query Block, Column Projection	컬럼 흐름 추적
ADVANCED	ALL + Outline + Peeked Binds	계획 재현·힌트 추출
ALLSTATS LAST	Starts, E-Rows, A-Rows, A-Time, Buffers, Reads, 메모리 사용	튜닝 시 기본
+PEEKED_BINDS	하드 파싱 시 엿본 바인드 값	바인드 영향 분석
+OUTLINE	계획을 재현하는 전체 힌트 세트	힌트 작성 참고
+ADAPTIVE	적응적 계획에서 비활성 단계(-)까지 표시	12c+ Adaptive Plan
+NOTE / -NOTE	Dynamic Sampling, SPM, Profile 사용 여부	계획 결정 근거

2.3 실측 통계 수집 방법

-- 방법 1: 힌트 (해당 SQL만)
SELECT /*+ GATHER_PLAN_STATISTICS */ ... ;

-- 방법 2: 세션 단위 (여러 SQL을 연속 분석할 때)
ALTER SESSION SET statistics_level = ALL;

-- 확인
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

-- 다른 세션 SQL은 SQL_ID와 CHILD_NUMBER 지정
SELECT sql_id, child_number, plan_hash_value, executions
  FROM v$sql WHERE sql_text LIKE '%찾을 문자열%' AND sql_text NOT LIKE '%v$sql%';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', &child_no, 'ALLSTATS LAST +PEEKED_BINDS'));

💡 ALLSTATS vs ALLSTATS LAST — LAST 없이 쓰면 모든 실행의 누적값이 나온다. 튜닝 전후 비교는 항상 LAST로 마지막 1회를 본다.

3. 실행계획 컬럼 해석

컬럼	의미	주의
Id	단계 번호. * 표시는 Predicate가 있는 단계	Predicate 섹션과 매칭
Operation / Name	오퍼레이션과 대상 객체	들여쓰기가 부모-자식 관계
Starts	이 단계가 시작된 횟수	NL Inner, FILTER 서브쿼리에서 크게 증가
E-Rows	예상 행 수 (1회 실행 기준)	A-Rows와 비교 시 E-Rows × Starts
A-Rows	실제 반환 행 수 (전체 누적)
A-Time	실제 경과 시간 (자식 포함 누적)	자식 시간을 빼야 순수 단계 시간
Buffers	Logical Read (자식 포함 누적)	부모 − 자식 = 해당 단계 자체 I/O
Reads / Writes	Physical Read / Write	Writes는 Temp 사용 의미
OMem / 1Mem / Used-Mem	최적 / One-Pass / 실사용 PGA	Used-Tmp가 있으면 디스크 정렬·해시
Pstart / Pstop	접근한 파티션 범위	KEY = 실행 시 결정, 숫자 = 파싱 시 결정

4. 주요 오퍼레이션 사전

오퍼레이션	의미	체크 포인트
TABLE ACCESS FULL	테이블 전체 Multi Block 스캔	대량 처리면 정상, 소량 결과면 인덱스 검토
TABLE ACCESS BY INDEX ROWID	ROWID로 테이블 단건 접근	인덱스 A-Rows 대비 버려지는 비율
TABLE ACCESS BY INDEX ROWID BATCHED	ROWID를 모아 블록 단위로 일괄 접근 (12c+)	랜덤 I/O 감소, 정렬 순서 보장 안 됨
INDEX UNIQUE SCAN	1건 탐색
INDEX RANGE SCAN	범위 탐색	access 조건 범위가 좁은가
INDEX RANGE SCAN DESCENDING	역순 범위 탐색	ORDER BY DESC 정렬 대체
INDEX FULL SCAN (MIN/MAX)	인덱스 한쪽 끝만 읽음	MIN/MAX 최적화
INDEX FAST FULL SCAN	인덱스 전체 Multi Block	필요한 컬럼이 인덱스에 모두 있을 때
INDEX SKIP SCAN	선두 컬럼 건너뛰기	대개 인덱스 설계 개선 신호
INLIST ITERATOR	IN 목록 값마다 인덱스 탐색 반복
NESTED LOOPS / HASH JOIN / MERGE JOIN	조인 방식	조인 튜닝 심화 참고
HASH JOIN SEMI / ANTI	EXISTS / NOT EXISTS 변환
FILTER	조건 필터, 또는 서브쿼리를 메인 행마다 실행	자식 2개면 서브쿼리 Filter 방식
VIEW	인라인 뷰·뷰가 병합되지 않고 독립 처리	뷰 병합 실패 여부
SORT ORDER BY / GROUP BY	정렬 / 정렬 기반 그룹핑	Used-Tmp 여부
HASH GROUP BY / HASH UNIQUE	해시 기반 그룹핑 / DISTINCT
SORT ORDER BY STOPKEY	Top-N 정렬 (N건만 유지)	페이징
COUNT STOPKEY	ROWNUM <= n / FETCH FIRST 조기 종료	부분 범위 처리
WINDOW SORT / WINDOW BUFFER	분석 함수 처리
PARTITION RANGE SINGLE / ITERATOR / ALL	파티션 1개 / 일부 / 전체	ALL이면 프루닝 실패
PX COORDINATOR / PX SEND / PX RECEIVE	병렬 처리	병렬 분배 방식(HASH, BROADCAST)
STATISTICS COLLECTOR	적응적 계획의 전환 판단 지점 (12c+)	+ADAPTIVE로 확인

5. 실습 문제

각 실습의 실행계획은 학습용으로 단순화한 예시이다. 본인 환경에서 직접 실행해 수치를 비교해 보자. 해설은 클릭하면 펼쳐진다.

실습 1. 읽는 순서

-----------------------------------------------------------------------------
| Id | Operation                     | Name            | Starts | A-Rows | Buffers |
-----------------------------------------------------------------------------
|  0 | SELECT STATEMENT              |                 |      1 |     20 |      67 |
|  1 |  NESTED LOOPS                 |                 |      1 |     20 |      67 |
|  2 |   NESTED LOOPS                |                 |      1 |     20 |      47 |
|  3 |    TABLE ACCESS BY INDEX ROWID| LAB_ORDER       |      1 |     20 |      23 |
|* 4 |     INDEX RANGE SCAN          | IX_LAB_ORDER_01 |      1 |     20 |       3 |
|* 5 |    INDEX UNIQUE SCAN          | PK_LAB_CUST     |     20 |     20 |      24 |
|  6 |   TABLE ACCESS BY INDEX ROWID | LAB_CUST        |     20 |     20 |      20 |
-----------------------------------------------------------------------------

Q. 실행 순서를 Id로 나열하고, 각 단계의 순수 Buffers를 구하라.

• 순서: 4 → 3 → 5 → 2 → 6 → 1 → 0 (NL은 Outer(3) 1행마다 Inner(5→6) 수행)
• 순수 Buffers: 4 = 3, 3 = 23 − 3 = 20, 5 = 24, 2 = 47 − 23 − 24 = 0, 6 = 20, 1 = 67 − 47 − 20 = 0
• 20건에 67블록 — 건당 약 3.3블록으로 양호하다.

실습 2. 카디널리티 오류 찾기

--------------------------------------------------------------------------------------
| Id | Operation                     | Name            | Starts | E-Rows | A-Rows | Buffers |
--------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT              |                 |      1 |        |  29700 |  187K   |
|  1 |  NESTED LOOPS                 |                 |      1 |     30 |  29700 |  187K   |
|  2 |   TABLE ACCESS BY INDEX ROWID | LAB_ORDER       |      1 |     30 |  29700 |  29812  |
|* 3 |    INDEX RANGE SCAN           | IX_LAB_ORDER_02 |      1 |     30 |  29700 |     84  |
|  4 |   TABLE ACCESS BY INDEX ROWID | LAB_CUST        |  29700 |      1 |  29700 |  157K   |
|* 5 |    INDEX UNIQUE SCAN          | PK_LAB_CUST     |  29700 |      1 |  29700 |  59400  |
--------------------------------------------------------------------------------------
   3 - access("ORDER_DT"=:B1)

Q. 문제 단계와 원인, 개선 방향은?

• Id 3: E-Rows 30 vs A-Rows 29,700 — 약 1,000배 과소 추정
• 옵티마이저는 30건으로 보고 NL을 선택했지만 실제로는 29,700번 Inner를 반복하여 Buffers 18만 블록 소모
• 원인 후보: 통계 오래됨(Stale), 바인드 값 분포 편중(Bind Peeking 시점 값과 현재 값 차이), 히스토그램 부재
• 개선: 통계 재수집 → 계획 재확인. 대량이 정상이라면 LEAD 순서 유지 + Hash Join (LAB_CUST Full Scan, 약 300블록 수준)

실습 3. access vs filter

SELECT /*+ GATHER_PLAN_STATISTICS INDEX(o ix_lab_order_01) */ *
  FROM lab_order o
 WHERE o.order_dt BETWEEN DATE '2026-01-01' AND DATE '2026-01-31'
   AND o.cust_no  = 'C000123';

|* 2 |   INDEX RANGE SCAN | IX_LAB_ORDER_01 |  1 |  2 |  2 |  3 |
Predicate Information:
   2 - access("CUST_NO"='C000123' AND "ORDER_DT">=... AND "ORDER_DT"<=...)

Q. 인덱스를 (ORDER_DT, CUST_NO)로 바꾸면 Predicate가 어떻게 달라지는가?

• access("ORDER_DT">=... AND "ORDER_DT"<=...) + filter("CUST_NO"='C000123')
• 선두 컬럼이 범위 조건이면 그 뒤 컬럼은 스캔 범위를 줄이지 못하고 filter가 된다 → 한 달치 인덱스 리프(약 9만 건)를 모두 스캔
• 원칙: = 조건 컬럼 선두, 범위 조건 컬럼 후행

실습 4. 테이블 랜덤 액세스 낭비

|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED | LAB_ORDER       | 1 |    12 |  45210 |
|* 2 |   INDEX RANGE SCAN                   | IX_LAB_ORDER_02 | 1 | 45000 |    130 |
Predicate:
   1 - filter("STAT_CD"='90')
   2 - access("ORDER_DT">=... AND "ORDER_DT"<...)

Q. 무엇이 비효율이고 어떻게 개선하는가?

• 인덱스에서 45,000건을 찾아 테이블을 45,000번 방문했지만 STAT_CD filter로 12건만 남았다 (99.97% 버림)
• 테이블 단계 순수 Buffers ≈ 45,080
• 개선: 인덱스 (STAT_CD, ORDER_DT) 또는 기존 인덱스에 STAT_CD 추가 → 인덱스 단계에서 걸러 테이블 접근 12회

실습 5. FILTER 서브쿼리

|  0 | SELECT STATEMENT     |            |      1 |   5000 |  60230 |
|* 1 |  FILTER              |            |      1 |   5000 |  60230 |
|  2 |   TABLE ACCESS FULL  | LAB_CUST   |      1 |  50000 |    310 |
|* 3 |   INDEX RANGE SCAN   | IX_LAB_ORDER_01 |  50000 |   5000 |  59920 |
   1 - filter( EXISTS (SELECT 0 FROM "LAB_ORDER" ... ))

Q. 이 계획의 처리 방식은? 개선 방법은?

• FILTER의 자식이 2개 → 서브쿼리를 메인 행마다 실행하는 Filter 방식 (Starts 50,000)
• 입력값(CUST_NO)이 모두 달라 서브쿼리 캐싱 효과가 없음
• 개선: 서브쿼리 Unnesting → HASH JOIN SEMI (/*+ UNNEST HASH_SJ */) 또는 조건 추가로 메인 집합 축소

실습 6. 정렬과 Temp 사용

| Id | Operation          | Name      | A-Rows | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp |
|  1 |  SORT ORDER BY     |           |  3000K |  15420  | 21540 | 21540  | 189M | 4.8M |  25M (1) |    172M  |
|  2 |   TABLE ACCESS FULL| LAB_ORDER |  3000K |  15420  |     0 |     0  |      |      |          |          |

Q. Used-Mem 옆의 (1)과 Used-Tmp는 무엇을 뜻하는가?

• 최적(OMem) 189MB가 필요했지만 PGA가 부족해 One-Pass 정렬 수행 — (0) Optimal, (1) One-Pass, (n) Multi-Pass
• Temp 172MB 사용, Reads/Writes는 Temp I/O
• 개선 순서: ① 정렬이 정말 필요한가 (인덱스로 정렬 대체, Top-N) ② 정렬 대상 컬럼 최소화 ③ PGA(PGA_AGGREGATE_TARGET) 검토

실습 7. 페이징 — STOPKEY

SELECT /*+ GATHER_PLAN_STATISTICS */ *
  FROM lab_order
 WHERE cust_no = 'C000123'
 ORDER BY order_dt DESC
 FETCH FIRST 10 ROWS ONLY;

|  1 |  VIEW                          |                 | 1 | 10 |  13 |
|* 2 |   WINDOW NOSORT STOPKEY        |                 | 1 | 10 |  13 |
|  3 |    TABLE ACCESS BY INDEX ROWID | LAB_ORDER       | 1 | 11 |  13 |
|  4 |     INDEX RANGE SCAN DESCENDING| IX_LAB_ORDER_01 | 1 | 11 |   3 |

Q. 정렬(SORT)이 없는 이유와, 부분 범위 처리가 깨지는 경우는?

• 인덱스 (CUST_NO, ORDER_DT)가 이미 정렬되어 있어 DESCENDING 스캔으로 정렬 대체 → NOSORT STOPKEY로 11건만 읽고 종료
• 깨지는 경우: ORDER BY 컬럼이 인덱스 순서와 다름, 인덱스에 없는 컬럼으로 정렬, 조건 컬럼 가공, 중간에 GROUP BY·DISTINCT·Hash Join 존재

실습 8. 파티션 프루닝

| Id | Operation                   | Name      | Pstart | Pstop | A-Rows | Buffers |
|  1 |  PARTITION RANGE ALL        |           |      1 |    36 |   3000 |  158K   |
|* 2 |   TABLE ACCESS FULL         | TB_ORDER  |      1 |    36 |   3000 |  158K   |
   2 - filter(TO_CHAR("ORDER_DT",'YYYYMM')='202611')

Q. 왜 36개 파티션을 모두 읽었는가?

• 파티션 키 ORDER_DT를 가공하여 프루닝 실패 → PARTITION RANGE ALL
• 개선: ORDER_DT >= DATE '2026-11-01' AND ORDER_DT < DATE '2026-12-01' → PARTITION RANGE SINGLE, Pstart=Pstop

실습 9. 계획 변경 추적

-- 같은 SQL의 과거 계획 이력 (Diagnostics Pack 필요)
SELECT snap_id, plan_hash_value,
       executions_delta AS execs,
       ROUND(buffer_gets_delta / NULLIF(executions_delta,0)) AS gets_per_exec,
       ROUND(elapsed_time_delta / NULLIF(executions_delta,0) / 1000, 1) AS ms_per_exec
  FROM dba_hist_sqlstat
 WHERE sql_id = '&sql_id'
 ORDER BY snap_id;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&sql_id', &plan_hash_value, NULL, 'TYPICAL'));

Q. "어제까지 빨랐는데 오늘 갑자기 느려졌다"를 어떻게 확인하는가?

• 위 쿼리로 PLAN_HASH_VALUE가 바뀐 시점과 gets_per_exec 변화를 찾는다
• 계획 변경 원인 후보: 통계 재수집, 바인드 피킹 값 변화, 인덱스 추가·삭제, 파라미터·패치 변경
• 긴급 조치: 좋은 계획을 SQL Plan Baseline으로 고정 (DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE 또는 AWR에서 로드)

실습 10. 종합

---------------------------------------------------------------------------------------------
| Id | Operation                       | Name            | Starts | E-Rows | A-Rows | Buffers |
---------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                |                 |      1 |        |     50 |  412K   |
|  1 |  SORT ORDER BY                  |                 |      1 |     12 |     50 |  412K   |
|  2 |   HASH GROUP BY                 |                 |      1 |     12 |     50 |  412K   |
|  3 |    NESTED LOOPS                 |                 |      1 |   1200 |   102K |  412K   |
|* 4 |     TABLE ACCESS FULL           | LAB_CUST        |      1 |    100 |   5000 |    310  |
|* 5 |     TABLE ACCESS BY INDEX ROWID | LAB_ORDER       |   5000 |     12 |   102K |  411K   |
|* 6 |      INDEX RANGE SCAN           | IX_LAB_ORDER_01 |   5000 |     60 |   306K |  15200  |
---------------------------------------------------------------------------------------------
   4 - filter("C"."GRADE_CD"='VIP')
   5 - filter("O"."STAT_CD"='10')
   6 - access("O"."CUST_NO"="C"."CUST_NO" AND "O"."ORDER_DT">=:B1)

Q. 문제점을 모두 찾고 개선안을 제시하라.

1. Id 4 카디널리티 오류: VIP E-Rows 100 vs A-Rows 5,000 (50배) → GRADE_CD 히스토그램 확인
2. Id 5 랜덤 액세스 낭비: 인덱스 306K건 → 테이블 방문 306K회 → filter 후 102K건. 테이블 단계 순수 Buffers 약 396K로 전체의 96%
3. 조인 방식: Outer 5,000건 × 반복 → 결과가 대량이므로 Hash Join이 유리할 수 있음

• 개선안 A: 인덱스 (CUST_NO, STAT_CD, ORDER_DT) + 집계 컬럼 포함 시 테이블 접근 제거
• 개선안 B: LEADING(c) USE_HASH(o) + LAB_ORDER Full Scan (약 15K 블록) → 412K → 약 16K
• 두 안을 실측 비교하고, 실행 빈도(OLTP/배치)에 따라 결정

6. 실행계획 분석 체크리스트

□ 실측 계획(ALLSTATS LAST)인가, 예상 계획인가
□ 전체 Buffers 중 가장 큰 비중을 차지하는 단계는 어디인가 (부모 − 자식)
□ E-Rows × Starts 와 A-Rows가 10배 이상 차이 나는 단계가 있는가
□ filter로 대량의 행이 버려지는 단계가 있는가 (특히 테이블 액세스 단계)
□ NL Inner 단계의 Starts가 과도하지 않은가
□ Used-Tmp (디스크 정렬·해시)가 발생하는가
□ 파티션 테이블에서 PARTITION RANGE ALL이 나오는가
□ Note 섹션에 dynamic sampling, SQL profile, baseline 사용이 표시되는가

이전 ← SQL 튜닝 입문  |  다음 → 인덱스 튜닝 심화 · 조인 튜닝 심화 · AWR 리포트 분석