by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

8. 통계정보 II




1. 전략적인 통계수집 정책의 중요성

1. CBO 능력을 최대한 끌어 올리는 핵심 요소

  • 통계정보 : 옵티마이저가 최선의 판단을 할 수 있도록 정확한 통계정보를 제공해야 한다.

2. DB관리자의 핵심 역할은 통계정보 관리

  • 문제없던 쿼리가 갑자기 악성 SQL로 돌변했다면 정확하지 않은 통계정보에서 비롯된 문제일 수 있다.

3. 통계정보 수집시 고려사항

 1) 시간 : 부하가 없는 시간대에 가능한 빠르게 수집을 완료해야 함
 2) 샘플 크기 : 가능한 적은 양의 데이터를 읽어햐 함
 3) 정확성 : 전수 검사할 때의 통계치에 근접해야 함
 4) 안정성 : 데이터에 큰 변화가 없을 경우 매번 통계치가 바뀌지 않아야 함

=> 충분한 신뢰수준을 갖춘 안정적인 통계정보를 옵티마이저에게 제공하려면 치밀한 전략이 필요하다.

4. 주기적으로 통계 수집하면서 안정적이어야 최선

  • 안정성을 중시하여 통계정보를 수집하지 않으면 안된다.

5. 통계 수집 정책 수립은 필수

  • 통계를 수집할 필요가 없는 오브젝트에 대해서는 Lock옵션으로 통계정보를 고정할 수 있다.
  • 운영 DB에서 수집한 통계정보를 개발 DB에도 반영한 상태에서 개발을 진행해야 한다.
  • 통계정보 변화로 인한 문제를 대비해 가장 안정적이었던 최근 통계정보를 백업해 둔다.
  • 전략을 세우는 세부 단계에서는 오브젝트별 통계수집 주기와 샘플링 비율등을 표로 정리해둔다.
  • 안정적인 고성능 데이터베이스 구축을 위해 시스템 여건과 오브젝트 특성에 맞는 통계수집 정책을 마련해야 한다.

2. DBMS_STATS

  • 과거 Analyze 명령어를 대신하여 dbms_stats패키지를 사용한다.
  • dbms_stats가 더 정교하게 통계를 계산하며, 파티션 테이블/인덱스일때는 반드시 dbms_stats를 사용한다.
  • dbms_stats.gather_table_stats 프로시저의 인자표 ( 참조 : page 446 ~ 447 )

3. 컬럼 히스토그램 수집

  • 히스토그램을 수집하고 관리하는 비용이 크기 때문에 필요한 컬럼에만 히스토그램을 수집한다.
  • 조건절에 자주 사용하면서 편중된 데이터 분표를 갖는 컬럼이 주 대상이다.
  • 주로 인덱스 컬럼에 히스토그램을 수집하는것이 좋으나, 인덱스가 없는 조건절 컬럼이더라도 테이블을 액세스하고 나서의 최종 선택도를 계산할때 필요한 컬럼의 히스토그램은 수집한다. ( 다른 집합과의 조인 순서 및 조인 방식 결정 )
  • 히스토그램이 불필요한 경우
     1) 컬럼 데이터 분포가 균일
     2) Unique하고 항상 등치조건으로만 검색되는 컬럼
     3) 항상 바인드 변수로 검색되는 컬럼
     
  • 관련 인자는 dbms_stats.gather_table_stats의 method_opt이다.
  • 8i, 9i 기본값은 for all columns size 1 : 모든 컬럼에 대해 히스토그램을 수집하지 말라는 의미이다.
  • 10g 기본값은 for all columns size auto : 오라클이 모든 컬럼에 대해 skew 여부를 조사해서 버킷 개수를 결정하는 의미이다. auto는 해당 컬럼이 조건절에 사용되는 비중까지 고려한다. ( sys.col_usage$뷰를 참조)
     SQL> desc col_usage$;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     OBJ#                                               NUMBER
     INTCOL#                                            NUMBER
     EQUALITY_PREDS                                     NUMBER
     EQUIJOIN_PREDS                                     NUMBER
     NONEQUIJOIN_PREDS                                  NUMBER
     RANGE_PREDS                                        NUMBER
     LIKE_PREDS                                         NUMBER
     NULL_PREDS                                         NUMBER
     TIMESTAMP                                          DATE
    
     
  • 10g dbms_stats 기본 동작 방식이 변경되어 히스토그램으로 인해 실행계획이 나쁜쪽으로 바뀌거나 통계정보 수집 시간이 늘어나는 문제점이 발생할 수 있다.
  • 대용량 테이블일때 관리자가 직접 히스토그램 수집 컬럼을 아래와 같이 지정해 주는것이 좋다.
     method_opt => 'for columns col1 size 20 col2 size 254 col3 size 100'
     
  • dbms_stats.set_param 프로시저를 통해 기본값을 설정할 수 있다.

