SQL 옵티마이저

선택도와 카디널리티

선택도(Selectivity)란, 전체 레코드 중에서 조건절에 의해 선택되는 레코드 비율을 말한다.

선택도 = 1/NDV

*NDV : 컬럼 값 종류 개수

카디널리티란, 전체 레코드 중에서 조건절에 의해 선택되는 레코드의 개수

카디널리티 = 총 로우 수 X 선택도 = 총 로우 수  / NDV

 

통계정보

통계정보에는 오브젝트 통계와 시스템 통계가 있다. 오브젝트 통계는 다시 테이블 통계, 인덱스 통계, 컬럼 통계(히스토그램 포함)로 나뉜다.

 

1) 테이블 통계

# 테이블 통계를 수집하는 명령어
begin
	dbms_stats.gather_table_stats('scott','emp');
end;

# 수집된 테이블 통계정보는 아래와 같이 조회할 수 있다.
select num_rows, blocks, avg_row_len, sample_size, last_analyzed
from all_tables
where owner = 'SCOTT'
and table_name = 'EMP'

 

2) 인덱스 통계

# 인데스 통곔반 수집
begin
	dbms_stats.gather_table_stats( ownname => 'scott', indname =>'emp_x01');
end;

# 테이블 통계를 수집하면서 인덱스 통계도 같이 수집
begin
	dbms_stats.gather_table_stats('scott','emp', cascade=>true);
end;

# 수집된 인덱스 통계정보는 아래와 같이 조회할 수 있다.
select blevel, leaf_blocks, num_rows, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key, clustering_factor
from all_indexes
where owner='SCOTT'
and table_name = 'EMP'
and index_name = 'EMP_X01' ;

 

3) 컬럼 통계

select num_distinct, density, avg_col_len, low_value, high_value, num_nulls, last_analyzed, sample_size
from all_tab_columns
where owner = 'SCOTT'
and table_name = 'EMP'
and column_name = 'DEPTNO';

 

오라클 12c에서 사용하는 히스토그램 유형으로는 네가지가 있다.

• 도수분표 : 값별로 빈도수 저장
• 높이균형 : 각 버킷의 높이가 동일하도록 데이터 분포 관리
• 상위도수분포 : 많은 레코드를 가진 상위 n개 값에 대한 빈도수 저장
• 하이브리드 : 도수분포와 높이 균형 히스토그램의 특성 결정

 

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

• 사용자 쿼리를 위해 후보군이 될만한 실행계획들을 도출하고
• 데이터 딕셔너리에 미리 수집해 둔 통계정보를 이용해 각 실행계획의 예상비용을 산정
• 그 중 가장 낮은 비용의 실행계획 하나를 선택

옵티마이저 모드

• ALL_ROWS: 전체 처리속도 최적화
• FIRST_ROWS : 최초 응답속도 최적화
• FIRST_ROWS_N : 최초 N건 응답속도 최적화

ALL_ROWS는 쿼리 결과집합 '전체를 읽는 것을 전제로' 시스템 리소스를 가장 적게 사용하는 실행계획

FIRST_ROWS는 전체 결과집합 중 '앞쪽 일부만 읽다가 멈추는 것을 전제로' 응답속도가 빠르다.

=> ALL_ROWS와 비교하면, Table full scan보다 인덱스를 더 많이 선택하고, 해시,소트머지 조인 보다 NL조인을 더 많이 선택하는 경향이 있다. 하지만 FIRST_ROWS는 곧 사라지므로 FIRST_ROWS_N을 사용해야한다.

FIRST_ROWS_N:  사용자가 '앞쪽 N개 로우만 읽고 멈추는 것을 전제로' 응답속도가 가장 빠르다

 

 

옵티마이저에 영향을 미치는 요소

1) SQL과 연산자 형태

2) 인덱스, IOT, 클러스터, 파티션, MV등 옵티마이징 팩터

3) 제약 설정

4) 통계정보

5) 옵티마이저 힌트

6) 옵티마이저 관련 파라미터  

 

 

출처: 개발자를 위한 SQL 튜닝 입문서 친절한 SQL 튜닝