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

h4.옵티마이저 행동의 차이는 궁극적으로 실행계획의 차이

실행계획에 영향을 미치는 요소 (1) SQL과 연산자 형태 (2) 인덱스,IOT,클러스터링,파티셔닝,MV 등 옵티마이징 팩터 (3) 제약 설정 : PK, FK, Not Null, Check (4) 옵티마이저 힌트 (5) 통계정보 : 오브젝트 통계， 시스템 통계 (6) 옵티마이저 관련 파라미터 (7) DBMS 버전과 종류

(1) SQL과 연산자 형태
SQL을 어떤 형태로 작성했는지 또는 어떤 연산자( =，in, like, between, 부등호 등)를 사용했는지에 따라 옴티마이저가 다른 선택을 할 수 있다

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

(3) 제약 설정
데이터 무결성을 보장해 줄뿐만 아니라 옵티마이저가 쿼리 성능을 최적화 하는 데에 매우 중요한 정보를 제공한다.
PK, FK, Check, Not Null 같은 제약(constraint) 설정 기능을 이용해야 완벽한 데이터 무결성을 확보.

(4) 옵티마이저 힌트
힌트는 옵티마이저에게 매우 강한 영향력을 끼친다.

1. 문법적으로 맞지 않게 힌트를 기술 2. 잘못된 참조사용: 없는테이블이나(alias)을사용한 경우， 없는 인텍스명을지정한경우등 3. 의미적으로 맞지 않게 힌트를 기술 : 예를 들어， 서브쿼리에 unnest와 push_subq를 같이 기술한 경우(unnest되지 않은 서브쿼리만이 push_subq 힌트의 적용 대상이며 ， 4장에서 자세히 설명함) 4. 논리적으로 불가능한 액세스 경로 : 조인절에 등치(=) 조건이 하나도 없는데 해시 조인으로 유 도하거나， 아래처럼 null 허용컬럼에 대한 인텍스를 이용해 전체 건수를 하려고 시도히는 등 se1ect /*+ index(e emp ename idx) / count() from emp e

• 옵티마이저 힌트에 관한 일반적인 사용 원칙
  1. 가급적 힌트 사용을 자제하고 옵티마이저가 스스로 좋은 선택
  2. 옵티마이저가 잘못된 선택을 할 때만 힌트를 사용

(5) 통계정보
옵티마이저에게 미치는 영향력이 절대적

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

11g {code:sql} SQL> select name, value, isdefault, default_value 2 from v$sys_optimizer_env 3 ;

NAME VALUE ISD DEFAULT_VALUE

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

- ---

---

---

---

---

parallel_execution_enabled true YES true
optimizer_features_enable 11.2.0.1 YES 11.2.0.1
cpu_count 4 YES 4
active_instance_count 1 YES 1
parallel_threads_per_cpu 2 YES 2
hash_area_size 131072 YES 131072
bitmap_merge_area_size 1048576 YES 1048576
sort_area_size 65536 YES 65536
sort_area_retained_size 0 YES 0
pga_aggregate_target 671744 KB YES 671744 KB
parallel_query_mode enabled YES enabled
parallel_dml_mode disabled YES disabled
parallel_ddl_mode enabled YES enabled
optimizer_mode all_rows YES all_rows
cursor_sharing exact YES exact
star_transformation_enabled false YES false
optimizer_index_cost_adj 100 YES 100
optimizer_index_caching 0 YES 0
query_rewrite_enabled true YES true
query_rewrite_integrity enforced YES enforced
workarea_size_policy auto YES auto
optimizer_dynamic_sampling 2 YES 2
statistics_level typical YES typical
skip_unusable_indexes true YES true
optimizer_secure_view_merging true YES true
result_cache_mode MANUAL YES MANUAL
transaction_isolation_level read_commited YES read_commited
optimizer_use_pending_statistics false YES false
optimizer_capture_sql_plan_baselines false YES false
optimizer_use_sql_plan_baselines true YES true
parallel_degree_policy manual YES manual
parallel_degree 0 YES 0
parallel_min_time_threshold 10 YES 10
parallel_query_default_dop 0 YES 0
is_recur_flags 0 YES 0
optimizer_use_invisible_indexes false YES false
cell_offload_processing true YES true
db_file_multiblock_read_count 128 YES 128
cell_offload_compaction ADAPTIVE YES ADAPTIVE
parallel_degree_limit 65535 YES 65535
parallel_force_local false YES false
parallel_max_degree 8 YES 8
total_cpu_count 4 YES 4
cell_offload_plan_display AUTO YES AUTO
dst_upgrade_insert_conv true YES true
parallel_autodop 0 YES 0
parallel_ddldml 0 YES 0

47 개의 행이 선택되었습니다.

||

optimizer_features_enable 파라미터를 이전 버전으로 설정하면 된다.
alter system set optimizer_features_enable = "9.2.0.4";

!제목 없음.jpg!

!버전별 기능.jpg!


(7) DBMS 버전과 종류

||예)MIN/MAX SCAN예
{code:sql}
SQL> select max(empno) from scott.emp;

MAX(EMPNO)
----------
      7934


Execution Plan
----------------------------------------------------------
Plan hash value: 1707959928

-------------------------------------------------------------------------------------
| Id  | Operation                  | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |        |     1 |     4 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |        |     1 |     4 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| PK_EMP |     1 |     4 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
        351  recursive calls
          0  db block gets
         63  consistent gets
         14  physical reads
          0  redo size
        542  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed


SQL> select min(empno) mx， max(empno) mn from scott.emp a;

        MX         MN
---------- ----------
      7369       7934


Execution Plan
----------------------------------------------------------
Plan hash value: 2937609675

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |     1 |     4 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |        |     1 |     4 |            |          |
|   2 |   INDEX FULL SCAN| PK_EMP |    14 |    56 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        602  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

||