4. 데이터 샘플링

  • 샘플링 비율을 높일수록 통계정보의 정확도는 높아지고, 통계정보 수집시간은 더 소요된다.
  • 샘플링 비율 조정하기 위해 dbms_stats.estimate_percent 인자를 사용한다. ( 일정 비율 이상이면 대개 충분한 신뢰 수준에 도달, 5%에서 시작해 값을 늘려가며 적정 크기를 결정한다.)
  • 블록 단위 샘플링 : block_sample인자를 통해 결정한다. 블록 단위 샘플링이 더 빠르고 효율적이지만 데이터 분포가 고르지 않을 때 정확도가 떨어진다. (기본값 : 로우 단위 샘플링 )
  • 안정적인 통계정보의 필요성 : 전수검사에 비해 SQL성능을 불안정하게 만든다. 컬럼에 Null값이 많거나 데이터 분포가 고르지 않을때 샘플링 방식은 부정확한 통계정보를 생성한다.
  • [참고] 선택도를 구하는 공식의 세가지 구성요소 : Null값을 제외한 로우 수, Distinct Value 개수, 총 레코드 개수
  • 해시 기반 알고리즘으로 NDV 계산 - 11g
    : 데이터 분포가 고르지 않은 상황에서 샘플링 방식을 사용하여 정확한 통계정보를 수집할 수 없는 문제를 해결하기 위해 11g는 해시 기반의 알고리즘을 고안했다.
    : 소트를 수행하지 않기 때문에 대용량 파티션 또는 테이블 전체 스캔을 하더라도 기존 샘플링 방식보다 빠르다.
    : 전체를 대상으로 NDV를 구하므로 정확도가 100%에 가깝다.

5. 파티션 테이블 통계 수집

  • 파티션 테이블일 때 테이블 레벨 통계와 파티션 레벨 통계를 따로 관리한다.
  • 파티션 레벨 통계 : Static Partition Pruning이 작동될 때 사용된다. 결합 파티션일 때는 서브파티션 레벨로 통계를 관리 할 수 있다.
  • 테이블 레벨 통계 : Dynamic Partition Pruning이 작동될 때 사용된다. 쿼리에 바인드 변수가 사용됐거나, 파티션 테이브링 NL조인에서 Inner쪽 테이블일 경우, 파티션 키에 대한 조건절이 없을 때도 테이블 레벨 통계가 사용된다.
  • dbms_stats은 global통계를 별도로 수행하는 반면 anlyze는 파티션 통계를 가지고 global 통계를 유추하므로 부정확하다.
  • granularity 옵션 사용하여 통계 수집한다.
     * granularity 설정 값
     1) global : 테이블 레벨 통계 수집
     2) partition : 파티션 레벨 통계 수집 ( 테이블 레벨 통계 수집이 되어있지 않을경우, 파티션 레벨 통계로부터 추정된 값으로 테이블 레벨 통게를 설정한다.)
     3) subpartition : 서브 파티션 레벨 통계 수집
     4) global and partition : 테이블과 파티션 레벨 통계 수집 ( 테이블 레벨 NDV 정보를 정확하게 수집하기 위해 global 통계를 위한 쿼리를 한번 더 수행한다.)
     5) all : 테이블, 파티션, 서브 파티션 레벨 통계 수집 
     6) auto : 파티션 유형에 따라 오라클이 결정
    
  • 대용량 파티션 테이블에 대한 통계 수집 효율성을 높일 방안과 전략이 필요하다.
  • 10g 이하 버전 : 최근 파티션만 통계를 수집하고 나서 테이블 전체 통계를 한 번 더 수행한다.
     begin 
       dbms_stats.gather_table_stats ('ods', 'order'
       , partname => 'order_part_200912'
       , granularity => 'partition'
       , estimate_percent => 20
       );
     end;
    
     begin
       dbms_stats.gather_table_stats ('ods', 'order'
       , granularity => 'global'
       , estimate_percent => 20
       );
      end;
    

