옵티마이저 입문: SQL의 실행 방법을 결정하는 원리

핵심 요약

SQL은 원하는 결과를 선언하지만, 그 결과를 만드는 구체적인 처리 순서와 접근 방법까지 모두 지정하지는 않습니다. Oracle의 옵티마이저(Optimizer) 는 SQL과 통계정보, 인덱스·제약조건, 데이터 분포, 최적화 환경을 바탕으로 여러 후보 실행 방법을 비교하고 실행계획을 선택합니다.

파싱된 SQL
  → Query Transformer: 의미가 같은 다른 SQL 형태 검토
  → Estimator: Selectivity·Cardinality·Cost 추정
  → Plan Generator: Access Path·Join Order·Join Method 후보 비교
  → 실행계획 선택
  → Row Source Tree가 계획을 실제 수행

옵티마이저를 이해할 때 가장 먼저 기억할 기준은 다음과 같습니다.

1. 같은 결과를 만드는 실행 방법은 여러 가지일 수 있습니다.
2. 옵티마이저는 검토한 후보 가운데 예상 Cost가 가장 낮은 계획을 선택합니다.
3. Cost는 실제 수행시간이 아니라 후보 계획을 비교하기 위한 내부 추정값입니다.
4. 계획 품질은 Selectivity와 Cardinality 추정의 정확성에 크게 영향을 받습니다.
5. 인덱스 사용 여부가 아니라 최종 결과를 만드는 전체 비용으로 판단해야 합니다.
6. 선택된 계획의 적절성은 실제 실행 결과로 검증해야 합니다.

이 이론의 범위

이 이론은 옵티마이저의 공통 작동 원리를 설명합니다. 인덱스 스캔별 세부 원리, 각 조인 방식의 내부 동작, 쿼리 변환별 상세 조건, 통계 수집 방법은 각각의 후속 이론에서 다룹니다.

학습 목표

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

• 옵티마이저가 필요한 이유를 설명한다.
• Query Transformer, Estimator, Plan Generator의 역할을 구분한다.
• Selectivity, Cardinality, Cost의 의미와 관계를 설명한다.
• Access Path, Join Order, Join Method의 의미를 설명한다.
• 옵티마이저와 실행계획, Row Source의 역할을 구분한다.
• 인덱스가 있어도 Full Table Scan이 선택될 수 있는 이유를 설명한다.
• 같은 SQL의 실행계획이 달라지는 조건을 설명한다.
• 예상과 실제 실행 결과를 비교해야 하는 이유를 설명한다.

1. 옵티마이저가 필요한 이유

다음 SQL은 급여가 3,000 이상인 사원을 조회합니다.

SELECT empno, ename, sal
FROM emp
WHERE sal >= 3000;

사용자가 요구한 결과는 하나지만, Oracle이 결과를 찾는 방법은 여러 가지일 수 있습니다.

방법 1: EMP 테이블 전체를 읽고 SAL 조건을 확인
방법 2: SAL 인덱스에서 조건에 맞는 ROWID를 찾은 뒤 EMP 테이블 접근
방법 3: 필요한 컬럼과 조건을 포함한 다른 인덱스 경로 검토
방법 4: 실행 환경과 설정이 허용하면 병렬 테이블 스캔 검토

어떤 방법이 유리한지는 인덱스의 존재만으로 결정되지 않습니다. 다음 요소를 함께 고려해야 합니다.

• EMP 테이블의 전체 행 수와 블록 수
• SAL >= 3000을 만족할 것으로 예상되는 행의 비율
• 인덱스의 컬럼 구성과 통계
• 인덱스에서 찾은 ROWID로 테이블을 방문해야 하는 횟수
• 인덱스 순서와 테이블 블록 배치의 관계
• 필요한 결과의 범위와 최적화 목표
• CPU·I/O 비용, 병렬 설정 등 최적화 환경

이처럼 여러 후보의 예상 작업량을 비교하여 실행 방법을 정하는 구성요소가 옵티마이저입니다.

2. 옵티마이저의 세 구성요소

Oracle 옵티마이저의 기본 구성은 다음 세 단계로 이해할 수 있습니다.

2.1 Query Transformer

Query Transformer는 원래 SQL과 결과가 같은 범위에서 다른 내부 표현을 검토합니다.

대표적인 변환 예시는 다음과 같습니다.

• Subquery Unnesting
• View Merging
• OR Expansion
• Predicate Pushing

이 단계에서는 각 변환의 세부 조건보다, 옵티마이저가 사용자가 작성한 SQL 문장 모양만 그대로 실행하는 것은 아니라는 점을 이해하면 됩니다.

2.2 Estimator

Estimator는 통계정보를 바탕으로 조건의 선택도, 단계별 예상 행 수, 예상 작업량을 계산합니다. 이 추정이 부정확하면 Access Path와 조인 계획도 부적절하게 선택될 수 있습니다.

