CBO의 판단 과정: Query Transformer·Estimator·Plan Generator

핵심 요약

비용 기반 옵티마이저(CBO)는 SQL을 곧바로 하나의 실행계획으로 바꾸지 않습니다. 파싱된 SQL을 구성하는 Query Block을 기준으로 의미가 같은 다른 내부 표현을 검토하고, 각 후보가 처리할 행 수와 자원 사용량을 추정한 뒤, 현재의 Optimizer Goal과 최적화 환경에서 가장 유리하다고 판단한 실행계획을 선택합니다.

Parsed Query를 구성하는 Query Block
  → Query Transformer: 의미가 같은 내부 SQL 형태 검토
  → Estimator: Selectivity·Cardinality·Cost 추정
  → Plan Generator: Access Path·Join Order·Join Method 후보 탐색
  → 현재 Optimizer Goal에서 Cost가 가장 낮은 실행계획 선택
  → Row Source Generator로 전달

세 구성요소의 역할은 다음처럼 구분할 수 있습니다.

Query Transformer
  → 결과가 같은 다른 내부 표현을 검토한다.

Estimator
  → 후보가 처리할 행 수와 자원 사용량을 추정한다.

Plan Generator
  → 후보 계획을 만들고 Cost를 비교해 계획을 선택한다.

이 이론의 범위

이 이론은 CBO의 판단 흐름을 설명합니다. Transformation별 세부 적용 조건, Histogram·Extended Statistics·Dynamic Statistics의 상세 사용법, 각 인덱스와 조인 방식의 내부 동작은 후속 이론에서 다룹니다.

학습 목표

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

• Query Block이 무엇이며 왜 중요한지 설명한다.
• Query Transformer, Estimator, Plan Generator의 역할을 구분한다.
• Selectivity, Cardinality, Cost의 관계를 설명한다.
• NDV와 균등 분포 가정을 이용해 단순한 Cardinality를 계산한다.
• 여러 조건의 추정에서 독립성 가정과 컬럼 상관관계 문제가 무엇인지 설명한다.
• Cardinality 추정 오류가 Access Path와 Join 계획에 영향을 주는 과정을 설명한다.
• Optimizer Goal에 따라 Cost 비교의 목표가 달라질 수 있음을 설명한다.
• CBO가 선택한 계획이 실제 수행 결과까지 확인한 절대적 최적 계획은 아닌 이유를 설명한다.
• RBO와 CBO를 현재 Oracle 튜닝 관점에서 구분한다.
• 예상 Rows와 실제 수행 정보를 이용해 계획을 검증하는 기본 절차를 설명한다.

1. 옵티마이저의 입력: Parsed Query와 Query Block

Oracle 문서에서는 파싱된 질의를 여러 Query Block의 집합으로 표현합니다. 일반적으로 각 SELECT 영역은 하나의 Query Block을 구성합니다.

SELECT e.empno, e.ename
FROM emp e
WHERE e.deptno IN (
    SELECT d.deptno
    FROM dept d
    WHERE d.loc = 'SEOUL'
);

이 SQL에는 다음 두 Query Block이 있습니다.

바깥 Query Block
  SELECT ... FROM EMP ...

안쪽 Query Block
  SELECT ... FROM DEPT ...

Query Block은 다음 내용을 이해하는 기준이 됩니다.

• Subquery를 Join 형태로 변환할 수 있는가?
• Inline View를 바깥 Query Block과 합칠 수 있는가?
• 조건을 View나 Subquery 안쪽으로 이동할 수 있는가?
• Hint가 어느 Query Block에 적용되는가?
• Transformation 전후에 테이블과 조건이 어느 블록에 속하는가?

필요하면 QB_NAME Hint로 Query Block에 사용자가 이름을 부여할 수 있습니다.

SELECT /*+ QB_NAME(main_qb) */
       e.empno, e.ename
FROM emp e
WHERE e.deptno IN (
    SELECT /*+ QB_NAME(dept_qb) */
           d.deptno
    FROM dept d
    WHERE d.loc = 'SEOUL'
);

이 예시는 Query Block의 식별 방법을 보여 주기 위한 것입니다. Hint의 정확한 사용법과 Transformation 제어는 후속 쿼리 변환 이론에서 다룹니다.

2. CBO의 전체 판단 흐름

CBO의 판단 과정은 다음과 같이 연결됩니다.

1. Parsed Query를 Query Block 단위로 인식한다.
2. Query Transformer가 의미가 같은 다른 내부 표현을 검토한다.
3. 각 Transformation과 실행계획 후보에 대해 Selectivity와 Cardinality를 추정한다.
4. Access Path·Join Order·Join Method 등의 후보 Cost를 계산한다.
5. Plan Generator가 현재까지의 유리한 후보를 중심으로 탐색 범위를 줄여 간다.
6. 현재 Optimizer Goal과 환경에서 가장 낮은 Cost의 계획을 선택한다.

