실행계획에 영향을 미치는 요소

(1) SQL과 연산자 형태

• 같은 결과를 출력하는 쿼리의 경우라도
• SQL을 어떤 형태로 작성했는지 (4장 쿼리 변환)
• 어떤 연산자 (=,IN,LIKE,BETWEEN, 부등호) (1장)
  

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

• 동일한 쿼리라도
• 인덱스 , IOT, 클러스터링, 파티셔닝, MV등의
• 구성유무 및 방법에 따라 실행계획과 성능이 달라짐
  

(3) 제약설정

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

• 애플리케이션으로도 구현가능 
• DBMS의 제약조건 사용 이용해야 완벽하게 무결성을 확보할수 있음
• pk,fk,check,not null
  

PK 제약과 옵티마이저

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

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

• 서브쿼리를 Unnesting(4장 2절) 하고서 고객 테이블을 기준으로 NL조인시
• PK 제약이 없다면 고객번호 중복을 제거하는 sort unique 오퍼레이션을 먼저 수행
• 실제로 없어도 옵티마이저에게 알려주지 않으면 소용없음
• 4장 2절 ‘(6) 서브쿼리가 M쪽 집합이거나 Nonunique 인덱스일때’ 참조
• 수정가능 조인뷰 (Updatable Join View) 사용시 (2장 7절)
  

FK 제약과 옵티마이저 (4장 6절)

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

Not Null 제약과 옵티마이저

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

※ 부서별 사원수를 집계

• not null 제약 조건이 있을경우
• deptno컬럼에 인덱스가 있으면 index full scan 또는 index fast full scan으로 빠르게 처리가능
• not null제약 조건이 없을경우
• null 값이 입력될 가능성을 염두해 두고 테이블 전체를 스캔

Check 제약과 옵티마이저

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

※  emp 테이블 sal컬럼에 5000을 초과하는 값은 입력되지 않음

• 옵티마이저 역시 쿼리 최적화시 이정보를 사용
• 급여가 5000을 초과 하는 사원을 조회하면 filter조건(null is not null) 을 추가해
  불필요한 I/O가 수행되지 않게함
• 수동 파티셔닝 기능(6장 1절)
• 파티션 뷰에서 참조하는 테이블에 check 제약을 설정하면 파티션 pruning 기능이 작동
  

(4) 옵티마이저 힌트

힌트가 무시 되는 경우

1. 문법적으로 맞지 않게 힌트를 기술
2. 잘못된 참조 사용 : 없는 테이블, 별칭을 사용정한 경우, 없는 인덱스를 지정한 경우
3. 의미적으로 맞지 않게 힌트를 기술: 서브쿼리에 unnest와 push_subq를 같이 기술한경우
   (4장- unnest되지 않은 서브쿼리많이 push_subq힌트 적용대상)
4. 논리적으로 불가능한 액세스 경로
   
   • 조인절에 = 이 하나도 없는 hash 유도,
   • null 허용컬럼을 대상으로 인덱스를 이용해 count할경우 /*+ index(e emp_ename_idx) */ count(*) 등
5. 버그(ㅡㅡ;;;;;)

• 등을 제외하고 옵티마지어는 기본적으로 힌트의 내용을 먼저따름
• 옵티마이저는 사용자로부터 주어진 명령어(directives)로 인식

조인에 참여할 기초 테이블 생성

create table t1 (a number, b varchar2(100) );
create table t2 (a number, b varchar2(100) );
create table t3 (a number, b varchar2(100) );
create table t4 (a number, b varchar2(100) );
create table t5 (a number, b varchar2(100) );

테스트 결과

ordered 힌트 사용

declare
       l_cnt number;
begin
       for i in 1..10000
       loop
               execute immediate 'select /*+ ordered */  count(*)' ||
                                 'from t1,t2,t3,t4,t5 ' ||
                                 'where t1.a=' || i ||
                                 'and t2.a=' || i ||
                                 'and t3.a=' || i ||
                                 'and t4.a=' || i ||
                                 'and t5.a=' || I into l_cnt;
       end loop;
end;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      4.92      13.34          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      4.92      13.35          0          0          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS

옵티마이져 가 실행계획 선택

declare
       l_cnt number;
begin
       for i in 1..10000
       loop
               execute immediate 'select   count(*)' ||
                                 'from t1,t2,t3,t4,t5 ' ||
                                 'where t1.a=' || i ||
                                 'and t2.a=' || i ||
                                 'and t3.a=' || i ||
                                 'and t4.a=' || i ||
                                 'and t5.a=' || I into l_cnt;
       end loop;
