SQL 옵티마이저
통계정보와 비용 계산 원리
#선택도(Selectivity)
전체 레코드 중에서 조건절에 의해 선택되는 레코드 비율을 말한다.
선택도 = 1 / NDV (Number of Distinct Value, 컬럼 값 종류 개수)
상품분류 컬럼에 가전, 의류, 식음료, 생활용품 4개의 값이 있을 때
선택도는 1/4 = 25% 이다.
#카디널리티(Cardinality)
전체 레코드 중에서 조건절에 의해 선택되는 레코드 개수
카디널리티 = 총 로우 수 * 선택도 = 총 로우 수 / NDV
전체 레코드가 10만건이면, 카디널리티는 10만 / 4 = 2만 5천이다.옵티마이저는 이렇게 카디널리티를 구하고, 그만큼의 데이터를 액세스하는 데 드는 비용을 계산해서 테이블 액세스 방식, 조인 순서, 조인 방식 등을 결정한다.
#통계정보
오브젝트 통계와 시스템 통계가 있다.
오브젝트 통계는 다시 테이블 통계, 인덱스 통계, 컬럼 통계로 나뉜다.
#테이블 통계 생성
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'
NUM_ROWS : 테이블에 저장된 총 레코드 개수
BLOCKS : 테이블 블록 수 = 사용된 익스텐트에 속한 총 블록 수
ALL_ROW_LEN : 레코드당 평균 길이(Bytes)
SAMPLE_SIZE : 샘플링한 레코드 수
LAST_ANALYZED : 통계정보 수집일시
#인덱스 통계
begin
dbms_stats.gather_index_stats(ownname => 'scott', indname => 'emp_idx1');
end;
--테이블 통계를 수집하면서 인덱스 통계도 같이 수집
begin
dbms_stats.gather_table_stats('scott','emp', cascade=>true);
end;
#인덱스 통계정보 조회
select *
from all_indexs
where owner = 'SCOTT'
and table_name = 'EMP'
and index_name = 'EMP_IDX1'
BLEVEL : 브랜치 레벨의 약자, 인덱스 루트에서 리프 블록에 도달하기 직전까지 읽게 되는 블록 수
LEAF_BLOCKS : 인덱스 리프 블록 총 개수
NUM_ROWS : 인덱스에 저장된 레코드 개수
DISTICT_KEYS : 인덱스 키캅의 조합으로 만들어지는 값의 종류 개수.
예를 들어 C1+C2로 구성한 인덱스에서 C1 컬럼 3개 C2 컬럼 4개 값이 있으면 최대 12개
AVG_LEAF_BLOCKS_PER_KEY : 인덱스 키값을 모두 '=' 조건으로 조회할 때 읽게 될 리프 블록 개수
AVG_DATA_BLOCKS_PER_KEY : 인덱스 키값을 모두 '=' 조건으로 조회할 때 읽게 될 테이블 블록 개수
CLUSTERING_FACTOR : 인덱스 키값 기준으로 테이블 데이터가 모여 있는 정도.
#컬럼 통계
select *
from all_tab_columns
where owner = 'SCOTT'
and table_name = 'EMP'
and column_name = 'DEPT_NO';
NUM_DISTINCT : 컬럼 값의 종류 개수
DENSITY : '=' 조건으로 검색할 때의 선택도를 미리 구해 놓은 값
AVG_COL_LEN : 컬럼 평균 길이
LOW_VALUE : 최소 값
HIGH_VALUE : 최대 값
NUM_NULLS : 값이 NULL인 레코드 수
#히스토그램
컬럼 값별로 데이터 비중 또는 빈도를 미리 계산해 놓은 통계 정보.
히스토그램 수집
begin
dbms_stats.gather_table_stats('scott','emp', cascade=>false, method_opt=>'for columns ename size 10, deptno size 4');
end;
begin
dbms_stats.gather_table_stats('scott','emp', cascade=>false, method_opt=>'for all columns size 75');
end;
begin
dbms_stats.gather_table_stats('scott','emp', cascade=>false, method_opt=>'for all columns size auto');
end;
히스토그램 정보 확인
select *
from all_histograms
#시스템 통계
1. CPU 속도
2. 평균적인 Single Blocks I/O 속도
3. 평균적인 Multiblock I/O 속도
4. 평균적인 Multiblock I/O 개수
5. I/O 서브시스템의 최대 처리량
6. 병렬 Slave의 평균적인 처리량
select *
from sys.aux_stats$;
#비용 계산 원리
1) 인덱스 키값을 모두 '=' 조건으로 검색할 때의 인덱스 통계
비용 = BLEVEL (인덱스 수직적 탐색 비용)
+ AVG_LEAF_BLOCKS_PER_KEY (인덱스 수평적 탐색 비용)
+ AVG_DATA_BLOCKS_PER_KEY (테이블 랜던 액세스 비용)
2) 인덱스 키 값이 모두 '=' 조건이 아닐 때
비용 = BLEVEL (인덱스 수직적 탐색 비용)
+ LEAF_BLOCKS * 유효 인덱스 선택도 (인덱스 수평적 탐색 비용)
+ CLUSTERING_FACTOR * 유효 테이블 선택도 (테이블 랜던 액세스 비용)
옵티마이저에 대한 이해
SQL 옵티마이저와 자동차 내비게이션은 유사하다.
#옵티마이저 모드
1. ALL_ROWS : 전체 처리속도 최적화
2. FIRST_ROWS : 최초 응답속도 최적화
3. FIRST_ROWS_N : 최초 N건 응답속도 최적화
예) select /*+ first_rows(30) */ * from t
#옵티마이저에 영향을 미치는 요소
1. SQL과 연산자 형태
2. 인덱스, IOT, 클러스터, 파티션, MV 등 옵티마이저 팩터
3. 제약설정
4. 통계정보
5. 옵티마이저 힌트
6. 옵티마이저 관련 파라미터
select * from v$sys_optimizer_env
#RDBMS 환경에서 SQL 성능을 높이기 위해 개발자가 할 일
1. 필요한 최소 블록만 읽도록 쿼리를 작성한다.
2. 최적의 옵티마이징 팩터를 제공한다.
1)전략적인 인덱스 구성
2)DBMS가 제공하는 다양한 기능 활용 (파티션, 클러스터, IOT, MV, Result Cache 등)
3)옵티마이저 모드 설정
4)정확하고 안정적인 통계정보
3. 필요하다면, 옵티마이저 힌트를 사용해 최적의 액세스 경로로 유도한다.
튜닝 학습
1. 옵티마이저가 SQL을 파싱하고 통계정보를 활용해 실행계획을 생성하는 원리
2. 옵티마이저 쿼리변환 원리를 바탕으로 실행계획을 분석하는 방법
3. 옵티마이저 힌트를 이용해 실행계획을 제어하는 방법
4. 옵티마이저가 좋은 실행계획을 생성하도록 유도하기 위한 효과적인 SQL 작성법
5. 애플리케이션에서 SQL을 실행할 때 사용하는 프로그래밍 인터페이스
6. SQL을 빠르게 처리할 수 있는 좋은 데이터 구조와 파티션/인덱스 설계
7. 정확성과 안정성을 확보할 수 있는 통계정보 수집 정책