by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

Gathering Histogram




Gathering Histogram

  • Histogram은 어떻게 수집하는가?
    • DBMS_STATS Package의 METHOD_OPT Parameter의 값에 의해 결정. 각 컬럼별로 Bucket Size를 지정하는 역할
    • 예) exec dbms_stats.gather_table_stats(user, 't1', method_opt=>'for all column size 1');
  • method_opt의 값 예시
    • for all column size 1: 모든 컬럼에 대해 Histogram을 수집하지 않는다.
    • for all column size auto: Oracle이 Data의 분포를 고려하여 Bucket Size를 계산.(단, 해당 Coloumn이 Predicate로 사용된 적이 있는 경우에만 Histogram 수집)
    • for all column size skewonly: Oracle이 Data의 분포를 고려하여 Bucket Size를 계산.(단, 해당 Coloumn이 Predicate로 사용된 적이 없어도 Histogram 수집)
    • for all indexed column size skewonly: Index가 존재하는 모든 컬럼에 대해서..
    • for all indexed column size auto: Index가 존재하는 모든 컬럼에 대해서..
    • for columns c1 size 5 c2 size skewonly: 개별 컬럼에 대해서..
    • for all hidden columns size skewonly: Function Based Index에 의해 생성된 Hidden Cloumn에 대해서..
    • for columns SYS_NC00003$ size skewonly: Hidden Column 이름을 직접명시해서..
      => 모든 컬럼, 모든 Index대상 컬럼, 개별컬럼, Hidden컬럼들에 대해서, Bucket Size를 정할 수 있는 여러가지 옵션(skewonly, auto, 숫자)을 주어 히스토그램을 생성할 수 있다.

(예제) Function Based Index에 의해 생성된 Hidden Column 정보확인 및 Histogram 생성법

  • Function Index와 Object Type의 컬럼을 가진 테이블 생성
    SQL> create or replace type obj_type as object(c1 int, c2 int);
      2  /
    SQL> create table t1(c1 int, c2 obj_type);
    SQL> create index t1_n1 on t1(c1+1);
    
  • 모든 Hidden Column에 대한 정보확인 (Hidden Column이 무슨이름으로 만들어졌나...)
    
    ------------------
    02. column stats
    ------------------
    TABLE_NAME                    : T1
    COLUMN_NAME                   : C1
    NUM_DISTINCT                  :
    NUM_NULLS                     :
    DENSITY                       :
    LOW_VALUE                     :
    HIGH_VALUE                    :
    HISTOGRAM                     : NONE
    -----------------
    TABLE_NAME                    : T1
    COLUMN_NAME                   : C2
    NUM_DISTINCT                  :
    NUM_NULLS                     :
    DENSITY                       :
    LOW_VALUE                     :
    HIGH_VALUE                    :
    HISTOGRAM                     : NONE
    -----------------
    TABLE_NAME                    : T1
    COLUMN_NAME                   : SYS_NC00003$
    NUM_DISTINCT                  :
    NUM_NULLS                     :
    DENSITY                       :
    LOW_VALUE                     :
    HIGH_VALUE                    :
    HISTOGRAM                     : NONE
    -----------------
    TABLE_NAME                    : T1
    COLUMN_NAME                   : SYS_NC00004$
    NUM_DISTINCT                  :
    NUM_NULLS                     :
    DENSITY                       :
    LOW_VALUE                     :
    HIGH_VALUE                    :
    HISTOGRAM                     : NONE
    -----------------
    TABLE_NAME                    : T1
    COLUMN_NAME                   : SYS_NC00005$
    NUM_DISTINCT                  :
    NUM_NULLS                     :
    DENSITY                       :
    LOW_VALUE                     :
    HIGH_VALUE                    :
    HISTOGRAM                     : NONE
    -----------------
    
  • Histogram 생성(모든 hidden column에 대해서 또는, 위에서 확인한 특정 hidden column만을 명시하여..)
    SQL> exec dbms_stats.gather_table_stats(user, 't1', method_opt=>'for all hidden columns size skewonly');
    SQL> exec dbms_stats.gather_table_stats(user, 't1', method_opt=>'for columns SYS_NC00003$ size skewonly');
    

Auto vs. Skewonly

  • DBMS_STATS Package의 METHOD_OPT Parameter에서 AUTO와 SKEWONLY 옵션은 Oracle이 Data의 분포를 고려하여, Bucket Size를 결정하는것은 동일하나, AUTO는 해당 Column이 Predicate로 사용된 적이 있는 경우에만 Histogram을 수집한다.
  • 특정 Column이 Predicate로 사용되었는지 여부는 SYS.COL_USAGE$VIEW에서 관리된다.

오해할 수 있는 Histogram에 의한 성능문제 예

상황

  1. Table 및 기타 Object들을 재생성한 후 Data Load
  2. 통계정보 생성(DBMS_STATS.GATHER_SCHEMA_STATS Procedure)
  3. Query들의 전반적인 성능들을 측정(수행시간:T1)
  4. 통계정보 재생성(DBMS_STATS.GATHER_SCHEMA_STATS Procedure)
  5. Query들의 전반적인 성능들을 측정(수행시간:T2)

문제제기

  • Data의 변화가 없으므로, T1 == T2이어야 하나, 실제는 그렇지 않을수 있다.
  • DBMS_STATS.GATHER_SCHEMA_STATS Procedure에서 METHOD_OPT Parameter의 디폴트 값은 AUTO이므로, 첫번째 통계정보를 수집할때는 Query가 수행된 적이 없으므로 Histogram이 수집되지 않으나, 두번째 통계정보 수집시에는 성능측정을 위해 Query들을 수행했으므로 Histogram이 수집되었을 수 있기 때문이다.

T1이 T2와 같이 되려면?

  1. DBMS_STATS.GATHER_SCHEMA_STATS Procedure에서 METHOD_OPT Parameter를 SKEWONLY로 변경
  2. 두번째 수집한 통계정보를 Export후 Import
  3. 두번째 수집한 통계정보에서 각 Histogram의 Bucket Size를 알아낸 후, 그 값을 첫번째 통계정보 수집에서 사용

문서에 대하여

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.