■ 1. 옵티마이저(Optimizer)란?
1) 정의
옵티마이저는 SQL 질의를 가장 빠르고 효율적으로 실행하기 위한 최적의 실행 계획을 선택하는 DBMS의 핵심 컴포넌트입니다.
2) 목적
- 사용자가 작성한 SQL은 논리적 질의일 뿐이며,
- DBMS는 이를 실제 물리적 연산으로 변환해 실행해야 합니다.
- 이 과정에서 여러 가능한 실행 계획 중 최적 경로를 선택합니다.
■ 2. 옵티마이저의 종류
| 구분 | 설명 |
|---|---|
| 규칙 기반 옵티마이저 (RBO) | - 사전에 정의된 규칙(rule)에 따라 실행 계획 선택 - 예: 인덱스 사용 우선, 조인 순서 고정 등 |
| 비용 기반 옵티마이저 (CBO) | - 통계 정보를 바탕으로 각 실행 계획의 비용을 산정하고 최저 비용 선택 - 현대 DBMS 대부분이 채택 |
기술사 포인트:
- 최신 DB는 대부분 CBO 기반
- 옵티마이저가 사용하는 통계정보는
ANALYZE,DBMS_STATS등을 통해 수집됨
■ 3. 실행 계획 (Execution Plan)
1) 정의
옵티마이저가 선택한 실제 실행 경로이며, 테이블 접근 방식, 조인 순서, 인덱스 사용 여부, 실행 순서 등이 포함됩니다.
2) 주요 요소
| 항목 | 설명 |
|---|---|
| ACCESS PATH | - 테이블에 접근하는 방식 예: Full Table Scan, Index Scan |
| JOIN METHOD | - 조인 방식 예: Nested Loop Join, Hash Join, Merge Join |
| JOIN ORDER | - 다중 테이블 간 어떤 순서로 조인할지 결정 |
| FILTER | - WHERE 조건 필터링 위치와 적용 방식 |
| SORT | - ORDER BY나 GROUP BY 수행 방식 |
■ 4. 옵티마이저의 주요 결정 요소
옵티마이저는 다음 정보를 기반으로 실행 계획을 수립합니다:
| 요소 | 설명 |
|---|---|
| 통계 정보 | 테이블, 인덱스, 컬럼의 분포, 데이터 수 등 |
| 시스템 자원 | 메모리 크기, CPU 속도, 병렬 처리 지원 여부 |
| SQL 구조 | 서브쿼리 여부, 집계 함수, 조인 수 등 |
| 힌트(HINT) | 사용자가 SQL에 직접 옵티마이저 지시 가능 (/*+ INDEX(...) */) |
■ 5. 실행 계획 확인 방법
(1) Oracle
EXPLAIN PLAN FOR
SELECT * FROM EMP WHERE DEPTNO = 10;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);또는 AUTOTRACE, SQL_TRACE, TKPROF 등을 사용하여 실행 경로 및 비용 확인
(2) MySQL
EXPLAIN SELECT * FROM EMP WHERE DEPTNO = 10;■ 6. 옵티마이저 튜닝 전략
| 전략 | 설명 |
|---|---|
| 통계정보 최신화 | ANALYZE, DBMS_STATS로 최신 데이터 반영 |
| 힌트 사용 | 옵티마이저에게 직접 인덱스 사용, 조인 순서 지정 |
| SQL 리팩토링 | 서브쿼리를 JOIN으로 변경, 불필요한 연산 제거 |
| 인덱스 설계 최적화 | 조건절 컬럼 중심 인덱스 적용 |
■ 7. 예제: 옵티마이저 실행 계획 비교
-- 인덱스 없는 경우:
SELECT * FROM EMP WHERE DEPTNO = 10;
-- → Full Table Scan 수행
-- 인덱스 존재 시:
CREATE INDEX IDX_EMP_DEPTNO ON EMP(DEPTNO);
-- 옵티마이저는 Index Range Scan을 선택옵티마이저는 비용(Cost) 기반으로 위 두 경로 중 더 빠른 것을 선택합니다.
■ 8. 기술사 시험 관점 정리
- 옵티마이저는 SQL의 물리적 실행 계획을 최적화하는 컴포넌트
- 실행 계획은 옵티마이저가 결정한 구체적 실행 로직
- CBO 기반 최적화, 통계 정보 활용이 핵심
- 실행 계획 분석 능력과 SQL 튜닝 기술이 실무 적용 핵심
■ 9. 어린이 버전 요약
옵티마이저는 길을 알려주는 내비게이션 같아요!
엄마가 “마트 가자”라고 하면, 내비는 가장 빠른 길을 알려주죠.
마찬가지로, SQL이 “데이터 줘!” 하면 옵티마이저가 제일 빠른 방법을 찾아줘요!
