SQL 옵티마이저는 데이터베이스에서 SQL 문장을 실행할 때, 가장 효율적인 실행 계획(Execution Plan)을 선택해주는 핵심 엔진입니다. 쉽게 말해, 같은 SQL이라도 실행 속도를 최대한 빠르게 만들어주는 전략가라고 생각하면 됩니다.
옵티마이저의 역할
옵티마이저는 사용자가 작성한 SQL 문을 그대로 실행하지 않고, 다음 단계를 거쳐 실행 계획을 수립합니다.
1. SQL 파싱(Parsing)
- SQL 문법을 분석하여 문장이 유효한지 확인
- 테이블, 컬럼, 권한 등을 검사
2. 실행 계획 생성(Execution Plan Generation)
- 가능한 여러 가지 접근 경로(인덱스 사용, 풀 테이블 스캔, 조인 방식 등)를 고려
- 각 경로의 비용(Cost)을 계산
3. 최적 실행 계획 선택
- 계산된 비용이 가장 낮은 경로를 채택
- 선택된 실행 계획을 바탕으로 실제 데이터를 읽고 가공
옵티마이저의 종류
DBMS에 따라 약간씩 다르지만, 전통적으로 두 가지 방식이 존재합니다.
1. 규칙 기반 옵티마이저(Rule-Based Optimizer, RBO)\
- 미리 정의된 규칙에 따라 실행 계획을 선택
- 예: 인덱스가 있으면 무조건 사용, 조건절이 특정 컬럼이면 먼저 필터
- 단점: 데이터 분포나 통계 정보를 고려하지 않으므로 비효율적일 수 있음
- 현재는 거의 사용되지 않음 (Oracle 10g 이후는 사실상 비용 기반만 지원)
2. 비용 기반 옵티마이저(Cost-Based Optimizer, CBO)
- 통계 정보(Statistics)를 활용해 각 실행 경로의 비용을 계산
- 비용(Cost) = 예상 I/O + CPU 소모량 + 네트워크 비용 등을 종합한 값
- 데이터 양, 인덱스 카디널리티, 조인 순서 등 다양한 요소를 고려
- 대부분의 최신 DBMS에서 기본 방식
옵티마이저가 고려하는 주요 요소
옵티마이저가 "이 경로가 더 빠르다"를 판단하는 데 참고하는 정보는 다음과 같습니다.
| 요소 | 설명 | 예시 |
| 통계 정보 | 테이블 행 수, 블록 수, 인덱스 분포 | ANALYZE TABLE 또는 DBMS_STATS로 수집 |
| 인덱스 유무 | 특정 조건절이 인덱스를 탈 수 있는지 여부 | WHERE emp_id = 100 |
| 조인 순서 | 테이블을 어떤 순서로 조인할지 결정 | Small → Large 순서 |
| 조인 방식 | NESTED LOOP, HASH JOIN, MERGE JOIN 중 선택 | 대량 데이터는 HASH JOIN |
| 병렬 처리 가능 여부 | 병렬 쿼리 실행 여부 판단 | PARALLEL 힌트 사용 가능 |
옵티마이저 관련 실무 팁
- 통계 정보 최신화:
통계 정보가 오래되면 잘못된 실행 계획을 선택할 수 있음.
→ 주기적으로 DBMS_STATS.GATHER_TABLE_STATS 수행 - 힌트(Hint) 사용은 신중하게:
옵티마이저가 자동으로 최적 경로를 선택하나, 특정 상황에서는 힌트로 조정 가능
→ 단, 데이터 양 변화 시 힌트가 오히려 성능을 악화시킬 수 있음 - 실행 계획 확인 습관:
쿼리 튜닝 시 EXPLAIN PLAN 또는 AUTOTRACE를 사용해 반드시 실행 계획 확인 - 필터 조건 위치 조정:
조건절 순서와 위치에 따라 옵티마이저의 선택이 달라질 수 있음
마무리
옵티마이저를 이해하면 단순히 "인덱스 만들자" 수준이 아니라, 왜 그 인덱스가 선택되는지, 어떤 조인 방식이 유리한지를 논리적으로 판단할 수 있습니다. SQL 튜닝의 절반은 옵티마이저의 의사결정 과정을 이해하는 것에서 시작됩니다.