Optimizing Oracle Optimizer (2009년)
Gathering Histogram 0 0 52,943

by 구루비스터디 Histogram [2018.07.14]


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를 알아낸 후, 그 값을 첫번째 통계정보 수집에서 사용
"데이터베이스 스터디모임" 에서 2009년에 "OPTIMIZING ORACLE OPTIMIZER " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3890

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입