잡동사니 보물섬

선택도 

 - 선택도는 전체 대상 레코드 중에서 특정 조건에 의해 선택될 것으로 예상되는 

    레코드 비율(%)을 말한다. 

 - 선택도 -> 카디널리티 -> 비용 -> 엑세스 방식, 조인 순서, 조인 방법 등 결정 

 - 히스토그램 없이 부등호, between 같은 범위검색 조건에 대한 선택도를 구하는 기본 공식은 

   다음과 같다.   

   선택도 = 조건절에서 요청한 값 범위 / 전체 값 범위 

 - 분자 , 분모에 사용된 두개의 '갑 범위'는 컬럼 통계로서 수집된 high_value, low_value 

   , num_distinct 등을 이용해 구한다. 

 - 컬럼 히스토그램이 없을 때 옵티마이저는 '조건걸에서 요청한 값 범위'에 속한 값들이 

    '전체 값 범위'에 고르게 분포돼 있음을 가정하고 선택도를 구한다. 

카디널리티 

 - 특정 액세스 단계를 거치고 나서 출력될 것으로 예상되는 결과 건수 

 - num_rows는 테이블 통계에서, num_distinct는 컬럼 통계에서 확인할 수 있다. 

     ● 테이블 통계 : dba_tables, dba

     ● 컬럼 통계 : dba_tab_columns, dba_tab_col_statistics

Null 값을 포함할 때 

 - null 값을 제외하고  카디널리티가 계산된다. (바인드 변수가 아닐 시) 

조건절이 두 개 이상일 때 

 - 각 컬럼의 선택도와 전체 로우 수를 곱해 주기만 하면 된다. 

범위검색 조건일 때 

 - 옵티마이저는 조건절에서 요청한 범위에 속한 값들이 전체 값 범위에 고르게 분포돼 있음을 

   가정한다. 

cardinality 힌트를 이용한 실행계획 제어 

 - cardinality 를 임의로 적어주는 힌트 /*+ cardinality(table 16) */ 

     ( 이런 식으로 cardinality 숫자를 적어 줄 수 있다. ) 

 - 10g 부터는 opt_estimate를 이용할 수 있다. 

    /*+ opt_estimate (table, d, scale_rows=4 ) */ 

    ( 옵티마이저가 예상한 카디널리티에 4를 곱하라는 의미 )

테이블 통계 

 - compute는 전수 검사, estimate는 표본 조사를 뜻한다. 

 - 통계정보를 수집할 때 이제는 analyze 명령어를 사용하지 말라는 것이 오라클의 

   공식적인 입장이다. 

인덱스 통계 

 - 인덱스 통계 사용시 analyze, dbms_stats 를 사용하는 방법 

 - 인덱스를 최초 생성하거나 재생성할 때 아래와 같이 compute statistic 옵셥을 주면 

    자동으로 인덱스 통계까지 수집된다. 

 - 인덱스는 이미 정렬돼 있으므로 통계정보 수집에 오랜 시간이 소요되지 않는다. 

 - 10g부터는 사용자가 이 옵션을 명시하지 않아도 오라클이 알아서 인덱스 통계까지 수집한다. 

컬럼 통계 

 - 테이블, 인덱스 통계를 제외하고 컬럼 통계만 수집하는 방법이다. 

 - size 옵션은 히스토그램의 최대 버킷 개수를 지정하는 옵셥으로서, 1부터 254까지 허용된다. 

   size를 명시하지 않으면 오라클이 75를 기본 값으로 사용하므로 히스토그램이 생성되지 않도록 

   하고 싶을 때는 size 옵션을 1로 명시해야 한다. 

 - 일부 컬럼에 대한 통계만 수집 가능하다. 

    ( 히스토그램 버킷 개수를 컬럼별로 따로 지정할 수도 있다. )

 - dbms_stats 패키지로 컬럼 통계만 따로 수집하느 방법은 없다. 

    ( 테이블 통계와 항상 같이 수집된다. )

