실행계획 읽기 기초: Row Source Tree와 예상·실제 계획

핵심 요약

실행계획(Execution Plan) 은 Oracle이 SQL을 수행하기 위해 선택한 Operation들의 구조입니다. 각 Operation은 데이터를 읽거나, 조인·정렬·집계처럼 행을 가공하며, 그 결과를 부모 Operation에 전달합니다.

Execution Plan
  → Operation의 계층 구조
  → Row Source Tree
  → 자식 Operation이 행을 생산하고 부모가 소비

실행계획을 읽을 때는 Operation 이름만 확인하지 않고 다음 항목을 함께 봅니다.

1. 계획의 출처: 예상 계획인가, 실제 Child Cursor 계획인가
2. Row Source Tree: 부모·자식과 형제 Operation은 어떻게 연결되는가
3. Predicate: 조건이 access와 filter 중 어디에 적용되는가
4. 예상 정보: E-Rows, Cost, 예상 Time은 무엇을 의미하는가
5. 실제 통계: Starts, A-Rows, A-Time, Buffers, Reads는 어떤 작업량을 보여 주는가
6. Note·Adaptive 정보: 동적 통계, Statistics Feedback, Adaptive Plan 여부가 있는가

계획의 모양
  ≠ SQL 성능의 최종 판정

계획의 모양
  + 실제 행 수
  + 반복 횟수
  + 논리·물리 I/O
  + 응답시간
  → 성능 분석의 근거

이 이론의 범위

이 이론은 SQLP의 SQL 고급활용 및 튜닝 → SQL 분석 도구 → 예상 실행계획 범위에서 실행계획을 읽는 공통 문법을 다룹니다. 각 인덱스 스캔·조인 방식·정렬·병렬 처리의 내부 원리는 후속 이론에서 상세히 다룹니다.

학습 목표

이 이론을 학습한 뒤에는 다음 내용을 설명할 수 있어야 합니다.

• Execution Plan, Operation, Row Source, Row Source Tree를 구분한다.
• 들여쓰기와 부모·자식 관계로 실행계획 구조를 파악한다.
• 구조를 보는 방향과 실제 행 흐름을 추적하는 방향을 구분한다.
• Id, Operation, Name, Rows·E-Rows, Cost, 예상 Time을 읽는다.
• Predicate Information의 access와 filter를 구분한다.
• EXPLAIN PLAN + DISPLAY와 DISPLAY_CURSOR의 차이를 설명한다.
• SQL_ID와 CHILD_NUMBER로 정확한 실제 Cursor를 선택한다.
• ALLSTATS와 ALLSTATS LAST의 차이를 설명한다.
• 실행 통계 수집 조건을 설명한다.
• 반복 Operation에서 Starts × E-Rows와 A-Rows ÷ Starts를 계산한다.
• A-Time, Buffers, Reads를 상하위 Row Source와 연결해 해석한다.
• Plan Hash Value와 Note·Adaptive Plan 표시를 올바르게 해석한다.

1. 옵티마이저와 실행계획

SQL은 필요한 결과를 선언하지만, 테이블과 인덱스를 어떤 순서와 방식으로 읽을지는 직접 작성하지 않습니다. 옵티마이저는 통계정보와 실행 환경을 이용하여 여러 후보 계획의 Cost를 계산하고, 검토한 후보 중 비용이 낮다고 판단한 계획을 선택합니다.

SELECT employee_id,
       last_name,
       salary
FROM   employees
WHERE  department_id = 50
AND    salary >= 5000;

가능한 후보는 다음처럼 달라질 수 있습니다.

후보 A
  → EMPLOYEES Full Table Scan
  → DEPARTMENT_ID와 SALARY 조건 검사

후보 B
  → DEPARTMENT_ID 인덱스로 후보 ROWID 탐색
  → 테이블에서 SALARY 조건 검사

옵티마이저가 최종 선택한 Operation의 조합이 실행계획입니다.

