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 이름을 직접명시해서..

h3.(예제) 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를 알아낸 후, 그 값을 첫번째 통계정보 수집에서 사용

문서에 대하여

• 최초작성자 : 오정희
• 최초작성일 : 2009년 4월 18일
• 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
• {*}이 문서의 내용은 조동욱님의 'Optimizing Oracle Optimizer'를 참고하였습니다.*