[오라클10g]DBMS_STATS.GATHER_SCHEMA_STATS 수행에 관한팁

dbms_stats.gather_schema_stats 프로시저를 이용해서 통계 정보를 수집하는 경우 수행 시간이 너무 많이 걸린다는 불만이 종종 있다. 이런 현상은 대부분 다음과 같은 경우에 발생한다. 

- 특정 테이블들의 크기가 매우 크다.
- 일부 큰 테이블들은 Partitioning이 되어 있다. 

즉 큰 테이블들이 있고 더구나 Partitioning까지 되어 있으니(Global/Partiton Statistics모두 수집해야 하므로) 그만큼 통계 정보 수집에 시간이 걸리기 마련이다. 하지만 Oracle 10g에서는 estimate_percent 파라미터와 method_opt 파라미터의 값이 Oracle에 의해 적절하게 판단되어 통계 정보 생성 시간을 최적화한다. 그렇다면 왜 필요 이상 많은 시간이 걸리는 경우가 발생하는가? 

이것은 이 두 파라미터에 다음과 같은 결함이 있기 때문이다.

- estimate_percent 파라미터의 값이 AUTO이다. 즉, Oracle이 테이블의 크기에 따라 적절한 샘플 크기를 선택한다는 의미이다. 대부분의 경우 Oracle은 테이블의 크기에 따라 적절한 값을 찾지만, 간혹 아주 큰 테이블에 대해 100%의 값을 사용하는 경우가 있다. 
- method_opt 파라미터의 기본값이 FOR ALL COLUMNS SIZE AUTO이다. 즉 모든 컬럼에 대해 통계정보를 수집하면 히스토그램의 생성 여부는 Oracle이 판단한다. 대부분의 경우 Oracle은 적절한 히스토그램 버킷 크기를 할당하지만, 간혹 불필요하게(가령 Unique Key에 대해) 히스토그램을 생성하는 경우가 있다.

위와 같은 상황이 발생하면 예상보다 지나치게 많은 시간이 걸리게 되는 셈이다. 

이런 상황을 해소할 수 있는 방법이 있을까? 가령 다음과 같이 하면...?

dbms_stats.gather_schema_stats(estimate_percent=>10, method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1');

물론 위의 방법으로 수행 시간 문제는 해결하겠지만, 좋은 방법은 될 수 없다. 만일 크기가 작은 테이블들이 있다면 샘플 크기가 작으면 왜곡된 통계 정보가 수집될 수 있다. 만일 특정 테이블에 대해서는 모든 컬럼(ALL COLUMNS)에 대해 통계 정보를 수집해야 한다면? 또는 특정 테이블의 특정 컬럼에 대해서는 히스토그램 크기를 주고 싶다면? 

그렇다고 테이블 레벨로 일일이 dbms_stats.gather_table_stats를 호출하는 것은 너무나 지겨운 일이다. 

그래서 보통 다음과 같은 트릭을 종종 사용한다.(편의상 정확한 문법은 생략)

-- 특정 테이블 통계 정보 변경을 막음
dbms_stats.lock_table_stats(user, 'TABLE1');

-- 스키마 레벨로 통계 정보 수집
dbms_stats.gather_schema_stats(user);

-- 통계 정보 활성화 후 이 테이블만 다시 통계 정보 수집
dbms_stats.unlock_table_stats(user, 'TABLE1');
dbms_stats.gather_table_stats(user,'TABLE', estimate_percent=>10, method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO');

위와 같이 하면 Schema 레벨로 대부분의 테이블에 대해 통계 정보를 수집한 후 특정 문제가 되는 테이블에 대해서만 별도로 통계 정보를 수집할 수 있다.

지저분한 트릭같지만 매우 유용한 방법이다.

Oracle 11g에서는 "Preference"라는 새로운 개념을 통해 위와 같은 불편을 일시에 해소하고 있다. Oracle 11g에서라면 다음과 같이 훨씬 직관적이고 깔끔한 방법을 쓸 수 있다.

-- 아래와 같이 한번만 설정
dbms_stats.set_table_prefs(user,'TABLE1', 'ESTIMATE_PERCENT', '10');
dbms_stats.set_table_prefs(user,'TABLE1','METHOD_OPT', 'FOR ALL INDEXED COLUMNS SIZE AUTO');

-- Schema 레벨에서 통계 정보 수집
dbms_stats.gather_schema_stats(user);

즉, 특정 테이블별로 Preference(선호도)를 저장할 수 있어서 10g에서와 같은 부자연스러운 트릭은 불필요한 것이다. 11g에서 가장 마음에 드는 New Feature중 하나이다.

출처: http://ukja.tistory.com/86