꿈꾸는 개발자, DBA 커뮤니티 구루비

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에 의한 성능문제 예

상황

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

문제제기

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

T1이 T2와 같이 되려면?

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