앞서 옵티마이저 행동에 영향을 미치는 요소들에 대해 살펴보았는데, 강조하고자 한 핵심은 옵티마이저가 절대 완벽할 수 없다는 것이다.
옵티마이저도 결국 사람이 만든 소프트웨어 엔진에 불과하며, 모든 프로그램이 업그레이드를 통해 조금씩 개선되듯 옵티마이저도 여러 가지 제약과 한계점들을 극복하며 발전해 나가는 과정 속에 있다.
옵티마이저는 주어진 환경에서 최선을 다할 뿐 적절한 옵티마이징 팩터를 제공하는 것은 결국 사람의 몫이다.
현실적인 제약 때문에 샘플링 방식으로 통계를 수집하다 보니 실제 데이터와 불일치가 발생하기 마련이다.
부정확한 통계의 연장선으로 볼 수 있는데, 히스토그램 버킷 개수로 254개까지만 허용된다는 점도 옵티마이저에겐 중요한 제약사항이다.
조건절에 바인드 변수를 사용하면 옵티마이저가 균등분포를 가정하고 비용을 계산한다.
조건절 컬럼이 서로 상관관계에 있으면 정확한 데이터 분포와 카디널리티를 산정하기 어렵다.
select * from 사원 where 직급 = '부장' and 연봉 >= 5000;
I/O 비용 모델 하에서의 비용은 단순히 I/O Call 횟수를 의미한다. 그런데 I/O 비용 모델이 사용하는 기본 가정에 따르면 Single Block Read와 Multiblock Read의 비용은 같다.
옵티마이저는 이 둘을 구분하지 않고 동일한 하나의 시스템 Call로 간주하는 것이다.
또한 옵티마이저는 다른 세션이나 다른 쿼리문에 의해 데이터 블록들이 이미 버퍼 캐시에 캐싱돼 있을 가능성을 배제한다.
아무리 비용기반 옵티마이저라 하더라도 부분적으로는 규칙에 의존한다.
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> exec dbms_stats.gather_table_stats(user, 't');
SQL> set autotrace traceonly exp
SQL> select * from t where a = 1 and b = 1;
----------------------------------------------------------------------------
| 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)|
----------------------------------------------------------------------------
-> t_x01, t_x02 둘 중 어느 것을 선택하든 쿼리 수행 비용은 같다.
옵티마이저가 t_x01 인덱스를 선택한 판단 근거는, 인덱스명의 알파벳 순서이다.
t_x01 인덱스명을 t_x03으로 바꾼 후 확인하면, t_x02 인덱스가 사용된다.
SQL> alter index t_x01 rename to t_x03;
SQL> select * from t where a = 1 and b = 1;
----------------------------------------------------------------------------
| 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)|
----------------------------------------------------------------------------