2.3 Plan Generator

Plan Generator는 여러 Access Path, Join Order, Join Method를 조합해 후보 계획을 검토합니다. 가능한 모든 조합을 실제로 실행해 보는 것이 아니라, 제한된 최적화 시간 안에서 후보를 탐색하고 현재까지의 최저 Cost 등을 기준으로 불필요한 탐색을 줄입니다.

3. Selectivity, Cardinality, Cost

3.1 Selectivity

Selectivity(선택도) 는 전체 행 집합에서 조건을 만족할 것으로 예상되는 비율입니다.

전체 100,000행 중 예상 100행 선택
Selectivity = 100 / 100,000 = 0.001 = 0.1%

선택도가 낮다는 것은 전체 중 적은 행만 선택된다는 의미입니다. 일반적으로 적은 행을 찾는 조건은 인덱스 접근이 유리할 가능성이 커지지만, 최종 판단은 테이블 접근을 포함한 전체 비용으로 해야 합니다.

3.2 Cardinality

Cardinality(카디널리티) 는 실행계획의 각 Operation이 반환할 것으로 예상되는 행 수입니다.

테이블 전체 행 수 × 조건 Selectivity
  → 조건 적용 후 예상 Cardinality

Cardinality는 Access Path뿐 아니라 Join Order, Join Method, Sort 작업량의 Cost 계산에도 영향을 줍니다. 앞 단계의 Cardinality를 크게 잘못 추정하면 뒤 단계의 예상 작업량도 연쇄적으로 달라질 수 있습니다.

3.3 Cost

Cost 는 옵티마이저가 후보 계획의 예상 자원 사용량을 같은 내부 기준으로 환산한 비교값입니다. I/O, CPU, 메모리 사용 예상과 Cardinality, 데이터 크기, 분포, 접근 구조 등이 Cost 계산에 영향을 줍니다.

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

같은 SQL과 같은 Optimizer 환경에서라면, A가 B보다 적은 작업량을 사용할 것으로 추정되었다는 의미입니다.

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

• Cost 120은 120초를 뜻하지 않습니다.
• Cost의 비율과 실제 수행시간의 비율은 같지 않을 수 있습니다.
• 서로 다른 SQL의 Cost 숫자만 직접 비교해 빠른 SQL을 결정할 수 없습니다.
• Optimizer Mode가 다르면 의미 있는 단순 비교가 어렵습니다.
• 낮은 Cost의 계획도 Cardinality 추정이 틀리면 실제로 느릴 수 있습니다.

4. Plan Generator가 결정하는 핵심 항목

4.1 Access Path

Access Path는 Row Source에서 필요한 행을 가져오는 방법입니다.

대표적인 예시는 다음과 같습니다.

• Table Full Scan
• Table Access by ROWID
• Index Unique Scan
• Index Range Scan
• Index Full Scan
• Index Fast Full Scan

각 인덱스 스캔의 세부 차이는 후속 인덱스 이론에서 다룹니다.

4.2 Join Order

세 개 이상의 테이블을 조인할 때는 어느 Row Source부터 시작하고 다음에 무엇을 결합할지 결정해야 합니다.

후보 1: A → B → C
후보 2: B → A → C
후보 3: C → B → A

선행 단계의 Cardinality가 커지면 후속 조인의 반복 또는 입력 데이터량도 커질 수 있으므로 조인 순서는 전체 Cost에 큰 영향을 줄 수 있습니다.

4.3 Join Method

두 Row Source를 결합하는 대표적인 방법은 다음과 같습니다.

• Nested Loops Join
• Hash Join
• Sort Merge Join

각 방식은 입력 데이터량, 인덱스, 조인 조건, 메모리, 초기 응답과 전체 처리량 목표 등에 따라 유리한 상황이 다릅니다. 세부 내부 동작은 후속 조인 이론에서 다룹니다.

5. 옵티마이저가 참고하는 정보

Clustering Factor 는 인덱스 키 순서와 테이블 블록의 행 배치가 얼마나 비슷한지를 나타내는 인덱스 통계입니다. 값이 좋지 않으면 Index Range Scan 뒤의 Table Access by ROWID가 더 많은 테이블 블록 방문을 유발할 것으로 추정될 수 있습니다.

옵티마이저 통계는 실행 중 수집되는 성능 통계와 목적이 다릅니다. 옵티마이저 통계는 계획을 만들기 위한 추정 입력이며, 실제 성능은 실행 후 측정값으로 확인합니다.

6. 인덱스가 있어도 Full Table Scan을 선택할 수 있다

ORDERS.STATUS에 인덱스가 있다고 가정합니다.

SELECT order_id, customer_id, order_date
FROM orders
WHERE status = 'COMPLETED';

전체 주문의 90%가 COMPLETED라면 인덱스를 사용해도 매우 많은 ROWID를 얻고 테이블 블록을 반복 방문할 수 있습니다.

