02.옵티마이저 행동에 영향을 미치는 요소

(1) SQL과 연산자 형태

  • 결과가 같더라도 SQL을 어떤 형태로 작성했는지 또는 어떤 연산지( =, in, like, between, 부등호 등)를 사용했는지에 따라 옵티마이저가 다른 선택을 할 수 있고, 궁극적으로 쿼리 성능에 영향

(2) 인텍스, IOT, 클러스터링, 파티셔닝, MV 등 옵티마이징 팩터

  • 쿼리를 똑같이 작성하더라도 인덱스, IOT, 클러스터링, 파티셔닝. MV 등을구성했는지 그리고 어떤 식으로 구성했는지에 따라 실행 계획과 성능에 영향

(3) 제약 설정

  • 데이터 무결성 규칙
    • 개체 무결성 (Entity Integrity)
    • 참조 무결성 (Referential Integrity)
    • 도메인 무결성 (Domain Integrity)
    • 사용자 정의 무결성(또는 업무 제약 조건)

PK 제약과 옵티마이저


 "가입 후 1년이 넘지 않은 고객의 지난 한 달간 주문실적을 조회"

  select sum(주문수량), sum(주문금액 ), count(*) , count(distinct 고객번호)
  from 주문
  where 고객번호 in ( select 고객번호 from 고객 where 가입일자 >= trunc(add_months(sysdate, -12))) and 주문일자 >= trunc(add_months(sysdate, -1))

서브쿼리를 Unnesting 하고서 고객 테이블을 기준으로 NL 조인하려 할 때, 만약 고객 태이블에 PK 제약이 없다면 고객번호 중복을 제거하는 sort unique 오퍼레이션을 먼저 수행해야 한다.
실제 고객번호에 중복 값이 없더라도 옵티마이저에게 그런 사실을 알려주지 않으면 소용이 없다.

FK 제약과 옵티마이저

  • 조인 제거(Join Elimination) 기능을 참조 (4장 6절 )
    -11g에 추가된 Reference 파티셔 닝 (6장 1절 6항 참조)

Not Null 제약과 옵티마이저


"부서 (deptno)별 사원 수를 집계하는 쿼리"

se1ect deptno, count(*) from emp group by deptno;

옵티마이저가 이 쿼리를 최적화할 때 deptno 컬럼에 인덱스가 있으면 index full scan 또는 index fast full scan으로 빠르게 처리, deptno 컬럼에 not null 제약이 있을 때나 가능
not null 제약을 설정하지 않으면 옵티마이저는 null 값이 입력될 가능성을 염두에 두고 실행계획을 수립해야 하므로 태이블 전체를 스캔
업무적으로 이 컬럼에 null 값을 허용하지 않더라도 옵티마이저에게 그런 사실을 알려주지 않으면 소용없는것이다.

Check 제약과 옵티마이저


"제약을 설정하면 5,000을 초과하는 값은 입력되지 않는다"

alter table emp modify sal check (sal <= 5000);


"옵티마이저도 쿼리를 최적화할 때 이 정보를 이용 "
 select * from emp where sal > 5000

 Rows Row Source Operation
 ---- ----------------------------------------------------
    0  FILTER (cr=O pr=O pw=O time=9 us)
    0  TABLE ACCESS FULL EMP (cr=O pr=O pw=O time=0 us)

  *급여(sal)가 5,000을 초과히는 사원을 조회할 때면 filter 조건(null is not null)을 추가해 불필요한 I/0가 수행 되지않도록한다. 

(4) 옵티마이저 힌트