시스템 통계

 - 시스템 통계가 포함하는 항목들 

     ● CPU 속도 

     ● 평균적인 Single Block I/O 속도 

     ● 평균적인 Multiblock I/O 속도 

     ● 평균적인 Multiblock I/O 개수 

     ● I/O 서브시스템의 최대 처리량 

     ● 병렬 Slave의 평균적인 처리량 

 - workload 시스템 통계 

     ● 9i에서 처음 도입된 workload 시스템 통계는, 애플리케이션으로부터 

         일정 시간 동안 발생한 시스템 부하를 측정, 보관함으로써 

          그 특성을 최적화 과정에 반영할 수 있게 한 기능이다.

     ● NoWorkload 시스템 통계는 오라클이 무작위로 I/O를 발생시켜 측정한 값이 반면 

         Workload 시스템 통계는 실제 애플리케이션에 발생하는 부하를 측정한 값이다. 

 - NoWorkload 시스템 통계 

    ● 관리자가 명시적으로 선택하지 않더라도 CPU 비용 모델이 기본 비용 모델로 사용되게 

        하려고 오라클 10g에서 NoWorkload 시스템 통계를 도입하였다. 

        CPU 비용 모델은 시스템 통계가 있을 때만 활성화되기 때문이다.

    ● Workload 시스템 통계를 수집하고 반영하는 순간 NoWorkload 시스템 통계는 무시된다. 

        Workload 시스템 통계가 수집되기 전까지는 공식을 이용해 추정된 값을 사용한다. 

    ● NoWorkload도 Workload와 마찬가지로 부하를 준 상태에서 측정된 값을 사용해야 

        시스템 통계로서 의미가 있다.

    ● Workload는 실제 애플리케이션에서 발생하는 부하를 기준으로 각 항목의 통계치를 

        측정하는 반면 NoWorkload는 모든 데이터파일 중에서 

        오라클이 무작위로 I/O를 발생시켜 통계를 수집한다.

    ●  NoWorkload 시스템 통계를 수집할 때는 dbms_stats_gather_system_stats 프로시저를 

         아무런 인자도 주지 않고 호출하거나 gathering_mode 인자를 'NOWORKLOAD' 로 

         지정하면 된다.

    ● 이 프로시저를 실행하는 동안 I/O 서브시스템에 약간의 부하가 발생하며, I/O 성능과 

        데이터베이스 크기에 따라 적게는 수초, 길게는 수 분이 소요될 수 있다.

부족한 옵티마이징 팩터 

 - 옵티마이저는 주어진 환경에서 최선을 다할 뿐 적절한 옵티마이징 팩터를 제공하는 것은 결국 

   사람의 몫 

부정화한 통계 

 - 샘플링 방식으로 통계를 수집할 때는 정확성 문제와 더불어 안정성 측면에도 관심을 기울여야 

    한다, 분포가 균등하지 않은 컬럼이라면 수집할 때마다 통계치가 바뀔 수 있어 실행계획을 

    불안정하게 만든다. 

 - 통계 수집 주기도 매우 중요하다. 

히스토그램의 한계 

 - 히스토그램 버킷 개수로 254개까지만 허용된다는 점도 옵티마이저에겐 주요한 제약사항이다. 

바인드 변수 사용 시 균등분포 가정 

 - 아무리 정확한 컬럼 히스토그램을 보유하더라도 바인드 변수를 사용한 SQL에는 무용지물이 

   되고 만다. 조건절에 바인드 변수를 사용하면 옵티마이저가 균등분포를 가정하고 

   비용을 계산하기 때문 

결합 선택도 산정의 어려움 

 - 조건절 컬럼이 서로 상관관계에 있으면 정확한 데이터 분포와 카디널리티를 산정하기 어려움 

 - 9i 부터는 동적 샘플링을 통해 이 문제를 해결하려고 시도, 소량의 데이터 샘플링을 통해 

    where 조건절에 사용된 두 개 이상 컬럼의 결합 분포를 구하는 기능으로, 동적 샘플링 레벨을 

    4 이상으로 설정할 때만 작동 

 - 11g부터는 사용자가 지정한 컬럼들에 대한 결합 선택도를 미리 수집해 두는 기능을 제공 

비현실적인 가정 

 - singleblock I/O 와 multiblock I/O 비용을 갖게 평가 

 - 캐싱 효과를 고려하지 않음 

 - 비용 평기시 디스크 I/O call 횟수만 더함

   * 옵티마이저는 메모리 자원에 대한 최악의 상황을 가정하고 비용을 산정하는 것인데, 

      이는 DW 환경에나 적합한 가정이다. 

 - 비현실적인 가정들을 보정할 수 있도록 오라클은 8i에서 아래 두 개의 파라미터를 제공 

     ● optimizer_index_caching 

     ● optimizer_index_cost_adj 

규칙에 의존하는 CBO 

 - 아무리 비용기반 옵티마이저라 하더라도 부분적으로는 규칙에 의존한다. 

 - 예를 들어, 원격 테이블이나 External 테이블에 대해서는 카디널리티, 평균 행 길이, 블록수 

   그리고 각종 인덱스 관련 통계항목들에 대해 고정된 상수 값을 사용한다. 

 - 9i 까지는 뷰 또는 서브쿼리를 만나면 무조건 쿼리 블록을 풀어 메인 쿼리와 Merging하려한다. 

 - 10g에서 대부분 쿼리 변환이 비용기반으로 개선 되었다. 

알파벳순 인덱스 선택 규칙 

 - CBO 에서, 두 대안 인덱스의 예상 비요이 같을 때 알파벳 순에서 앞선 것을 선택한다. 

 - 물리 데이터베이스 설계 단계에서 인덱스 명명 규칙을 정할 때 매우 중요한 시사점이다. 

하드웨어 성능 특성 

 - 오라클 9i부터 시스템 통계를 수집하는 기능이 도입되었다. 

동적 실시간 최적화 

 - 쿼리 최적화가 단일 SQL문 성능을 최적화하는 데 초점을 맞추는 반면, 동적 실시간 최적화는 

   수많은 SQL이 동시에 수행되는 환경에서 시스템 전체 최적화를 이루는 데 초점을 맞춘다.