Devin.KR
통합 검색 Ctrl K

실행계획 해석 연습 - 자체 문제 20선

개발자KR 조회 5

이 장에서 배우는 것

지금까지는 인덱스 구조, 조인 방식, 옵티마이저가 통계를 쓰는 방식을 하나씩 따로 배웠다. 실제 튜닝 현장에서는 이 지식들이 한 장의 실행계획 안에 뒤섞여 나온다. 연산이 몇 단계로 겹쳐 있고, 조인 방식이 두세 개 섞여 있고, Rows 추정치가 어딘가에서 크게 어긋나 있는 것이 보통이다. 이 장은 새 개념을 배우지 않는다. 대신 온라인 서점 스키마를 기준으로 만든 문제 20개를 다섯 유형으로 묶어 놓고, 그중 몇 개를 직접 풀어 보면서 "실행계획을 순서대로 읽어내는 손놀림"을 굳히는 데 목적을 둔다.

  • DBMS_XPLAN 출력에서 Id·Operation·들여쓰기만 보고 실행 순서를 재구성한다
  • Rows·Cost 추정치와 실제 처리 건수(A-Rows)의 차이를 진단 실마리로 읽는다
  • 조인 방식·인덱스 스캔 방식의 조합이 만드는 병목 패턴을 다섯 유형으로 분류해 식별한다
  • Oracle DBMS_XPLAN과 MySQL EXPLAIN의 표기 차이를 혼동하지 않는다

문제 상황

야간 배치가 끝나지 않는다는 연락을 받았다고 하자. 담당자가 전달한 것은 로그 몇 줄과 EXPLAIN PLAN 결과 캡처 화면뿐이다. 이런 상황에서 실행계획을 처음부터 끝까지 정독할 시간은 없다. 실제로 필요한 것은 "이 계획에서 어디를 먼저 봐야 하는가"를 빠르게 판단하는 절차다. 연산 순서를 잘못 읽으면 병목의 위치 자체를 틀리게 되고, Rows 추정치를 실제 값으로 착각하면 엉뚱한 인덱스를 추가하게 된다. 이 장의 문제 20개는 바로 그런 오독이 자주 일어나는 지점을 겨냥해서 만들었다.

문제를 읽는 순서 복습

실행계획은 트리 구조를 표 형태로 펼쳐 놓은 것이다. Id 번호와 들여쓰기만으로 부모-자식 관계를 알 수 있고, 실행은 가장 안쪽(들여쓰기가 가장 깊은) 연산부터 시작해 바깥쪽으로 결과가 모인다. 아래 그림은 이 장의 문제 1번 계열에서 반복해서 등장하는 패턴, 즉 인덱스 레인지 스캔 뒤에 정렬이 붙는 구조를 예로 든 것이다.

들여쓰기가 가장 깊은 인덱스 스캔이 가장 먼저 실행되고 결과는 위쪽 연산으로 모인다

이 순서를 반대로 읽으면, 정렬(SORT ORDER BY)이 상위에 있다는 이유로 정렬이 먼저 실행됐다고 착각하게 된다. 실제로는 인덱스 스캔이 행을 하나씩 넘겨주고 나서야 정렬이 시작된다. 이 착각이 이번 장 문제의 상당수에서 첫 번째 함정으로 등장한다.

문제 20선의 구성

문제 20개는 아래 다섯 유형으로 묶었다. 각 유형은 실행계획에서 보는 위치와 흔한 함정이 다르다. 표 다음에 오는 완성 코드는 이 중 다섯 개(각 유형에서 하나씩)를 실제로 풀어 보는 스크립트다. 나머지 문제는 같은 스키마와 같은 관점으로 스스로 만들어 풀어 보는 것을 권한다.

문제 20선 체크리스트 — 유형별 확인 포인트
번호확인 포인트자주 나오는 함정관련 개념
1~4Id·들여쓰기로 연산 실행 순서 재구성바깥쪽 연산이 먼저 실행됐다고 착각실행계획 읽기
5~8NL·해시·소트 머지 중 무엇이 쓰였고 왜인지NL은 항상 느리다는 선입견조인 방식
9~12SORT·HASH GROUP BY가 어디서 왜 붙었는지ORDER BY와 GROUP BY 정렬을 혼동블록 I/O·정렬
13~16Range·Unique·Full·Fast Full·Skip Scan 구분인덱스를 탔으니 무조건 빠르다는 판단인덱스 스캔 방식
17~20E-Rows와 A-Rows 격차, 함수로 인한 카디널리티 왜곡추정치를 실제 처리 건수로 오해옵티마이저와 통계
다섯 클러스터의 문제가 모두 같은 체크리스트, 즉 순서·방식·병목 확인으로 수렴한다

