옵티마이저 행동의 차이는 궁극적으로 실행계획의 차이를 말하며, 실행계획에 영향을 미치는 요소로는 아래와 같은 것들이 있다.
(1) SQL과 연산자 형태
(2) 인덱스, IOT, 클러스터링, 파티셔닝, MV등 옵티마이징 팩터
(3) 제약 설정 : PK, FK, Not Null, Check
(4) 옵티마이저 힌트
(5) 통계정보 : 오브젝트 통계, 시스템 통계
(6) 옵티마이저 관련 파라미터
(7) DBMS 버젼과 종류
결과가 같더라도 SQL을 어떤 형태로 작성했는지 또는 어떤 연산자를 사용했는지에 따라 옵티마이저가 다른 선택을 할 수 있고, 궁극적으로 쿼리 성능에 영향을 미친다.
쿼리를 똑같이 작성하더라도 인덱스, IOT, 클러스터링, 파티셔닝, MV 등을 구성했는지 그리고 어떤 식으로 구성했는지에 따라 실행계획과 성능이 크게 달라진다.
데이터베이스가 논리적으로 의미 있는 자료만을 포함하도록 하는 데이터 무결성 규칙
이들 규칙을 애플리케이션으로 구현할 수도 있지만 DBMS가 제공하는 PK, FK, Not Null, Check 같은 제약(constraint)설정 기능을 이용해야 완벽한 데이터 무결성을 확보할 수 있다.
제약 설정은 데이터 무결성을 보장해 줄뿐만 아니라 옵티마이저가 쿼리 성능을 최적화하는 데에 매우 중요한 정보를 제공한다.
select sum(주문수량), sum(주문금액), count(*), count(distinct 고객번호)
from 주문
where 고객번호 in (select 고객번호
from 고객
where 가입일자 >= trunc(add_months(sysdate, -12)))
and 주문일자 >= trunc(add_months(sysdate, -1))
서브쿼리 Unnesting(4장 2절 또는 코어 오라클 데이터베이스 스터디 자료(서브쿼리 Unnesting) 참조) 하고서 고객 테이블을 기준으로 NL 조인하려 할 때, 만약 고객 테이블에 PK 제약이 없다면 고객번호 중복을 제거하는 sort unique 오퍼레이션을 먼저 수행해야 한다.
실제 고객번호에 중복 값이 없더라도 옵티마이저에게 그런 사실을 알려주지 않으면 소용이 없다.
SYS@ora10g>select deptno, count(*) from scott.emp group by deptno ;
위 쿼리는 부서(deptno)별 사원 수를 집계하는 쿼리다.
옵티마이저가 이 쿼리를 최적화할 때 deptno 컬럼에 인덱스가 있으면 index full scan 또는 index fast full scan으로 바르게 처리할 수 있다.
하지만 deptno 컬럼에 not null 제약이 있을 때나 가능한 예기다.
not null 제약을 설정하지 않으면 옵티마이저는 null 값이 입력될 가능성을 염두에 두고 실행계획을 수립해야 하므로 테이블 전체를 스캔한다.
-- scott.emp 테이블 sal 컬럼에 아래와 같은 제약을 설정하면 5,000을 초과하는 값은 입력되지 않는다.
SYS@ora10g>alter table scott.emp modify sal check (sal <= 5000);
-- 옵티마이저도 쿼리를 최적화할 때 이 Check 제약 정보를 이용한다.
-- 아래와 같이 급여(sal)가 5,000을 초과하는 사원을 조회할 때면 filter 조건(null is not null)을 추가해 불필요한 I/O가 수행되지 않도록 한다.
SYS@ora10g>select * from scott.emp ;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 555 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 15 | 555 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
SYS@ora10g>select * from scott.emp where sal > 5000 ;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 0 (0)| |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| EMP | 1 | 37 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
2 - filter("SAL">5000)
아래와 같은 경우가 아니면 힌트를 가장 우선적으로 따른다.
1. 문법적으로 맞지 않게 힌트를 기술
2. 잘못된 참조 사용 : 없는 테이블이나 별칭을 사용한 경우, 없는 인덱스명을 지정한 경우
3. 의미적으로 맞지 않게 힌트를 기술 : 예를 들어, 서브쿼리에 unnest와 push_subq를 같이 기술한 경우
4. 논리적으로 불가능한 액세스 경로 : 조인절이 등치(=) 조건이 하나도 없는데 해시 조인으로 유도하거나, 아래처럼 null 허용컬럼에 대한 인덱스를 이용해 전체 건수를 세려고 시도하는 등
select /*+ index(e emp_ename_idx) */ count(*) from emp e
5. 버그
통계정보에 대해서는 4절과 8절에서 자세히 설명한다.
어찌 보면 3장 전체가 통계정보의 중요성을 다룬다 해도 과언이 아니다. 그만큼 통계정보는 중요하고, 옵티마이저에게 미치는 영향력이 절대적이다.
뒤에서 카디널리티와 비용 계산 원리에 대해 설명할 때 느끼겠지만 CBO의 모든 판단 기준은 통계정보에서 나온다.
통계정보가 없으면 작동하지 않는 기능들도 많다.
이에 대한 정확한 이해없이는 데이터베이스 성능 문제를 다루는 것 자체가 불가능할 정도로 최신 옵티마이저는 통계정보를 중심으로 움직인다.
SQL, 데이터, 통계정보, 하드웨어 등 모든 환경이 동일한데도 오라클 버전을 업그레이드하면 옵티마이저가 다르게 작동하는 경험을 누구나 한다.
옵티마이저의 그런 행동 변화는 대개 파라미터의 추가 또는 변경을 통해 이루어진다.
옵티마이저 모드 외에도 옵티마이저 행동에 영향을 미치는 파라미터가 많다.
10g부터는 아래 쿼리를 통해 그 목록을 쉽게 얻을 수 있다.
SYS@ora10g>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 10.2.0.1 YES 10.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 16384 KB YES 16384 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
25 rows selected.
옵티마이저의 행동 변화는 대개 긍정적인 방향으로 작용하지만 그렇지 못한 기능들 때문에 Hidden 파라미터가 필요하다.
오라클은 새로 구현한 기능을 곧바로 적용하지 않고 Hidden 파라미터(기본적으로 off된 상태)를 함께 제공함으로써 충분한 테스트와 검증을 거치고, 문제가 없다고 판단될 때 공식적으로 발표하는 순서를 밟는다.
공식화된 후라도 문제가 생기면 파라미터로 기능을 off 시킬 수 있다.
충분한 테스트를 거치더라도 오라클을 업그레이드하면 전에 없던 문제점들이 발생하기 마련인데,
만약 옵티마이저의 갑작스런 변화를 원치 않는다면 optimizer_features_enable 파라미터를 이전 버전으로 설정하면 된다.
alter system set optimizer_features_enable = "9.2.0.4" ;
옵티마이저 관련 파라미터와 상관없이 버전에 따라 다른 실행계획을 수립하는 경우도 있다.
적용했을 때 확실히 좋은 성능을 보장해 준다면 굳이 그 기능을 off 시킬 필요가 없으므로 파라미터도 불필요하다.
DBMS 버전에 따라 실행계획이 변하는 경우부터 살펴보자.
select max(empno) from emp ;
오라클7 버전까지는 위와 같은 쿼리를 최적화하려고 아래와 같이 index_desc 힌트와 rownum 조건을 사용하곤 했다.
select /*+ index_desc(emp emp_pk) */ empno from emp where rownum = 1 ;
Row Row Source Operation
----- ---------------------------------------
1 COUNT STOPKEY (cr=1 pr=0 pw=0 time=61 us)
1 INDEX FULL SCAN EMP_PK (cr=1 pr=0 pw=0 time 36 us)
하지만 오라클8 버전부터는 그럴 필요가 없다.
아래와 같이 옵티마이저가 알아서 그런 처리를 대신해 주기 때문이다.
다만, 해당 오브젝트에 대한 통계정보가 수집돼 있어야 한다.
10g부터는 기본 동적 샘플링 레벨이 2이기 때문에 통계정보가 없더라도 아래와 같은 방식으로 잘 작동한다.
SYS@ora10g>select max(empno) from scott.emp ;
-----------------------------------------------------------------------------------------
| 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_IDX | 15 | 60 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
9i 이전 버전에서 통계정보를 지우고 수행해 보면 아래와 같은 결과가 나타나므로 주의해야 한다.
select max(empno) from emp ;
Rows Row Source Operation
----- -----------------------------------
1 SORT AGGREGATE
14 INDEX FULL SCAN EMP_PK