11g DBMS_STATS 패키지 성능개선의 3가지 원리

1번부분은 오류가 있으므로 코멘트를 반드시 참조하기 바란다.(2009.06.20)

DBMS_STATS 정녕 필요악인가?
  오라클 DBMS의 모든 통계정보는 DBMS_STATS 패키지에 의해서 관리된다고 할수 있다. 오라클이 제공하는 모든 패키지가 중요하지만 가장 중요한 패키지 하나만 선정하라고 하면 주저없이 이 패키지를 꼽는다. 하지만 이패키지에는 모든사람이 알고 있는 치명적인 단점이 하나 있다. 바로 통계정보 생성 속도가 느리다는 것이다. 하지만 11g 에서 몇가지의 단점을 보완하였다.

11g 에서 성능이 빨라지다.
  오늘은 11g 에서 DBMS_STATS  패키지의 개선사항에 대하여 알아보려 한다. 11g 에서 개선된 것은 크게 3가지 이다.
1. 컬럼 단위의 MIN/MAX 통계정보 생성에 의한 SQL 성능향상.
    DBMS_STATS 패키지를 이용한 통계정보 생성시 컬럼단위의 MIN/MAX 값을 생성한다는것을 아는가?
    11g 부터 MIN/MAX aggregation 이 포함된 SQL 을 만나면 컬럼단위의 통계정보를 참조함으로서 SQL 의 성능을 향상 시킨다. 예를 들어 다음과 같은 SQL 을 수행시킬경우 10g 에 비하여 약 30% 의 성능향상을 얻을수 있다.

select  min(c2), max(c2), min(c3), max(c3), min(c6), max(c6),
           ……
           min(c32), max(c32)
  from CUSTOMERX; 

 통계정보로 부터  컬럼별로 MIN/MAX 값을 참조한후에 테이블을 scan 하면서 MIN~ MAX 값에서 벗어나는 값들만 MIN/MAX 함수를 적용하여 보여주면 되기 때문이다. 이것은 DBMS_STATS  패키지 자체의 성능향상이 아니라 DBMS_STATS  패키지에 의한 Aggregation SQL 의 성능 향상인 셈이다.
 
2. NDV(Number Of Distinct Value) 통계정보 생성시의 성능향상.
    NDV 값을 생성하기 위해서는 다음과 같은 SQL 이 수행되어야만 한다.

select  count(distinct c1), count(distinct c2), ..
  from T  

위와 같은 SQL 은 테이블 SIZE 가 클경우 심각한 SORT 부하를 발생시킨다. 그렇다고 NDV 생성시 ESTIMATE 1% 로 할경우 데이터에 NULL 이 많다거나 컬럼값이 하나로 편중되는 현상이 심할경우 부정확한 NDV 를 생성할수 있다. 이것은 SQL 의 성능에 부정적으로 작용할수 있다.
  11g  부터는 새로운 hash-based algorithm 을 추가하였다. 10g 에서 Sort Group By 대신에 Hash Group By 를 적용하면서 성능이 향상된것과 같은 개념이다. 주의 해야될사항은 새로운 hash-based algorithm 을  사용하려면 Sampling 을 하면 안된다는 것이다. Sampling을 하지 않으면 전체데이터를 scan 하지만 수행시간은 오히려 Sampling 보다 같거나 빠르다. 아래는 오라클사에서 테스트 한 결과이다.

DataBase Ver                          Elapsed Time (s)
----------------------------- ----------------
Oracle Database 10g Release 2 10%           2155
Oracle Database 10g Release 2 100%        24242
Oracle Database 11g                                 1516

10g R2 에서 Sampling 10% 로 한것보다 오히려 11g 에서  전체건에 대하여 생성하는것이 더빠르다는 결론이 나왔다. 또한 NDV 의 정확성 또한 거의 100% 에 가깝다. 새로운 algorithm을 사용할경우 획기적인 성능향상과 정확성을 기대할수 있다. 자세한 내용은 아래의 White Paper 를 참조하라.


3. 파티션 적용 테이블에서 Incremental NDV 생성기능.
    파티션 적용테이블에서 거래내역이나 이력데이터를 보관할경우 대부분이 일자로 Range 파티션을 하게된다.
