SQL튜닝이론 - 통계정보와 비용계산원리 (선택도, 카디널리티, 통계정보, 컬럼히스토그램, 비용계산원리)

SQL튜닝이론 - 통계정보와 비용계산원리 (선택도, 카디널리티, 통계정보, 컬럼히스토그램, 비용계산원리)

 

선택도와 카디널리티

 

SQL 실행의 통계정보를 구성하는 요소에는 선택도와 카디널리티가 포함됩니다. 선택도는 전체 레코드 중에서 조건절에 의해 선택되는 레코드의 비율을 의미합니다. 가장 단순한 = 조건으로 검색할 때의 선택도는 칼럼 값 종류의 개수(NUMBER OF DISTINCT VALUES , 'NDV')로 결정됩니다.

 

선택도 = 1 / NDV

 

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

 

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

 

옵티마이저는 조건의 카디널리티를 구하고, 그만큼의 데이터를 엑세스하는 데 드는 비용을 계산해서 테이블 액세스 방식, 조인 순서, 조인 방식을 결정합니다. 비용을 계산하는 출발점은 선택도입니다. 선택도를 잘 못 계산하면, 비효율적인 액세스 방식과 조인방식을 선택하게 됩니다.

 

통계정보

 

통계정보는 오브젝트 통계와 시스템 통계가 있습니다. 오브젝트 통계는 다시 테이블 통계, 인덱스 통계, 컬럼 통계로 구분됩니다.

 

테이블 통계

 

테이블의 통계를 수집하는 명령은 다음과 같습니다.

 

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

 

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

 

SELECT NUM_ROWS, BLOCKS, AVG_ROW_LEN, SAMPLE_SIZE, LAST_ANALYZED
FROM ALL_TABLES
WHERE OWNER = 'SCOTT'
AND TABLE_NAME = 'EMP'

 

• NUM_ROWS : 테이블에 저장된 총 레코드 수
• BLOCKS : 테이블 블록수(사용된 익스텐트에 속한 총 블록 수)
• AVG_ROW_LEN : 레코드당 평균 길이
• SAMPLE_SIZE : 샘플링한 레코드의 수
• LAST_ALALYZED : 통계정보 수집일시

 

인덱스 통계

 

-- 인덱스 통계만 수집
BEGIN
    DBMS_STATS.GATHER_INDEX_STATS(OWNNAEM => 'SCOTT', INDNAME=>'EMP_X01');
END;
/

-- 테이블 통계를 수집하면서 인덱스 통계도 같이 수빚
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMP', CASCADE=>TRUE);
END;
/

 

수집된 인덱스 통계정보는 아래와 같이 조회할 수 있으며, ALL_IDN_STATISTICS 뷰에서도 같은 정보를 확인할 수 있습니다.

 

SELECT BLEVEL, LEAF_BLOCKS, NUM_ROWS, DISTINCT_KEYS
, AVE_LEAF BLOCKS_PER_KEY, AVG_DATE_BLOCKS_PER_KEY, CLUSTERING_FACTOR
FROM ALL_INDEXES
WHERE OWNER = 'SCOTT'
AND TABLE_NAME = 'EMP'
AND INDEX_NAME = 'EMP_X01'

 

- BLEVEL : 브렌치 레벨로 인덱스 루트에서 리프 블록에 도달하기 까지 읽게 되는 블록의 수
- LEAF_BLOCKS : 인덱스 리프 블록의 총 개수
- NUM_ROWS : 인덱스에 저장된 레코드 개수
- DISTINCT_KEYS : 인덱스 키값의 조합으로 만들어지는 값의 종류 개수
- AVGF_LEAF_BLOCKS_PER_KEY : 인덱스 키값을 모두 = 조건으로 조회할 때 읽게 될 리프 블록 수
- AVG_DATA_BLOCKS_PER_KEY : 인덱스 키값을 모두 = 조건으로 조회할 때 읽게 될 테이블 블록 수
- CLUSTERING-FACTOR : 인덱스 키값 기준으로 테이블 데이터가 모여있는 정도. 인덱스 전체 레코드를 스캔하면서 테이블 레코드를 찾아갈 때 읽게 될 테이블 블록 개수를 미리 계산해 놓은 수치

 

칼럼통계

 

칼럼통계는 테이블 통계를 수집할 때 함께 수집됩니다. 수집된 컬럼 통계정보는 아래와 같이 조회할 수 있습니다.

 

