행위

오라클 히스토그램

DB CAFE

1 히스토그램 유형

- 오라클이 사용하는 히스토그램

  1. 높이균형 히스토그램
  2. 도수분포 히스토그램
    - 히스토그램을 생성하려면 컬럼 통계 수집 시 버킷 개수를 2 이상으로 지정.
    - 히스토그램 정보는 dba_histograms 또는 dba_tab_histograms 뷰를 통해 확인.
    특히, 10g에서는 dba_tab_columns 뷰에 histogram 컬럼이 추가되면서 히스토그램 유형을
    쉽게 파악할 수 있음.
  3. FREQUENCY : 도수 분포 히스토그램
  4. HEIGHT-BALANCED : 높이균형 히스토그램
  5. NON : 히스토그램 생성하지 않음

1.1 도수분포 히스토그램

  1. 도수분포 히스토그램은 컬럼 값마다 하나의 버킷을 할당.
  2. 사용자가 요청한 버킷 개수가 컬럼이 가진 값의 수보다 많거나 같을 때 사용
  3. 최대 254개의 버킷만 허용하므로 값의 수가 254를 넘는 컬럼에는 이 히스토그램을 사용할 수 없다.
  4. 254개를 요청하더라도 값의 수만큼만 버킷을 할당하므로 정확한 히스토그램을 위해서라면 항상 254개를 요청하는 것이 좋다.
  5. 히스토그램 정보를 조회할 수 있는 뷰에는 아래 두 컬럼이 있다.
    1. endpoint_value  : 버킷에 할당된 컬럼 값
    2. endpoint_number : endpoint_value로 정렬했을 때, 최소 값부터 현재 값까지의 누적 수량


1.2 높이균형 히스토그램

  1. 높이균형 히스토그램에서는 버킷 개수보다 값의 수가 많기 때문에 하나의 버킷이 여러 개 값을 담당.
  2. 요청할 수 있는 최대 버킷 개수는 254개이므로 값의 수가 254개를 넘으면 무조건 히스토그램이 만들어진다.
    (컬럼수보다 버킷을 적게 적어주어도 높이균형 히스토그램이 생성된다.)
  3. 높이균형 히스토그램에서는 말 그대로 각 버킷의 높이가 같다. 각 버킷은 {1/(버킷 개수) x100}% 의 데이터 분포를 갖는다.
  4. 빈도 수가 많은 값을 포함할 때는 두 개 이상의 버킷이 할당.
  5. 오라클은 popular value에 대한 카디널리티를 구할 때만 버킷에 의한 계산식을 사용하고 나머지는 미리 구해 놓은 density 값을 이용.
- popular value에 대한 선택도/카디널리티 계산 
  1. 조건절 값이 두 개 이상 버킷을 가진 popular value이면 아래 공식을 따른다.
        선택도 = (조건절 값의 버킷 개수) / (총 버킷 개수)
- non-popular value에 대한 선택도/카디널리티 계산 
  1. 카디널리티 = 총 로우 수 x 선택도 = 총 로우 수 x density
- 바인드 변수 사용 시 카디널리티 계산 
  1. 바인드 변수 사용시 변수 값 입력이 나중에 되니 실행계획에서 카디널리티를 계산할 수가
        없다. 그래서 컬럼 분포가 균일할 때는 문제될 것이 없지만 그렇지 않을 때는 실행 시점에 
        바인딩되는 값에 따라 최적이 아닌 실행계획일 수 있어 문제다. 
  1. 바인드 변수를 사용하면 정확한 컬럼 히스토그램에 근건하지 않고 카디널리티를 구하는
        정해진 계산식에 기초해 비용을 계산하므로 최적이 아닌 실행계획을 수립할 가능성이 
        커진다.
  1. 좋은 실행계획을 위해서라면 DW, OLAP, 배치 프로그램에서 수행되는 쿼리는
        바인드 변수보다 상수를 사용하는 것이 좋고, 날짜 컬럼처럼 부등호, between 같은 
        범위 조건으로 자주 검색되는 컬럼일 때 특히 그렇다.
  1. OLTP성 쿼리이더라도 값의 종류가 적고 분포가 균일하지 않을 때는 상수 조건을 쓰는 것이
        유용할 수 있다.    


2 결합 선택도

- 동적 샘플링은 소량의 데이터 샘플링을 통해 where 조건절에 사용된 두개 이상 컬럼의 결합 
  분포를 구하는 기능으로서, 동적 샘플링 레벨을 4 이상으로 설정할 때만 작동한다. 
   ( 힌트 사용 시 /*+ dynamic_sampling(4) */ 이렇게 사용 
- 다중 컬럼 통계 11g에서는 다중 컬럼에 대한 히스토그램도 생성할 수 있게 되었다. 


출처: 성능고도화-히스토그램

3 히스토그램 생성

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'XSOFT_T', ESTIMATE_PERCENT=>100, METHOD_OPT=>'FOR COLUMNS SIZE 2 ID', CASCADE=>TRUE);