Optimizer

SQL을 아무리 잘 짠다고 해도, 실행 속도가 느리다면 사용자는 불편함을 느끼고 시스템 자원은 낭비된다. 그렇다면 이 SQL이 실제로 어떻게 실행될지는 누가 결정할까? 바로 오라클의 옵티마이저(Optimizer)이다.

옵티마이저는 오라클 데이터베이스에서 SQL 문을 분석하고 최적의 실행 계획을 선택하는 핵심 엔진이다. 데이터베이스 성능은 이 옵티마이저가 어떤 실행 계획을 선택하는가에 따라 크게 달라질 수 있다. 이 글에서는 오라클 옵티마이저의 구조와 동작 방식, 종류, 튜닝 방법까지 깊이 있게 살펴본다.

---

1. 옵티마이저란 무엇인가?

옵티마이저는 SQL 문장을 실행할 때 사용할 수 있는 여러 가지 실행 계획(Execution Plan) 중에서 가장 비용이 적은 계획을 선택하는 역할을 한다. 여기서 비용(Cost)이란 단순한 CPU나 메모리 사용량만이 아닌, 디스크 I/O, 네트워크, 행 수 등 여러 요소를 종합적으로 고려한 상대적 실행 비용을 의미한다.

즉, 옵티마이저는 "이 SQL을 어떻게 실행해야 가장 빠르고 효율적인가?"에 대한 답을 찾아주는 역할을 한다.

---

2. 옵티마이저의 종류

오라클 옵티마이저는 두 가지 방식으로 구분된다.

▸ 2.1 Rule-Based Optimizer (RBO)

• 고정된 규칙에 따라 실행 계획을 결정한다.
• 예: 인덱스가 있으면 무조건 인덱스를 사용한다.
• 상황이나 통계에 관계없이 우선순위만 따르므로 유연성이 떨어진다.
• Oracle 10g부터는 공식적으로 지원이 종료되었다.

▸ 2.2 Cost-Based Optimizer (CBO)

• 현재 오라클의 표준 옵티마이저이다.
• 통계 정보(Statistics)를 기반으로 여러 실행 계획의 비용을 계산하고, 그 중 가장 효율적인 경로를 선택한다.
• 인덱스를 사용할지, 풀 테이블 스캔을 할지, 어떤 조인 방식을 쓸지 등을 모두 이 옵티마이저가 결정한다.

---

3. 옵티마이저의 실행 흐름

옵티마이저가 작동하는 과정은 다음과 같은 단계를 따른다.

▸ 3.1 SQL 파싱(Parsing)

• SQL 문장이 파싱되어 내부적으로 파싱 트리(Parsing Tree)로 변환된다.
• 이 트리가 옵티마이저에게 전달된다.

▸ 3.2 통계 정보 수집

• 옵티마이저는 실행 계획 생성을 위해 테이블, 인덱스, 컬럼의 통계 정보를 참조한다.
• 통계 정보는 DBMS_STATS 패키지를 통해 수집된다.

 

EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');

▸ 3.3 실행 계획 시뮬레이션

• 옵티마이저는 다양한 접근 경로(Access Path)와 조인 순서, 조인 방법(Nested Loop, Hash Join 등)을 조합하여 실행 계획 후보를 만든다.
• 각 계획의 비용을 계산하여 최적의 경로를 선택한다.

▸ 3.4 최종 실행 계획 선택

• 가장 비용이 낮은 실행 계획이 선택되고, SQL 실행 시 해당 계획이 사용된다.
• 실행 계획은 라이브러리 캐시에 저장되어 추후 같은 SQL 실행 시 재사용된다 (Soft Parse).

---

4. 옵티마이저가 고려하는 요소들

요소	설명
통계 정보	테이블과 인덱스의 행 수, 블록 수, 분포 등
인덱스 유무	접근 경로로 인덱스를 사용할 수 있는지
조인 조건	어떤 조인 방식이 효율적인지 (NL, Hash, Merge)
WHERE 조건절	조건의 선택도(Selectivity) 계산
정렬 및 그룹핑	정렬, 집계가 필요한 경우 처리 비용
파티셔닝	파티션 프루닝 가능 여부
옵티마이저 모드	전체 행 빠르게 vs 일부 결과 빠르게

 

---

5. 옵티마이저 통계의 중요성

통계 정보는 옵티마이저가 가장 의존하는 데이터이다. 통계가 오래되었거나 실제 데이터와 불일치할 경우, 잘못된 실행 계획을 선택할 수 있다. 따라서 통계 정보는 정기적으로 수집하고, 대량 데이터 변경 후에는 즉시 갱신해 주는 것이 좋다.

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');

또한, 컬럼 값의 데이터 분포가 특정 값에 몰려 있는 경우에는 히스토그램(Histogram)을 활용해 옵티마이저가 더 정밀한 판단을 하게 만들 수 있다.

---

6. 옵티마이저 모드 설정

오라클은 다양한 실행 전략에 대응하기 위해 여러 옵티마이저 모드를 제공한다.

 

모드	설명
ALL_ROWS	전체 결과를 빠르게 처리 (배치 처리 중심)
FIRST_ROWS_n	처음 n개의 결과를 빠르게 반환 (인터랙티브 처리)

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_10;​

---

7. 실행 계획 확인하기

옵티마이저가 선택한 실행 계획은 다음 명령어를 통해 확인할 수 있다.

EXPLAIN PLAN FOR 
SELECT * FROM emp WHERE empno = 7788; 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

 

또는 실제 실행된 SQL의 실행 계획은 V$SQL_PLAN, V$SQL 등의 뷰에서 확인 가능하다.

---

8. 옵티마이저 튜닝 방법

옵티마이저는 자동으로 실행 계획을 선택하지만, 상황에 따라 수동 조정이 필요한 경우가 있다. 이럴 때 사용할 수 있는 기법들은 다음과 같다.

▸ 힌트(Hint)

• SQL 내에 힌트를 삽입하여 옵티마이저의 결정을 직접 제어할 수 있다.

SELECT /*+ INDEX(emp emp_name_idx) */ * FROM emp WHERE ename = 'KING';

 

▸ SQL Profile

• SQL 튜닝 어드바이저를 통해 생성되며, 실행 계획 선택에 영향을 준다.

▸ SQL Plan Baseline

• 옵티마이저가 선택한 계획 중 특정 계획만 사용하도록 고정할 수 있다.

▸ 바인드 변수 처리

• 바인드 변수 사용 시 옵티마이저는 바인드 피킹(Bind Peeking)을 통해 첫 실행 시의 값을 기준으로 계획을 선택한다. 이로 인해 계획이 비효율적이 될 수 있으므로, 바인드 인식 옵티마이저(Adaptive Cursor Sharing)가 도입되었다.

---