Oracle과 MySQL의 실행계획 도구 차이

이 책은 Oracle 19c를 기준으로 하지만, MySQL 8을 쓰는 독자를 위해 표기 차이를 짚어 둔다. 개념은 같아도 명령과 지원 범위가 다르므로 그대로 옮겨 적으면 안 된다.

실행계획 조회 방식 비교 — Oracle 19c와 MySQL 8
항목Oracle 19cMySQL 8차이가 만드는 함정
실행계획 조회EXPLAIN PLAN FOR + DBMS_XPLAN.DISPLAYEXPLAIN 또는 EXPLAIN FORMAT=TREEOracle은 기본이 추정치, 실제값은 별도 조회 필요
실제 처리 건수 확인DBMS_XPLAN.DISPLAY_CURSOR(... 'ALLSTATS LAST')EXPLAIN ANALYZEMySQL은 EXPLAIN만으로는 실제 행수를 알 수 없음
해시 조인 지원버전 초기부터 자유롭게 선택8.0.18부터 동등 조인에 한해 지원구버전 MySQL 계획을 Oracle 기준으로 해석하면 오판
인덱스 강제 지정INDEX / NO_INDEX 힌트FORCE INDEX / IGNORE INDEX힌트 문법이 달라 예제를 그대로 옮기면 오류

자세한 옵션은 DBMS_XPLAN 패키지 공식 문서를 확인하는 것이 정확하다.

완성 코드

01_schema.sql

-- 온라인 서점 예제 스키마 (주문 1천만 건 규모를 가정)
CREATE TABLE member (
    member_id     NUMBER        NOT NULL,
    member_grade  VARCHAR2(10)  NOT NULL,
    join_date     DATE          NOT NULL,
    CONSTRAINT pk_member PRIMARY KEY (member_id)
);

CREATE TABLE book (
    book_id       NUMBER        NOT NULL,
    category_cd   VARCHAR2(10)  NOT NULL,
    publisher     VARCHAR2(40),
    price         NUMBER(8)     NOT NULL,
    CONSTRAINT pk_book PRIMARY KEY (book_id)
);

CREATE TABLE orders (
    order_id      NUMBER        NOT NULL,
    member_id     NUMBER        NOT NULL,
    order_date    DATE          NOT NULL,
    order_status  VARCHAR2(10)  NOT NULL,
    CONSTRAINT pk_orders PRIMARY KEY (order_id),
    CONSTRAINT fk_orders_member FOREIGN KEY (member_id)
        REFERENCES member(member_id)
);

CREATE TABLE order_item (
    order_id      NUMBER        NOT NULL,
    line_no       NUMBER(3)     NOT NULL,
    book_id       NUMBER        NOT NULL,
    qty           NUMBER(4)     NOT NULL,
    CONSTRAINT pk_order_item PRIMARY KEY (order_id, line_no),
    CONSTRAINT fk_item_orders FOREIGN KEY (order_id)
        REFERENCES orders(order_id),
    CONSTRAINT fk_item_book FOREIGN KEY (book_id)
        REFERENCES book(book_id)
);

CREATE TABLE review (
    review_id     NUMBER        NOT NULL,
    book_id       NUMBER        NOT NULL,
    member_id     NUMBER        NOT NULL,
    score         NUMBER(1)     NOT NULL,
    review_date   DATE          NOT NULL,
    CONSTRAINT pk_review PRIMARY KEY (review_id)
);

CREATE INDEX ix_orders_member_date ON orders(member_id, order_date);
CREATE INDEX ix_item_book          ON order_item(book_id);
CREATE INDEX ix_book_category      ON book(category_cd);
CREATE INDEX ix_review_book_score  ON review(book_id, score);

02_plans.sql

-- 문제 1 : 회원 한 명의 최근 90일 주문, NL + 인덱스 레인지 스캔 확인
VARIABLE member_id NUMBER
EXEC :member_id := 1001;

EXPLAIN PLAN FOR
SELECT o.order_id, o.order_date, o.order_status
FROM   orders o
WHERE  o.member_id = :member_id
AND    o.order_date >= TRUNC(SYSDATE) - 90
ORDER BY o.order_date DESC;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(FORMAT => 'BASIC +PREDICATE'));

