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

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

7.1.1 선택도와 카디널리티

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

'=' 조건으로 검색 할 때 경우

선택도 = 1 / NDV

NDV : 컬럼값 종류 개수(성별이라면 남/여 2)

카디널리티 : 전체 레코드 중에서 조건절에 의해 선택되는 레코드 개수

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

*옵티마이저는 카디널리티 값을 통하여 비용을 산출하고 비용을 통하여 옵티마이징 방식을 결정한다. 따라서 카디널리티값의 기초가 되는 선택도 값의 계산이 매우 중요하다.

7.1.2 통계정보

오브젝트 통계

테이블 통계

테이블 통계 조회

select 
       num_rows -- 총 레코드 계수
     ,blocks -- 테이블 블록 수 = '사용된' 익스텐트에 속한 총 블록 수
     ,avg_row_len -- 레코드당 평균 길이
     ,sample_size -- 샘플링한 레코드 수
     ,last_analyzed -- 통계정보 수집일시
from all_tables
where owner='SCOTT'
and table_name = 'EMP'
;

인덱스 통계

인덱스 통계 조회

select blevel -- 브랜치 레벨. 인덱스 루트에서 블록에 도달하기 직전까지 읽게되는 블록 수
     ,leaf_blocks  -- 인덱스 리프 블록 총 개수
     ,num_rows -- 인덱스에 저장된 레코드 개수
     ,distnct_keys -- 인덱스 키값의 조합으로 만들어지는 값의 종류 개수. c1(3) * c2(4) = 12. 선택도 계산에 사용.
     ,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'
;

컬럼 통계

컬럼 통계 조회

select num_distinct -- 컬럼 값의 종류 개수(NDV)
     , density -- '=' 조건일 때 선택도. 1/NDV
     , avg_col_len -- 컬럼 평균 길이(bytes)
     , low_value -- 최소값
     , high_value -- 최대값
     , num_nulls -- null 인 레코드 수
     , last_analyzed
     , sample_size
from all_tab_columns
where owner = 'SCOTT'
and table_name = 'EMP'
and column_name = 'DEPTNO'
;

컬럼 히스토그램

'=' 조건 선택도는 1/NDV 나 Density 값을 활용.

하지만 데이터 분포가 균열하지 않다면 적용하기 쉽지 않기 때문에 이런 경우 옵티마이저는 통계 외에 히스토그램 사용.

히스토그램 : 컬럼 값 별로 데이터 비중이나 빈도를 미리 계산해 놓은 통계정보. 실제 테이터를 읽어서 계산해 둔 값.

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

히스토그램 조회

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의 평균적인 처리량

7.1.3 비용 계산 원리

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

'=' 조건 검색 시

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

• blevel : 브랜치 레벨. 인덱스 루트에서 블록에 도달하기 직전까지 읽게되는 블록 수
• avg_leaf_blocks_per_key : 인덱스 키값을 모두 '=' 조건으로 조회할 때 읽게 될 리프 블록 개수
• avg_data_blocks_per_key : 인덱스 키값을 모두 '=' 조건으로 조회할 때 읽게 될 테이블 블록 개수

'=' 조건 검색 아닐 시(아래 컬럼 통계까지 활용)

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

• leaf_blocks : 인덱스 리프 블록 총 개수
• clustering_factor : 인덱스 키값 기준으로 테이블 데이터가 모여 있는 정도. 인덱스를 스캔하면서 테이블 레코드를 찾을 때 읽게 될 테이블 블록 개수를 미리 계산 한 수치
• 유효 인덱스 선택도 : 전체 인덱스 레코드 중 액세스 조건에 의해 선택될 것으로 예상되는 레코드 비중
• 유효 테이블 선택도 : 전체 인덱스 레코드 중 인덱스 컬럼에 대한 모든 조건절에 의해 선택 될 것으로 예상되는 레코드 비중