잡동사니 보물섬

통계정보 수집 시 고려사항 

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

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

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

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

통계 수집 정책 수립은 필수 

 - 통계를 수집할 필요가 없는 오브젝트에 대해서는 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 윈도우 이내에 통계 수집이 완료되지 않는 

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

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

   준비하는 것이 좋다. 

I/O 비용 모델 

 - I/O 비용 모델에서 비용은 디스크 I/O Call 횟수를 의미한다. 

 - 인덱스를 경유한 테이블 엑세스 시에는 Single Block I/O 방식이 사용된다. 

   이는 읽게 될 물리적 블록 개수가 엑세스 비용과 일치한다. 

 - cost 

     = blevel + (리프 블록 수 x 유효 인덱스 선택도 ) + (클러스터링 팩터 x 유효 테이블 선택도)

Full Scan에 의한 테이블 엑세스 비용 

 - 테이블을 Full Scan 할 때는 HWM 아래쪽 블록을 순차적으로 읽어 들이는 과정에서 발생하는 

    I/O Call 횟수로 비용을 계산한다. 

 - Multiblock I/O 단위를 증가시킬수록 I/O Call 횟수가 줄고 예상 비용도 준다 

I/O 비용 모델의 비현실적인 가정 

 - Single Block I/O 와 Multiblock I/O는 비용이 같다. 

 - 캐싱 효과를 전혀 고려하지 않는다. 

 - 이를 보정하기 위한 8버전 부터 제공된 파라미터 

     ● optimizer_index_cost_adj 

          - 이 파라미터는 인덱스 탐색 비용을 조정하고자 할 때 사용하며, 설정할 수 있는 값의 

            범위는 1 ~ 10000 이다. 기본 값으로 설정된 100이란 수치는, 한 번의 I/O Call을 통해 

            single block read 방식으로 한 블록을 읽는 비용과 multiblock read 방식으로 

            여러 블록을 읽는 비용을 같게 평가하라는 뜻이다. 

          - 이 값을 낮게 설정할수록 옵티마이저는 테이블 스캔보다 인덱스를 이용한 

            테이블 엑세스를 선호하게 된다. 

     ● optimizer_index_caching 

          - NL 조인에서 inner 쪽 인덱스 블록이 캐싱돼 있을 가능성을 옵티마이저에게 알려주는 

            파라미터이며, IN-List Iterator 방식으로 인덱스를 탐색할 때 읽게 되는 인덱스 블록 

            엑세스 비용에도 영향을 미친다. 

          - 설정할 수 있는 값의 범위는 0 ~ 100이며, 이 값을 높게 설정할수록 옵티마이저는 

            인덱스를 이용한 NL 조인을 선호하게 된다. 