이 흐름은 각 구성요소가 한 번씩만 동작하는 단순한 직선 과정으로만 보면 안 됩니다. Transformation 후보와 실행계획 후보를 비교하는 동안 추정과 Cost 계산이 반복될 수 있습니다.

3. Query Transformer: 의미가 같은 다른 형태를 검토한다

Query Transformer는 사용자가 작성한 SQL의 결과 의미를 유지하면서, 더 유리한 실행계획을 만들 수 있는 내부 SQL 형태가 있는지 검토합니다.

대표적인 Transformation은 다음과 같습니다.

예를 들어 다음 EXISTS Subquery는 조건과 제약을 만족하면 Semi Join 형태의 후보로 검토될 수 있습니다.

SELECT e.empno, e.ename
FROM emp e
WHERE EXISTS (
    SELECT 1
    FROM dept d
    WHERE d.deptno = e.deptno
      AND d.loc = 'SEOUL'
);

원래 표현
  → EMP 행마다 조건에 맞는 DEPT 행의 존재 여부 확인

Transformation 후보
  → EMP와 조건에 맞는 DEPT를 Semi Join 방식으로 결합

Transformation은 결과를 바꾸기 위한 작업이 아닙니다. 같은 결과를 만드는 더 넓은 실행계획 후보를 얻기 위한 작업입니다.

4. Estimator: Selectivity·Cardinality·Cost를 추정한다

Estimator는 통계정보와 현재 최적화 환경을 이용해 후보 실행계획이 처리할 데이터량과 자원 사용량을 추정합니다.

4.1 Selectivity

Selectivity는 전체 Row Set에서 조건을 만족할 것으로 예상되는 비율입니다.

Selectivity
  = 조건을 만족할 것으로 예상되는 행 수 / 전체 행 수

예를 들어 100,000행 중 1,000행이 조건을 만족할 것으로 예상되면 다음과 같습니다.

1,000 / 100,000 = 0.01 = 1%

Selectivity가 0에 가까울수록 적은 행을 선택하는 조건이며, 일반적으로 더 선택적인 조건이라고 표현합니다.

Selectivity 자체는 일반적인 실행계획의 Rows 칼럼에 직접 표시되지 않습니다. 실행계획의 Rows는 Selectivity 등을 이용해 계산한 예상 Cardinality를 나타냅니다.

4.2 Cardinality

Cardinality는 실행계획의 각 Operation이 반환할 것으로 예상되는 행 수입니다.

단일 테이블의 단순 조건에서는 다음처럼 이해할 수 있습니다.

Cardinality
  = 전체 행 수 × Selectivity

ORDERS 테이블에 100,000행이 있고 STATUS 컬럼의 NDV가 100이며, Histogram이 없고 값이 균등하게 분포한다고 가정합니다.

SELECT *
FROM orders
WHERE status = 'READY';

Selectivity ≈ 1 / NDV
            = 1 / 100
            = 1%

Cardinality ≈ 100,000 × 1%
            = 1,000행

실제 READY 행이 40,000행이라면 예상 1,000행과 큰 차이가 생깁니다. 이 오차는 Access Path뿐 아니라 Join Order, Join Method, Sort 작업량의 Cost에도 연쇄적으로 영향을 줄 수 있습니다.

4.3 여러 조건과 독립성 가정

다음과 같이 조건이 두 개라면 옵티마이저는 각 조건의 Selectivity를 결합해 결과 Cardinality를 추정해야 합니다.

SELECT *
FROM orders
WHERE status = 'READY'
  AND order_date >= DATE '2026-07-01';

두 조건이 서로 독립적이라고 단순 가정하면 개념적으로 다음과 같이 계산할 수 있습니다.

결합 Selectivity
  ≈ STATUS 조건 Selectivity × 날짜 조건 Selectivity

하지만 READY 상태가 최근 주문에 집중되어 있다면 두 컬럼은 서로 독립적이지 않습니다. 이때 단순 곱셈 결과는 실제 Cardinality와 크게 달라질 수 있습니다. 컬럼 상관관계를 보완하는 Extended Statistics 등의 상세 내용은 후속 통계정보 이론에서 다룹니다.

4.4 Cost

Cost는 후보 실행계획이 사용할 것으로 예상되는 작업량과 자원을 내부 단위로 표현한 비교값입니다.

Cost 계산에는 다음과 같은 요소가 영향을 줍니다.

• 예상 I/O 작업량
• 예상 CPU 작업량
• 예상 메모리 사용량
• 각 단계의 예상 Cardinality
• 초기 데이터 집합의 크기
• 데이터 분포
• 인덱스 등 Access Structure
• 병렬 설정과 Optimizer 환경

후보 계획 A Cost = 120
후보 계획 B Cost = 350