-- 문제 5 : 카테고리별 판매량 집계, 해시 조인 확인
EXPLAIN PLAN FOR
SELECT b.category_cd, SUM(oi.qty) AS total_qty
FROM   order_item oi
JOIN   book b ON b.book_id = oi.book_id
WHERE  b.category_cd IN ('IT', 'ESSAY')
GROUP BY b.category_cd;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(FORMAT => 'BASIC +PREDICATE'));

-- 문제 9 : VIP 등급 주문 목록, 힌트로 소트 머지 조인을 강제
EXPLAIN PLAN FOR
SELECT /*+ USE_MERGE(o m) */
       m.member_grade, o.order_id, o.order_date
FROM   member m
JOIN   orders o ON o.member_id = m.member_id
WHERE  m.member_grade = 'VIP'
ORDER BY o.order_date;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(FORMAT => 'BASIC +PREDICATE'));

-- 문제 14 : 평점 5점 리뷰가 있는 도서, EXISTS 서브쿼리의 스캔 방식
EXPLAIN PLAN FOR
SELECT b.book_id, b.category_cd
FROM   book b
WHERE  EXISTS (
    SELECT 1
    FROM   review r
    WHERE  r.book_id = b.book_id
    AND    r.score = 5
);

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(FORMAT => 'BASIC +PREDICATE'));

-- 문제 18 : 컬럼에 함수를 씌운 조건, 카디널리티 왜곡 확인
EXPLAIN PLAN FOR
SELECT COUNT(*)
FROM   orders o
WHERE  TO_CHAR(o.order_date, 'YYYY-MM') = '2026-08';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(FORMAT => 'BASIC +PREDICATE'));

줄별 해설

  • 01_schema.sql: ORDERS는 MEMBER_ID·ORDER_DATE 결합 인덱스를 갖는다. 회원별 최근 주문 조회가 잦다고 가정했으므로 선두 컬럼을 MEMBER_ID로 뒀다.
  • 문제 1: 바인드 변수 :member_id로 등치 조건을, ORDER_DATE로 범위 조건을 건다. IX_ORDERS_MEMBER_DATE가 그대로 맞아떨어지는 인덱스 레인지 스캔 사례다.
  • 문제 5: ORDER_ITEM과 BOOK을 모두 큰 범위로 읽고 GROUP BY로 묶는다. 두 테이블 모두 필터 조건이 느슨해 옵티마이저가 해시 조인을 고를 가능성이 큰 구조다.
  • 문제 9: USE_MERGE 힌트로 소트 머지 조인을 강제했다. 힌트가 없으면 옵티마이저가 다른 방식을 고를 수 있으므로, 강제로 계획을 바꿔 비교하는 용도다.
  • 문제 14: 상관 서브쿼리가 REVIEW.BOOK_ID, SCORE 조건을 건다. IX_REVIEW_BOOK_SCORE 결합 인덱스가 있어 EXISTS가 세미조인으로 바뀌는지가 확인 포인트다.
  • 문제 18: TO_CHAR로 ORDER_DATE를 감싸 인덱스 선두 컬럼을 가공했다. 인덱스를 쓸 수 없게 되는 전형적인 조건이다.

실행 결과

세 문제의 출력을 예로 든다. 실제 행 수는 환경마다 다르지만 연산 구조와 Predicate Information은 스키마가 같으면 동일하게 나온다.

SQL> @02_plans.sql   -- 문제 1 구간만 발췌

Plan hash value: 1802345671

--------------------------------------------------------------------------
| Id  | Operation                     | Name                    |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                         |
|   1 |  SORT ORDER BY                 |                         |
|   2 |   TABLE ACCESS BY INDEX ROWID  | ORDERS                  |
|   3 |    INDEX RANGE SCAN            | IX_ORDERS_MEMBER_DATE   |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("O"."MEMBER_ID"=:MEMBER_ID)
       filter("O"."ORDER_DATE">=TRUNC(SYSDATE@!)-90)

-- 문제 5 구간

Plan hash value: 2915607733

------------------------------------------------------------
| Id  | Operation             | Name              |
------------------------------------------------------------
|   0 | SELECT STATEMENT      |                   |
|   1 |  HASH GROUP BY        |                   |
|   2 |   HASH JOIN           |                   |
|   3 |    TABLE ACCESS FULL  | BOOK              |
|   4 |    TABLE ACCESS FULL  | ORDER_ITEM        |
------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("B"."BOOK_ID"="OI"."BOOK_ID")
   3 - filter("B"."CATEGORY_CD"='IT' OR "B"."CATEGORY_CD"='ESSAY')

