옵티마이저 (Optimizer)

• DBMS의 소프트웨어
• 개발자가 작성한 SQL에 대해 실행계획 수립 후 실행
• 결과는 동일해도 실행계획에 따라 성능이 달라짐
• PLAN_TABLE 테이블에 실행계획을 저장

힌트(HINT)
개발자는 힌트(HINT)를 통해서 옵티마이저에게 실행방법을 알려줄 수 있다.

옵티마이저 실행 및 SQL 최적화 과정

1) 옵티마이저 실행/구동 단계

1. SQL 실행
   개발자가 SQL문을 작성하고 실행

2. SQL Parsing(구문분석)
   SQL의 문법검사 및 구문분석 수행 (구문에러 및 구성요소 등을 파악)

3. 실행계획 수립
   옵티마이저가 SQL 실행계획 수립 (SQL문을 이리저리 변환해서 후보 실행계획을 만듦)

4. 실행계획 저장
   실행계획 수립 및 선정 완료 후 PLAN_TABLE 테이블에 저장함 (후보들 중 최소비용의 실행계획 하나를 선택함)

5. SQL 실행 및 인출
   SQL 실행 및 데이터 인출

옵티마이저 종류

• 규칙 기반 옵티마이저 (RBO, Rule Based Optimizer)
• 비용 기반 옵티마이저 (CBO, Cost Based Optimizer)

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

우선순위 규칙에 따라 실행계획을 생성하며 인덱스가 있으면 반드시 인덱스를 사용한다.

우선순위 규칙

순위	액세스 기법	설명
1	Single row by rowid	ROWID를 통해서 테이블에서 하나의 행을 엑세스하는 방식
2	Single row by cluster join
3	Single row by has cluster key with unique or primary key
4	Single row by unique or primary key	유일 인덱스를 통해 하나의 행을 엑세스하는 방식
5	Cluster join
6	Hash cluster key
7	Indexed cluster key
8	Composite index	복합인덱스에 동등(=) 조건으로 검색하는 경우
9	Single column index	단일 칼럼 인덱스에 '=' 조건으로 검색하는 경우
10	Bounded range search on indexed columns	BETWEEN, LIKE 등- A BETWEEN '10' AND '20' 또는 A LIKE '1%'규칙
11	Unbounded range search on indexed columns	>, <, <=, >= 등규칙
12	Sort merge join
13	MAX or MIN of indexed column
14	ORDER BY on indexed column
15	Full table scan	전체 테이블을 엑세스 하면서 조건절에 주어진 조건을 만족하는 행만을 결과로 추출

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

테이블 및 인덱스 등의 통계 정보(Data Dictionary/시스템 통계정보)를 활용하여 SQL문을 실행하는데 소요될 처리시간 및 CPU, I/O 자원량 등을 계산하여 가장 최소비용으로 효율적일 것으로 예상되는 실행계획을 선택하는 옵티마이저

통계정보

• DBMS가 관리한다.
• 관리항목에는 인덱스, 데이터 밀집도, NULL값 분포도, 컬럼 평균 길이, 테이블별 row 개수, 블록 개수, row 평균 길이 등이 있다.

옵티마이저 엔진 (3가지 모듈)

엔진	역할
Query Transformer 질의 변환기	- SQL문을 효율적으로 실행하기 위함 - 좀 더 일반적이고 표준적인 형태로 변환 - 변환되어도 결과는 동일함
Estimator 비용 예측기	- 최적의 실행계획을 위해 총비용을 계산 - 통계정보를 사용하여 SQL 실행비용 계산 - 각 단계의 선택도, 카디널리티, 비용 계산
Plan Generator 대안 계획 생성기	- SQL을 실행할 실행계획 수립 - 하나의 쿼리를 수행하는 데에 후보군이 될만한 실행계획들을 생성

주의사항

• 통계정보가 부족하거나 부적절한 경우 성능 저하 발생할 수 있다.

---

실행 계획 (Execution Plan)

• SQL 처리를 위한 실행 절차와 방법을 표현한 것
• 조인 방법, 조인 순서, 액세스 기법 등이 표현된다.
• ⭐️ 동일 SQL에 대해서 실행계획이 다르다고 결과가 달라지지는 않는다.
• ⭐️ 비용 기반 옵티마이저(CBO, Cost Based Optimizer)의 실행계획에는 단계별 예상 비용 및 건수가 표시된다.

⭐️ 실행 계획을 통해 아래 정보 등을 알 수 있다.

• 조인 기법
• 최적화 정보(질의 처리 예상 비용 : Cost, Card, Bytes)
• 연산
• 액세스 기법

⭐️ 실행 계획은 예상정보이다.
실제 처리 건수는 트레이스 정보를 통해 알수 있다.

⭐️ 실행 계획 읽는 순서
1. 위 ▶️ 아래
2. 안 ▶️ 밖

SQL 처리 흐름도

• SQL 실행계획을 시각화해서 표현한 것
  ▶️ SQL의 내부적인 처리 절차를 시각적으로 표현해준 것