친절한 SQL 튜닝_7장.SQL 옵티마이저_1

 

SQL 옵티마이저

 

7.1 통계정보와 비용 계산 원리

 

7.1.1 선택도와 카디널리티

 

선택도(Selectivity)란, 전체 레코드 중에서 조건절에 의해 선택되는 레코드 비율을 말하낟. 가장 단순한 ‘=’ 조건으로 검색할 때의 선택도만 살펴보면, 컬럼 값 종류 개수(Number of Distinct Values, 이하 ‘NDV’)를 이용해 아래와 같이 구한다.

 

선택도 = 1 / NDV

 

카디널리티(Cardinality)란, 전체 레코드 중에서 조건절에 의해 선택되는 레코드 개수이며, 아래 공식으로 구한다.

 

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

 

 

 

7.1.2 통계정보

 

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

 

 

 

(1) 테이블 통계

 

테이블 통계를 수집하는 명령어는 다음과 같다.

 

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(‘SCOTT’, ‘EMP’);
END;
/

 

수집된 테이블 통계정보는 아래와 같이 조회할 수 있고, ALL_TAB_STATISTICS 뷰에서도 같은 정보를 확인할 수 있다.

SELECT *
  FROM ALL_TABLES
 WHERE OWNER = 'SCOTT'
   AND TABLE_NAME = ‘EMP’;

 

 

 

주요 테이블 통계항목에 대한 설명

 

NUM_ROWS : 테이블에 저정된 총 레코드 개수

 

BLOCKS : 테이블 블록 수 = ‘사용된’ 익스텐트(데이터가 한 건이라도 입력된 적이 있는 모든 익스텐트)에 속한 총 블록 수

 

* 테이블에 ‘할당된’ 총 블록 수는 DBA_SEGMENTS 또는 USER_SEGMENTS 뷰에서 확인 가능

 

AVG_ROW_LEN : 레코드당 평균 길이(Bytes)

 

SAMPLE_SIZE : 샘플링한 레코드 수

 

LAST_ANALYZED : 통계정보 수집일시

 

 

 

(2) 인덱스 통계

 

인덱스 통계를 수집하는 명령어는 다음과 같다.

 

-- 인덱스 통계만 수집

 

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 : 인덱스 키값 기준으로 테이블 데이터가 모여 있는 정도, 인덱스 전체 레코드를 스캔하면서 테이블 레코드를 찾아 갈 때 읽게 될 테이블 블록 개수를 미리 계산해 놓은 수치

 

 

 

(3) 컬럼 통계

 

컬럼 통계는 테이블 통계 수집할 때 함께 수집된다. 수집된 컬럼 통계정보는 아래와 같이 조회할 수 있다. 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 값을 이용하면 된다. 일반적인 컬럼에는 이 공식이 비교적 잘 들어맞지만, 데이터 분포가 균일하지 않은 컬럼에는 그렇지 못하다. 선택도를 잘못 구하면 데이터 액세스 비용을 잘못 산정하게 되고, 결국 최적이 아닌 실행계획으로 이어진다. 그래서 옵티마이저는 일반적인 컬럼 통계 외에 히스토그램을 추가로 활용한다.

 

 

 

(4) 시스템 통계

 

시스템 통계는 애플리케이션 및 하드웨어 성능 특성을 측정한 것이며, 아래 항목들을 포함한다.

 

- CPU 속도

 

- 평균적인 Single Block I/O 속도

 

- 평균적인 Multiblock I/O 속도

 

- 평균적인 Multiblock I/O 개수

 

- I/O 서브시스템의 최대 처리량(Throughput)

 

- 병렬 Slave의 평균적인 처리량(Throughput)

 

 

 

7.1.3 비용 계산 원리

 

단일 테이블을 인덱스로 액세스할 때의 비용 계산 원리를 간단히 살펴보자.

 

인덱스 키값을 모두 ‘=’ 조건으로 검색할 때는 아래와 같이 인덱스 통계만으로도 쉽게 비용을 계산할 수 있다.

 

비용 = BLEVEL -- 인덱스 수직적 탐색 비용

 

+ AVG_LEAF_BLOCKS_PER_KEY -- 인덱스 수평적 탐색 비용

 

+ AVG_DATA_BLOCKS_PER_KEY -- 테이블 랜덤 액세스 비용

 

인덱스 키값이 모두 ‘=’ 조건이 아닐 때는 아래와 같이 컬럼 통계까지 활용한다.

 

비용 = BLEVEL -- 인덱스 수직적 탐색 비용

 

+ LEAF_BLOCKS * 유효 인덱스 선택도 -- 인덱스 수평적 탐색 비용

 

+ CLUSTERING_FACTOR * 유효 테이블 선택도 -- 테이블 랜덤 액세스 비용

 

BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR는 인덱스 통계에서 얻을 수 있고, 유효 인덱스 선택도와 유효 테이블 선택도는 컬럼 통계 및 히스토그램을 이용해 계산한다.

출처 : 친절한 SQL 튜닝 - 조시형 지음