[SQLD-SQL 최적화 기본원리]1.옵티마이저와 실행계획

 

옵티마이저는 SQL 문을 실행하기 위한 최적의 실행 계획을 결정하는 핵심 엔진입니다.

SQL 문에서 데이터를 검색하거나 처리하는 데 소요되는 리소스를 최소화하는 실행 방법을 선택합니다.

옵티마이저에 대한 이해는 데이터베이스 성능을 최적화하는 데 필수적입니다.

---

1. Oracle 옵티마이저의 주요 특징

1) 옵티마이저 유형

Oracle 옵티마이저는 크게 두 가지 방식으로 작동합니다:

1. 규칙 기반 옵티마이저(RBO, Rule-Based Optimizer)
   • Oracle 9i까지 지원되었으며, 고정된 규칙에 따라 실행 계획을 선택합니다.
   • 더 이상 사용되지 않고, 최신 버전에서는 비권장.
2. 비용 기반 옵티마이저(CBO, Cost-Based Optimizer)
   • 최신 Oracle 버전에서 기본적으로 사용되는 옵티마이저입니다.
   • 통계 정보를 기반으로 실행 비용을 계산하여 최적의 실행 계획을 선택합니다.
   • 통계 정보가 정확할수록 더 나은 계획을 선택합니다.

---

2. 옵티마이저의 실행 계획 결정 과정

1) SQL 문 분석

• 쿼리의 구문(Syntax) 및 의미론(Semantics)을 분석합니다.

2) 객체 접근 방법 결정

• 테이블 스캔(Full Table Scan)
• 인덱스 접근(Index Scan)
• 클러스터 접근 등.

3) 조인 순서 및 방식 결정

• Nested Loops Join
• Hash Join
• Sort-Merge Join

4) 실행 비용 계산

• 각 접근 방법과 조합의 비용을 계산하여 가장 낮은 비용의 실행 계획을 선택합니다.

---

3. 옵티마이저의 핵심 요소

1) 통계 정보(Statistics)

• 옵티마이저는 테이블, 인덱스, 열(column) 등과 관련된 통계 정보를 바탕으로 실행 계획을 결정합니다.
• 최신 통계 정보를 유지하는 것이 중요합니다. 

BEGIN 
    DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME'); 
END;

2) 힌트(Hints)   SQLD에는 출제되지 않음

• 옵티마이저의 기본 선택을 우회하고 특정 실행 계획을 강제할 수 있습니다. 

SELECT /*+ FULL(employees) */ * 
FROM employees;

SELECT /*+ USE_NL(emp dept) */ emp.name, dept.name 
FROM employees emp
   , departments dept 
WHERE emp.dept_id = dept.id;

3) 파라미터 설정

• 옵티마이저 동작을 제어하는 중요한 초기화 파라미터:
  • OPTIMIZER_MODE:
    • ALL_ROWS: 전체 처리량 최적화.
    • FIRST_ROWS(n): 빠른 응답 시간 최적화.
  • OPTIMIZER_DYNAMIC_SAMPLING: 동적 샘플링 수준 제어.

---

4. 옵티마이저 실행 계획 조회

EXPLAIN PLAN

• SQL 문에 대한 실행 계획을 사전 분석합니다.

  EXPLAIN PLAN FOR
  SELECT * 
  FROM employees 
  WHERE department_id = 10;
  
  SELECT * 
  FROM TABLE(DBMS_XPLAN.DISPLAY);

V$SQL_PLAN

• 현재 실행 중인 SQL 문에 대한 실행 계획 확인.

  SELECT * 
  FROM V$SQL_PLAN 
  WHERE SQL_ID = 'YOUR_SQL_ID';

DBMS_XPLAN.DISPLAY_CURSOR

• 커서의 실행 계획을 확인.

  SELECT * 
  FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

---

5. 옵티마이저를 활용한 성능 튜닝 전략

(튜닝은 SQLP에서 출제되므로 대략적인 것만 알아두시면 됩니다.)

1. 통계 최신화 : 최신 통계 정보는 비용 기반 옵티마이저의 정확도를 높입니다.
2. 인덱스 최적화 : 적절한 인덱스를 생성하여 쿼리 성능을 개선합니다.
3. 힌트 사용 : 옵티마이저의 비효율적 선택을 방지할 수 있습니다.
4. SQL 리팩토링 : WHERE 절, 조인 순서, 집계 방식 등을 최적화합니다.
5. 병렬 처리 : 대용량 데이터 처리 시 병렬 실행을 고려합니다.
6. 옵티마이저 파라미터 조정 : 데이터베이스 워크로드에 적합한 옵티마이저 모드를 선택합니다.

---