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

1.   옵티마이저

-       옵티마이저(Optimizer)
사용자가 질의한 SQL문에 대해 최적의 실행 방법을 결정하는 역할 수행
이러한 최적의 실행방법을 실행계획(Execution plan)이라 한다.
 - 최적의 실행 방법 결정 : 어떤 방법으로 처리하는 것이 최소 일량으로 동일한 일을 처리할 수 있을지 결정하는 것.

-       옵티마이저가 최적의 실행방법을 결정하는 방식

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

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

가.  규칙기반 옵티마이저

-       규칙기반 옵티마이저는 규칙(우선 순위)을 가지고 실행계획을 생성한다.

-       실행계획을 생성하는 규칙을 이해하면 누구나 실행계획을 비교적 쉽게 예측 가능하다.

 

인덱스를 이용한 액세스 방식이 전체 테이블 액세스 방식보다 우선순위가 높다.

-       이용 가능한 인덱스가 존재한다면 전체 테이블 액세스 방식보다는 항상 인덱스를 사용하는 실행계획을 생성한다.

조인순서를 결정 시 조인 칼럼 인덱스의 존재 유무가 중요한 판단의 기준

조인 칼럼에 대한 인덱스가 양쪽에 존재 : 우선순위가 높은 테이블이 선행테이블(Driving Table)

한쪽에만 인덱스 존재 : 인덱스가 없는 테이블이 선행 테이블(NL Join 사용)

모두 인덱스가 존재하지 않을 경우 : FROM 절의 뒤에 나열된 테이블이 선행 테이블로 선택(Sort Merge Join 사용)

우선순위가 동일 : FROM 절에 나열된 테이블의 역순으로 선행 테이블 선택

 

나.  비용기반 옵티마이저

-       비용(예상되는 소요시간, 자원 사용향)이 가장 적은 실행계획을 선택하는 방식

-       규칙기반 옵티마이저의 단점을 극복하기 위해서 출현

-       다양한 객체 통계정보와 시스템 통계정보 등 이용 – 통계정보 없을 경우 : 정확한 비용 예측이 불가능해 비효율적인 실행계획을 생성한다.
(정확한 통계정보 유지하는 것은 비용기반 최적화에 중요한 요소)

l  질의 변환기 : 사용자가 작성한 SQL문을 처리하기에 보다 용이한 형태로 변환하는 모듈

l  대안 계획 생성기 : 동일한 결과를 생성하는 다양한 대안 계획을 생성하는 모듈

n  대안계획은 연산의 적용 순서 변경, 연산 방법 변경, 조인 순서 변경 등을 통해 생성한다.

n  대안계획의 생성이 많아지면 최적화를 수행하는 시간이 그만큼 오래걸린다.

 

대부분의 상용 옵ㅌ마이저들은 대안 계획의 수를 제약하는 다양한 방법을 사용한다.

대안 계획들 중에서 최적의 대안 계획이 포함되지 않을 수도 있다.

l  비용 예측기 : 생성된 대안 계획의 비용을 예측하는 모듈

n  연산의 중간 집합의 크기 및 결과 집합의 크기, 분포도 등의 예측이 정확해야 한다.

n  정확한 통계정보, 대안 계획을 구성하는 각 연산에 대한 비용 계산식이 정확

 

규칙기반 옵티마이저

-       항상 인덱스를 사용할 수 있다면 전체 테이블 스캔 보다는 인덱스를 사용하는 실행계획을 생성한다.

비용기반 옵티마이저

-       인덱스를 사용하는 비용이 전체 테이블 스캔 비용보다 크다고 판단되면 전체 테이블 스캔을 수행하는 방법으로 실행계획을 생성할 수도 있다.

-       통계정보, DBMS 버전, DBMS 설정 정보 등의 차이로 인해 동일 SQL문도 서로 다른 실행계획이 생성될 수 있다.

-       또한 비용기반 옵티마이저의 다양한 한계들로 인해 실행계획의 예측 및 제어가 어렵다.

 

2.   실행계획

-       SQL에서 요구한 사항을 처리하기 위한 절차와 방법

-       다양한 처리 방법(실행계획)마다 실행 시간(성능)은 서로 다를 수 있다.(옵티마이저는 최적의 실행계획 생성)

-       실행계획 구성 요소

n  조인 순서(Join Order)

n  조인 기법(Join Method)

n  액세스 기법(Access Method)

n  최적화 정보(Optimization Information) : 실행계획의 각 단계마다 예상되는 비용 사항 표시
* Cost : 상대적인 비용정보
 Card : Cardinality의 약자, 결과집합의 건수
 Bytes : 결과집합이 차지하는 메모리 양의 바이트

 

* 비용정보 : 실제 SQL 실행하고 얻은 결과가 아니라 통계 정보 바탕으로 계산한 예상치

n  연산(Opreation) : 여러 조작을 통해 원하는 결과를 얻어내는 일련의 작업

 

3.   SQL 처리 흐름도

-       SQL의 내부적인 처리 절차를 시각적으로 표현한 도표(실행계획을 시각화)

-       조인 순서, 액세스 기법과 조인 기법 등을 표현 가능

-       액세스 건수 : TAB1을 액세스한 건수
A.COL1 관련 인덱스가 없어서 전체 테이블 스캔 수행을 의미(액세스 건수 = TAB1테이블 총 건수)

-       조인 시도 건수 : TAB1에서 읽은 건에 대해 A.COL1 =: condition1 조건을 만족한 건수

-       테이블 액세스 건수 : B.KEY로 구성된 인덱스인 I01_TAB2에서 즉, 조인 시도한 건들 중 B.KEY = A.KEY 조건을 만족한 건수

-       성공 건수 : 결과 건수로 TAB2 에서 B.COL2 =: condition2 조건에 만족하는 건수