INDEX RANGE SCAN
  → 대량의 ROWID 획득
  → TABLE ACCESS BY INDEX ROWID 반복
  → 대부분의 행 반환

개념적으로는 다음과 같은 계획이 더 낮은 Cost로 평가될 수 있습니다.

SELECT STATEMENT
  TABLE ACCESS FULL ORDERS

Full Table Scan은 테이블의 넓은 범위를 멀티블록 I/O로 읽을 수 있으므로, 대부분의 행을 반환해야 하는 상황에서 인덱스와 테이블을 반복 방문하는 방식보다 유리할 수 있습니다.

반대로 전체 주문의 0.1%만 CANCELLED라면 다음 SQL에서는 인덱스 접근이 유리할 가능성이 커집니다.

SELECT order_id, customer_id, order_date
FROM orders
WHERE status = 'CANCELLED';

SELECT STATEMENT
  TABLE ACCESS BY INDEX ROWID ORDERS
    INDEX RANGE SCAN IDX_ORDERS_STATUS

핵심은 인덱스가 존재하는가가 아니라 다음 전체 비용입니다.

인덱스 탐색 비용
+ ROWID 획득 비용
+ 테이블 블록 방문 비용
+ 추가 필터·조인·정렬 비용

7. 같은 SQL의 실행계획이 달라지는 조건

같은 SQL Text라도 다음과 같은 최적화 조건이 달라지고 새로운 Hard Parse 또는 재최적화가 발생하면 다른 실행계획이 선택될 수 있습니다.

중요한 점은 다음과 같습니다.

테이블의 데이터가 변했다는 사실만으로 현재 재사용 중인 실행계획이 즉시 자동 교체되는 것은 아닙니다.

데이터 변화가 계획 선택에 반영되려면 일반적으로 통계정보가 갱신되고, Cursor 무효화·Aging Out·Optimizer 환경 변화 등으로 Hard Parse 또는 재최적화가 발생해야 합니다.

따라서 과거와 실행계획이 달라졌다면 단순히 데이터량만 보는 것이 아니라 다음 항목을 함께 확인해야 합니다.

• 통계 수집 시점과 통계 변경
• 객체 DDL 또는 인덱스 변경
• Child Cursor와 Optimizer 환경 차이
• Bind 값과 데이터 편중
• Oracle 버전과 관련 파라미터
• 기존 Cursor의 무효화 또는 Aging Out

8. 옵티마이저, 실행계획, Row Source의 관계

옵티마이저가 실행계획 선택
  → Row Source Generator가 실행 구조 준비
  → Row Source Tree 실행
  → 결과 행 반환

옵티마이저는 계획을 선택하는 구성요소이며, SQL 수행 중 모든 행을 직접 읽는 실행 주체와는 구분해야 합니다.

9. 선택된 계획을 검증하는 기본 순서

옵티마이저가 선택한 계획을 분석할 때는 다음 순서로 확인합니다.

1. SQL이 요구하는 결과와 실제 필요한 행 수를 확인한다.
2. 각 조건의 Selectivity와 단계별 Cardinality를 예상한다.
3. 사용할 수 있는 인덱스·테이블·조인 경로를 확인한다.
4. 선택된 Access Path·Join Order·Join Method를 확인한다.
5. 옵티마이저의 예상 행 수와 Cost를 확인한다.
6. 실제 행 수, I/O, CPU, 대기, 응답시간과 비교한다.
7. 예상과 실제 차이가 크면 통계·데이터 분포·SQL·인덱스를 점검한다.

실행계획의 Cost가 낮다는 이유만으로 계획을 확정적으로 평가해서는 안 됩니다. 특히 예상 Cardinality와 실제 처리 행 수가 크게 다르면 옵티마이저가 잘못된 입력 정보나 부정확한 분포 가정을 사용했을 가능성을 점검해야 합니다.

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

11. 핵심 정리

Query Transformer
  → 결과가 같은 다른 SQL 형태를 검토한다.

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

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

Selectivity
  → 조건을 만족할 것으로 예상되는 행의 비율이다.

Cardinality
  → 각 실행 단계가 반환할 것으로 예상되는 행 수다.

Cost
  → 같은 SQL의 후보 계획을 비교하기 위한 예상 작업량이다.

Execution Plan
  → 옵티마이저가 선택한 처리 방법을 Operation Tree로 표현한 결과다.

Row Source
  → 실행계획의 Operation을 실제로 수행하며 행을 생산한다.

옵티마이저 학습의 핵심은 특정 실행계획을 무조건 좋은 계획으로 외우는 것이 아닙니다. 데이터량·분포·통계·접근 구조·처리 목표에 따라 왜 그 계획이 선택되었으며, 예상과 실제가 일치하는지를 설명하는 것이 중요합니다.