1. NDV를 제외한 Incremental Grobal 통계 - 10.2.0.4

  • 10.2.0.4 에서 granularity의 설정값에 'approx_global and partition' 추가 : 테이블 레벨 통계를 따로 수행하지 않고 파티션 레벨 통계로부터 집계한다. 컬럼 히스토그램도 파티션 레벨로 부터 집계한다. 단, 컬럼 NDV와 인덱스의 Distinct Key개수는 제외된다.
     begin 
       dbms_stats.gather_table_stats ('ods', 'order'
       , partname => 'order_200911'
       , granularity => 'approx_global and partition'
       );
     end;
     => 2009년 11월 파티션만 통계를 수집하고 NDV를 제외한 나머지 테이블 레벨 통계는 방금 수집한 새 파티션 통게와 다른 파티션의 기존 통계를 이용해 구한다.
    
  • Unique 인덱스를 가진 컬럼이나 파티션 키 컬럼의 경우는 테이블 레벨 NDV도 갱신이 이루어진다.
    : Unqiue 인덱스를 가진 컬럼 : 전체 레코드 개수가 NDV와 일치
    : 파티션 키 컬럼은 파티션 레벨 NDV를 더한 값과 테이블 레벨 NDV가 일치
  • 위 두 경우를 제외한 다른 컬럼의 테이블 레벨 NDV는 좀 더 넓은 주기로 통계를 수집하거나 테이블 전반에 많은 양의 추가/갱신이 일어날 때마다 한 번씩 수집해 주어야 한다.
  • 새로운 값이 계속 입력되는 컬럼은 low_value/high_value를 매일 갱신해 주어야 한다. 기존 파티션 통계와 추가된 파티션 통계로 부터 테이블 레벨 통계를 구할 수 있다.

2. NDV를 포함한 완벽한 Incremental Grobal 통계 - 11g

  • 11g에서 파티션 레벨 NDV를 이용해 Grobal NDV를 정확하게 구할 수 있다.
  • 파티션 레벨 컬럼별로 synopsis라고 하는 별도의 메타 데이터를 관리하여 NDV를 포함한 완벽한 Incremental Grobal 통계수집 기능을 제공한다.
  • sysnopsis : Distinct Value에 대한 샘플로 파티션마다 각 컬럼이 갖는 값의 집합을 보관했다가 이를 머지하여 Grobal NDV를 구한다. ( 기존에는 집합 개수만 보관했기 때문에 불가능하였다.)
  • 기본적으로 비활성화되어 있으며, 테이블 또는 스키마 별로 활성화한다.
     begin 
       dbms_stats.set_table_prefs ('ods', 'order', 'incremental', 'true');
     end;
     => 아래와 같이 파티션 테이블에 대한 통계수집
     => 'global and partition' 옵션을 지정했지만 테이블 전체를 두번 읽지 않으며 통계 정보가 누락되었거나 stale 상태에 있는 파티션만 통계를 수집한다.
     => NDV를 제외한 파티션 레벨 통계와 sysnopsis를 이용해 테이블 레벨 Global 통계를 갱신한다.
     begin
       dbms_stats.gather_table_stats ('ods', 'order'
       , granularity => 'global and partition'
       , estimate_percent => 20
       );
      end;
    

6. 인덱스 통계 수집

  • 테이블 통게를 수집하면서 cascade 옵션을 true로 설정하여 해당 테이블에 속한 모든 인덱스 통계도 같이 수집한다.
  • 인덱스 마다 gather_index_stats 프로시저를 따로 수행하는 것과 일량은 동일하다.
  • 인덱스는 이미 정렬된 상태여서 소프 연산이 불필요하기 때문에 통계 수집에 걸리는 시간이 짧다. ( 샘플링 방식 불필요 )
  • 그러나 테이블 통계와 같이 진행될 경우 테이블에 적용된 샘플링 비율이 인덱스 통계 수집에도 적용되는 문제가 있다.
  • 이런 경우, 테이블 통계만 샘플링 방식을 사용하고, 인덱스는 전수 검사를 하도록 각기 통계를 수집하는 것이 좋다.
      begin
       dbms_stats.gather_table_stats ('user', 'big_table', cascade=>false, estimate_percent=>10);
      end;
    
      dbms_stats.gather_index_stats ('user', 'big_table_pk', estimate_percent=>100);
      dbms_stats.gather_index_stats ('user', 'big_table_x1', estimate_percent=>100);
    
  • 10g부터는 인덱스를 처음 생성하거나 재생성할 때 인덱스 통계가 자동 수집된다. (_optimizer_compute_index_stats = true )

