옵티마이저와 실행계획

실행계획

• sql에서 요구한 사항을 처리하기 위한 절차와 방법을 의미
• SQL 실행계획이 달라진다해도 결과값은 바뀌지 않는다.
• 최적화 정보는 실행 계획의 단계별 예상 비용을 표시한 것이다.
• 실행 계획은 액세스 기법, 조인 순서, 조인 방법 등으로 구성된다.

SQL 처리 흐름도

• 실행 계획을 시각화한것이다.
• SQL문의 처리 절차를 시각저긍로 표현한 것이다.
• 인덱스 스캔 및 전체 테이블 스캔 등의 액세스 기법을 표현할 수 있다.
• 성능적인 측면의 표현을 고렿나다. (일량적 측면의 표현과 인덱스 스캔 또는 테이블 스캔 등을 표현 가능)

옵티마이저

• 옵티마이저는 sql의 실행 계획을 수립하고 sql을 실행하는 데이터베이스 관리 시스템의 소프트웨어!
• 사용자가 질의한 sql문에 대해 최적의 실행방법을 결정하는 역할을 수행

옵티마이저의 특징

1. 비용 기반 옵티마이저

• 데이터 딕셔너리에 있는 오브젝트 통계, 시스템 통계 등의 정보를 사용해서 예상되는 비용을 산정한다.
• 여러 개의 실행계획 중 최저 비용을 가지고 있는 계획을 선택해서 sql을 실행한다.

2. 규칙 기반 옵티마이저

• 우선 순위를 가지고 실행계획을 생성한다.
• 우선 순위가 높은 규칙이 적은 일량으로 해당 작업을 수행한다고 판단 (적은 일량 = 높은 우선순위)
• 인덱스의 유무와 sql문에서 참조하는 객체 등을 참고하여 판단

옵티마이저의 필요성

• sql 개발자가 작성한 sql문을 어떻게 실행하느냐에 따라 성능이 달라진다
  
  Ex ) emp 테이블을 먼저 읽느냐, dept 테이블을 먼저 읽느냐에 따라 비교횟수 차이가 발생 ( 성능 차이 발생 )
  

옵티마이저의 실행 방법

• 개발자가 SQL 실행 -> 파싱(문법 검사, 구문 분석) -> 규칙기반 또는 비용기반 실행 계획 수립 -> SQL 실행 / 데이터 인출 (Fetch)

옵티마이저의 실행 계획 확인

• 옵티마이저는 sql실행 계획을 PLAN_TABLE에 저장한다! (개발자가 확인 가능)

옵티마이저 종류

• 옵티마이저 엔진

옵티마이저	설명
Parser	sql문장의 각 요소를 파싱해서 파싱트리 생성 (문법 검사, 구문 분석)
Query Transformer	sql문을 효율적으로 실행하기 위해 더 일반적이고 표준적 형태로 변환
Estimator	통계정보를 사용해서 sql 실행비용 계산
Plan Generator	sql 실행 계획들을 수립
Row-Source Generator	옵티마이저가 생성한 계획을 sql engine이 실행 가능한 코드로 포맷
Sql Engine	sql 실행

규칙 기반 옵티마이저

• 실행 계획을 수립할 때 15개의 우선순위를 기준으로 삼는다!
• 최신 oracle 버전은 규칙 기반 옵티마이저보다 비용 기반 옵티마이저를 사용

우선순위	설명
1	Rowid를 사용한 단일 행인 경우
2	클러스터 조인에 의한 단일 행인 경우
3	유일하거나 기본키를 가진 해시 클러스터 키에 의한 단일 행인 경우
4	유일하거나 기본키에 의한 단일 행인 경우
5	클러스터 조인인 경우
6	해시 클러스터 조인인 경우
7	인덱스 클러스터 키인 경우
8	복합 칼럼 인덱스인 경우
9	단일 칼럼 인덱스인 경우
10	인덱스가 구성된 칼럼에서 제한된 범위를 검색하는 경우
11	인덱스가 구성된 칼럼에서 무제한 범위를 검색하는 경우
12	정렬-병합 조인인 경우
13	인덱스가 구성된 칼럼에서 max 또는 min을 구하는 경우
14	인덱스가 구성된 칼럼에서 order by 하는 경우
15	전체 테이블을 스캔하는 경우

규칙기반 옵티마이저에서 1순위인 rowid 조회 예시 -> select * from emp where rowid='aa';

비용 기반 옵티마이저

• 오브젝트 통계 및 시스템 통계를 사용해서 총 비용을 계산
• sql문을 실행하기 위해 예상되는 소요시간 혹은 자원의 사용량을 총 비용으로 봄
• 총비용이 적은 쪽으로 실행 계획 수립 ( 단, 통계정보가 부적절한 경우 성능 저하 발생할 가능성 있음 )