[📕 친절한 SQL 튜닝] 통계정보와 비용 계산 원리

1. 통계 정보와 비용 계산 원리

복잡한 비용 계산 원리까지 학습할 필요는 없지만, 옵티마이저가 통계정보를 어떻게 활용하는지는 간략하게 알아야하므로 정리함

 

• 선택도와 카디널리티, 통계정보에 구체적으로 어떤 항목들이 있는지 확인
• 옵티마이저가 통계정보를 이용해 비용을 계산하는 원리 간략하게 정리

 

 

선택도와 카디널리티

✳️ 선택도 (Selectivity)  

전체 레코드 중에서 조건절에 의해 선택되는 레코드 비율 

 

👉 선택도 = 1 / NDV*

 

* NDV (Number of Distinct Values) : 컬럼 값 종류 개수

 

✳️ 카디널리티 (Cardinality)  

전체 레코드 중에서 조건절에 의해 선택되는 레코드 개수

 

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

 

---

🧐 예를 들어, 상품 분류 컬럼에 '가전', '의류', '식음료', '생활가전' 4개의 값이 있을 때,
WHERE 상품분류='가전'에 대한 선택도는 25%(=1/4)이고,
전체 레코드가 10만 건이면 카디널리티는 2만 5천이다. 

👉 옵티마이저는 이렇게 카디널리티를 구하고, 그만큼의 데이터를 액세스하는 데 드는 비용을 계산해서 테이블 액세스 방식, 조인 순서, 조인 방식 등을 결정

 

비용을 계산하는 출발점은 선택도이므로, NDV를 통계 정보 수집 과정에서 정확히 구하는 것이 매우 중요 !

 

 

통계 정보

🔹 오브젝트 통계

• 테이블 통계
  • 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 (값이 NULL인 레코드 수)

 

✳️ 컬럼 히스토그램

'=' 조건에 대한 선택도는 1/NUM_DISTINCT 공식으로 구하거나 미리 구해놓은 DENSITY 값을 이용하면 됨.
일반적인 컬럼에는 이 공식이 비교적 적합하지만, 데이터 분포가 균일하지 않은 컬럼에는 그렇지 못함. 

👉 그러므로 일반적인 컬럼 통계 외에 히스토그램을 추가로 활용

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

 

🔹 시스템 통계

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

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

 

비용 계산 원리

단일 테이블을 인덱스로 액세스할 때의 비용 (예상 I/O Call 횟수) 계산 원리를 살펴보자.

 

인덱스 키 값을 모두 '=' 조건으로 검색할 때 인덱스 통계만으로 쉽게 비용 계산 가능 

 

👉 비용
= BLEVEL (인덱스 수직적 탐색 비용)
+ AVG_LEAF_BLOCKS_PER_KEY (인덱스 수평적 탐색 비용)
+ AVG_DATA_BLOCKS_PER_KEY (테이블 랜덤 액세스 비용)

 

---

 

인덱스 키 값이 모두 '=' 조건이 아닐 때는 통계 컬럼까지 활용

 

👉 비용

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

+ LEAF_BLOCKS x 유효 인덱스 선택도 (인덱스 수평적 탐색 비용)

+ CLUSTERING_FACTOR x 유효 테이블 선택도 (테이블 랜덤 액세스 비용)

 

유효 인덱스 선택도와 유효 테이블 선택도는 컬럼 통계 및 히스토그램을 이용해 계산함. 

• 유효 인덱스 선택도 : 전체 인덱스 레코드 중 액세스 조건에 의해 선택될 것으로 예상되는 레코드 비중 의미
• 유효 테이블 선택도 : 전체 인덱스 레코드 중 인덱스 컬럼에 대한 모든 조건절에 의해 선택될 것으로 예상되는 레코드 비중 의미