2. 옵티마이저 행동에 영향을 미치는 요소 (by wooa1201) [2017.04.21]
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) 옵티마이저 힌트
힌트가 적용되지 않는 경우
- 문법적으로 맞지 않게 힌트를 기술
- 잘못된 참조 사용 : 없는 테이블이나 별칭(Alias), 인덱스명을 지정한 경우 등
- 의미적으로 맞지 않게 힌트를 기술 : 예를 들어 ， 서브쿼리에 unnest와 push_subq를 같이 기술한 경우(unnest되지 않은 서브쿼리만이 push_subq 힌트의 적용 대상)
- 논리적으로 불가능한 액세스 경로 : 조인절에 등치(=) 조건이 하나도 없는데 해시 조인으로 유도하거나，null 허용컬럼에 대한 인텍스를 이용해 전체 건수를 세려고 시도하는 등
select /*+ index (e emp_ename_idx) / count() from emp e - 버그
1~4 에 해당하지 않는 한 옵티마이저는 기본적으로 힌트의 내용을 먼저 따르고 남은 부분만을 자신의 판단에 따라 최적화
옵티마이저는 힌트를 선택 가능한옵션 정도로 여기는게 아니라 사용자로부터 주어진 명령어(directives)로 인식한다.
옵티마이저 사용원칙
- 가급적 힌트 사용을 자제하고， 옵티마이저가 스스로 좋은 선택을 할 수 있도록 돕는다.
- 옵티마이저가 잘못된 선택을 할 때만 힌트를 사용한다.
(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 조인
- 10g
 # 11g |  # MS_SQL |  |