실행계획은 권장 실행 방법을 보여 주지만 실제 처리시간을 직접 보장하지 않습니다. 실제 데이터량, Bind 값, Cache 상태, 동시 부하와 대기시간은 별도로 확인해야 합니다.

2. 처음 알아야 할 핵심 용어

실행계획에서 각 단계는 물리적으로 데이터를 읽거나, 자식이 생산한 행을 다음 단계에 맞게 준비합니다.

3. 실행계획은 Row Source Tree다

다음은 이해를 위해 단순화한 계획입니다.

------------------------------------------------------------------------
| Id  | Operation                            | Name        | Rows | Cost |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |             |      |    3 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED | EMPLOYEES   |    3 |    3 |
|*  2 |   INDEX RANGE SCAN                   | EMP_DEPT_IX |    5 |    1 |
------------------------------------------------------------------------

Predicate Information
---------------------
  1 - filter("SALARY">=5000)
  2 - access("DEPARTMENT_ID"=50)

들여쓰기는 다음 부모·자식 관계를 나타냅니다.

SELECT STATEMENT
  TABLE ACCESS BY INDEX ROWID BATCHED
    INDEX RANGE SCAN

행은 가장 안쪽 자식에서 생산되어 부모로 전달됩니다.

INDEX RANGE SCAN
  → DEPARTMENT_ID=50 범위에서 인덱스 Entry와 ROWID 5개 생산

TABLE ACCESS BY INDEX ROWID
  → ROWID로 테이블 행 읽기
  → SALARY>=5000 Filter
  → 3행 생산

SELECT STATEMENT
  → 최종 3행 반환

3.1 구조 파악 방향과 행 흐름 방향

구조 파악
  → 위에서 아래로 들여쓰기와 부모·자식 확인

행 흐름
  → 실행되는 자식 Row Source에서 부모로 전달되는 행 추적

단순한 단일 자식 구조에서는 아래쪽 자식부터 행 흐름을 추적하기 쉽습니다. 조인처럼 형제 자식이 여러 개인 경우에는 부모 Join Operation이 자식에게 데이터를 요청하는 방식이 다르므로 화면의 위·아래 위치나 Id 숫자만으로 실행 순서를 단정하지 않습니다.

Oracle의 계획 Operation은 자식에게 데이터를 요청하고, 자식이 생산한 행을 받아 처리합니다. 조인 순서와 호출 관계는 Join Method, 형제의 POSITION, Adaptive·Parallel 구조를 함께 확인해야 합니다.

4. 기본 출력 항목

4.1 Cost 해석

Cost는 실제 초 단위 시간이 아닙니다.

같은 SQL·같은 Optimizer 환경
  Plan A Cost 20
  Plan B Cost 100
  → Optimizer는 일반적으로 A를 더 유리하게 평가

다음 방식으로 해석하면 안 됩니다.

• Cost 20은 20초라는 의미
• 서로 다른 SQL의 Cost 숫자만으로 실제 성능 비교
• 모든 Operation의 Cost를 더해 전체 Cost 계산

상위 Operation의 Cost는 하위 처리 비용을 반영하는 누적 성격을 가지므로 각 행의 Cost를 단순 합산하지 않습니다.

5. Predicate Information: access와 filter

+PREDICATE 형식을 사용하면 조건이 적용된 위치를 확인할 수 있습니다.

5.1 access Predicate

access는 후보 행의 탐색 범위를 정하거나 Row Source 사이의 연결 조건으로 사용된 Predicate입니다.

2 - access("DEPARTMENT_ID"=50)

인덱스 Range Scan에서는 Start·Stop Key를 정하는 조건이 대표적인 access Predicate입니다.

5.2 filter Predicate

filter는 Operation이 읽거나 전달받은 행 중 조건을 만족하는 행만 생산하도록 검사하는 Predicate입니다.

1 - filter("SALARY">=5000)

5.3 올바른 판단 기준

access
  → 후보 탐색·연결에 사용