같은 SQL과 같은 Optimizer 환경이라면, 옵티마이저가 A의 예상 자원 사용량을 B보다 작게 평가했다는 의미입니다.

Cost를 해석할 때는 다음 기준을 지켜야 합니다.

• Cost 120은 실제 120초를 뜻하지 않습니다.
• Cost 비율과 실제 수행시간 비율은 일치하지 않을 수 있습니다.
• 서로 다른 SQL의 Cost 숫자만 직접 비교해 빠른 SQL을 결정할 수 없습니다.
• 같은 SQL이라도 Optimizer Goal과 환경이 다르면 Cost 비교 조건이 달라집니다.
• Cardinality 추정이 틀리면 낮은 Cost로 선택된 계획도 실제로 느릴 수 있습니다.

5. Optimizer Goal과 Cost 비교 목표

CBO는 단순히 하나의 고정된 목표만 사용하는 것이 아닙니다. OPTIMIZER_MODE에 따라 전체 처리량 또는 초기 응답시간을 우선할 수 있습니다.

현재 기본값은 ALL_ROWS이며, FIRST_ROWS_n도 비용 기반 접근을 사용합니다.

따라서 다음 표현이 가장 정확합니다.

CBO는 현재 Optimizer Goal과 환경에서
검토한 후보 중 Cost가 가장 낮은 계획을 선택한다.

같은 SQL이라도 전체 처리량을 우선할 때와 첫 행 응답을 우선할 때 유리한 Access Path와 Join Method가 달라질 수 있습니다.

6. Selectivity와 Cardinality가 계획 선택에 미치는 영향

다음 SQL을 비교합니다.

SELECT order_id, customer_id, order_date
FROM orders
WHERE status = :status;

상황 A: 예상 Cardinality가 100행

조건 결과가 매우 적다고 예상
  → Index Scan 비용이 작게 계산될 가능성
  → Table Access by ROWID 횟수도 적다고 예상
  → Index Plan 후보가 유리해질 수 있음

상황 B: 예상 Cardinality가 800,000행

조건 결과가 대부분이라고 예상
  → 대량의 Index Entry와 ROWID 처리 필요
  → 많은 테이블 블록 접근 비용 예상
  → Full Table Scan 후보가 유리해질 수 있음

같은 SQL 구조에서도 Cardinality 추정이 달라지면 Access Path, Join Order, Join Method의 Cost가 함께 달라집니다.

7. Plan Generator: 후보 계획을 만들고 비교한다

Plan Generator는 Transformation 결과와 Estimator의 추정값을 이용해 다양한 실행계획 후보를 검토합니다.

각 테이블의 Access Path
  × 가능한 Join Order
  × 가능한 Join Method
  × Sort·Aggregate·Parallel 처리 방식

세 테이블 CUSTOMERS, ORDERS, ORDER_ITEMS를 조인하면 다음과 같은 후보가 있을 수 있습니다.

후보 1
CUSTOMERS → ORDERS → ORDER_ITEMS
Nested Loops → Nested Loops

후보 2
ORDERS → CUSTOMERS → ORDER_ITEMS
Hash Join → Hash Join

후보 3
ORDER_ITEMS → ORDERS → CUSTOMERS
Hash Join → Nested Loops

가능한 조합은 매우 많습니다. Plan Generator는 모든 계획을 실제로 실행해 보지 않으며, 현재까지 발견한 최저 Cost 등을 기준으로 유리하지 않은 탐색을 줄입니다.

최종 선택의 의미는 다음과 같습니다.

통계와 현재 Optimizer Goal·환경을 기준으로
검토한 후보 중 예상 Cost가 가장 낮은 계획

이는 실제 실행 결과를 이미 측정한 절대적 최적 계획이라는 뜻이 아닙니다.

8. 하나의 SQL로 전체 파이프라인 연결하기

다음 SQL을 예로 들어 보겠습니다.

SELECT o.order_id,
       o.order_date,
       c.customer_name
FROM orders o
JOIN customers c
  ON c.customer_id = o.customer_id
WHERE o.status = 'READY'
  AND o.order_date >= DATE '2026-07-01';

8.1 Query Transformer

이 SQL에는 눈에 띄는 Inline View나 Subquery가 없으므로 Subquery Unnesting이나 View Merging 예시로 사용하기는 어렵습니다. 다만 실제 내부 Transformation 적용 여부는 SQL 문장 모양만 보고 단정하지 않고 최적화 과정에서 결정됩니다.

8.2 Estimator

• STATUS = 'READY'의 Selectivity를 추정합니다.
• 날짜 조건의 Selectivity를 추정합니다.
• 두 조건의 결합 Selectivity와 ORDERS Cardinality를 추정합니다.
• CUSTOMERS와 Join한 뒤의 Cardinality를 추정합니다.
• 각 Access Path와 Join Method의 Cost를 계산합니다.