-- 문제 18 구간

Plan hash value: 3720981144

------------------------------------------------
| Id  | Operation          | Name    |
------------------------------------------------
|   0 | SELECT STATEMENT   |         |
|   1 |  SORT AGGREGATE    |         |
|   2 |   TABLE ACCESS FULL| ORDERS  |
------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TO_CHAR(INTERNAL_FUNCTION("O"."ORDER_DATE"),
              'YYYY-MM')='2026-08')

문제 18의 계획에는 INDEX RANGE SCAN이 없다. ORDER_DATE에 인덱스가 있어도 TO_CHAR로 감싼 순간 옵티마이저는 그 인덱스를 후보에서 제외한다.

실무에서 자주 틀리는 것

Cost가 낮으면 무조건 빠르다는 착각

Cost는 옵티마이저가 계획끼리 비교할 때 쓰는 내부 점수일 뿐, 실제 수행 시간과 비례한다는 보장이 없다. 통계가 오래됐거나 바인드 변수 값에 따라 카디널리티가 달라지면 Cost가 낮은 계획이 실제로는 더 느릴 수 있다.

-- 틀린 접근: Cost만 보고 결론
EXPLAIN PLAN FOR
SELECT /*+ USE_NL(o oi) */
       o.order_id, oi.book_id
FROM   orders o
JOIN   order_item oi ON oi.order_id = o.order_id
WHERE  o.order_date >= TRUNC(SYSDATE) - 1;
-- DBMS_XPLAN.DISPLAY로 Cost만 확인하고 운영 반영

-- 고친 접근: 실제 수행 통계까지 확인
SELECT /*+ USE_NL(o oi) GATHER_PLAN_STATISTICS */
       o.order_id, oi.book_id
FROM   orders o
JOIN   order_item oi ON oi.order_id = o.order_id
WHERE  o.order_date >= TRUNC(SYSDATE) - 1;

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

컬럼에 함수를 씌워 인덱스를 스스로 못 쓰게 만드는 조건

-- 틀린 조건: 인덱스 선두 컬럼을 함수로 가공
WHERE TO_CHAR(order_date, 'YYYY-MM') = '2026-08'

-- 고친 조건: 컬럼은 그대로 두고 범위로 비교
WHERE order_date >= DATE '2026-08-01'
AND   order_date <  DATE '2026-09-01'

Rows 추정치를 실제 처리 건수로 착각

DBMS_XPLAN.DISPLAY가 보여주는 Rows는 옵티마이저의 추정치(E-Rows)다. 이 값이 1이라고 해서 실제로 행 1건만 처리한다는 뜻은 아니다. 실제 값은 DISPLAY_CURSOR의 A-Rows 열로 따로 확인해야 한다. 둘의 차이가 10배 이상이면 통계가 낡았거나 조건절이 옵티마이저가 다루기 어려운 형태라는 신호다.

예상 Rows가 1이어도 실제 처리 건수는 48000건까지 벌어질 수 있다

NESTED LOOPS는 무조건 느리다는 오해

-- 틀린 접근: 무조건 해시 조인으로 강제
SELECT /*+ USE_HASH(o m) */
       o.order_id, m.member_grade
FROM   orders o
JOIN   member m ON m.member_id = o.member_id
WHERE  o.order_id = 70012345;

-- 고친 접근: 드라이빙 쪽 결과가 소수 건이면 NL이 유리
SELECT /*+ USE_NL(o m) */
       o.order_id, m.member_grade
FROM   orders o
JOIN   member m ON m.member_id = o.member_id
WHERE  o.order_id = 70012345;

두 번째 쿼리는 ORDER_ID 등치 조건으로 ORDERS를 PK 하나만 읽으므로, 이어지는 MEMBER 조회 한 건에 NL을 쓰는 편이 해시 테이블을 만드는 비용보다 싸다.

한눈에 보기

실행계획을 볼 때 확인할 순서와 신호
확인 순서볼 것이상 신호조치 방향
1Id·들여쓰기로 실행 순서안쪽 연산의 대상 행수가 비정상으로 큼조건절·인덱스 재검토
2조인 방식(NL/해시/소트 머지)드라이빙 테이블이 전체 스캔필터 조건 인덱스화
3인덱스 스캔 방식결합 인덱스인데 Skip Scan 발생선두 컬럼 재배치 검토
4E-Rows vs A-Rows둘의 차이가 10배 이상통계 재수집, 조건절 함수 제거