filter
  → 후보를 검사해 출력 여부 결정

access는 좋은 조건이고 filter는 나쁜 조건이라는 등급이 아닙니다. Filter가 매우 적은 행만 검사한다면 부담이 작을 수 있고, Access Predicate가 넓은 범위를 찾으면 많은 작업이 발생할 수 있습니다.

다음 항목을 함께 봅니다.

• 어느 Operation에 Predicate가 적용되었는가
• 후보 행과 최종 행의 차이가 얼마나 큰가
• Starts가 몇 회인가
• Access Path가 무엇인가
• Buffers와 Reads가 얼마나 발생했는가

6. 예상 계획과 실제 Cursor 계획

6.1 EXPLAIN PLAN의 예상 계획

EXPLAIN PLAN FOR
SELECT employee_id,
       last_name,
       salary
FROM   employees
WHERE  department_id = 50
AND    salary >= 5000;

SELECT *
FROM TABLE(
  DBMS_XPLAN.DISPLAY(
    NULL,
    NULL,
    'TYPICAL +PREDICATE'
  )
);

EXPLAIN PLAN은 대상 SQL을 실제로 실행하지 않고 설명 시점의 환경에서 Optimizer가 선택한 계획을 Plan Table에 저장합니다.

6.2 DISPLAY_CURSOR의 실제 Cursor 계획

SELECT *
FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    sql_id          => :sql_id,
    cursor_child_no => :child_no,
    format          => 'TYPICAL +PREDICATE +ALIAS +NOTE'
  )
);

DISPLAY_CURSOR는 Shared Pool에 현재 Load되어 있는 Cursor의 실행계획을 보여 줍니다. Cursor가 Aging Out되어 Shared Pool에 없다면 DISPLAY_CURSOR로 확인하지 못할 수 있습니다.

두 인자를 NULL로 지정하면 현재 Session의 직전 Cursor를 편리하게 확인할 수 있지만, 진단 SQL을 실행하는 과정에서 대상이 바뀔 수 있습니다. 운영 분석에서는 SQL_ID와 CHILD_NUMBER를 명시하는 방식이 안전합니다.

6.3 두 계획이 달라질 수 있는 이유

• 실제 Bind 값과 Bind 데이터 타입
• Parsing Schema와 권한
• Session·System Optimizer Parameter
• 통계정보와 Object 상태
• 같은 Parent Cursor 아래 여러 Child Cursor
• SQL Profile·Patch·Plan Baseline 등의 적용 상태
• Adaptive Plan의 최종 Runtime 선택

실행된 SQL을 분석할 때는 가능한 범위에서 실제 Child Cursor의 계획을 우선 확인합니다.

7. DBMS_XPLAN Format과 실제 통계 수집

7.1 ALLSTATS LAST의 전제

Format 문자열에 ALLSTATS LAST를 작성했다고 실제 통계가 자동 생성되는 것은 아닙니다.

대표적인 수집 방법은 다음과 같습니다.

SELECT /*+ GATHER_PLAN_STATISTICS */
       employee_id,
       last_name,
       salary
FROM   employees
WHERE  department_id = 50
AND    salary >= 5000;

또는 Session에서 다음 설정을 사용할 수 있습니다.

ALTER SESSION SET STATISTICS_LEVEL = ALL;

실행 후 정확한 SQL_ID와 Child Number로 확인합니다.

SELECT *
FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    :sql_id,
    :child_no,
    'ALLSTATS LAST +PREDICATE +ALIAS +NOTE'
  )
);

실행 통계가 수집되지 않았다면 A-Rows, Buffers, Reads 등이 비어 있거나 충분히 표시되지 않을 수 있습니다.

7.2 ALLSTATS와 LAST의 차이

ALLSTATS
  → Cursor의 여러 실행에 누적된 통계가 표시될 수 있음

ALLSTATS LAST
  → 마지막 실행의 Row Source 통계