CPU 비용 모델 

 - 모든 데이터베이스 오퍼레이션은 CPU를 사용하며, 경우에 따라서는 

   I/O 보다 성능에 더 큰 영향을 끼치기도 한다. 

 - 블록 I/O가 소량인데도 쿼리 수행 시간이 상당히 오래 걸리는 경우 

      ● 해시 조인할 때, 해시 체인에 달린 레코드가 많아 해시 체인을스캔하는 부하가 심할때 

      ● 캐싱된 블록을 반복적으로 읽는데, 한 블록 내에서 매번 비효율적으로 많은 레코드를 

          스캔할때 

      ● 버퍼를 Pin한 상태에서 같은 블록을 반복 엑세스할 때 

      ● 다량의 레코드를 읽으면서 건건이 여러 개의 사용자 정의 함수를 반복 호출할 때 

      ● 메모리 소트를 반복할 때

 - 다소 CPU 사용량이 증가하는 경우 

      ● 조건절 개수가 아주 많을 때 

      ● 조건절이나 select-list에 연산 집약적인 작업을 많이 포함할 때

 - CPU 비용 모델에서 비용계산식 

     cost = ( #SRds * sreadtim + #MRds *mreadtim + #CPUCycles / cpuspeed ) / sreadtim 

CPU 비용 

 - CPU 비용 모델에서 말하는 비용은, 쿼리의 예상 총 수행 시간을 single Block I/O 시간 단위로 

   표현한 것이라고 할 수 있다.   

히스토그램 유형

 - 오라클이 사용하는 히스토그램 

     ● 높이균형 히스토그램 

     ● 도수분포 히스토그램

 - 히스토그램을 생성하려면 컬럼 통계 수집 시 버킷 개수를 2 이상으로 지정하면 된다. 

 - 히스토그램 정보는 dba_histograms 또는 dba_tab_histograms 뷰를 통해 확인할 수 있다. 

    특히, 10g에서는 dba_tab_columns 뷰에 histogram 컬럼이 추가되면서 히스토그램 유형을 

    쉽게 파악할 수 있게 되었다. 

     ● FREQUENCY : 도수 분포 히스토그램 

     ● HEIGHT-BALANCED : 높이균형 히스토그램 

     ● NON : 히스토그램 생성하지 않음  

도수분포 히스토그램 

 - 도수분포 히스토그램은 컬럼 값마다 하나의 버킷을 할당한다. 사용자가 요청한 버킷 개수가 

   컬럼이 가진 값의 수보다 많거나 같을 때 사용되며, 최대 254개의 버킷만 허용하므로 

   값의 수가 254를 넘는 컬럼에는 이 히스토그램을 사용할 수 없다. 

 - 254개를 요청하더라도 값의 수만큼만 버킷을 할당하므로 정확한 히스토그램을 위해서라면 

   항상 254개를 요청하는 것이 좋다. 

 - 히스토그램 정보를 조회할 수 있는 뷰에는 아래 두 컬럼이 있다. 

     ● endpoint_value  : 버킷에 할당된 컬럼 값 

     ● endpoint_number : endpoint_value로 정렬했을 때, 최소 값부터 현재 값까지의 누적 수량

높이균형 히스토그램 

 - 높이균형 히스토그램에서는 버킷 개수보다 값의 수가 많기 때문에 하나의 버킷이 여러 개 

   값을 담당한다. 

 - 요청할 수 있는 최대 버킷 개수는 254개이므로 값의 수가 254개를 넘으면 무조건 히스토그램이 

   만들어진다.  (컬럼수보다 버킷을 적게 적어주어도 높이균형 히스토그램이 생성된다.)

 - 높이균형 히스토그램에서는 말 그대로 각 버킷의 높이가 같다. 각 버킷은 {1/(버킷 개수) x100}%

   의 데이터 분포를 갖는다. 

 - 빈도 수가 많은 값을 포함할 때는 두 개 이상의 버킷이 할당된다.  

 - 오라클은 popular value에 대한 카디널리티를 구할 때만 버킷에 의한 계산식을 사용하고 

   나머지는 미리 구해 놓은 density 값을 이용한다. 

 - popular value에 대한 선택도/카디널리티 계산 

     ● 조건절 값이 두 개 이상 버킷을 가진 popular value이면 아래 공식을 따른다. 

         선택도 = (조건절 값의 버킷 개수) / (총 버킷 개수)

 - non-popular value에 대한 선택도/카디널리티 계산 

     ● 카디널리티 = 총 로우 수 x 선택도 = 총 로우 수 x density

 - 바인드 변수 사용 시 카디널리티 계산 

     ● 바인드 변수 사용시 변수 값 입력이 나중에 되니 실행계획에서 카디널리티를 계산할 수가 

         없다. 그래서 컬럼 분포가 균일할 때는 문제될 것이 없지만 그렇지 않을 때는 실행 시점에 

         바인딩되는 값에 따라 최적이 아닌 실행계획일 수 있어 문제다. 

     ● 바인드 변수를 사용하면 정확한 컬럼 히스토그램에 근건하지 않고 카디널리티를 구하는 

         정해진 계산식에 기초해 비용을 계산하므로 최적이 아닌 실행계획을 수립할 가능성이 

         커진다.

     ● 좋은 실행계획을 위해서라면 DW, OLAP, 배치 프로그램에서 수행되는 쿼리는 

         바인드 변수보다 상수를 사용하는 것이 좋고, 날짜 컬럼처럼 부등호, between 같은 

         범위 조건으로 자주 검색되는 컬럼일 때 특히 그렇다.

     ● OLTP성 쿼리이더라도 값의 종류가 적고 분포가 균일하지 않을 때는 상수 조건을 쓰는 것이 

         유용할 수 있다.    

결합 선택도 

 - 동적 샘플링은 소량의 데이터 샘플링을 통해 where 조건절에 사용된 두개 이상 컬럼의 결합 

   분포를 구하는 기능으로서, 동적 샘플링 레벨을 4 이상으로 설정할 때만 작동한다. 

    ( 힌트 사용 시 /*+ dynamic_sampling(4) */ 이렇게 사용 

 - 다중 컬럼 통계 11g에서는 다중 컬럼에 대한 히스토그램도 생성할 수 있게 되었다.