자동튜닝옵티마이저(Automatic Tuning Optimizer) :

오프라인옵티마이저로 온라인옵티마이저(runtime optimizer)의 상대적 개념이다.

통계를 분석하고, SQL Profiling하며, 액세스 경로 및 SQL 구조분석을 실시한다.

튜닝모드에서 작동하며 긴 시간이 주어진다(약 10분 정도)

SQL 단위로 프로파일을 수집하며 런타임 옵티마이저가 사용하도록 정보를 저장한다.

사용방법은 SQL Tuning Optimizer를 사용한다.

예 : select * from 사원 where 직급=‘부장’ and 연봉 >= 5000;

직급 = {부장, 과장, 대리, 사원}, 각각 25%

전체사원 = 1000명, 연봉 >= 5000 10%

=> 옵티마이저의 카디널리티 예측 = 1000*0.25*0.1

=> 상식적인 예측으로는 부장이 대부분 고액 연봉이므로 카디널리티가 훨씬 크다.

결과적으로 옵티미이저의 예측은 틀리고 조인을 여러번 거치면 실행계획이 틀릴 수있다.

SQL> create table t

2 as

3 select rownum a, rownum b from dual

4 connect by level <= 10000;

테이블이 생성되었습니다.

SQL> create index t_x01 on t(a);

SQL> create index t_x02 on t(b);

SQL> select * from t where a = 1 and b = 1;

Execution Plan

--------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     7 |     2   (0)|
|*  1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |     7 |     2   (0)|
|*  2 |   INDEX RANGE SCAN          | T_X01 |     1 |       |     1   (0)|
--------------------------------------------------------------------------

Predicate Information (identified by operation id):

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

1 - filter("B"=1)

2 - access("A"=1)

SQL> alter index t_x01 rename to t_x03;

인덱스가 변경되었습니다.

SQL> select * from t where a = 1 and b = 1;

Execution Plan

--------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     7 |     2   (0)|
|*  1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |     7 |     2   (0)|
|*  2 |   INDEX RANGE SCAN          | T_X02 |     1 |       |     1   (0)|
--------------------------------------------------------------------------

Predicate Information (identified by operation id):

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

1 - filter("A"=1)

2 - access("B"=1)

(실제 발생할 수 있는 사례)

- 테이블명_PK, 테이블명_N01, 테이블명_N02

- 고객이 평균적으로 두세달에 한번씩 주문한다면?

- 질의 문

where 고객번호=:cust_no and 주문일자=:ord_dt

- ORDER_PK와 ORDER_01 인덱스의 예상 비용은 둘다 카디널리티가 1 미만이기 떄문에 같다.

- 상식적으로 PK 인덱스를 사용해야하지만 옵티마이저는 N01 인덱스를 사용한다.

- 거의 매일 주문하는 경우 N01 인덱스는 주문일자 필터링에 많은 Random Access 일으킨다(정말?)

- 인덱스명을 운영중인 시스템에서 바꾸기 어렵기 때문에 인덱스명은 PK, X01, X02... 순이 좋다.

동적 실시간 최적화(Dynamic Runtime Optimization)

- 정적인 통계정보와 옵티마이저 모델은 동적인 상황에서 틀릴 수 있으므로 최적화 필요하다.

- 런타임때 시스템 리소스를 배분한다. 예를 들어 시스템부하에 따라 병렬쿼리 프로세스 조절, PGA 메모리자동 조절(9i), SGA 서브메모리 자동조절(10g) 등이 있다.

- 동적 실시간 최적화는 단일 SQL 최적화보다는 여러 SQL의 전체 시스템 최적화에 초점을 맞춘다.