특정 Bind 값과 한 번의 실행을 분석할 때는 일반적으로 LAST를 포함해 마지막 실행 기준으로 확인합니다.

8. Plan Hash Value와 Note·Adaptive Plan

8.1 Plan Hash Value

SQL_ID  abc...
Child number 0
Plan hash value: 1234567890

• SQL_ID: Parent Cursor를 식별하는 대표 값
• Child number: 같은 Parent 아래 특정 Child Cursor 번호
• Plan Hash Value: 주요 Plan Operation 구조를 빠르게 비교하는 값

Plan Hash Value가 다름
  → 일반적으로 주요 Plan 구조가 다름

Plan Hash Value가 같음
  → 주요 구조가 같을 가능성
  → Predicate·Projection·Bind·실제 작업량까지 같다는 뜻은 아님

Hash 값은 비교용 식별자이므로 이론적으로 충돌 가능성을 완전히 배제하는 절대 증명값으로 사용하지 않습니다.

8.2 Note 확인

Note
-----
- dynamic statistics used
- statistics feedback used for this statement
- this is an adaptive plan

Note는 계획 본문만으로 놓치기 쉬운 최적화 정보를 알려 줍니다.

8.3 Adaptive Plan

ADAPTIVE 형식에서는 Default Plan의 대안과 최종 선택을 확인할 수 있습니다.

- 표시가 붙은 Row Source
  → 최종 실행에서 비활성화된 대안일 수 있음

Adaptive Plan에서는 계획이 Runtime에 최종 결정될 수 있으므로 예상 계획과 실제 Final Plan을 구분합니다.

9. 실제 실행 통계의 의미

9.1 반복 Operation 계산

예상 총 행 수
  ≈ Starts × E-Rows

실제 1회당 평균 행 수
  = A-Rows ÷ Starts

예를 들어 다음과 같습니다.

Starts = 100
E-Rows = 2
A-Rows = 5,000

예상 총 행 수 = 100 × 2 = 200행
실제 1회당 평균 = 5,000 ÷ 100 = 50행

Optimizer는 한 번 시작할 때 2행을 예상했지만 실제로는 평균 50행을 생산했습니다. 반복 횟수를 고려하지 않고 E-Rows 2와 A-Rows 5,000을 바로 비교하면 추정 오차를 잘못 해석할 수 있습니다.

9.2 A-Time과 Buffers의 누적 성격

상위 Row Source의 A-Time과 Buffers에는 하위 작업이 반영되어 보일 수 있습니다.

부모 A-Time
  → 자식 수행시간을 포함할 수 있음

부모 Buffers
  → 자식에서 발생한 Buffer 작업이 반영될 수 있음

따라서 상위와 하위 행의 값을 모두 더해 SQL 전체 시간이나 I/O를 계산하지 않습니다. 어느 하위 Operation에서 작업량이 증가했는지 비교하는 용도로 사용합니다.

Buffers는 단순한 고유 Block 개수가 아니라 논리 Buffer 요청 횟수입니다. 같은 Block을 반복 방문하면 작업량에 반복 반영될 수 있습니다.

9.3 Reads와 Cache 상태

Reads는 물리 읽기 작업량을 보여 줍니다. 동일 Plan이라도 Cache 상태와 동시 부하에 따라 물리 읽기와 경과시간은 달라질 수 있습니다.

10. 실행계획을 읽는 기본 순서

1. 분석 대상 SQL의 SQL_ID, CHILD_NUMBER, 실행 Bind를 확인합니다.
2. DISPLAY의 예상 계획인지 DISPLAY_CURSOR의 실제 Cursor 계획인지 구분합니다.
3. Plan Hash Value와 Note·Adaptive 여부를 확인합니다.
4. 들여쓰기로 부모·자식·형제 Operation을 파악합니다.
5. 가장 안쪽 Object Access와 Predicate 위치를 확인합니다.
6. 자식이 만든 행이 부모로 전달되는 흐름을 추적합니다.
7. access와 filter를 후보 탐색·검사 역할로 구분합니다.
8. 실제 통계가 있다면 Starts × E-Rows와 A-Rows를 비교합니다.
9. A-Rows ÷ Starts로 한 번당 실제 행 수를 확인합니다.
10. Buffers, Reads, A-Time이 급증한 Row Source를 찾습니다.
11. 상하위 통계를 단순 합산하지 않습니다.
12. Operation 이름만으로 좋고 나쁨을 결정하지 않고 전체 작업량을 근거로 판단합니다.

