[SQLD] 28. 옵티마이저와 실행계획

제1절 옵티마이저와 실행계획

1. 옵티마이저

• 사용자가 실행하는 SQL문은 파서에게 전달되고 파서는 데이터 딕셔너리 정보를 참조하여 SQL문에 대한 구문분석을 수행한다.이결과를parse-tree라고 함
• 파스트리는 옵티마이저에게 전달 옵티마이저에 의해 산출된 적정플랜은 실행계획으로 결정, 실행계획은 SQL엔진에의해 테이블과 인덱스를 참조하여 결과를 사용자에게 리턴

• 옵티마이저는 사용자가 질의한 SQL문에 대해 최적의 실행 방법을 결정하는 역할을 수행
• 옵티마이저가 최적의 실행 방법을 결정하는 방식에 따라 규칙기반 옵티마이저와,비용기반 옵티마이저로 구분할 수 있다.

가. 규칙기반 옵티마이저

• 규칙기반 옵티마이저는 우선순위가 높은 규칙이 적은 일량으로 해당작업을 수행하는 방법

Oracle의 규칙기반 옵티마이저의 15가지 규칙

순위 엑세스 기법 설명

순위	액세스 기법	설명
1	Single Row By Rowid	ROWID에 의한 단일 로우
2	Single Row By Cluster Join	클러스터 조인에 의한 단일 로우
3	Single Row By Hash Cluster Key whit Unique or Primary Key	유일하거나 PK를 가진 해시 클러스터키에 의한 단일 로우
4	Single Row By Unique or Primary Key	유일하거나 PK에 의한 단일 로우
5	Clustered Join	클러스터 조인
6	Hash Cluster Key	해시 클러스터 키
7	Indexed Cluster Key	인덱스 클러스터 키
8	Composite Index	복합 컬럼 인덱스
9	Single-Column Indexes	단일 컬럼 인덱스
10	Bounded Range Search on Indexed Columns	인덱스기 구성된 컬럼에서 제한된 범위 검색
11	Unbounded Range Search on Indexed Columns	인덱스가 구성된 컬럼에서 무제한 범위 검색
12	Sort Merge Join	정렬-병합 조인
13	MAX or MIN of Indexed Column	인덱스가 구성된 열에서 MAX 또는 MIN
14	ORDER BY on Indexed Column	인덱스가 구성된 열에서 ORDER BY
15	Full Tabel Scan	풀 테이블 스캔

• 이용가능한 인덱스가 존재한다면 항상 풀테이블 스캔보다 인덱스 스캔을 사용하는 실행계획 생성
• 규칙기반 옵티마이저가 조인 순서를 결정할 때는 조인 칼럼 인덱스의 존재 유무가 중요한 판단의 기준
• 한쪽 조인 칼럼에만 인덱스가 존재하는경우에는 인덱스가 없는 테이블을 선행테이블로 선택해서 조인을 수행
• 조인 칼럼에 모두 인덱스가 존재하지 않으면 From절의 뒤에 나열된 테이블을 선행테이블로 선택
• 조인테이블의 우선순위가 동일하다면 From절에 나열된 테이블의 역순으로 선행테이블 선택

• 양쪽 조인 칼럼에 모두 인덱스가 없는 경우에는 Sort Merge Join을 사용하고 둘 중하나라도 조인 칼럼에 인덱스가 존재한다면 NL Join을 사용한다.

SELECT ENAME
FROM EMP
WHERE JOB = 'SALESMAN'
AND SAL BETWEEN 3000 AND 6000

INDEX
-------------------------------------------
EMP_JOB : JOB
EMP_SAL : SAL
PK_EMP : EMPNO(UNOQUE)

• JOB조건은 규칙9 단일 칼럼 인덱스 만족 <=우선순위 높음
• SAL조건은 규칙10 인덱스상의 양쪽 한정 검색을 만족

나. 비용기반 옵티마이저

• SQL문을 처리하는데 필요한 비용이 가장 적은 실행계획을 선택하는 방식
  

  

• 대안계획생성기 : 동일한 결과를 생성하는 다양한 대안 계획을 생성하는 모듈, 동일한 결과를 생성하는 가능한 모든 대안 계획을 생성해야 보다 나은 최적화 수행 가능, 대안 계획의 생성이 너무 많아지면 최적화를 수행하는 시간이 오래 걸릴 수 있다.

• 비용 예측기: 대안계획 생성기에 의해 생성된 대안 계획의 비용을 예측하는 모듈, 보다 나은 예측을 위해 옵티마이저는 정확한 통계정보를 필요

2. 실행계획

• 실행계획(Execution Plan)이란 SQL에서 요구한 사항을 처리하기 위한 절차와 방법을 의미

• 실행계획을 구성하는 요소에는 조인순서(Join Order), 조인기법(Join Method), 엑세스 기법(Access Method), 최적화 정보(Optimization Information), 연산(Operation) 등이 있다.
  

  

• cardinality: 주어진 조건을 만족한 결과 집합 혹은 조인 조건을 만족한 결과 집합의 건수
  full-table scan조건을 만족하는 행수를 의미하지만 검색되는 컬럼이 어떤 속성을 가지고 있느냐에 따라 계산 공식이 달라진다.
• Distinct Cardinality(Unique-Key),Efficient Cardinality(Non-Unique-Key),Group Cardinality(Broup by절),
  Cost : 상대적인 비용정보
• Unique index Scan,Fast Full Index Scan,Index Range Scan,Sort-Merge Join, Nest-Loop조인,Hash조인 등에따라 계산공식이 조금씩 달라짐

3. SQL 처리 흐름도

• 실행계획을 시각화
  

  

• TABL1을 outer Table 또는 Driving table 이라하고 TAB2를 Inner TAble 또는 Lookup table이라고 한다.
• 조인순서 TAB1->TAB2
• TAB1 : 풀스캔 TAB2는 인덱스스캔
• 조인방법 NL Join
• TAB1에대한 엑세스는 SCAN, TAB2는 랜덤엑세스(대량의 데이터를 랜덤엑세스하면 많은 I/O 발생으로 성능에 좋지않다.)

 

 

[출처]

wiki.gurubee.net/pages/viewpage.action?pageId=27428156