6.히스토그램

(1) 히스토그램 유형

• 높이균형(Height-Balanced) 히스토그램
• 도수분포(Frequency) 히스토그램

• 히스토그램 생성조건 : 컬럼 통계 수집 시 버킷 개수를 2 이상으로 지정 (ex. for columns SIZE 10, col1, col2, col3)

• 히스토그램 정보 : dba_histograms, dba_tab_histograms 뷰

• 참고
• 10g 이후, dba_tab_columns 뷰의 histogram 컬럼 값을 통해 히스토그램 유형 파악 가능
• FREQUENCY : 값 별로 빈도수를 저장하는 도수분포 히스토그램 (값의 수 = 버킷 개수)
• HEIGHT-BALANCED : 각 버킷의 높이가 동일한 높이균형 히스토그램 (값의 수 > 버킷 개수)
• NONE : 히스토그램을 생성하지 않은 경우

• 예제 구성 정보

- 테이블명 : Member

- 컬럼 : memb_id(pk), age (2개)

- 2000명 회원 정보 데이터 

10대 이하 : 50         40대  : 1200 (40세 회원 1000명 포함) 

20대 : 270 50대  : 100

30대 : 330      60대 이상  : 50      

create sequence seq ;

create table member( memb_id number, age number(2) );

exec dbms_random.seed(0);

insert into member(memb_id, age)

select seq.nextval, dbms_random.value( 1,19) from dual connect by level <= 50;

insert into member(memb_id, age)

select seq.nextval, dbms_random.value(20,29) from dual connect by level <= 270;

insert into member(memb_id, age)

select seq.nextval, dbms_random.value(30,39) from dual connect by level <= 330;

insert into member(memb_id, age)

select seq.nextval, 40 from dual connect by level <= 1000; ☞ popular value

insert into member(memb_id, age)

select seq.nextval, dbms_random.value(41,49) from dual connect by level <= 200;

insert into member(memb_id, age)

select seq.nextval, dbms_random.value(50,59) from dual connect by level <= 100;

insert into member(memb_id, age)

select seq.nextval, dbms_random.value(60,99) from dual connect by level <= 50 ;

(2) 도수분포 히스토그램 (≒ 'value-based 히스토그램') : 값별로 빈도수를 저장하는 히스토그램

• 특징

• 컬럼 값마다 하나의 버킷 할당 (값의 수 = 버킷 개수)
• 사용자가 요청한 버킷 개수가 컬럼이 가진 값의 수보다 많거나 같을 때 사용
• 최대 254개의 버킷만 허용 ☞ 값의 수가 254개를 넘는 컬럼에 사용 할 수 없음

SQL> SELECT COUNT( * ), COUNT( DISTINCT age ) FROM   member ;

  COUNT(*) COUNT(DISTINCTAGE)
---------- ------------------
      2000                 89

☞ age 컬럼 값의 수 (89개) <= 254 (최대 버킷 허용 수) ∴ 도수분포 히스토그램 생성 가능

SQL> begin
  2    dbms_stats.gather_table_stats(user, 'member', method_opt => 'for all columns size 100');
  3  end;
  4  /

SQL> SELECT num_distinct, num_buckets, histogram
  2  FROM   user_tab_col_statistics
  3  WHERE  table_name = 'MEMBER'
  4  AND    column_name = 'AGE' ;

NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------ ----------- ---------------
          89          89 FREQUENCY?

☞ 도수분포 히스토그램에서 컬럼 값의 수와 버킷 개수가 항상 일치 (∵ 컬럼 값마다 단일 버킷 할당)

    버킷 수를 크게 요청하여도 실제 값의 수만큼만 버킷 할당  ∴ 정확한 히스토그램을 위해 항상 254개 요청하는 것이 좋음

• dba/all/user_histogram 뷰

• endpoint_value : 버킷에 할당된 컬럼 값
• endpoint_number : endpoint_value 정렬 시, 최소 값부터 현재 값까지 누적 수량

SQL> SELECT t.age "연령", t.cnt "인원수(명)",
  2         nvl2(h.prev, h.running_total - h.prev, h.running_total) frequency, h.running_total
  3  FROM   (SELECT age, count(*) cnt FROM member GROUP BY age) t,
  4         (SELECT endpoint_value age, endpoint_number running_total,
  5                 lag(endpoint_number) over (ORDER BY endpoint_value) prev
  6          FROM   user_histograms
  7          WHERE  table_name = 'MEMBER'
  8          AND    column_name = 'AGE') h
  9  WHERE  h.age = t.age;

      연령 인원수(명)  FREQUENCY RUNNING_TOTAL
