실행계획 읽기 - 연산 순서·Rows·Cost 해석
이 장에서 배우는 것
결합 인덱스 컬럼 순서를 정하는 방법을 다룬 앞 장에 이어, 이 장에서는 옵티마이저가 실제로 어떤 순서로 데이터를 가져오는지 실행계획을 읽어서 확인하는 법을 다룬다. DBMS_XPLAN 출력은 들여쓰기로 계층을 표현하기 때문에, 이 들여쓰기를 잘못 읽으면 조인 순서와 인덱스 사용 여부를 반대로 이해하게 된다. 또한 옵티마이저가 예상한 건수(E-Rows)와 실제로 처리한 건수(A-Rows)를 나란히 놓고 봐야 통계 정보의 신뢰도를 판단할 수 있다.
- DBMS_XPLAN 출력의 들여쓰기가 가리키는 실행 순서를 정확히 읽는다
- Rows 열이 예상치라는 사실과 A-Rows(실제 건수)를 함께 확인하는 방법을 익힌다
- Predicate Information 의 access 조건과 filter 조건을 구분한다
- Oracle DBMS_XPLAN 과 MySQL EXPLAIN 의 열을 서로 대응시켜 읽는다
문제 상황
온라인 서점의 고객센터 화면에서 "회원별 최근 주문 내역" 조회가 느려졌다는 신고가 들어왔다. 담당자가 실행계획을 뽑아 보니 회원 아이디로 인덱스를 타고 있어서 이상이 없어 보였다. 하지만 실제로는 옵티마이저가 예상한 건수와 실제 처리한 건수 사이에 큰 차이가 있었고, 이 차이가 조인 단계를 거치며 점점 커지고 있었다. Rows 열 하나만 보고 "인덱스를 쓰니까 문제없다"고 판단하면 이런 차이를 놓친다. 이 장에서는 같은 쿼리 하나를 예로 삼아 실행계획을 처음부터 끝까지 읽는 순서를 정리한다.
실행계획을 읽는 순서
들여쓰기가 말해주는 실행 순서
DBMS_XPLAN 출력은 각 연산을 Id 번호 순서대로 위에서 아래로 나열하지만, 실제 실행 순서는 이 나열 순서와 다르다. 실행 순서를 정하는 것은 Id 번호가 아니라 들여쓰기 깊이다. 자식 연산(더 깊이 들여쓰기된 연산)이 부모 연산보다 먼저 끝나야 부모가 그 결과를 받아 처리할 수 있기 때문이다. 규칙은 두 가지다.
- 같은 들여쓰기 깊이에서는 위에 있는 연산이 먼저 시작한다
- 더 깊이 들여쓰기된 자식 연산이 모두 끝나야 부모 연산이 결과를 만든다
그래서 실행계획을 읽을 때는 가장 안쪽(가장 깊이 들여쓰기된) 줄부터 찾아 바깥쪽으로 시선을 옮기는 것이 안전하다. 아래 그림은 이 장에서 다루는 예제 쿼리의 실행계획을 Id 순서(표시 순서)와 실제 실행 순서로 함께 보여준다.
Id 번호와 트리 구조
Id 번호는 실행 순서가 아니라 각 연산을 가리키는 식별자일 뿐이다. 같은 Id 번호라도 어떤 부모 밑에 있는지에 따라 의미가 완전히 달라진다. 예를 들어 NESTED LOOPS 밑에 있는 TABLE ACCESS 는 왼쪽 자식(드라이빙 테이블)의 각 행마다 한 번씩 반복 실행되는데, 이때 반복 횟수는 DBMS_XPLAN(ALLSTATS LAST) 출력의 Starts 열에 그대로 나타난다. Starts 값이 왼쪽 형제 연산의 A-Rows 값과 같다면 두 연산이 부모-자식 관계로 묶여 있다는 뜻이다.
Rows 로 예측과 실제를 비교하기
E-Rows 와 Cost 만으로는 부족하다
기본 EXPLAIN PLAN 이나 AUTOTRACE 로 보는 실행계획에는 옵티마이저가 예상한 건수(E-Rows)와 예상 비용(Cost)만 들어 있다. 이 값은 통계 정보를 근거로 계산한 추정치이며, 실제로 쿼리를 실행한 결과가 아니다. 통계 정보가 오래됐거나 조건 사이의 상관관계를 반영하지 못하면 E-Rows 는 실제와 크게 어긋날 수 있다. 이 어긋남을 확인하려면 실제로 쿼리를 실행해 A-Rows(실제 처리 건수)를 받아봐야 한다.
A-Rows 로 실제 건수 확인하기
Oracle 19c 에서는 쿼리에 gather_plan_statistics 힌트를 붙이거나 세션에 STATISTICS_LEVEL=ALL 을 설정한 뒤 실행하고, 그 다음 DBMS_XPLAN.DISPLAY_CURSOR 를 ALLSTATS LAST 옵션으로 호출하면 Starts, E-Rows, A-Rows, Buffers 열이 함께 나온다. 아래 표는 이 열들이 각각 무엇을 뜻하는지 정리한 것이다.
| 열 | 의미 | 확인 시점 |
|---|---|---|
| Starts | 해당 연산이 실행된 횟수(반복 루프 포함) | 실행 후 |
| E-Rows | 옵티마이저가 통계로 예측한 건수(1회 실행 기준) | 실행 전에도 확인 가능 |
| A-Rows | 실제로 처리된 누적 건수 | 실행 후, ALLSTATS 필요 |
E-Rows 와 A-Rows 의 차이가 몇 배 이상 벌어지면 통계 정보를 의심할 근거가 된다. 특히 그 차이가 조인을 거치며 배로 커진다면, 잘못된 예측을 기반으로 조인 방식이나 조인 순서가 결정되었을 가능성이 있다. 아래 그림은 이 장의 예제에서 회원 필터링 단계의 작은 차이가 조인 결과 전체로 가면서 더 커지는 모습을 보여준다.
Predicate Information 읽기
DBMS_XPLAN 출력 아래쪽의 Predicate Information 은 각 연산에서 어떤 조건이 어떻게 적용되었는지 보여준다. 여기서 access 와 filter 는 뜻이 다르다.
- access — 인덱스에서 탐색 범위를 좁히는 데 쓰인 조건이다. 이 조건에 맞는 인덱스 항목만 읽는다.
- filter — 이미 읽은 행(또는 인덱스 항목) 중에서 나중에 걸러내는 조건이다. 인덱스 구성 컬럼이 아닌 조건은 대부분 filter 로 처리된다.
filter 조건이 있다고 해서 인덱스 스캔 범위가 줄어드는 것은 아니다. access 로 좁혀진 범위를 먼저 다 읽은 다음, 그중 일부를 filter 로 버리는 것이다. 아래 그림은 같은 인덱스 스캔 결과에서 access 로 가져온 건수와 filter 를 거쳐 최종적으로 남는 건수의 차이를 보여준다.
MySQL EXPLAIN 과의 대응
MySQL 8 은 DBMS_XPLAN 과 형식은 다르지만 같은 정보를 제공한다. 기본 EXPLAIN 은 표 형태라 조인 순서를 트리로 보기 어렵고, EXPLAIN FORMAT=TREE 를 쓰면 Oracle 의 들여쓰기와 비슷한 구조로 볼 수 있다. 실제 처리 건수는 EXPLAIN ANALYZE 를 실행해야 나온다.
| Oracle 19c | 의미 | MySQL 8 대응 | 비고 |
|---|---|---|---|
| Id·들여쓰기 | 연산과 실행 순서(트리) | EXPLAIN FORMAT=TREE 의 들여쓰기 | 기본 EXPLAIN 표는 트리 구조가 없다 |
| E-Rows | 예상 처리 건수 | rows 열 | 예측치라는 성격은 동일 |
| A-Rows | 실제 처리 건수 | EXPLAIN ANALYZE 의 actual rows | 기본 EXPLAIN 에는 실제 건수가 없음 |
| Predicate Information | access·filter 구분 | Extra 열(Using index condition, Using where) | MySQL 은 문구로만 access·filter 를 구분한다 |
완성 코드
01_schema.sql
CREATE TABLE members (
member_id NUMBER PRIMARY KEY,
member_name VARCHAR2(50) NOT NULL,
email VARCHAR2(100) NOT NULL,
grade VARCHAR2(10) NOT NULL,
joined_date DATE NOT NULL
);
CREATE TABLE books (
book_id NUMBER PRIMARY KEY,
title VARCHAR2(200) NOT NULL,
category VARCHAR2(30) NOT NULL,
price NUMBER(8,0) NOT NULL,
publisher VARCHAR2(50)
);
CREATE TABLE orders (
order_id NUMBER PRIMARY KEY,
member_id NUMBER NOT NULL REFERENCES members(member_id),
order_date DATE NOT NULL,
status VARCHAR2(10) NOT NULL
);
CREATE TABLE order_items (
order_id NUMBER NOT NULL REFERENCES orders(order_id),
book_id NUMBER NOT NULL REFERENCES books(book_id),
quantity NUMBER(4,0) NOT NULL,
item_price NUMBER(8,0) NOT NULL,
CONSTRAINT pk_order_items PRIMARY KEY (order_id, book_id)
);
CREATE INDEX ix_orders_member_date ON orders(member_id, order_date);
02_seed_data.sql
BEGIN
FOR i IN 0..49 LOOP
INSERT INTO members (member_id, member_name, email, grade, joined_date)
VALUES (1000 + i, '회원' || i, 'user' || i || '@example.com', 'NORMAL', DATE '2024-01-01');
END LOOP;
FOR i IN 1..22 LOOP
INSERT INTO books (book_id, title, category, price, publisher)
VALUES (2000 + i, '도서' || i, 'IT', 18000, '출판사' || MOD(i, 5));
END LOOP;
COMMIT;
END;
/
BEGIN
FOR i IN 1..300 LOOP
INSERT INTO orders (order_id, member_id, order_date, status)
VALUES (i, 1000 + MOD(i, 50), DATE '2026-01-01' + MOD(i, 240), 'DONE');
END LOOP;
COMMIT;
END;
/
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS');
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDER_ITEMS');
-- 통계 수집 이후에도 신규 주문이 계속 쌓였다고 가정한다
BEGIN
FOR i IN 301..450 LOOP
INSERT INTO orders (order_id, member_id, order_date, status)
VALUES (i, 1000 + MOD(i, 50), DATE '2026-01-01' + MOD(i, 240), 'DONE');
END LOOP;
COMMIT;
END;
/
BEGIN
FOR i IN 1..450 LOOP
INSERT INTO order_items (order_id, book_id, quantity, item_price)
VALUES (i, 2001 + MOD(i, 20), MOD(i, 3) + 1, 15000);
IF MOD(i, 2) = 0 THEN
INSERT INTO order_items (order_id, book_id, quantity, item_price)
VALUES (i, 2001 + MOD(i, 20) + 1, 1, 12000);
END IF;
END LOOP;
COMMIT;
END;
/
03_check_plan.sql
SELECT /*+ gather_plan_statistics */
o.order_id, o.order_date, b.title, oi.quantity
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN books b ON b.book_id = oi.book_id
WHERE o.member_id = 1029
AND o.order_date >= DATE '2026-06-01'
AND o.status = 'DONE'
ORDER BY o.order_date DESC;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
줄별 해설
- CREATE INDEX ix_orders_member_date — 회원별 최근 주문을 찾는 조회를 위한 결합 인덱스다. member_id 가 선행 컬럼이라 등치 조건에, order_date 가 후행 컬럼이라 범위 조건에 그대로 대응한다.
- pk_order_items(order_id, book_id) — order_id 가 선행 컬럼이므로 주문 하나에 딸린 주문상세를 찾을 때도 이 기본키 인덱스가 그대로 쓰인다.
- 1..300 루프 후 GATHER_TABLE_STATS — 이 시점의 데이터 분포로 통계 정보를 고정한다. 이후 들어오는 주문은 이 통계에 반영되지 않는다.
- 301..450 루프 — 통계 수집 뒤에 새 주문이 계속 쌓이는 상황을 재현한다. 이 구간이 뒤에서 E-Rows 와 A-Rows 차이를 만드는 원인이다.
- gather_plan_statistics 힌트 — 이 쿼리 실행 시 A-Rows 를 포함한 실측 통계를 남기라고 지시한다. 힌트 없이 실행하면 이후 DISPLAY_CURSOR 에서 A-Rows 열이 비어 있다.
- o.status = 'DONE' 조건 — ix_orders_member_date 인덱스에 없는 컬럼이므로 access 가 아니라 filter 로 처리된다.
- DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST') — 커서 캐시에서 방금 실행한 SQL 의 실행계획을 Starts·E-Rows·A-Rows·Buffers 와 함께 가져온다.
실행 결과
SQL> @03_check_plan.sql
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 21 |
| 1 | SORT ORDER BY | | 1 | 8 | 21 |
| 2 | NESTED LOOPS | | 1 | 8 | 21 |
| 3 | NESTED LOOPS | | 1 | 8 | 21 |
|* 4 | TABLE ACCESS BY INDEX ROWID | ORDERS | 1 | 8 | 14 |
|* 5 | INDEX RANGE SCAN | IX_ORDERS_MEMBER_DATE | 1 | 8 | 14 |
| 6 | TABLE ACCESS BY INDEX ROWID | ORDER_ITEMS | 14 | 1 | 21 |
|* 7 | INDEX RANGE SCAN | PK_ORDER_ITEMS | 14 | 1 | 21 |
| 8 | TABLE ACCESS BY INDEX ROWID | BOOKS | 21 | 1 | 21 |
|* 9 | INDEX UNIQUE SCAN | PK_BOOKS | 21 | 1 | 21 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("O"."STATUS"='DONE')
5 - access("O"."MEMBER_ID"=1029 AND "O"."ORDER_DATE">=TO_DATE(' 2026-06-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
7 - access("OI"."ORDER_ID"="O"."ORDER_ID")
9 - access("B"."BOOK_ID"="OI"."BOOK_ID")
Id 4·5 의 E-Rows 는 8건인데 A-Rows 는 14건이다. 통계를 수집한 뒤에도 회원 1029번의 주문이 계속 들어왔기 때문에 생긴 차이다. 이 차이는 조인을 거치며 Id 0 의 최종 A-Rows 21건까지 그대로 이어진다.
실무에서 자주 틀리는 것
Id 번호 순서대로 실행된다고 읽는다
-- 틀린 읽기: Id 0 → 1 → 2 → ... 순서로 위에서 아래로 실행된다고 본다
-- 옳은 읽기: 가장 깊이 들여쓰기된 Id 5(INDEX RANGE SCAN)부터 실행되고
-- Id 0(SELECT STATEMENT)이 마지막에 결과를 낸다
autotrace 만 보고 성능을 판단한다
-- 틀린 방법: 실행 없이 예상치만 본다
SET AUTOTRACE TRACEONLY EXPLAIN;
SELECT * FROM orders WHERE member_id = 1029;
-- 고친 방법: 실제 실행 후 A-Rows 까지 확인한다
SELECT /*+ gather_plan_statistics */ * FROM orders WHERE member_id = 1029;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
filter 조건도 인덱스가 걸러준다고 오해한다
-- 틀린 판단: status 조건도 인덱스 범위를 줄여준다고 생각한다
-- Predicate Information 확인 결과
-- 4 - filter("O"."STATUS"='DONE') ← 인덱스가 아니라 테이블 접근 단계에서 걸러진다
-- 5 - access("O"."MEMBER_ID"=1029 ...) ← 실제로 인덱스 범위를 좁히는 조건은 이것뿐이다
Rows 급증 구간을 지나치고 전체 Cost 만 비교한다
-- 틀린 비교: 두 실행계획의 마지막 줄 Cost 값만 비교한다
-- 고친 비교: 중간 단계 A-Rows 가 갑자기 커지는 지점(예: NESTED LOOPS 의 A-Rows)을
-- 먼저 찾고, 그 지점의 E-Rows 와 비교해 예측이 빗나간 위치를 짚는다
한눈에 보기
| 확인 항목 | 질문 | 놓치면 생기는 문제 |
|---|---|---|
| 들여쓰기 | 가장 안쪽 연산이 어디인가 | 조인 순서를 반대로 이해한다 |
| E-Rows vs A-Rows | 예측과 실제가 몇 배 차이 나는가 | 통계 문제를 놓친다 |
| Predicate Information | access 인가 filter 인가 | 인덱스가 다 걸러준다고 오해한다 |
연습 문제
- 다음 실행계획에서 가장 먼저 실행이 끝나는 연산의 Id 를 쓰고 그 근거를 한 문장으로 설명하라.
----------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 31 | | 1 | NESTED LOOPS | | 1 | 5 | 31 | |* 2 | TABLE ACCESS BY INDEX ROWID | REVIEWS | 1 | 5 | 31 | |* 3 | INDEX RANGE SCAN | IX_REVIEWS_BOOK | 1 | 20 | 52 | | 4 | TABLE ACCESS BY INDEX ROWID | BOOKS | 31 | 1 | 31 | |* 5 | INDEX UNIQUE SCAN | PK_BOOKS | 31 | 1 | 31 | ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("R"."RATING">=4) 3 - access("R"."BOOK_ID"=3078) 5 - access("B"."BOOK_ID"=3078) - 위 실행계획에서 access 조건과 filter 조건이 각각 어느 Id 에 있는지 쓰고 두 조건의 차이를 설명하라.
- Id 2 의 E-Rows(5)와 A-Rows(31)가 크게 차이 나는 이유를 추정하고, 이 차이가 조인 방식 선택에 어떤 영향을 줄 수 있는지 설명하라.
- 이 실행계획을 MySQL 8 에서 확인한다면 A-Rows 에 해당하는 값을 어떤 명령의 어떤 출력에서 읽어야 하는지 쓰라.
정답과 해설
- Id 3(INDEX RANGE SCAN)이 가장 먼저 끝난다. 들여쓰기가 가장 깊은 연산이며, 그 결과를 Id 2(TABLE ACCESS BY INDEX ROWID)가 받아 처리하기 때문이다.
- access 는 Id 3(BOOK_ID=3078)과 Id 5(BOOK_ID=3078)에 있고, filter 는 Id 2(RATING>=4)에 있다. access 는 인덱스 탐색 범위를 좁히는 조건이고, filter 는 access 로 읽은 행 중 일부를 나중에 걸러내는 조건이다. Id 3 의 A-Rows(52)가 Id 2 의 A-Rows(31)보다 큰 것이 filter 가 걸러낸 21건을 보여준다.
- rating 컬럼은 인덱스에 없어 옵티마이저가 값 분포를 정확히 알기 어렵고, 균등 분포를 가정해 선택도를 과대평가했을 가능성이 높다(즉 필터링 효과를 실제보다 크게 잡아 E-Rows 를 낮게 예측했다). 이런 과소 예측이 심하면 옵티마이저가 더 적합한 해시 조인 대신 네스티드 루프 조인을 선택해 반복 횟수가 예상보다 훨씬 많아질 수 있다.
- EXPLAIN ANALYZE 명령을 실행했을 때 각 테이블 줄에 나오는 actual rows 값에서 읽는다. 기본 EXPLAIN 명령에는 실제 처리 건수가 나오지 않는다.