11. 예제 1: 단순 인덱스 계획

---------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | Starts | E-Rows | A-Rows | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |             |      1 |        |      3 |       8 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED | EMPLOYEES   |      1 |      3 |      3 |       8 |
|*  2 |   INDEX RANGE SCAN                   | EMP_DEPT_IX |      1 |      5 |      5 |       3 |
---------------------------------------------------------------------------------------

Predicate Information
---------------------
  1 - filter("SALARY">=5000)
  2 - access("DEPARTMENT_ID"=50)

해석은 다음과 같습니다.

1. Id 2가 DEPARTMENT_ID=50을 Index Range로 사용했습니다.
2. Index Operation은 예상 5행, 실제 5행에 해당하는 ROWID를 생산했습니다.
3. Id 1은 테이블을 읽고 SALARY>=5000을 Filter했습니다.
4. 5행 중 3행이 통과했습니다.
5. 예상과 실제 Cardinality가 유사합니다.
6. 최상위 Buffers 8을 하위 Buffers 3과 다시 더하지 않습니다.

12. 예제 2: 반복 Operation의 추정 오차

--------------------------------------------------------------------------------
| Id | Operation           | Name       | Starts | E-Rows | A-Rows | Buffers |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT    |            |      1 |        |  5,000 |  20,000 |
|  1 |  NESTED LOOPS       |            |      1 |    200 |  5,000 |  20,000 |
|  2 |   TABLE ACCESS FULL | DEPARTMENT |      1 |    100 |    100 |     100 |
|  3 |   INDEX RANGE SCAN  | EMP_DEPT_IX|    100 |      2 |  5,000 |  19,900 |
--------------------------------------------------------------------------------

Id 3을 해석합니다.

예상 총 행 수
  = Starts 100 × E-Rows 2
  = 200행

실제 총 행 수
  = A-Rows 5,000행

실제 1회당 평균
  = 5,000 ÷ 100
  = 50행

내부 Index Scan은 한 번에 2행을 예상했지만 실제 평균 50행을 반환했습니다. 이 오차는 Nested Loops의 전체 반복 접근과 Buffers 증가에 영향을 줄 수 있습니다.

13. 자주 혼동하는 판단

14. 핵심 정리

실행계획
  = Operation의 Row Source Tree

구조 파악
  = 들여쓰기와 부모·자식·형제 관계

행 흐름
  = 자식 Row Source가 생산한 행을 부모가 소비

Predicate
  = access는 후보 탐색·연결
  = filter는 후보 검사

계획 출처
  = EXPLAIN PLAN + DISPLAY: 예상 계획
  = DISPLAY_CURSOR: Shared Pool의 실제 Child Cursor 계획

실제 통계
  = Plan Statistics 수집 필요
  = ALLSTATS LAST: 마지막 실행
  = 예상 총 행 ≈ Starts × E-Rows
  = 실제 평균 행 = A-Rows ÷ Starts

작업량
  = Buffers·Reads·A-Time
  = 상하위 값을 단순 합산하지 않음

실행계획 해석의 핵심은 Operation 이름 암기가 아닙니다. 어디에서 행을 찾았는지, 몇 번 시작했는지, 한 번에 몇 행을 예상하고 실제 몇 행을 만들었는지, 어떤 Predicate로 줄였는지, 어느 단계에서 I/O와 시간이 증가했는지를 연결해 설명하는 것입니다.