8.3 Plan Generator

다음과 같은 후보를 비교할 수 있습니다.

후보 A
ORDERS Full Table Scan
  → CUSTOMERS와 Hash Join

후보 B
ORDERS Index Range Scan
  → Table Access by ROWID
  → CUSTOMERS PK를 반복 탐색하는 Nested Loops Join

READY 주문과 날짜 조건 결과가 적다고 추정되면 후보 B가 유리할 수 있습니다. 조건 결과가 많다고 추정되면 후보 A가 유리할 수 있습니다.

8.4 간단한 예상 실행계획 읽기

--------------------------------------------------------------------------------
| Id | Operation                           | Name              | Rows | Cost |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |                   |  500 |   45 |
|  1 |  NESTED LOOPS                       |                   |  500 |   45 |
|  2 |   TABLE ACCESS BY INDEX ROWID        | ORDERS            |  500 |   30 |
|  3 |    INDEX RANGE SCAN                  | IDX_ORDERS_STATUS |  500 |    8 |
|  4 |   TABLE ACCESS BY INDEX ROWID        | CUSTOMERS         |    1 |    2 |
|  5 |    INDEX UNIQUE SCAN                 | PK_CUSTOMERS      |    1 |    1 |
--------------------------------------------------------------------------------

여기서 Rows는 각 Operation의 예상 Cardinality이며, Cost는 해당 계획을 비교하기 위한 추정값입니다. 실제 수행 후의 행 수와 자원 사용량은 별도의 실행 통계로 검증해야 합니다.

9. 추정이 어긋나는 대표 원인

Histogram, Extended Statistics, Dynamic Statistics, Bind Peeking과 Adaptive Cursor Sharing의 상세 원리는 후속 통계정보 및 SQL 공유 이론에서 다룹니다.

10. RBO와 CBO의 현재 위치

규칙 기반 옵티마이저(RBO)

RBO는 데이터 통계에 기반한 Cost 계산보다 미리 정해진 Access Path 우선순위를 중심으로 계획을 선택하던 과거 방식입니다.

비용 기반 옵티마이저(CBO)

CBO는 테이블·컬럼·인덱스·시스템 통계와 Optimizer 환경을 이용해 후보 계획의 Cost를 비교합니다.

RBO
  → 고정된 규칙과 우선순위 중심

CBO
  → 통계와 예상 Cost를 이용한 후보 비교 중심

현재 Oracle의 OPTIMIZER_MODE에서 사용하는 값은 ALL_ROWS, FIRST_ROWS_n, FIRST_ROWS이며, RULE과 CHOOSE는 현재 지원되는 모드가 아닙니다. 따라서 SQLP 학습에서는 RBO를 역사적 비교 개념으로 이해하고, 실제 튜닝 원리는 CBO를 중심으로 학습해야 합니다.

11. 선택된 계획을 검증하는 기본 절차

옵티마이저가 선택한 계획은 다음 순서로 검증합니다.

1. SQL이 요구하는 결과와 실제 필요한 행 수를 확인한다.
2. 조건별 Selectivity와 단계별 Cardinality를 예상한다.
3. 선택된 Access Path·Join Order·Join Method를 확인한다.
4. 실행계획의 예상 Rows와 Cost를 확인한다.
5. 실제 행 수, I/O, CPU, 대기, 응답시간을 측정한다.
6. 예상과 실제의 차이가 크면 통계·데이터 분포·SQL·인덱스를 점검한다.
7. 결과 정합성과 성능을 함께 확인한다.

Cost가 낮거나 인덱스를 사용했다는 이유만으로 좋은 계획이라고 확정해서는 안 됩니다. 튜닝은 예상과 실제의 차이를 확인하고 그 원인을 설명하는 과정입니다.

12. 자주 혼동하는 판단과 정확한 기준

13. 핵심 정리

Query Block
  → 파싱된 질의를 구성하며 Transformation과 Hint 적용 범위를 이해하는 단위다.

Query Transformer
  → 의미가 같은 다른 내부 SQL 형태를 검토한다.

Estimator
  → Selectivity·Cardinality·Cost를 추정한다.

Plan Generator
  → Access Path·Join Order·Join Method 후보를 탐색하고 비교한다.

Optimizer Goal
  → 전체 처리량 또는 초기 응답시간 중 Cost 비교의 목표를 정한다.

세 단계의 인과관계는 다음과 같습니다.

데이터 분포와 통계
  → Selectivity 추정
  → Cardinality 추정
  → Access·Join Cost 계산
  → 현재 Optimizer Goal에서 실행계획 선택
  → 실제 실행 결과로 검증

옵티마이저 문제를 분석할 때는 계획 모양을 외우기보다 어떤 통계와 추정값이 어떤 계획 선택을 만들었는지 추적하는 관점이 중요합니다.