꿈꾸는 개발자, DBA 커뮤니티 구루비

03. 옵티마이저의 한계

앞서 옵티마이저 행동에 영향을 미치는 요소들에 대해 살펴보았는데, 강조하고자 한 핵심은 옵티마이저가 절대 완벽할 수 없다는 것이다.
옵티마이저도 결국 사람이 만든 소프트웨어 엔진에 불과하며, 모든 프로그램이 업그레이드를 통해 조금씩 개선되듯 옵티마이저도 여러 가지 제약과 한계점들을 극복하며 발전해 나가는 과정 속에 있다.

(1) 부족한 옵티마이징 팩터

옵티마이저는 주어진 환경에서 최선을 다할 뿐 적절한 옵티마이징 팩터를 제공하는 것은 결국 사람의 몫이다.

(2) 부정확한 통계

현실적인 제약 때문에 샘플링 방식으로 통계를 수집하다 보니 실제 데이터와 불일치가 발생하기 마련이다.

(3) 히스토그램의 한계

부정확한 통계의 연장선으로 볼 수 있는데, 히스토그램 버킷 개수로 254개까지만 허용된다는 점도 옵티마이저에겐 중요한 제약사항이다.

(4) 바인드 변수 사용시 균등분포 가정

조건절에 바인드 변수를 사용하면 옵티마이저가 균등분포를 가정하고 비용을 계산한다.

(5) 결합 선택도 산정의 어려움

조건절 컬럼이 서로 상관관계에 있으면 정확한 데이터 분포와 카디널리티를 산정하기 어렵다.


select * from 사원 where 직급 = '부장' and 연봉 >= 5000;

(6) 비현실적인 가정

I/O 비용 모델 하에서의 비용은 단순히 I/O Call 횟수를 의미한다. 그런데 I/O 비용 모델이 사용하는 기본 가정에 따르면 Single Block Read와 Multiblock Read의 비용은 같다.
옵티마이저는 이 둘을 구분하지 않고 동일한 하나의 시스템 Call로 간주하는 것이다.
또한 옵티마이저는 다른 세션이나 다른 쿼리문에 의해 데이터 블록들이 이미 버퍼 캐시에 캐싱돼 있을 가능성을 배제한다.

(7) 규칙에 의존하는 CBO

아무리 비용기반 옵티마이저라 하더라도 부분적으로는 규칙에 의존한다.

예를 들어, 원격 테이블이나 External 테이블에 대해서는 카디널리티, 평균 행 길이, 블록 수, 그리고 각종 인덱스 관련 통계항목들에 대해 고정된 상수 값을 사용
옵티마이저 모드를 first_rows로 설정했을 때 order by 소트를 대체할 인덱스가 있으면 부조건 인덱스를 사용
9i까지 옵티마이저는 뷰 또는 서브쿼리를 만나면 무조건 쿼리 블록을 풀어 메인 쿼리와 Merging하려고 시도
알파벳순 인덱스 선택 규칙 : 두 대안 인덱스의 예상 비용이 같을 때 알파벳 순에서 앞선 것을 선택 (인덱스 명명 규칙의 중요성 시사)


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)| 
----------------------------------------------------------------------------

(8) 하드웨어 성능 특성

문서에 대하여

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