[DB] 데이터베이스 옵티마이저

 

옵티마이저란 SQL문을 수행할 최적의 액세스 경로를 찾아내고 실행계획을 생성하는 DB의 핵심엔진입니다.

최적의 액세스 경로를 찾기 위하여 Rule 또는 통계정보를 활용하여 비용이 적게 드는 액세스 경로를 선택합니다.

 

사용자가 작성한 SQL을 Parser로 파싱한 다음 옵티마이저 mode에 따라 옵티마이저가 선택됩니다.

CBO는 미리 생성한 통계정보를 이용하여 실행계획을 생성하게 됩니다.

옵티마이저에서 생성한 실행계획을 SQL 실행엔진에서 실행하고 실행결과를 사용자에게 보여줍니다.

 

옵티마이저 유형

옵티마이저는 크게 Rule 기반 옵티마이저(RBO)와 비용 기반 옵티마이저(CBO) 이렇게 2가지 유형이 있습니다.

대부분 RDBMS는 CBO를 지원하며, Oracle 10g부터 RBO 지원을 중단하였다고 합니다.

 

 

구분	규칙 기반 옵티마이저(CBO)	비용 기반 옵티마이저(RBO)
개념	사전에 정의된 규칙 기반	최소비용 계산 실행계획 수립
기준	실행우선 순위(Ranking)	액세스 비용(Cost)
인덱스	인덱스 존재 시 가장 우선시 사용	Cost에 의한 결정
성능	사용자 SQL작성 숙련도	옵티마이저 예측 성능
장점	판단이 매우 규칙적 실행 예상 가능	통계 정보를 통한 현실 요소 적용
단점	예측 통계정보 요소 무시	최소 성능 보장 계획의 예측 제어 어려움
사례	AND 중심 양쪽 '=' 시 Index Merge 사용	AND 중심 양쪽 '=' 시 분포도별 Index 선택

 

RBO의 규칙 우선순위 (15가지)

RBO는 우선 순위 규칙이 정해져 있고, 우선 순위를 기반으로 우선 순위가 높은 규칙에 해당하는 실행계획을 생성합니다.

우선순위 숫자가 낮은 것이 높은 우선순위를 갖습니다.

우선순위	설명
1	ROWID를 사용한 단일 행
2	클러스터 조인에 의한 단일 행
3	유일하거나 기본키(Primary Key)를 가진 해시 클러스터 키에 의한 단일 행
4	유일하거나 기본키(Primary Key)에 의한 단일 행
5	클러스터 조인
6	해시 클러스터 조인
7	인덱스 클러스터 키
8	복합 컬럼 인덱스
9	단일 컬럼 인덱스
10	인덱스가 구성된 컬럼에서 제한된 범위를 검색
11	인덱스가 구성된 컬럼에서 무제한 범위를 검색
12	정렬-병합(Sort-Merge) 조인
13	인덱스가 구성된 컬럼에서 MAX 혹은 MIN을 구하는 경우
14	인덱스가 구성된 컬럼에서 ORDER BY를 실행하는 경우
15	전체 테이블을 스캔(FULL TABLE SCAN)하는 경우

 

 

CBO(비용 기반 옵티마이저)

비용 기반 옵티마이저는 옵티마이저에서 실행 계획을 세운 뒤 비용(Cost)이 최소한으로 나온 실행 계획을 수행합니다. 

여기서 비용이란 SQL문을 처리하기 위해 예상되는 소요시간 또는 자원 사용량을 의미합니다.

비용기반 옵티마이저는 비용을 예측하기 위해서 규칙 기반 옵티마이저가 사용하지 않는 테이블, 인덱스, 칼럼 등의 다양한 객체 통계정보와 시스템 통계정보를 이용합니다. 
통상적으로 사용량이 적은 새벽시간에 통계정보를 생성하도록 합니다.

 

CBO 모드

CBO는 모드에 따라 최적의 비용을 계산하는 방식이 약간씩 달라집니다.

모드	설명
CHOOSE	- SQL이 실행되는 환경에서 통계정보를 가져올 수 있으면 CBO를 사용하고 그렇지 않으면 RBO를 작동시키는 모드
FIRST_ROWS	- 옵티마이저가 처리결과 중 첫 건을 출력하는데 걸리는 시간을 최소화할 수 있는 실행계획 수립 모드
FIRST_ROWS_n	- SQL의 실행결과를 출력하는데까지 걸리는 응답속도를 최적화하는 모드
ALL_ROWS	- SQL 실행결과 전체를 빠르게 처리하는데 최적화된 실행계획을 세우는 모드 - 마지막으로 출력될 행까지 최소한의 자원을 사용하여 빠른 시간 안에 가져오도록 함 - Oracle 10g 이후 기본값으로 설정

 

통계정보

CBO에서 사용하는 통계정보는 수동으로 생성하거나 스케쥴러를 이용하여 사용시간이 적은 시간대에 동작하도록 합니다.

통계정보 수집에 따른 일시적인 DB 성능저하가 있을 수 있으므로 수동 및 자동 실행 시 유의해야 합니다.

DBMS_STATS 패키지를 사용하면 데이터베이스, 스키마 및 계정, 테이블 인덱스 단위로 구분하여 통계 정보를 수집할 수 있습니다.

구분	세부 통계 정보
테이블	테이블의 전체 행의 개수
	테이블이 차지하고 있는 전체 블록 개수
	테이블의 행들이 가지고 있는 평균 길이
컬럼	컬럼 값의 종류
	컬럼 내부 NULL 값의 분포도
	컬럼 값의 평균 길이
	컬럼 내부 데이터 분포의 추정치
인덱스	Leaf Block수 : 데이터를 보관하는 블록수
	Levels: 인덱스 트리의 LEVEL 정보
	Clustering Factor : 접근하고자 하는 데이터가 모여있는 밀집도
시스템 통계정보	I/O 성능 및 사용율
	CPU 성능 및 사용율

 

 

통계정보 수집방법

데이터베이스의 통계정보 수집 방법은 크게 두 가지가 있습니다.

ANALYZE는 AVG_SPACE, CHAIN_CNT 등 컬럼들의 통계를 수집하는 반면, DBMS_STAT는 CBO와 관련된 정보만 통계정보로 수집합니다.

 

-- 테이블 통계
ANALYZE TABLE [소유자] COMPUTE STATISTICS FOR [테이블명];
 
ANALYZE TABLE [소유자] ESTIMATE STATISTICS SAMPLE 50 PERCENT
 
FOR [테이블명];
* COMPUT : 전수 검사, ESTIMATE : 표본 검사
 
 
-- 인덱스 통계
ANALYZE INDEX [소유자].[인덱스명] COMPUTE STATISTICS;
⇒ 필요한 인덱스의 통계정보를 수집
 
ANALYZE TABLE [소유자].[테이블명] COMPUTE STATISTICS FOR ALL INDEXES;
⇒ 테이블에 속해 있는 모든 인덱스의 통계정보 수집

 

DBMS_STAT의 패키지의 주요 프로시져

• GATHER_DATABASE_STATS : 데이터베이스의 모든 Object에 대해 통계정보 생성
• GATHER_SCHEMA_STATS : 해당 스키마의 Object에 대한 통계정보 생성
• GATHER_TABLE_STATS : 테이블과 연관된 인덱스에 대한 대해 통계정보 생성
• GATHER_INDEX_STATS : 인덱스에 대해 대해 통계정보 생성