통계정보 Ⅱ

1.전략적인 통계수집 정책의 중요성
  1.CBO 능력을 최대한 끌어 올리는 핵심 요소
  2.DB 관리자의 핵심 역할은 통계정보 관리
  3.통계정보 수집시 고려사항
  4.주기적으로 통계정보 수집하면서 안정적이어야 최선
  5.통계정보 수집 정책 수립은 필수
2.DBMS_STATS
3.컬럼 히스토그램 수집
4.데이터 샘플링
5.파티션 테이블 통계수집
  1.NDV(the Number of Distinct Value)를 제외한 Incremental Global 통계 - 10.2.0.4
  2.NDV를 포함한 완벽한 Incremental Global 통계 - 11g
6.인덱스 통계정보 수집
7.캐싱된 컬럼 Invalidation
8.자동 통계정보 수집
  1.통계정보 갱신 대상 식별
  2.자동통계 수집기능 활용 가이드
9.Statistics Preference

=======================================================================================================

1.전략적인 통계수집 정책의 중요성
  1.CBO 능력을 최대한 끌어 올리는 핵심 요소
    -통계정보 : CBO에게 미치는 영향력이 절대적이다.
  2.DB 관리자의 핵심 역할은 통계정보 관리
    -문제 없던 쿼리가 갑자기 악성 SQL로 돌변했다면... 통계정보에서 비롯된 문제일 가능성이 높다.
  3.통계정보 수집시 고려사항
    ▶시간 : 부하가 없는 시간대에 가능한 빠르게 수집을 완료해야 함
    ▶샘플크기 : 가능한 적은 양의 데이터를 읽어야 함
    ▶정확성 :  전수 검사할 때의 통계치에 근접해야 함
    ▶안정성 : 데이터에 큰 변화가 없는데 매번 통계치 정보가 바뀌지 않아야 함
  4.주기적으로 통계정보 수집하면서 안정적이어야 최선
  5.통계정보 수집 정책 수립은 필수

2.DBMS_STATS
  -과거 Analyze 명령어 대신 DBMS_STATS 패키지를 사용하는 것이 바람직
  -특히 파티션 테이블/인덱스일 때는 반드시 DBMS_STATS를 사용해야 함
  -DBMS_STATS.GATHER_TABLE_STATS 프로시저 인자표(p446~447)

3.컬럼 히스토그램 수집
  -비싼 비용관리로 필요한 컬럼에만 히스토그램을 수집함
  -조건절에 자주 사용하면서 편중된 데이터 분포를 갖는 컬럼이 주 대상임
  -아래와 같은 컬럼에는 Histogram이 불필요함(★★★)
   ▶컬럼 데이터 분포가 균일
   ▶Unique하고 항상 등치조건으로만 검색되는 컬럼
   ▶항상 Bind 변수로 검색되는 컬럼

4.데이터 샘플링
  -샘플링 비율을 높일수록 통계정보의 정확도는 높아지고 수집시간은 더 소요된다.
   ▶샘플링 비율
     -샘플링 비율 조정을 위해선 DBMS_STATS.ESTIMATE_PERCENT 인자를 사용한다.
      (5%에서 시작해 값을 늘려가며 두세번만 통계를 수집해 보면 적정 크기를 결정할 수 있음)
   ▶블록 단위 샘플링
     -기본 값은 ROW단위 샘플링
      (블록 단위 샘플링이 더 효과적이긴 하지만 데이터 분포가 고르지 않을 때 정확도 문제발생)
   ▶안정적인 통계정보의 필요성
     -전수조사에 비해 매번 통계치가 다르게 구해질 수 있음. 실행계획에 영향이 있음
      (특히, NULL 값이 많거나 데이터 분포가 고르지 않을 때 더욱 그러함)
   ▶해시 기반 알고리즘으로 NDV 계산 - 11g의 New Feature
     -소트를 수행하지 않기 때문에 기존 샘플링 방식보다 빠르다.
     -전체를 대상으로 NDV(the Number of Distinct Values)를 구하므로 정확도가 100%에 가깝다.

5.파티션 테이블 통계수집
  -파티션 테이블일 때 테이블 Level 통계(global 통계)와 파티션 Level 통계를 따로 관리함
   ▶파티션 Level 통계
     -Static Partition Pruning이 작동될 때 사용됨
     -결합 파티션(Hash + Range)일 때는 서브파티션 Level로 통계를 관리할 수 있다.
   ▶테이블 Level 통계
     -Dynamic Partition Pruning이 작동될 때 사용됨
     -쿼리에 Bind변수가 사용됐거나 파티션 키에 대한 조건절이 없을 때도 테이블레벨 통계가 사용됨
 
  (아래의 내용은 이해가 잘 되지 않아서...일단 Skip 처리함 B조 자료 참고해야 할 듯...)
  1.NDV(Number of Distinct Value)를 제외한 Incremental Global 통계 - 10.2.0.4
  2.NDV를 포함한 완벽한 Incremental Global 통계 - 11g