7. 캐싱된 커서 invalidation

  • no_invalidate 옵션에 따라 통계를 수집한 테이블과 관련된 SQL커서의 무효화시점이 달라진다.
    1) false : 통계정보 변경시 관련된 SQL커서들이 즉시 무효화되고, 첫 번째 수행하는 세션에 의해 새로 갱신된 통계정보를 이용한 실행계획이 로드된다.
    2) true : SQL커서가 자동으로 Shared Pool에서 밀려났다가 다시 로드될 때 새로 갱신된 통계정보를 사용한다. 통계정보 변경시 관련된 SQL 커서들을 무효화하지 않는다.
    3) dbms_stats.auto_invalidate : 통계정보 변경시 관련된 SQL커서들이 한꺼번에 무효화하지 않고 정해진 시간 동안 조금씩 무효화한다. ( 무효화된 커서들이 동시에 수행되면서 하드파싱에 의한 라이브러리 캐시 경합을 방지하기 위하여 10g에서 도입한 기능)
    _optimizer_invalidation_period파라미터에 무효화 소요 시간을 설정한다. ( 기본값 : 18000초 )
    4) 기본값 : 9i => false, 10g => dbms_stats.auto_invalidate

8. 자동 통계 수집

  • 10g부터 매일밤 10기 부터 다음날 아침 6시까지 모든 사용자 오브젝트에 대한 통계를 자동 수집하도록 Job이 등록되어있다.
  • gather_stats_job에 의해 자동 수행되며 통계정보가 없거나 통계 테이블의 state_stats컬럼을 참조하여 통계정보 수집 후 DML이 많이 발생한 모든 오브젝트를 대상으로 한다.
  • gather_stats_job : 데이터베이스 생성시 자동 등록되며, Maintenance 윈도우 그룹에 의해 등록된 윈도우가 열릴 때마다 스케쥴러에 의해 수행된다.

1. 통계정보 갱신 대상 식별

  • 테이블 모니터링 기능
  • 9i는 nomonitorting이 기본값으로 필요한 테이블만 monitoring옵션을 지정한다 ( alter table emp monitoring; )
  • 10g는 모든 테이블 모니터링 한다,
  • DML발생량 모니터링 : statistic_level이 typical, all일 때 monitoring옵션이 지정된 테이블에서 발생하는 DML 발생량을 모니터링
  • 모니터링 대상 테이블이 10%이상 변경이 발생했을때 해당 테이블을 stale 상태로 변경 ( *_tab_statistic 뷰의 stale_stats = 'YES' )
  • stale 상태인 테이블들에 대해 통계정보 새로 수집 : gather_database_stats 또는 gather_schema_stats프로시저를 호출하면서 option인자에 'gather_stale' 또는 'gather_auto' 지정한다.
  • 11g에서는 stale상태로 바뀌는 임계치를 오브젝트별로 조정 가능하다.
  • 테이블 변경 사항은 Shared Pool에 모았다가 SMON이 주기적으로 데이터 딕셔너리에 반영하는 시점이후 *_tab_modification과 *_tab_statistic 뷰의 stale_stats컬럼이 변경된다.
  • dbms_stats.flush_database_monitoring_info 프로시저를 호출하면 현재 변경사항이 딕셔너리에 바로 반영된다.
  • gather_stats_job 등록상태 조회 : 457page
  • Maintenance 윈도우 그룹 등록상태 조회 : 458page

    2. 자동 통계 수집 기능 활용 가이드

  • 중대형급 이상 데이터베이스의 경우 10g에서 제공하는 자동 통계 수집 가능은 사용하지 않는것이 좋다.
  • Maintenance 윈도우 이내에 통계 수집이 완료되지 않은 경우에 시스템이 불안정한 상태에 빠질 수 있다.
  • 오브젝트별 전략을 세우고 짧은 시간 내에 정확하고 안정적인 통계정보를 수집할 수 있도록 별도의 스크립트를 준비한다.
  • 11g부터 Statistics Preference 기능을 이용하여 오브젝트별 통계정보 수집 선택사양을 설정한다.

9. Statistics Preference

  • gather_stats_jobs를 활성화한 상태에서 테이블 또는 스키마별로 통계 수집 방법을 따로 설정한다.
  • 시스템 여건과 테이블 특성에 맞는 통계수립 정책이 자동 통계 수집 기능에 반영되었다.
  • dbms_stats.set_table_prefs, dbms_stats.set_schema_prefs 프로시저를 사용하여 설정한다. ( 460 page )
  • perference 설정 내용 조회 : select * from dba_tab_stst_prefs;

문서에 대하여

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.