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

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

(1) SQL과 연산자 형태

• 결과가 같더라도 SQL을 어떤 형태로 작성했는지 또는 어떤 연산자를 사용했는지에 따라 옵티마이저가 다른 선택을 할 수 있고, 궁극적으로 쿼리 성능에 영향을 미친다.

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

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

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

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

• PK 제약과 옵티마이저

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

• FK 제약과 옵티마이저 : 4장 6절 참고
• Not Null 제약과 옵티마이저

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

• Check 제약과 옵티마이저

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

select * from emp where sal > 5000

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

(4) 옵티마이저 힌트

아래와 같은 경우가 아니면 힌트를 가장 우선적으로 따른다.

• 1. 문법적으로 맞지 않게 힌트를 기술
• 2. 잘못된 참조 사용 : 없는 테이블이나 별칭을 사용한 경우, 없는 인덱스명을 지정한 경우
• 3. 의미적으로 맞지 않게 힌트를 기술 : 예를 들어, 서브쿼리에 unnest와 push_subq를 같이 기술한 경우
• 4. 논리적으로 불가능한 액세스 경로 : 조인절이 등치(=) 조건이 하나도 없는데 해시 조인으로 유도하거나, 아래처럼 null 허용컬럼에 대한 인덱스를 이용해 전체 건수를 세려고 시도하는 등
  select /*+ index(e emp_ename_idx) / count() from emp e
• 5. 버그

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

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

(7) DBMS 버젼과 종류

문서에 대하여

• 최초작성자 : smkang
• 최초작성일 : 2010년 04월 17일
• 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
• {*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.*