선택도와 카디널리티
선택도
선택도란, 전체 레코드 중에서 조건절에 의해 선택되는 레코드 비율을 말함.
가장 단순한 조건으로 검색할 때의 선택도만 살펴보면, 컬럼 값 종류 갯수를 이용해 아래와 같이 구한다.
💡 NDV : Number of Distinct Values
선택도 = 1 / NDV
카디널리티
카디널리티란 전체 레코드중에서 조건절에 의해 선택되는 레코드 갯수임.
카디널리티 = 총 로우 수 * 선택도 = 총 로우 수 / NDV
예시
상품컬럼에 가전, 의류, 식음료, 생활용품 4개의 값일 있을 때, 아래 조건절 선택도는 25%다.
만약 전체 레코드가 10만건이면, 카디널리티는 2만 5천건이다.
where 상품분류 = '가전'- 옵티마이저는 카디널리티 구함 ⇒ 데이터 액세스 비용 계산 ⇒ 테이블 액세스방식, 조인순서, 조인 방식 결정
- 비용 계산 출발점은 선택도
- 선택도 계산에 NDV를 사용하므로 통계정보 수집과정에서 이 값을 정확히 구하는게 중요.
통계정보
- 오브젝트 통계
- 테이블 통계
- 인덱스 통계
- 컬럼 통계
- 시스템 통계
테이블 통계
-- 테이블 수집 명령어
begin
dbms_stats.gather_table_stats('scott', 'emp');
end;수집된 테이블 통계정보, ALL_TAB_STATISTICS 뷰에서도 같은 정보를 확인할 수 있다.
select num_rows, blocks, avg_row_len, sample_size, last_analyzed
fro, all_tables
where owner = 'SCOTT'
and table_name = 'EMP';| 통계항목 | 설명 |
|---|---|
| NUM_ROWS | 테이블에 저장된 총 레코드 갯수 |
| BLOCKS | 테이블 블록 수 |
| = 사용된 익스텐트에 속한 총 블록수 | |
| AVG_ROW_LEN | 레코드당 평균 길이 (Bytes) |
| SAMPLE_SIZE | 샘플링한 레코드 수 |
| LAST_ANALYZED | 통계정보 수집일시 |
인덱스 통계
-- 인덱스 통계 수집 명령어
-- 인덱스 통계만 수집
begin
dbms_stats.gether_index_stats( ownname => 'scott', indname => 'emp_x01');
end;
-- 테이블 통계를 수집하면서 인덱스 통계도 같이 수집
begin
dbms_stats.gether_table_stats ('scott', 'emp', cascade => true);
end;수집된 인덱스 통계정보는 아래와 같이 조회할 수 있으면, ALL_IND_STATISTICS 뷰에서도 같은 정보를 확인할 수 있다.
select *
from all_indexes
where owner = 'scott'
and table_name = 'emp'
and index_name = 'emp_x01';| 통계항목 | 설명 | 용도 |
|---|---|---|
| BLEVEL | 브랜치 레벨의 약자, 인덱스 루트에서 리프 블록에 도달하기 직전까지 읽게 되는 블록 수 | 인덱스 수직적 탐색 비용 계산 |
| LEAF_BLOCKS | 인덱스 리프 블록 총 개수 | 인덱스 수평적 탐색 비용 계산 |
| NUM_ROWS | 인덱스에 저장된 레코드 개수 | 인덱스 수평적 탐색 비용 계산 |
| DISTINCT_KEYS | 인덱스 키값의 조합으로 만들어지는 값의 종류 개수, 예를 들어, C1 + C2로 구성한 인덱스에서 C1 컬럼에 3개, C2 컬럼에 4개 값이 있으면 최대 12개 값의 종류가 만들어질텐데, 인덱스에 저장된 데이터 기준으로 실제 입력된 값의 종류 개수를 구해 놓은 수치, 인덱스 키값을 모두 '=' 조건으로 조회할 때의 선택도(Selectivity)를 계산하는 데 사용 | 인덱스 수평적 탐색 비용 계산 |
| AVG_LEAF_BLOCKS_PER_KEY | 인덱스 키값을 모두 '=' 조건으로 조회할 때 읽게 될 리프 블록 개수 | 인덱스 수평적 탐색 비용 계산 |
| AVG_DATA_BLOCKS_PER_KEY | 인덱스 키값을 모두 '=' 조건으로 조회할 때 읽게 될 테이블 블록 개수 | 테이블 액세스 비용 계산 |
| CLUSTERING_FACTOR | 인덱스 키값 기준으로 테이블 데이터가 모여 있는 정도, 인덱스 전체 레코드를 스캔하면서 테이블 레코드를 찾아 갈 때 읽게 될 테이블 블록 개수를 미리 계산해 놓은 수치 | 테이블 액세스 비용 계산 |
컬럼 통계
컬럼 통계는 테이블 통계 수집할 때 함께 수집된다.
수집된 컬럼 통계정보 조회. ALL_TAB_COL_STATISTICS 뷰에서도 같은 정보를 확인할 수 있다.
select *
from all_tab_columns
where owner = 'scott'
and table_name = 'emp'
and column_name = 'deptno'| 통계항목 | 설명 |
|---|---|
| NUM_DISTINCT | 컬럼 값의 종류 개수(NDV, Number of Distinct Values) |
| 예를 들어, 성별 컬럼이면 2 | |
| DENSITY | ‘=’ 조건으로 검색할 때의 선택도를 미리 구해 놓은 값. |
| 히스토그램이 없거나, 있더라도 100% 균일한 분포를 갖는다면, 1 / NUM_DISTINCT 값과 일치 | |
| AVG_COL_LEN | 컬럼 평균 길이(Bytes) |
| LOW_VALUE | 최소 값 |
| HIGH_VALUE | 최대 값 |
| NUM_NULLS | 값이 NULL인 레코드 수 |
컬럼 히스토그램
- ‘=’ 조건에 대한 선택도는 1/NUM_DISTINCT 공식으로 구하거나 미리 구해 놓은 DENSITY 값을 이용하면 된다.
- 일반적인 컬럼에는 이 공식이 비교적 잘 들어맞지만, 데이터 분포가 균일하지 않은 컬럼에는 그렇지 못하다.
- 선택도를 잘못 구하면 데이터 액세스 비용을 잘못 산정하게 되고, 결국 최적이 아닌 실행계획으로 이어진다.
- 그래서 옵티마이저는 일반적인 컬럼 통계 외에 히스토그램을 추가로 활용.
오라클 12c에서 사용하는 히스토그램 유형
| 히스토그램 유형 | 설명 |
|---|---|
| 도수분포 | 값별로 빈도수 저장 |
| 높이균형 | 각 버킷의 높이가 동일하도록 데이터 분포 관리 |
| 상위도수분포 | 많은 레코드를 가진 상위 n개 값에 대한 빈도수 저장 (12c) |
| 하이브리드 | 도수분포 높이 균형 히스토그램의 특성결합 (12c) |
시스템 통계
시스템 통계는 애플리케이션 및 하드웨어 성능 특성을 측정한 것이며, 아래 항목들을 포함한다.
- CPU 속도
- 평균적인 Single Block I/O 속도
- 평균적인 Multiblock I/O 속도
- 평균적인 Multiblock I/O 개수
- I/O 서브시스템의 최대 처리량(Throughput)
- 병렬 Slave의 평균적인 처리량(Throughput)
비용 계산 원리
단일 테이블을 인덱스로 액세스할 때의 비용 계산 방법
- 인덱스 키값을 모두 ‘=’ 조건으로 검색할 때, 인덱스 통계만으로도 쉽게 비용을 계산 가능.
비용 = BLEVEL -- 인덱스 수직적 탐색 비용
+ AVG_LEAF_BLOCKS_PER_KEY -- 인덱스 수평적 탐색 비용
+ AVG_DATA_BLOCKS_PER_KEY -- 테이블 랜덤 액세스 비용- 인덱스 키값이 모두 ‘=’ 조건이 아닐 때는 컬럼 통계까지 활용
비용 = BLEVEL -- 인덱스 수직적 탐색 비용
+ LEAF_BLOCKS * 유효 인덱스 선택도 -- 인덱스 수평적 탐색 비용
+ CLUSTERING_FACTOR * 유효 테이블 선택도 -- 테이블 랜덤 액세스 비용- BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR는 인덱스 통계에서 얻을 수 있고, 유효 인덱스 선택도와 유효 테이블 선택도는 컬럼 통계 및 히스토그램을 이용해 계산한다.
- 유효 인덱스 선택도 : 전체 인덱스 레코드 중, 액세스 조건에 의해 선택될 것으로 예상되는 레코드 비중을 의미.
- 유효 테이블 선택도 : 전체 인덱스 레코드 중 인덱스 컬럼에 대한 모든 조건절에 의해 선택될 것으로 예상되는 레코드 비중
뭉실뭉실 코더 운구름