Global 통계정보를 생성하려면 10g 까지는 2단계의 과정을 거쳐야 했다.
첫번째로는 파티션 레벨의 통계정보를 생성 하기위해 각파티션을 scan 하는작업수행.
두번째로는 Global 통계정보를 생성하기위해 전체 파티션을 scan 하는 작업수행.
정말 비효율적이지 않는가? 같은 파티션을 두번씩 읽은 셈이다.

10g 에서는 아래처럼 granularity를 옵션으로 사용하거나 아니면 수동으로 2번 돌려야 했다. 아래예제 에서는
estimate_percent 를 10% 로 한정 하였다.

exec dbms_stats.gather_table_stats('CUST', 'CUSTOMERX', estimate_percent =>10, granularity =>'GLOBAL and PARTITION');

위의 방법대로 하면 전체 파티션을 내부적으로 2번씩 scan 해야 하므로 성능면에서 최악이다.
경험 많은 DBA 는 새로이 추가된 파티션의 통계정보만 생성하고 Global 통계정보를 생성함으로 전체 파티션에 대하여 2번씩 scan 하는 비효율을 제거한다. 아래처럼 새로 추가된 파티션을 명시하면 된다. 

exec dbms_stats.gather_table_stats('CUST', 'CUSTOMERX', 'PART10', estimate_percent =>10, granularity =>'PARTITION');
exec dbms_stats.gather_table_stats('CUST', 'CUSTOMERX', estimate_percent =>10, granularity =>'GLOBAL');

  하지만 2번째의 방법으로도 Global 통계정보의 생성을 위한 전체 파티션 scan은 막을수 있는 방법이 없었다. 
이러한 성능 이슈 때문에 11g 에서는 2단계의 과정이 없어지고 과정이 하나로 줄었다. 쉽게 말하면 각파티션 통계정보를 생성해놓고 Global 통계정보 생성시 파티션의 통계정보를 sum 하여 이용한다는 것이다. 또한 특정파티션을 지정하지 않아도 아래와 같이 gather_table_stats 을 수행하기전에 set_table_prefs를 수행하게 되면 바뀌거나 새로이 insert 된 파티션만 scan 을 하게된다.

11g :
exec dbms_stats.set_table_prefs('CUST', 'CUSTOMERX', 'INCREMENTAL', 'TRUE');
exec dbms_stats.gather_table_stats(‘CUST', 'CUSTOMERX', granularity =>'GLOBAL and PARTITION');

 11g 에서는 estimate_percent를 10%  로 한정 하지 않았기 때문에 성능 어떨지 궁금하지 않은가?
아래의 테스트 결과를 보자.

DataBase Ver                       Elapsed Time (s)
-------------------------------    ----------------
Oracle Database 11g Incremental                1.25
Oracle Database 10g Release 2 10%              2152     --> 전체를 2번 scan 한 경우
Oracle Database 10g Release 2 10% Manual       1058     --> 추가된 파티션만 명시한 경우

10g 에서 추가된 파티션만 명시한 경우에 비하여 무려 800 배 정도 빨라졌다. 대단하지 않은가?
이것은 3가지의 효과가 서로 상호작용한 효과이다.
1.Global 통계정보 생성시 2단계의 작업(각 파티션의 작업 + Global 작업) 이 1단계로 축소되고
2.Imcremental NDV 작업이 가능 해졌다는것
3.미리 언급한 hash-based algorithm 의 효과


결론 :
10g 까지 DBMS_STATS 패키지는 그중요성에 비하여 느린 성능이슈 때문에 Necessary Evil (필요악) 으로 인식되어 왔다.  하지만 드디어 11g 에서 몇가지의 개선사항으로 좀더 귀여운 모습을 하고 우리에게 돌아왔다. 11g 로 업그레이드 할이유가 몇가지 더 생긴것이다.

Reference : DSS Performance in Oracle 11g

편집후기 : 위에 링크된 백서는 DBMS_STATS 패키지 뿐만 아니라 11g 의 매뉴얼에 없는 여러가지 비급이 실려 있으므로 필독 하기 바란다.

백서내용 요약
1.Result Cache 신기능.
2.Composite Partitioning 신기능.
3.Full Outer Join 개선사항.
4.Nested Loop Join 개선사항.