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_CDfilter로 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. 문제점을 모두 찾고 개선안을 제시하라.
- Id 4 카디널리티 오류: VIP E-Rows 100 vs A-Rows 5,000 (50배) → GRADE_CD 히스토그램 확인
- Id 5 랜덤 액세스 낭비: 인덱스 306K건 → 테이블 방문 306K회 → filter 후 102K건. 테이블 단계 순수 Buffers 약 396K로 전체의 96%
- 조인 방식: 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 리포트 분석