힌트가 적용되지 않는 경우

  1. 문법적으로 맞지 않게 힌트를 기술
  2. 잘못된 참조 사용 : 없는 테이블이나 별칭(Alias), 인덱스명을 지정한 경우 등
  3. 의미적으로 맞지 않게 힌트를 기술 : 예를 들어 , 서브쿼리에 unnest와 push_subq를 같이 기술한 경우(unnest되지 않은 서브쿼리만이 push_subq 힌트의 적용 대상)
  4. 논리적으로 불가능한 액세스 경로 : 조인절에 등치(=) 조건이 하나도 없는데 해시 조인으로 유도하거나,null 허용컬럼에 대한 인텍스를 이용해 전체 건수를 세려고 시도하는 등
    select /*+ index (e emp_ename_idx) / count() from emp e
  5. 버그
    1~4 에 해당하지 않는 한 옵티마이저는 기본적으로 힌트의 내용을 먼저 따르고 남은 부분만을 자신의 판단에 따라 최적화
    옵티마이저는 힌트를 선택 가능한옵션 정도로 여기는게 아니라 사용자로부터 주어진 명령어(directives)로 인식한다.

옵티마이저 사용원칙

  1. 가급적 힌트 사용을 자제하고, 옵티마이저가 스스로 좋은 선택을 할 수 있도록 돕는다.
  2. 옵티마이저가 잘못된 선택을 할 때만 힌트를 사용한다.

(5) 통계정보

통계정보는 중요하고,옵티마이저에게 미치 는 영향력이 절대적이다.
CBO의 모든 판단 기준은 통계정보에서 나온다.

(6) 옵티마이저 관련 파라미터

  • SQL, 데이터, 통계정보, 하드웨어 등 모든 환경이 동일한데도 오라클 버전을 업그레이드하면 옵티마이저가 다르게 작동
  • 옵티마이저의 그런 행동 변화는 대개 파라미터의 추가 또는 변경을 통해 이루어진다.

select id, name, sql_feature, isdefault , value, default_value
from v$sys_optimizer_env 
order by id

ID        NAME                                     SQL_FEATURE                                                      ISDEFAULT          VALUE                     DEFAULT_VALUE              
--------- ---------------------------------------- ---------------------------------------------------------------- ------------------ ------------------------- -------------------------- 
        2 parallel_execution_enabled               QKSFM_CBO                                                        YES                true                      true                      
        9 optimizer_features_enable                QKSFM_CBO                                                        YES                11.2.0.3                  11.2.0.3                  
       11 cpu_count                                QKSFM_ALL                                                        YES                16                        16                        
       12 active_instance_count                    QKSFM_ALL                                                        YES                1                         1                         
       13 parallel_threads_per_cpu                 QKSFM_CBO                                                        YES                2                         2                         
       14 hash_area_size                           QKSFM_ALL                                                        YES                131072                    131072                    
       15 bitmap_merge_area_size                   QKSFM_ALL                                                        YES                1048576                   1048576                   
       16 sort_area_size                           QKSFM_ALL                                                        YES                65536                     65536                     
       17 sort_area_retained_size                  QKSFM_ALL                                                        YES                0                         0                         
       24 pga_aggregate_target                     QKSFM_ALL                                                        YES                512000 KB                 512000 KB                 
       35 parallel_query_mode                      QKSFM_ALL                                                        YES                enabled                   enabled                   
       36 parallel_dml_mode                        QKSFM_ALL                                                        YES                disabled                  disabled                  
       37 parallel_ddl_mode                        QKSFM_ALL                                                        YES                enabled                   enabled                   
       38 optimizer_mode                           QKSFM_ALL                                                        YES                all_rows                  all_rows                  
       48 cursor_sharing                           QKSFM_CBO                                                        YES                exact                     exact                     
       50 star_transformation_enabled              QKSFM_STAR_TRANS                                                 YES                false                     false                     
       66 optimizer_index_cost_adj                 QKSFM_CBO                                                        YES                100                       100                       
       67 optimizer_index_caching                  QKSFM_CBO                                                        YES                0                         0                         
       70 query_rewrite_enabled                    QKSFM_TRANSFORMATION                                             YES                true                      true                      
       71 query_rewrite_integrity                  QKSFM_TRANSFORMATION                                             YES                enforced                  enforced                  
      101 workarea_size_policy                     QKSFM_ALL                                                        YES                auto                      auto                      
      105 optimizer_dynamic_sampling               QKSFM_CBO                                                        YES                2                         2                         
      112 statistics_level                         QKSFM_CBO                                                        YES                typical                   typical                   
      114 skip_unusable_indexes                    QKSFM_CBO                                                        YES                true                      true                      
      167 optimizer_secure_view_merging            QKSFM_ALL                                                        YES                true                      true                      
      212 result_cache_mode                        QKSFM_ALL                                                        YES                MANUAL                    MANUAL                    
      218 transaction_isolation_level              QKSFM_ALL                                                        YES                read_commited             read_commited             
      228 optimizer_use_pending_statistics         QKSFM_CBO                                                        YES                false                     false                     
      238 optimizer_capture_sql_plan_baselines     QKSFM_CBO                                                        YES                false                     false                     
      239 optimizer_use_sql_plan_baselines         QKSFM_CBO                                                        YES                true                      true                      
      245 parallel_degree_policy                   QKSFM_PQ                                                         YES                manual                    manual                    
      246 parallel_degree                          QKSFM_PQ                                                         YES                0                         0                         
      247 parallel_min_time_threshold              QKSFM_PQ                                                         YES                10                        10                        
      256 parallel_query_default_dop               QKSFM_PQ                                                         YES                0                         0                         
      257 is_recur_flags                           QKSFM_ALL                                                        YES                0                         0                         
      258 optimizer_use_invisible_indexes          QKSFM_INDEX                                                      YES                false                     false                     
      262 cell_offload_processing                  QKSFM_EXECUTION                                                  YES                true                      true                      
      264 db_file_multiblock_read_count            QKSFM_ALL                                                        YES                89                        89                        
      267 cell_offload_compaction                  QKSFM_EXECUTION                                                  YES                ADAPTIVE                  ADAPTIVE                  
      268 cell_offload_plan_display                QKSFM_EXECUTION                                                  YES                AUTO                      AUTO                      
      272 parallel_degree_limit                    QKSFM_PQ                                                         YES                65535                     65535                     
      273 parallel_force_local                     QKSFM_PQ                                                         YES                false                     false                     
      274 parallel_max_degree                      QKSFM_PQ                                                         YES                32                        32                        
      275 total_cpu_count                          QKSFM_ALL                                                        YES                16                        16                        
      286 dst_upgrade_insert_conv                  QKSFM_ALL                                                        YES                true                      true                      
      289 parallel_autodop                         QKSFM_PQ                                                         YES                0                         0                         
      290 parallel_ddldml                          QKSFM_PQ                                                         YES                0                         0                         
      317 deferred_segment_creation                QKSFM_PARTITION                                                  YES                true                      true                      
      324 total_processor_group_count              QKSFM_ALL                                                        YES                1                         1   


  • 확인 가능한 목록은 극히 일부에 지나지 않으며, 종종 변경할 때가 있는 공식 파라미터를 주로 보여주며, 공개되지 않은 무수히 많은 Hidden 파라미터 중에서는 관리자가 기본 값을 변경한 것만 보여줌
  • 옵티마이저의 행동 변화는 대개 긍정적인 방향으로 작용하지만 그렇지 못한 기능들 때문에 Hidden 파라미터가 필요
  • 오라클은 새로 구현한 기능을 곧바로 적용하지 않고 Hidden 파라미터(기본적으로 off된 상태)를 함께 제공함으로써 충분한 테스트와 검증을 거치고,문제가 없다고 판단될 때 공식적으로 발표
  • 공식화된 후라도 문제가 생기면 파라미터로 기능을 off 시킬수있다.
  • 옵티마이저의 갑작스런 변화를 원치 않는다면 optimizer_features_enable 파라미터를 이전 버전으로 설정

  alter system set optimizer_features_enable = "9.2.0 .4";

(7) DBMS 버전과 종류

  • 옵티마이저 관련 파라미터와 상관없이 버전에 따라 다른 실행계획을 수립하는 경우도 있다.
  • 적용했을 때 확실히 좋은 성능을 보장해 준다면 굳이 그 기능을 off 시킬 필요가 없으므로 파라미터도 불필요
  • 같은 형태의 SQL인데도 DBMS 종류에 따라 내부적으로 처리하는 방식에 차이

  select max(ernpno) from ernp


   "오라클 7 버전 : index_desc 힌트, rownum " 
   select /*+ index_desc(emp PK_EMP) */ empno from scott.emp where rownum = 1


   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=4)
   1    0   COUNT (STOPKEY)
   2    1     INDEX (FULL SCAN DESCENDING) OF 'SCOTT.PK_EMP' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=4)
 


 "오라클 8 버전부터는 "

 select  max(empno) from scott.emp 

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=4)
   1    0   SORT (AGGREGATE) (Card=1 Bytes=4)
   2    1     INDEX (FULL SCAN (MIN/MAX)) OF 'SCOTT.PK_EMP' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=4)


 "오라클 9i 이전 버전부터에서 통계정보 지우면"

 select  max(empno) from scott.emp 
 
   1    0   SORT  AGGREGATE 
   2    1     INDEX FULL SCAN  PK_EMP  


 "min 추가하면 index_full_scan  (위, 아래 하나씩이 아님) "

 select   min(empno),  max(empno)  from scott.emp 
 
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=4)
   1    0   SORT (AGGREGATE) (Card=1 Bytes=4)
   2    1     INDEX (FULL SCAN) OF 'SCOTT.PK_EMP' (INDEX (UNIQUE)) (Cost=1 Card=14 Bytes=56)


MS_SQL

Outer 조인

  1. 10g

# 11g

# MS_SQL