[DAP자격증] 과목5 - 옵티마이저(Optimizer)란?

1. 옵티마이저(Optimizer)란?

**옵티마이저(Optimizer)**는 데이터베이스 관리 시스템(DBMS)에서 SQL을 실행할 때 최적의 실행 계획(Execution Plan)을 선택하는 역할을 하는 핵심 엔진이야.

SQL이 실행되면, DBMS는 다양한 실행 방법을 고려할 수 있어. 예를 들어:

• 인덱스를 사용할지, 풀 테이블 스캔을 할지
• Nested Loop Join을 할지, Hash Join을 할지
• 병렬 처리를 활용할지, 단일 처리할지

이러한 선택을 자동으로 결정하여 가장 효율적인 실행 계획을 선택하는 역할을 하는 것이 옵티마이저야.

---

2. 옵티마이저의 종류

옵티마이저는 SQL을 최적화하는 방식에 따라 두 가지 방식으로 나뉘어:
1️⃣ 규칙 기반 옵티마이저(Rule-Based Optimizer, RBO)
2️⃣ 비용 기반 옵티마이저(Cost-Based Optimizer, CBO)

각각의 차이점을 상세히 설명할게.

---

3. 규칙 기반 옵티마이저 (Rule-Based Optimizer, RBO)

미리 정해진 규칙(우선순위)에 따라 실행 계획을 결정하는 방식

(1) 동작 방식

• 미리 정해진 **우선순위(Heuristic Rules)**를 기준으로 실행 계획을 선택함.
• 예를 들어, 옵티마이저가 다음과 같은 규칙을 가지고 있다고 가정하자:
  1. 인덱스 스캔(Index Scan)은 항상 테이블 풀 스캔보다 우선한다.
  2. Nested Loop Join은 항상 Hash Join보다 우선한다.
  이렇게 고정된 규칙을 기반으로 실행 계획을 결정해.
• 실행 비용(Cost)을 계산하지 않고, 규칙에 따라 실행 계획을 선택하기 때문에 간단하지만 유연성이 부족함.

(2) 장점

✅ 예측 가능성이 높음 → 같은 SQL이면 항상 동일한 실행 계획을 선택함.
✅ 오버헤드가 적음 → 실행 계획을 빠르게 선택할 수 있음.

(3) 단점

❌ 실제 데이터 분포를 고려하지 않음 → 최적의 실행 계획이 아닐 수도 있음.
❌ 정적인 규칙만 따름 → 일부 경우에는 비효율적인 실행 계획을 선택할 수도 있음.
❌ 복잡한 SQL 최적화에 한계 → 대용량 데이터를 다룰 때 적절하지 않을 수 있음.

(4) 사용 예시

과거 Oracle 7 이전 버전까지 사용되었으며, 지금은 거의 사용되지 않음.
현재는 **비용 기반 옵티마이저(CBO)**가 대체하고 있음.

---

4. 비용 기반 옵티마이저 (Cost-Based Optimizer, CBO)

SQL 실행 비용을 계산하여 최적의 실행 계획을 선택하는 방식

(1) 동작 방식

• **통계 정보(Statistics)**를 활용하여 SQL 실행에 필요한 예상 비용(Cost)을 계산함.
• 실행 가능한 다양한 실행 계획을 평가한 후, 가장 낮은 비용을 가지는 실행 계획을 선택함.

(2) 실행 계획 선택 과정

1️⃣ 가능한 모든 실행 계획(Execution Plan) 생성

• 예를 들어, 같은 SQL이라도 풀 테이블 스캔, 인덱스 스캔, 다양한 조인 방식(Nested Loop, Hash Join, Sort Merge Join) 등을 고려하여 여러 실행 계획을 생성함.

2️⃣ 각 실행 계획의 비용(Cost) 계산

• DB 내부의 통계 정보를 활용하여 예상 I/O, CPU 사용량, 메모리 사용량 등을 평가함.
• 예를 들어, WHERE 조건에 의해 선택될 행(Row)의 개수를 예측하여 인덱스를 사용할지 여부를 결정함.

3️⃣ 가장 낮은 비용을 가진 실행 계획 선택

• 예측된 비용이 가장 낮은 실행 계획을 최종적으로 선택하여 실행함.

(3) 장점

✅ 데이터 분포에 따라 최적의 실행 계획 선택 가능 → 더 효율적인 SQL 실행이 가능
✅ 대용량 데이터에 적합 → RBO보다 더 나은 성능을 제공
✅ 다양한 최적화 기법 적용 가능 → 병렬 처리, 인덱스 사용 여부, 캐시 활용 등

(4) 단점

❌ 통계 정보가 부정확하면 비효율적인 실행 계획이 선택될 수 있음
❌ 규칙 기반보다 옵티마이저 오버헤드가 증가할 수 있음
❌ 실행 계획이 환경에 따라 달라질 수 있어 예측이 어려울 수도 있음

(5) 사용 예시

현재 거의 모든 DBMS(Oracle, MySQL, PostgreSQL, SQL Server 등)는 **비용 기반 옵티마이저(CBO)**를 사용하고 있음.
특히 Oracle 10g 이후부터는 기본적으로 CBO만 사용하도록 설정됨.

---

728x90

 

5. RBO vs CBO 비교 정리

비교 항목규칙 기반 옵티마이저 (RBO)비용 기반 옵티마이저 (CBO)

결정 방식	미리 정의된 규칙 기반	실행 비용을 계산하여 최적의 계획 선택
데이터 통계 활용	❌ 사용하지 않음	✅ 사용함 (행 수, 인덱스, 분포도 등)
SQL 실행 최적화 수준	제한적 (정적인 최적화)	동적 최적화 가능
실행 계획 예측 가능성	✅ 일정함 (항상 같은 계획 선택)	❌ 데이터 변화에 따라 달라질 수 있음
대용량 데이터 처리	❌ 비효율적일 수 있음	✅ 더 적합함
사용 여부	현재는 거의 사용되지 않음	현재 대부분의 DBMS에서 기본 사용

---

6. 결론 (현재는 CBO가 표준!)

💡 과거에는 RBO가 사용되었지만, 데이터 양이 증가하고 복잡한 SQL이 많아지면서 RBO의 한계가 드러남.
💡 현재는 거의 모든 DBMS가 비용 기반 옵티마이저(CBO)를 사용하여 최적의 실행 계획을 선택함.
💡 CBO를 제대로 활용하려면 정확한 통계 정보(Statistics)를 유지하는 것이 중요함!