성능고도화 3-8. 통계정보2

통계정보 수집 시 고려사항 

 - 시간 : 부하가 없는 시간대에 가능한 빠르게 수집을 완료해야 함 

 - 샘플 크기 : 가능한 적은 양의 데이터를 읽어야 함 

 - 정확성 : 전수 검사할 때의 통계치에 근접해야 함 

 - 안정성 : 데어터에 큰 변화가 없는데 매번 통계치가 바뀌지 않아야 함 

통계 수집 정책 수립은 필수 

 - 통계를 수집할 필요가 없는 오브젝트에 대해서는 lock 옵션으로 통계정보를 고정할 수 있다.

 - 통계정보에 영향을 받아선 안 되는 중요한 일부 핵심 프로그램에 대해선 옵티마이저 힌트를 

   적용해 실행계획을 고정시키는 것이 최선이다. 

 - 통계정보 변화 때문에 애플리케이션 성능에 심각한 문제가 발생했을 때를 대비해 가장 

   안정적이었던 최근 통계정보를 항상 백업해 두기 바란다. 

DBMS_STATS 

 - 통계정보 수집을 위해 오랫동안 사용돼 온 Analyze 명령어를 버리고 이제는

   dbms_stats가 더 정요하게 통계를 계산해 내기 때문이여, 특히 파티션 테이블/인덱스일 때는 

   반드시 dbms_stats를 사용해야 한다. 

 - analyze 명령어를 사용해야 하는 경우 

    ● freelist 블록 정보 ( 그러나 요즘 대부분 ASSM을 사용하므로 freelist 정보는 필요 없다. )

    ● 테이블별로 로우 체인(또는 로우 마이그레이션) 발생 현황 진단  

컬럼 히스토그램 수집 

 - 히스토그램은 이를 수집하고 관리하는 비용이 만만치 않다. 따라서 필요한 컬럼에만 

   히스토그램을 수집해야 하며, 조건절에 자주 사용되면서 편중된 데이터 분포를 갖는 컬럼이 

   주 대상이다. 

 - 인덱스 컬럼에만 히스토그램이 필요한것이 아니라 테이블을 엑세스하고 나서의 최종 선택도를 

   계산할 때는 인덱스가 없는 조건절 컬럼의 선택도도 인자로 사용되고, 그렇게 구해진 선택도에

   따라 다른 집합과의 조인 순서 및 조인 방식이 결정되기 때문에 히스토그램이 필요하다. 

 - 히스토그램이 불필요한 컬럼 

    ● 컬럼 데이터 분포가 균일 

    ● Unique하고 항상 등치조건으로만 검색되는 컬럼

    ● 항상 바인드 변수로 검색되는 컬럼 

데이터 샘플링

 - 샘플링 비율을 높일수록 통계정보의 정확도는 높아지지만 통계정보를 수집하는 데 더 많은 

   시간이 소요된다. 반대로 샘플링 비율을 낮추면 정확도는 다소 떨어지지만 더 효율적이고 

   빠르게 통계를 수집할 수 있다. 

 - 블록 단위 샘플링이 더 빠르고 효율적이긴 하지만 데이터분포가 고르지 않을 때 정확도가 

   많이 떨어진다. 기본 값은 로우 단위 샘플링이다. 

 - 샘플링 방식을 사용하면 매번 통계치가 다르게 구해질 수 있고 이는 실행계획에 영향을 미쳐 

   SQL 성능을 불안정하게 만든다. 

 - 특히 컬럼에 Null 값이 많거나 데이터 분포가 고르지 않을 때 그렇다. 

   null 값을 제외한 로우 수, Distinct value 개수는 컬럼 분포가 고르지 않을 때 샘플링 비율에 

   의해 영향을 크게 받는다. 

 - 해시 기반 알고리즘으로 NDV 계산 - 11g 

    ● 오라클 11g는 해시 기반의 새로운 알고리즘을 고안, 대용량 파티션 또는 테이블 전체를 

        스캔하더라도 기존에 샘플링 방식을 사용할 때보다 오히려 빠른 속도를 낼 수 있게 

        되었다. 소트를 수행하지 않아, 전체를 대상으로 NDV를 구하므로 정확도는 당연히 100%에 

        가깝다. 빠르고, 정확하면서도 안정적인 통계정보를 구현할 수 있게 된 것이다.

 