---------- ---------- ---------- -------------
         1          2          2             2
         2          3          3             5
         3          8          8            13

	：	   ：	      ：	 	    ：			

        39         12         12           650
        40       1000       1000          1650
        41         19         19          1669

	：	   ：	      ：	 	    ：			 

        98          3          3          1999
        99          1          1          2000

• 값별로 빈도수를 미리 계산해 두는 방식  ☞ 쉽고 정확하게 구할 수 있음

• 단, 시스템 자원의 한계로(1000만 고객 도수분포 관리 시 등) 컬럼 값의 빈도수를 모두 동일한 방식으로 저장 할 수 없음

  ☞ 값의 수가 많을 때 높이 균형 히스토그램 사용

(3) 높이균형 히스토그램 (≒ 'equi-depth 히스토그램')

• 특징

• 컬럼 값의 수보다 버킷 수가 적을 때 생성 (값의 수 > 버킷 개수)
• 하나의 버킷이 여러 개의 값 담당
• 빈도 수가 많은 값 (popular value) 포함 시 두 개 이상의 버킷 할당

SQL> begin
  2    dbms_stats.gather_table_stats(user, 'member', method_opt => 'for all columns size 20');
  3  end;
  4  /
SQL> SELECT num_distinct, num_buckets, histogram
  2  FROM   user_tab_col_statistics
  3  WHERE  table_name = 'MEMBER'
  4  AND    column_name = 'AGE' ;NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------ ----------- ---------------
          89          20 HEIGHT BALANCED

☞ user_tab_col_statistics 뷰에는 사용자가 요청한 20개의 버킷이 생성된 것으로 조회되지만 

실제 히스토그램은 다음과 같이 11개의 버킷만 할당  ∵ POPULAR VALUE 압축 표현

SQL> SELECT endpoint_number, endpoint_value
  2  FROM   user_histograms
  3  WHERE  table_name = 'MEMBER'
  4  AND    column_name = 'AGE'
  5  ORDER  BY 1 ;

ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
              0              1  <= 첫번째 레코드 : 버킷이 아니라 '최소 값' 표현
              1             21
              2             25
              3             28
              4             32
              5             35
              6             38
             16             40  <= popular value
             17             43
             18             46
             19             55
             20             99

• dba/all/user_histogram 뷰

• endpoint_number : 버킷 번호 ☞ 1씩 증가하다가 39 ~ 40 연령대에서 10 칸 (6 → 16) 건너 뜀 (압축 표현)
• endpoint_value  : 버킷의 Max 값

SQL> SELECT '~' || age "연령대", endpoint_number, diff,
  2         ROUND( 100 * diff /sum(diff) over() ) "RATIO(%)" ,
  3         ROUND( t.num_rows * diff /sum(diff) over() ) "인원수(명)"
  4  FROM  (SELECT table_name, endpoint_value age, endpoint_number,
  5                endpoint_number - lag(endpoint_number) over (ORDER BY endpoint_value) diff ,
  6                lag(endpoint_number) over (ORDER BY endpoint_value) prev
  7          FROM  user_histograms
  8          WHERE table_name = 'MEMBER'
  9          AND   column_name = 'AGE') h, user_tables t
 10  WHERE  h.endpoint_number > 0
 11  AND    t.table_name = h.table_name
 12  ORDER  BY 1 ;

연령대          ENDPOINT_NUMBER       DIFF   RATIO(%) 인원수(명)
--------------- --------------- ---------- ---------- ----------
~21                           1          1          5        100
~25                           2          1          5        100
~28                           3          1          5        100
~32                           4          1          5        100
~35                           5          1          5        100
~38                           6          1          5        100
~40                          16         10         50       1000
~43                          17          1          5        100
~46                          18          1          5        100
~55                          19          1          5        100
~99                          20          1          5        100

연령별 인원수(카디널리티)를 실제로 위처럼 구할 시, 39세, 40세는 각각 500명으로 계산 (실제값과 차이 있음)

☞ 오라클은  popular value(40세)에 대한 카디널리티를 구할 때만 버킷에 의한 계산식 사용

    non- popular value는 미리 구해 놓은 density 값 이용

popular value에 대한 선택도 / 카디널리티 계산

• 조건절 값이 두 개 이상 버킷을 가진 popular value 이면 아래 공식을 따름

선택도 = (조건절 값의 버킷 개수) / (총 버킷 개수)

☞ popular value 40 의 경우 총 20개의 버킷 중 10개를 사용하므로 선택도 = 1 / 2 

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

                 = 총 로우 수 * 조건절 값의 버킷 개수 / 총 버킷 개수

                 = 2,000 * 10 / 20 = 1,000

