■ 옵티마이저 행동에 영향을 미치는 요소는 다음과 같다.

(1) SQL과 연산자의 형태

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

(3) 제약 설정: PK, FK, Not Null, Check

(4) 옵티마이저 힌트

(5) 통계정보: 오브젝트 통계, 시스템 통계

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

(7) DBMS 버전과 종류

 

(1) SQL과 연산자 형태

- 결과가 같더라도 SQL 작성 형태(4장 쿼리 변환에서 학습), 연산자(=, IN, LIKE, BETWEEN, 부등호 등)에 따라 다르다(1장 참고).

 

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

- 인덱스, IOT, 클러스터링, 파티셔닝, MV 에 따라 달라진다.

 

(3) 제약 설정

- 무결성 원칙 정리

무결성 종류

설명

SQL 명령문

비고

개체무결성

entity integrity

모든 테이블은 primary key가 있고 primary key 값은 NULL이 아니며 unique 해야한다.

PRIMARY KEY

NOT NULL

3가지

기본

제약

도메인 무결성

domain integrity

도메인의 값은 해당 속성의 타입과 값 제약을 준수해야한다.

CHECK

참조 무결성

referential integrity

다른 릴레이션을 참조하는 속성의 값은 해당 릴레리션에 존재하는 값을 참조해야한다.

FOREIGN KEY

사용자 정의 무결성

(business rule)

 

 

 

1. PK 제약과 옵티마이저

예) 1년이 넘지않은 고객의 지난 1달간의 주문실적

select sum(주문수량), sum(주문금액), count(*), count(distinct 고객번호)

from 주문

where 고객번호 in

                     (select 고객번호 from 고객 where 가입일자 >= trunc(add_month(sysdate, -12)))

              and 주문일자 >= trunc(add_month(sysdate, -1))

 

-> subquery unnesting(4장 2절) 후, NL 조인한다.

-> 만약 고객테이블에 PK 제약이 없다면 sort unique 연산을 먼저 수행해야한다.

     PK 제약이 없으면 4장2절 서브쿼리 unnesting의 (6)번의 서브쿼리가 M쪽 집합이거나

    nonunique 인덱스 일 경우가 된다.

-> 다른 예로 수정가능 조인뷰(updatable join view)의 경우 1쪽 테이블에 PK 제약이 있어야 한다.

    (2장 7절 참조)

 

2. FK 제약과 옵티마이저

- 4장 6절 - 조인제거(join elimination) 기능 참조

- 6장 1절 6항 - Reference 파티셔닝(11g)은 FK 제약이 있을 때만 작동

 

3. NOT NULL 제약과 옵티마이저

예)

select deptno, count(*) from emp

group by deptno 

 

-> deptno 칼럼에 인덱스가 있는 경우 알고리즘

    if  deptno 속성에 NOT NULL 제약이 있을 경우

    then   index full scan, index fast full scan 사용

    else   table full scan(null 값의 가능성 때문에)

4. Check 제약과 옵티마이저

예)

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

 

-> 옵티마이저는 check 제약을 이용한다.

-> 아래 질의의 경우 filter(null is not null)을 추가해 불필요한 I/O를 줄인다.

    select * from emp where sal > 5000

   Rows Row Source Operation

   ---- ----------------------------------

   0 FILTER(cr=0 pr=0 time=9 us)

   0 TABLE ACCESS FULL EMP ( cr=0 pr=0 pw=0 time=0 us)

- 6장 1절 - 수동 파티셔닝 - 파티션 뷰에서 참조하는 테이블에 CHECK 제약을 설정하면 파티션 prunning이 작동한다.

 

(4) 옵티마이저 힌트

- 옵티마이저는 힌트를 따르지만 다음의 경우는 그렇지 않다.

1. 문법적으로 맞지 않는 힌트

2. 잘못된 참조 - 없는 테이블 이나 alias 사용, 없는 인덱스 명

3. 의미적으로 맞지 않게 힌트를 기술 - 예를 들면 unnest와 push_subq를 같이 사용한 경우 등

4. 논리적으로 불가능한 액세스 경로 - 예를 들면 조인절에 = 가 없는데 해시조인 힌트 사용,

    NULL 허용 컬럼에 대한 인덱스 사용으로 count(*) 계산할 경우

5. 버그

(힌트 사용 예) - ordered 힌트를 사용하여 옵티마이저가 최적화를 빠르게 완료했다.

 

SQL> create table t1 ( a number, b varchar2(100) );

SQL> create table t2 ( a number, b varchar2(100) );

SQL> create table t3 ( a number, b varchar2(100) );

SQL> create table t4 ( a number, b varchar2(100) );

SQL> create table t5 ( a number, b varchar2(100) );

SQL> alter system flush shared_pool;

시스템이 변경되었습니다.

SQL>