SELECT NUM_DISTINCT, DENSITY, AVG_COL_LEN, LOW_VALUE, HIGH_VALUE, NUM_NULLS
FROM ALL_TAB_COLUMNS
WHERE OWNER = 'SCOTT'
AND TABLE_NAME = 'EMP'
AND COLUMN_NAME = 'DEPTNO'

 

- NUM_DISTINCT : 컬럼 값의 종류 개수
- DENSITY : = 조건으로 검색할 때의 선택도를 미리 구해 놓은 값.
- AVG_COL_LEN : 칼럼의 평균 길이
- LOW_VALUE : 최소 값
- HIGH_VALUE : 최대 값
- NUM_NULLS : 값이 NULL 인 레코드 수

컬럼 히스토그램

'=' 조건에 대한 선택도는 1/NUM_DISTINCT 공식으로 구하거나 미리 구해놓은 DENSITY 값을 이용하면 됩니다. 일반적인 칼럼에는 이 공식이 잘 들어맞지만 데이터 분포가 균일하지 않은 컬럼에는 적용하기 어렵습니다. 선택도를 잘못구하면 최적이 아닌 실행계획으로 이어지기 때문에 옵티마이저는 일반적인 컬럼 통계 외에 히스토그램을 추가로 활용합니다.

오라클 12C에서 사용하는 히스토그램의 유형은 다음과 같습니다.

 

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

 

히스토그램을 수집하려면, 테이블 통계를 수집할 때 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 ALL COLUMNS SIZE AUTO');
END

 

수집된 칼럼 히스토리는 아래와 같이 조회할 수 있습니다. ALL_TAB_HISTOGRAMS 뷰에서도 같은 정보를 확인할 수 있습니다.

 

SELECT ENDPOINT_VALUE, ENDPOINT_NUMBER
FROM ALL_HISTOGRAMS
WHERE OWNER = 'SCOTT'
AND TABLE_NAME = 'EMP'
AND COLUMN_NAME = 'DEPTNO'
ORDER BY  ENDPOINT_VALUE

 

시스템 통계

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

 

- CPU 속도
- 평균적인 SINGLE BLOCK I/O 속도
- 평균적인 MULTIBLOCK I/O 속도
- 평균적인 MULTIBLOCK I/O 개수
- I/O 서브시스템의 최대 처리량
- 병렬 SLAVE의 평균적인 처리량

 

시스템 통계는 아래와 같이 SYS, AUX_STATS$ 뷰에서 조회할 수 있습니다.

 

SELECT SNAME, PANME, PVAL1, PVAL2 FROM SYS/AUX_STATS$;

 

비용계산원리

 

단일 테이블을 인덱스로 액세스할 때의 비용 계산 원리는 다음과 같습니다.

 

비용 = BLEVEL -- 인덱스 수직적 탐색 비용
+ AVG_LEAF_BLOCKS_PER_KEY -- 인덱스 수평적 탐색 비용
+ AVG_DATA_BLOCKS_PER_KEY -- 테이블 랜덤 엑세스 비용

 

인덱스 키값이 모두 = 조건이 아닐 때는 아래와 같은 칼럼 통계까지 활용합니다.

비용 = BLEVEL -- 인덱스 수직적 탐색 비용
+ LEAF_BLOCKS * 유효 인덱스 선택도 -- 인덱스 수평적 탐색 비용
+ CLUSTERING_FACTOR * 유효 테이블 선택도 -- 테이블 랜덤 엑세스 비용

 

유효 인덱스 선택도란, 전체 인덱스 레코드 중 액세스 조건에 의해 선택될 것으로 예상되는 레코드의 비중을 의미합니다. 유효 테이블 선택도란, 전체 인덱스 레코드 중 인덱스 컬럼에 대한 모든 조건절에 의해 선택 될 것으로 예상되는 레코드 비중을 의미합니다. 이들 조건절에 의해 테이블 엑세스 여부가 결정됩니다.

 

COST의 정확한 의미

기존 비용 계산식은 모두 I/O 비용 모델입니다. 이때 COST는 예상 I/O CALL 횟수를 의미합니다. 반면 최신 CPU 비용 모델에서 COST는 SINGLE BLOCK I/O를 기준으로 한 상대적 시간을 표현합니다. 예를 들어 COST 가 100으로 표시되면 시스템에서 SINGLE BLOCK I/O를 100번 하는 정도의 시간으로 해석합니다. 상대적 시간개념입니다. 이는 같은 비용의 데이터라도 애플리케이션 및 하드웨어 성능에 따라 절대 소요시간이 다를 수 있기 때문입니다.