non-popular value에 대한 선택도 / 카디널리티 계산

• non-popular value 일 때 미리 구해 놓은 density 값 이용

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

SQL> SELECT 1 /num_distinct, density, ROUND(density * 2000) cardinality
  2  FROM   user_tab_col_statistics
  3  WHERE  table_name = 'MEMBER'
  4  AND    column_name = 'AGE' ;

1/NUM_DISTINCT    DENSITY CARDINALITY
-------------- ---------- -----------
    .011235955   .2567285         513

   ※ 참고 density

컬럼 통계 ( *_tab_col_statistics )에서 조회 가능

해당 컬럼을 '=' 조건으로 검색 시, 선택도를 미리 구해 놓은 값

히스토리 유무에 상관없이 100% 균일 분포 시 density 값은 항상 1 / distinct 값과 동일

카디널리티 = 총 로우 수 * 선택도 = num_rows * density

(4) 바인드 변수 사용시 카디널리티 계산

• 변수 바인딩 시점 → 실행시점 (최적화 다음 발생)

∴ SQL 최적화 시점에 조건절 컬럼의 데이터 분포를 화용하지 못하는 문제 발생

     ☞ 변수 사용 시, 옵티마이저는 평균 분포를 가정한 실행계획 생성 (컬럼 분포가 불균일 할 경우 역효과 발생 가능)

'=' 조건일 때

• 바인드 변수 '=' 검색 시 선택도
• 히스토그램이 없을 때 : 1 / num_distinct 사용
• 도수분포 히스토그램일 때 : 1 / num_distinct 사용
• 높이균형 히스토그램일 때 : density 사용

범위검색 조건일 때

• 범위 검색 조건 사용 시, 옵티마이저는 고정된 규칙으로 선택도 추정 (but, 십중팔구 틀릴 가능성이 크다!! -_-;;)

          [ 1~4 번의 선택도는 5%, 5~8 번의 선택도는 0.25로 계산 ]

================================================

| 번호 |  조건절 | 번호 | 조건절

================================================

|  1  | 번호 > :no |  5   | 번호 between :no1 and :no2

|  2  | 번호 < :no |  6  | 번호 > :no1 and 번호 <= :no2

|  3  | 번호 >= :no |  7  | 번호 >= :no1 and 번호 < :no2

|  4  | 번호 <= :no |  8  | 번호 > :no1 and 번호 < :no2

================================================

• 바인드 변수 사용 시, 정확한 컬럼 히스토그램에 근거하지 않고, 정해진 카디널리티 계산식에 근거한 비용 계산으로 최적이 아닌 실행계획 수립 가능

• 상수 조건식을 사용 할 시, 거의 정확한 카디널리티 계산

• DW, OLAP, 배치프로그램(Loop 내 수행 쿼리 제외)에서 수행되는 쿼리는 바인드 변수보다 상수 사용이 유리

  (특히, 날짜 컬럼처럼 부등호, between 같은 범위 조건이 자주 검색되는 컬럼일 경우)

(5) 결합 선택도

• 히스토그램을 많이 만들어도 두 개 이상 컬럼에 대한 결합 선택도를 구할 시 정확성 ↓

동적 샘플링(Dynamic Sampling)

• 9i 부터 동적 샘플링을 통해 이 문제를 해결하기 위해 시도
• 소량의 데이터 샘플링을 통해 where 조건절에 사용된 두 개 이상 컬럼의 결합 분포를 구하는 기능 (동적 샘플링 레벨 4 이상 설정 시)
• 교재 p.430~431 예제 
• 동적 샘플링(레벨 4 적용) 전/후 실행으로 옵티마이저의 예상 카디널리티의 정확도 차이 비교

다중 컬럼 통계(Multi-column Statistics)

• 11g 부터 '확장형 통계(extended statistics)'라 불리는 기능을 통해 다중 컬럼 히스토그램 생성 가능

• 다중 컬럼 히스토그램 컬럼 그룹 생성 방법

var ret varchar2(30);

exec :ret := dbms_stats.create_extended_stats(user, '사원', '(급여, 상여)');

print ret;

begin

  dbms_stats.gather_table_stats(user, '사원', method_opt > 'for all columns size skewonly for columns (급여, 상여) size 254');

end;

• 오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
• 작성자: 위충환 (실천하자)
  
• 최초작성일: 2011년 04월 24 일
• 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
• 문서의 잘못된 점이나 질문사항은 본 문서에 댓글로 남겨주세요. ^^