SQL> declare

          2 l_cnt number;

          3 begin

          4 for i in 1..10000

          5 loop

          6 execute immediate ' select /*+ ordered */ count(*)' ||

          7 ' from t1, t2, t3, t4, t5 ' ||

          8 ' where t1.a = ' || i ||

          9 ' and t2.a = ' || i ||

         10 ' and t3.a = ' || i ||

         11 ' and t4.a = ' || i ||

         12 ' and t5.a = ' || I into l_cnt;

         13 end loop;

         14 end;

         15 /

PL/SQL 처리가 정상적으로 완료되었습니다.

경 과: 00:00:15.31

SQL>

SQL> alter system flush shared_pool;

시스템이 변경되었습니다.

SQL>

SQL> declare

          2 l_cnt number;

          3 begin

          4 for i in 1..10000

          5 loop

         6 execute immediate ' select count(*)' ||

         7 ' from t1, t2, t3, t4, t5 ' ||

         8 ' where t1.a = ' || i ||

         9 ' and t2.a = ' || i ||

         10 ' and t3.a = ' || i ||

         11 ' and t4.a = ' || i ||

         12 ' and t5.a = ' || I into l_cnt;

         13 end loop;

         14 end;

         15 /

PL/SQL 처리가 정상적으로 완료되었습니다.

경 과: 00:00:33.81

- 옵티마이저 사용에 관한 일반 원칙

1. 가급적 힌트 사용을 자제하고 옵티마이저가 스스로 선택하도록 한다.

2. 옵티마이저가 잘못된 선택을 할 때만 힌트를 사용한다.

 

(5) 통계 정보

- CBO의 모든 판단 기준은 통계정보를 사용한다.

 

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

- 옵티마이저의 파라메터 값은 오라클 버전을 오리면 바뀔 수 있다.

- 옵티마이저 파라메터 목록을 보는 명령어

select name, value, isdefault, default_value

from v$sys_optimizer_env;

 

NAME                           VALUE                     ISD DEFAULT_VA
------------------------------ ------------------------- --- ----------
parallel_execution_enabled     true                      YES true
optimizer_features_enable      10.2.0.1                  YES 10.2.0.1
cpu_count                      2                         YES 2
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           198656 KB                 YES 198656 KB
parallel_query_mode            enabled                   YES enabled

NAME                           VALUE                     ISD DEFAULT_VA
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_VA
statistics_level               typical                   YES typical
skip_unusable_indexes          true                      YES true
optimizer_secure_view_merging  true                      YES true

- 그 외 공개되지 않은 HIDDEN 파라메터가 많다.

   HIDDEN 파라메터는 기본적으로 off 상태이나 테스트와 검증을 거쳐 문제가 없으면 공식적으로 사용하는 과정을 거친다.

- 오라클 버전을 업그레이드하면 변화가 생기는데 원하지 않으면 optimizer_features_enable 파라메터를 이전 버전으로 설정한다.

   alter system set optimizer_features_enable = "9.2.0.4";

 

(7) DBMS 버전과 종류

- 같은 SQL 문도 DBMS 종류에 따라 다르다(오라클 , SQL Server, Sybase, DB2)

(SQL 예 - 오라클 7,8,9,10 버전)

- select max(empno) from emp;

 

- 오라클 7 : 최적화를 위하여 index_desc 힌트와 rownum 조건을 사용

   select /*+ index_desc(emp emp_pk */ from emp where rownum=1

 

- 오라클 8i, 9i :

    if 통계정보 있으면

    then 옵티마이저가 알아서 처리해준다(INDEX FULL SCAN (MIN/MAX) EMP_PK)

    else (INDEX FULL SCAN EMP_PK)

 

- 오라클 10g : 동적 샘플링을 통해 바로 동작

    select max(empno) from emp

 

(SQL 예 1 - 오라클 11g와 MS-SQL Server)

- select min(empno), max(empno) from emp;

 

- 오라클 11g :

   INDEX FULL SCAN EMP_PK

 

- MS-SQL Server :

   index를 앞에서 한번 뒤에서 한번 읽는다.

 

- MS-SQL이 효율적인 방법을 사용한다!!

 

(SQL 예 2 - 오라클 11g와 MS-SQL Server)

- M쪽 집합 PK가 설정된 1쪽과 Outer 조인을 하면 결과건수는 M쪽 집합의 개수이다.

  조인조건외 1쪽 집합을 참조하지 않으면 1쪽 집합과 조인 액세스는 필요없다.

  (join elimination 혹은 table elimination 이라고 하며 4장 6절에서 설명)

 

- 오라클 10g : 무조건 1쪽 집합과 조인을 한다.

   select e.empno, e.ename, e.sal, e.hiredate

   from emp e, dept d

   where d.deptno(+)=e.deptno

 

- MS-SQL Server : M쪽 outer 테이블만 액세스

   select e.empno, e.ename, e.sal, e.hiredate

   from emp e left outer join dept d

   on d.deptno = e.deptno

 

- 오라클 11g : emp만 액세스

   select e.empno, e.ename, e.sal, e.hiredate

   from emp e left, dept d

   where d.deptno(+)=e.deptno

 

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