end;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      4.91       5.95          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      4.91       5.95          0          0          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
********************************************************************************

※ ordered 힌트를 명시하면 옵티마이저는 모든 조인 순서를 고려하지 않고

※ 사용자가 지정한 순서로만 실행계획 후보군을 선정하고 비용을 계산

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

(5) 통계정보

• 4절과 5절에서 자세히 설명
• CBO의 모든 판단 정보는 통계 정보에서 나옴

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

• 동일한 환경(sql, 시스템,hw,통계정보등)에서
  
• 파라미터의 추가 변경사항에 따라 옵티마이저가 다르게 작동

• 옵티마이저 환경 파라미터 조회 (10g이상)

11.2.0.1.0

SQL> select name,value,isdefault,default_value from v$sys_optimizer_env;

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                                1                         YES 1
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                     143360 KB                 YES 143360 KB
parallel_query_mode                      enabled                   YES enabled

NAME                                     VALUE                     ISD DEFAULT_VALUE
---------------------------------------- ------------------------- --- -------------------------
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

NAME                                     VALUE                     ISD DEFAULT_VALUE
---------------------------------------- ------------------------- --- -------------------------
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

NAME                                     VALUE                     ISD DEFAULT_VALUE
---------------------------------------- ------------------------- --- -------------------------
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            60                        YES 60
cell_offload_compaction                  ADAPTIVE                  YES ADAPTIVE
parallel_degree_limit                    65535                     YES 65535
parallel_force_local                     false                     YES false
parallel_max_degree                      2                         YES 2
total_cpu_count                          1                         YES 1
cell_offload_plan_display                AUTO                      YES AUTO

NAME                                     VALUE                     ISD DEFAULT_VALUE
---------------------------------------- ------------------------- --- -------------------------
dst_upgrade_insert_conv                  true                      YES true
parallel_autodop                         0                         YES 0
parallel_ddldml                          0                         YES 0

47 rows selected.

• Hidden 파라미터
• 새로 구현한 기능을 곧바로 적용하지 않고 Hidden파라미터로 제공
• 테스트와 검정이 완료되면 공식적으로 발표
• 오라클을 업그레이드 하면서 전에 없던 문제들이 발생할 경우
  
• optimizer_feature_enable파라미터를 이전버전으로 설정
  

alter system set optimizer_features_enable =”9.2.0.4”;

(7) DBMS 버전과 종류

버젼간 차이

select max(empno) from emp

• 오라클 7
  
• Index_desc 힌트와 rownum 조건을 사용
• select /*+ index_desc(emp emp_pk */ from emp where rownum=1
  

• 오라클 8 이상
• 9i까지, 통계정보가 있을경우  옵티마이저가 알아서 처리 (min/max)
• 통계정보가 없을경우 주의
• 10g부터 동적 샘플링을 통해 바로 동작
• select max(empno) from emp

DBMS별 차이 (min, max,index가 있을경우)

select min(empno) mx, max(empno) mn from emp a

• 오라클 (현재까지)

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 |
---------------------------------------------------------------------------

• MSSQL 서버
• 인덱스 레코드를 앞에서 한건, 뒤에서 한건씩 읽는 것이 가능
• ordered forward
• ordered backward

M쪽 집합을 기준으로 1쪽 집합과 outer 조인

• 결과 건수는 M쪽 집합으로 고정
• 조인 조건 (where) 외에 어디에서 1쪽 집합을 참조하지 않는다면
  
• 1쪽 집합에는 엑스스 할필요가 없음
  
• 1쪽 조인 컬럼에 PK가 설정되었을경우)

select e.empno,e.ename,e.sal,e.hiredate
from scott.emp e,scott.dept d
where d.deptno(+) = e.deptno

10.2.0.1.0


Execution Plan
----------------------------------------------------------
Plan hash value: 438843259

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |    14 |   308 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER|         |    14 |   308 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP     |    14 |   280 |     3   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK_DEPT |     1 |     2 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  3 - access("D"."DEPTNO"(+)="E"."DEPTNO")

• 10g 옵티마이져는 PK를 통해 두 집합간 카디널리티 정보를 얻어라도 무조건 조인 액세스 수행

11.2.0.1.0

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   308 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   308 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

• 11g ( 4장 6절, Join elimination’ 또는 ‘Table Elimination’이라고 부름)에서는  dept에 액세스 하지않음
• MSSQL 동일
  

• 오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
• 작성자: 남송휘 (tofriend)
• 최초작성일: 2011년 4월 17일
• 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
• 문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^