연습 문제

  1. 문제 6번 계열이다. 다음 계획 조각에서 NESTED LOOPS의 바깥쪽(드라이빙) 연산이 TABLE ACCESS FULL MEMBER이고, WHERE 조건은 m.join_date >= DATE '2026-01-01' 하나뿐이다. 무엇이 병목이 될 수 있는가. 
    | 0 | SELECT STATEMENT             |
| 1 |  NESTED LOOPS                |
| 2 |   TABLE ACCESS FULL          | MEMBER |
| 3 |   TABLE ACCESS BY INDEX ROWID| ORDERS |
| 4 |    INDEX RANGE SCAN          | IX_ORDERS_MEMBER_DATE |
  2. 문제 11번 계열이다. VIP 등급 주문 목록을 ORDER BY로 정렬하는데, 계획에 SORT JOIN이 두 번 나타난다. 이 연산이 나타나는 이유와, 임시 세그먼트(TEMP) 사용을 줄이는 방법을 설명하라.
  3. 문제 15번 계열이다. 결합 인덱스 IX_ORDERS_MEMBER_DATE(MEMBER_ID, ORDER_DATE)가 있는데도 WHERE order_date >= DATE '2026-08-01'만 건 쿼리의 계획에 INDEX SKIP SCAN이 나온다. 이 스캔이 나온 이유와, Range Scan으로 바꿀 수 있는지 판단하라.
  4. 문제 19번 계열이다. 아래 Predicate Information에서 카디널리티 추정이 왜곡될 수 있는 부분을 찾아라. 
    2 - filter(NVL("O"."ORDER_STATUS",'N')='CANCEL')

정답과 해설

  1. 드라이빙 테이블 MEMBER를 조건 없이(또는 선택도가 낮은 조건으로) 전체 스캔한 뒤, 그 결과 각 행마다 ORDERS를 인덱스로 조회한다. MEMBER 쪽 대상 행이 많으면 내부 루프 실행 횟수가 그만큼 늘어나 NL 전체 비용이 커진다. JOIN_DATE에 인덱스를 추가하거나, MEMBER를 먼저 좁힌 결과 집합을 해시 조인으로 ORDERS와 묶는 방식을 검토한다.
  2. USE_MERGE 힌트로 소트 머지 조인을 강제하면 양쪽 입력을 조인 키로 각각 정렬해야 한다. MEMBER와 ORDERS 모두 정렬 순서가 보장돼 있지 않으므로 SORT JOIN이 두 번(양쪽 각각) 나타난다. 정렬 대상 행이 많으면 PGA를 넘어 TEMP를 쓰게 된다. 힌트를 빼서 옵티마이저가 해시 조인이나 인덱스를 활용한 정렬 생략을 고르게 하거나, ORDER BY 컬럼을 포함한 인덱스를 준비해 정렬 자체를 없애는 방법이 있다.
  3. WHERE 절에 결합 인덱스의 선두 컬럼(MEMBER_ID) 조건이 전혀 없으므로 일반적인 Range Scan은 불가능하다. 옵티마이저는 MEMBER_ID 값을 인덱스 안에서 훑어가며 각 구간마다 ORDER_DATE 조건을 적용하는 Skip Scan으로 대신 처리한다. MEMBER_ID의 서로 다른 값 개수가 적을 때만 이 방식이 효율적이며, 값이 많다면 ORDER_DATE를 선두로 하는 별도 인덱스를 만드는 편이 낫다.
  4. NVL로 ORDER_STATUS를 감싸면 옵티마이저는 컬럼 히스토그램을 그대로 활용하지 못하고 기본 선택도 규칙으로 되돌아간다. 실제 CANCEL 건수와 무관하게 추정치가 지나치게 크거나 작게 나올 수 있다. NULL을 허용하지 않는 컬럼으로 설계를 바꾸거나, ORDER_STATUS = 'CANCEL' 조건과 ORDER_STATUS IS NULL 조건을 OR로 분리해 원래 컬럼 통계를 쓰게 하는 것이 낫다.

댓글 0

아직 댓글이 없습니다. 첫 댓글을 남겨 보세요.

댓글을 남기려면 로그인이 필요합니다.