[SQLP] 7-1. 통계정보와 비용 계산 원리

통계정보에 해당하는 항목과

옵티마이저가 통계정보를 활용해 비용을 계산하는 원리

 

1. 선택도와 카디널리티

선택도	전체 레코드 중에서 조건절에 의해 선택되는 레코드 비율
'=' 조건 검색시 선택도 = 1 / NDV (컬럼 값 종류 개수)
카디널리티	전체 레코드 중에서 조건절에 의해 선택되는 레코드 개수
카디널리티 = 총 로우 수 x 선택도 = 총 로우 수 / NDV

조건절에 대한 선택도가 25%일 때, 전체 레코드가 10만 건이라면,

카디널리티는 2만 5천건이다.

 

옵티마이저는 조건절에 의해 카디널리티를 구하고, 그만큼의 데이터를 액세스하는 데 드는 비용을 계산해

테이블 액세스 방식, 조인 순서, 조인 방식을 결정한다.

 

이처럼 비용을 계산하는데에는 선택도부터 시작되기 때문에, 선택도를 정확히 계산해야 한다.

---

 

2. 통계정보

통계 정보 종류	특징
오브젝트 통계	테이블 통계, 인덱스 통계, 컬럼 통계(히스토그램 포함)
시스템 통계

---

(1) 오브젝트 통계 정보 수집 명령어

테이블 통계		begin  dbms_stats.gather_table_stats('scott', 'emp'); end; /
인덱스 통계	인덱스 통계만	begin  dbms_stats.gather_index_stats ( ownname => 'scott', indname => 'emp_x01'); end; /
	테이블 통계 + 인덱스 통계	begin  dbms_stats.gather_table_stats ( 'scott', 'emp', cascade => true); end; /
컬럼 통계		테이블 통계 수집시 함께 수집된다.

 

(2) 통계 항목

테이블 통계	NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED
인덱스 통계	BLEVEL LEAF_BLOCKS NUM_ROWS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR
컬럼 통계	NUM_DISTINCT DENSITY AVG_COL_LEN LOW_VALUE, HIGH_VALUE NUM_NULLS

 

(3) 수집한 통계정보 조회

(3-1) 테이블 통계정보 조회  [all_tab_statistics 뷰에서도 정보 확인 가능]

SELECT num_rows, blocks, avg_row_len, sample_size, last_analyzed
FROM ALL_TABLES
WHERE owner = 'SCOTT'
AND table_name = 'EMP';

(3-2) 인덱스 통계정보 조회  [all_ind_statistics 뷰에서도 정보 확인 가능]

SELECT blevel, leaf_blocks, num_rows, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key, clustering_factor, sample_size, last_analyzed
FROM ALL_INDEXES
WHERE owner = 'SCOTT'
AND table_name = 'EMP'
AND index_name = 'EMP_X01';

(3-3) 컬럼 통계정보 조회  [all_tab_col_statistics 뷰에서도 정보 확인 가능]

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';

(4) 컬럼 히스토그램

데이터 분포가 균일한 컬럼에서 '=' 조건에 대한 선택도	1/NUM_DISTINCT DENSITY 값 이용
데이터 분포가 균일하지 못한 컬럼의 경우	컬럼 통계에 추가적으로 히스토그램을 활용

히스토그램

: 칼럼 값별로 실제 데이터 비중 또는 빈도를 미리 계산해 놓은 통계정보로, 데이터 분포가 크게 변하지 않는한 정확

 

도수분포 FREQUENCY	값별로 빈도수 저장
높이균형 HEIGHT-BALANCED	각 버킷의 높이가 동일하도록 데이터 분포 관리
상위도수분포 (12c 이상) TOP-FREQUENCY	많은 레코드를 가진 상위 n개 값에 대한 빈도수 저장
하이브리드 (12c 이상) HYBRID	도수분포와 높이균형 히스토그램의 특성 결합

 

(4-1) 히스토그램 수집 명령어

: 테이블 통계 수집시 method_opt 파라미터 지정

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 columns ename size auto' ); end; /

 

(4-2) 수집된 히스토그램 조회 [all_tab_histograms 뷰에서도 정보 확인 가능]

SELECT endpoint_value, endpoint_number
FROM ALL_HISTOGRAMS
WHERE owner = 'SCOTT'
AND table_name = 'EMP'
AND column_nmae = 'DEPTNO'
ORDER BY endpoint_value;

---

 

(5) 시스템 통계

: 애플리케이션 및 하드웨어 성능 특성 측정한 것

 

(5-1) 통계정보 항목

CPU 속도 평균 Single Block I/O, Multiblock I/O 속도 평균 Multiblock I/O 개수 I/O 서브시스템의 최대 처리량 (Throughput) 병렬 Slave의 평균적인 처리량 (Throughput)

옵티마이저가 고려하지 않고, 고정된 상수값으로 처리헀지만, 최적의 실행계획을 위해 변경

애플리케이션 특성 (OLTP, DW) 및 동시 트랜잭션 발생량에 따라서도 성능 특성 변화 가능

-> 하드웨어 및 애플리케이션 특성을 반영하기 위해 시스템 통계 수집 기능 도입

 

(5-2) 시스템 통계 조회

SELECT sname, pname, pval1, pval2
FROM sys.aux_stats$;

---

3. 비용 계산 원리

: 옵티마이저의 통계정보 활용 방법 이해

 

(1) 단일 테이블을 인덱스로 액세스 할 때 비용 계산 원리

인덱스 키값을 모두 '=' 조건으로 검색 : 인덱스 통계만으로 비용 계산	비용=BLEVEL + AVG_LEAF_BLOCKS_PER_KEY + AVG_DATA_BLOCKS_PER_KEY	--인덱스 수직적 탐색 비용 --인덱스 수평적 탐색 비용 --테이블 랜덤 액세스 비용
인덱스 키값을 모두 '=' 조건이 아닐 때 검색 : 인덱스 통계 + 컬럼 통계	비용=BLEVEL + LEAF_BLOCKS x 유효 인덱스 선택도 + CLUSTERING_FACTOR x 유효 테이블 선택도	--인덱스 수직적 탐색 비용 --인덱스 수평적 탐색 비용 --테이블 랜덤 액세스 비용

 

유효 인덱스 선택도	전체 인덱스 레코드 중 액세스 조건 (스캔 범위를 결정하는 조건절)에 의해 선택될 것으로 예상되는 레코드 비중
유효 테이블 선택도	전체 인덱스 레코드 중 인덱스 칼럼에 대한 모든 조건절에 의해 선택될 것으로 예상되는 레코드 비중 -> 이 조건절에 의해 테이블 액세스 여부 결정
컬럼 통계 및 히스토그램을 이용해 계산

---

비용 계산

- I/O 비용 모델의 경우	I/O 비용 모델 사용시 예상 I/O Call 횟수
- CPU 비용 모델의 경우	Single Block I/O 기준 상대적 시간을 표현

 

-> CPU 비용 모델로의 변화 이유

: 같은 실행계획으로 같은 양의 데이터를 읽어도 애플리케이션 및 하드웨어 성능 특성에 따라 절대 소요시간이 다를 수 있기 때문에