파티션 테이블 통계 수집 

 - 파티션 테이블일 때 오라클은 테이블 레벨 통계와 파티션 레벨 통계를 따로 관리한다. 

     ● 파티션 레벨 통계 : Static Partition Pruning이 작동할 때 사용된다. 결합 파티션일 때는 

                                   서브파티션 레벨로 통계를 관리할 수도 있다. 

     ●테이블 레벨 통계 : Dynamic Partition Pruning이 작동할 때 사용된다. 쿼리에 바인드 

                                   변수가 사용됐거나, 파티션 키에 대한 조건절이 없을 때도 

                                   테이블 레벨 통계가 사용된다. 

 - dbms_stats는 global 통계를 위한 쿼리를 별도로 수행하는 반면, analyze는 파티션 통계를 

   가지고 global 통계를 유추하므로 부정확하다. 

 - dbms_stats은 global 통계를 위한 쿼리를 별도로 수행하는 반면, 

   analyze는 파티션 통계를 가지고 global 통계를 유추하므로 부정확하다. 

 - dbms_stats 패키지 사용 시 granularity 옵션 

    global : 테이블 레벨 통계 수집  

    partiton : 파티션 레벨 통계 수집

    subpartition : 서브 파티션 레벨 통계 수집 

    global and partition : 테이블과 파티션 레벨 통계 수집 

    all : 테이블, 파티션, 서브 파티션 레벨 통계 수집

    auto : 파티션 유형에 따라 오라클이 결정 

 - dba/all/user_tables에서 볼 수 있는 항목 중 global_stats은 테이블 레벨 통계 수치들이 

   어떻게 구해졌는지를 알려준다. 

   (즉, 테이블 레벨 통계를 따로 수집했다면 'YES', 파티션 통계를 이용해 추정했다면 'NO'로 표시)

 - 10g 이하 버전을 사용하고 있다면, 아래처럼 최근 파티션만 통계를 수집하고 나서 테이블 

   전체 통계를 한 번 더 수행하는 방식을 사용하는 것이 효과적이다. 테이블마다 프로시저를 

   두 번 호출하도록 스크립트를 작성하는 것이 성가시더라도 어쩔 수 없다. 

 - NDV를 제외한 Incremental Global 통계 - 10.2.0.4 

    ● 'approx_global and partition' 옵션이 'global and partition'과 다른 점은, 

        테이블 통계를 위한 쿼리를 따로 수행하지 않고 파티션 레벨 통계로부터 집계한다는 데에 

        있다. 테이블 레벨 컬럼 히스토그램도 파티션 레벨로부터 집계한다. 단, 컬럼 NDV와 

        인덱스의 Distinct Key 개수는 제외된다. 

    ● unique 인덱스나 파티션 키 컬럼이 있으면 NDV도 같이 갱신된다. 

 - NDV를 포함한 호나전한 Incremental Global 통계 - 11g 

      11에선 파티션 레벨 NDV를 이용해 Global NDV를 정확히 구할 수 있는 방법까지 제공 

      ( 이 기능은 기본적으로 비활성화돼 있으며, 프로시저를 호출해 필요한 테이블별로 활성화 )

인덱스 통계 수집 

 - 대용량 테이블이어서 샘플링 비용을 지정하면 인덱스 통계까지도 같은 비율이 적용된다

   ( 인덱스는 통계 수집에 걸리는 시간은 매우 짧아 굳이 샘플링 방식을 사용할 필요가 없다 ) 

 - 그럴 때는 테이블 통계만 샘플링 방식을 사용하고, 인덱스는 전수 검사하도록 각기  

   통계를 수집해 주는 것이 좋다. 

 - 10g부터는 인덱스를 처음 생성하거나 재생성할 때는 인덱스 통계가 자동 수집되며 

   '_optimizer_compute_index_stats' 파라미터를 통해 설정을 변경할 수 있다. 

캐싱된 커서 Invalidation 

 - no_invalidation 옵션을 어떻게 지정하느냐에 따라 통계를 수집한 테이블과 관련된 SQL 커서의 

   무효과 시점이 달라진다. 

      false : 통계정보 변경 시 관련된 SQL 커서들이 즉시 무효화된다.

      true  : 통계정보 변경 시 관련된 SQL 커서들을 무료화하지 않는다. 

      dbms_stats.auto_invalidate : 통계정보 변경 시 관련된 SQL 커서들을 한꺼번에 무료화하지 

                                             않고 정해진 시간 동안 조금씩 무효화한다. 

 - 9i는 기본 값이 false 10g에서 dbms_stats.auto_invalidate로 바뀌었다. 

 - 이 기능을 제어하는 파라미터는 _optimizer_invalidation_period이고, 기본 값은 18,000초다 

   ( 즉, 늦어도 5시간 이내에는 관련 SQL 커서가 무효화된다. ) 

자동 통계 수집 

 - 오라클 10g 부터 기본적으로 매일 밤 10시부터 다음날 아침 6시까지 모든 사용자 오브젝트에 

   대한 통계를 자동 수집하도록 Job이 등록돼 있다. 이 기능은 gather_stats_job에 의해 

   자동 수행되며, 통계정보가 없거나 통계정보 수집 후 DML이 많이 발생한 모든 오브젝트를 

   대상으로 한다. 

 - 자동 통계 수집 기능을 사용하지 않으려면 스케쥴러 job을 제거하면 된다. 

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

 - 중대형급 이상 데이터베이스를 관리한다면 10g에서 제공하는 자동 통계 수집 기능은 

   사용하지 않는 편이 좋겠다. 특히 Maintenance 윈도우 이내에 통계 수집이 완료되지 않는 

   경우가 생기면 시스템을 불안정한 상태에 빠뜨릴 수 있으므로 주의해야 한다. 

 - 될 수 있으면 정확하고 안정적인 통계정보를 수집할 수 있도록 별도의 스크립트를 

   준비하는 것이 좋다.