6.인덱스 통계정보 수집
  -테이블 통계를 수집하면서 CASCADE옵션을 TRUE로 설정하면 해당 테이블 모든 인덱스통계도 같이 수집함
  -별도로 인덱스마다 GATHER_INDEX_STATS를 따로 수행하는 것과 일량은 동일함
  -이미 정렬된 상태여서 SORT 연산이 불필요하기에 수집에 걸리는 시간이 짧다(샘플링 방식 불필요)
  -테이블 Level의 통계와 같이 진행될 경우 샘플링 비율이 인덱스 통계 수집에도 적용되는 문제 발생
   이런 경우는 테이블 통계만 샘플링 방식 처리, 인덱스는 전수 검사를 하도록 수집하는 것이 좋다.
   /* 테이블 통계는 ESTIMATE MODE */
   EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMPLOYEES', CASCADE=>FALSE, ESTIMATE_PERCENT=>10);
  
   /* 인덱스 통계는 COMPUTE MODE */
   EXEC DBMS_STATS.GATHER_INDEX_STATS(USER, 'EMP_DEPARTMENT_IX', ESTIMATE_PERCENT=>100);
   EXEC DBMS_STATS.GATHER_INDEX_STATS(USER, 'EMP_EMP_ID_PK'    , ESTIMATE_PERCENT=>100);

  -cf. 10g부터는 인덱스를 처음 생성하거나 재생성할 때 인덱스 통계가 자동 수집된다
       (_optimizer_compute_index_stats = true)

7.캐싱된 커서 Invalidation
  DBMS_STATS.GATHER_TABLE_STATS (
  OWNNAME          VARCHAR2,
  TABNAME          VARCHAR2,
  PARTNAME         VARCHAR2 DEFAULT NULL,
  ESTIMATE_PERCENT NUMBER   DEFAULT TO_ESTIMATE_PERCENT_TYPE(GET_PARAM('ESTIMATE_PERCENT')),
  BLOCK_SAMPLE     BOOLEAN  DEFAULT FALSE,
  METHOD_OPT       VARCHAR2 DEFAULT GET_PARAM('METHOD_OPT'),
  DEGREE           NUMBER   DEFAULT TO_DEGREE_TYPE(GET_PARAM('DEGREE')),
  GRANULARITY      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
  CASCADE          BOOLEAN  DEFAULT TO_CASCADE_TYPE(GET_PARAM('CASCADE'))
  STATTAB          VARCHAR2 DEFAULT NULL,
  STATID           VARCHAR2 DEFAULT NULL,
  STATOWN          VARCHAR2 DEFAULTNULL,
  NO_INVALIDATE    BOOLEAN  DEFAULT TO_NO_INVALIDATE_TYPE(GET_PARAM('NO_INVALIDATE')),
  FORCE            BOOLEAN  DEFAULT FALSE );

  NO_INVALIDATE : 의존적인 CURSOR를 INVALIDATE 할 지, 안할 지 결정
                  9i
                  TRUE  - 관련된 CURSOR 를 INVALIDATE 하지 않는다.
                  FALSE - 관련된 CURSOR 를 INVALIDATE 한다. (9I까지의 DEFAULT VALUE)
 
                  10g
                  DBMS_STATS.AUTO_INVALIDATE
                        - DBMS가 의존적 CURSOR를 언제 INVALIDATE 할지 자동으로 결정 10g DEFAULT VALUE)
 
  이때 작용하는 PARAMETER는 _OPTIMIZER_INVALIDATION_PERIOD 이고, DEFAULT로 18000초

  (5시간) 이다. 즉, 통계 정보 수집에 의해 통계 정보가 변경된 후 약 5시간에 걸쳐 랜덤한 시점에 

  해당 CURSOR가 실행될 때 INVALIDATION이 발생한다. 이것을 AUTO INVALIDATION이라고 부른다.
  일정 시간에 걸쳐 랜덤하게 CURSOR를 INVALIDATION함으로써 특정 시점에 HARD PARSE가  한꺼번에

  몰리는 현상을 피할 수 있다. 

8.자동 통계정보 수집
  1.통계정보 갱신 대상 식별
    -테이블별 모니터링 가능
     9i  선택적 가능 : alter table emp monitoring
     10g 모든 테이블
    -DML 발생량 모니터링 : statistic_level이 typical, all일 때는 모니터링 테이블에서

                         발생하는 DML모니터링(statistic_level은 파라미터임)
    -10% 이상 변경이 발생했을 때는 테이블 상태가 stale로 변경됨
     *_tab_statistics 뷰의 stale_stats = 'YES'로 바뀜(SELECT * FROM USER_TAB_STATISTICS;)
     stale 상태인 테이블들에 대해선 통계정보를 새롭게 수집합
     (gather_database_stats OR gather_schema_stats 프로시저 호출하면서 옵션인자를 별도 지정함
    -11g에선 stale 상태로 바뀌는 임계치를 오브젝트별로 조정가능
  2.자동 통계 수집기능 활용 가이드
    -중대형급 이상일 경우는 10g에서 제공하는 자동통계 수집가능은 사용하지 않는 것이 좋음
     Maintenance 윈도우 이내에 통계수집이 완료되지 않을 경우는 불안정한 상태에 빠질 수 있음
    -오브젝트별 전략을 세우고 짧은 시간 내에 정확하고 안정적인 통계정보를 수집할 수 있도록 별도의
     스크립트를 준비하는 것이 좋음
    
9.Statistics Preference
  -11g의 New Feature
  -dbms_stats.set_table_prefs, dbms_stats.set_scheme_prefs 프로시저 사용하여 설정
  -Preference 설정 내용조회 View : *_tab_stat_prefs