RBO
CBO
의도적인 Optimizer 동작조정
옵티마이저(최적기)는 SQL의 실행계획1을 만들어주는 역활을 한다. 이 옵티마이저의 기술은 모든 RDBMS가 다르며 이 옵티마이저의 성능으로 Query 의 성능이 달라진다.
이런 옵티마이저에도 두가지가 있는데 RBO 와 CBO 두개의 옵티마이저 타입으로 나뉘게 된다.
RBO(Rule Based Optimizer) : 규칙 기반
CBO (Cost Based Optimizer) : 비용 기반
간략히 RBO는 규칙기반이라고 하며 여러개의 규칙을 정해 놓고 부합되는 규칙중 가장 비용이 적은 규칙을 선택하여 실행계획을 생성한다. 하지만 그 비용이라는 것이 실제 SQL을 실행하여 발생하는 비용이 아닌 규칙 자체의 비용이므로 유연하지 못하다. 그래서 CBO는 이러한 RBO의 단점을 보완하고자 통계정보를 사용하여 SQL을 실제 실행했을때 가장 비용이 적은 실행계획을 만들어 낸다. 하지만 CBO가 무조건 RBO보다 좋은것은 아니므로 참고하자.
RBO
미리 정해진 규칙에 의한 실행 계획을 결정한다. 9i까지는 RBO의 성능이 CBO보다 낫다. CBO는 통계정보를 이용하는데 9i에서는 통계정보를 자동으로 갱신하지 않기 때문이다. RBO는 일반적인 상식에 의거하지만 융통성이 없기 때문에 SQL 문장의 작성이나 연산자 우선순위, 인덱스의 생성시 각별히 주의해야 한다. 10gR2부터는 RBO를 아에 사용하지 못한다.
15가지의 규칙이 있다. 1부터 15의 순위를 가진 조건들이 해당 sql 에 부합되면 가장 낮은 순위의 규칙을 가지고 실행계획을 생성한다.
규칙 순위
Level
1 | Single Row by Rowid
2 | Single Row by Cluster Join
3 | Single Row by Hash Cluster Key with Unique or Primary Key
4 | Single Row by Unique or Primary Key
5 | Clustered Join
6 | Hash Cluster Key
7 | Indexed Cluster Key
8 | Composite Index
9 | Single-Column Indexes
10 | Bounded Range Search on Indexed Columns
11 | Unbounded Range Search on Indexed Columns
12 | Sort-Merge Join
13 | MAX or MIN of Indexed Column
14 | ORDER BY on Indexed Column
15 | Full Table Scan
단점
예를들어 RBO는 인덱스가 존재하면 무조건 사용한다. 상황에 따라서는 인덱스를 사용하지 않는것이 성능에 이득이 될 수 있지만 RBO는 그런 상황에 대처하지 못한다.
CBO
Oracle7에서 도입된 CBO는 RBO의 단점을 극복하고자 통계정보를 사용한다. 그 통계정보는 Dictionary에 들어 있는데 9i에서는 통계정보를 자동으로 갱신을 하지 않고 10g부터 통계정보를 갱신을 자동으로 수행한다. 9i에서 CBO 를 사용하려면 수동으로 통계정보를 갱신해야 한다. 즉, CBO가 발전된 형태의 방식이긴 하지만 Dictionary가 정확해야 한다.
실제 SQL을 실행할 때 통계정보를 바탕으로 현실적이고 지능적인 실행계획이 가능해진다.
특징
- 9i 까지는 수동으로 Dictionary 를 업데이트 해야 한다.
- 10gR2 부터는 자동으로 통계정보를 수집한다.
비용 계산의 요소
Cardinality (카디널리티)
- 값의 종류를 말한다. 100만건의 데이터에 '남'과 '여' 만있으면 Cardinality는 2이다.
Selectivity
- 조건에 만족되는 값으로 출력되어질 확률
- 100건의 데이터에서 남자 90건 여자 10건 일때 남자의 Selectivity는 90 여자는 10 이다.
Effective Cardinality
- Effective Cardinality = Base Cardinality * selectivety
- 인덱스는 전체 데이터의 5% 이내의 결과에 대해 조회할 때 사용하는것이 좋다. CBO는 Selectivity를 계산하여 Index 사용 여부를 판단한다. 여기서에서 5% 이내의 결과라는 말의 의미는 100건의 데이터중 90건의 데이터를 출력한다면 Index가 아닌 Full Scan 이 더 나은 방법이라는 뜻이다.
전제조건
- Dictionary 정보가 정확해야 한다.
- RBO에 비해 사용자의 의도대로 하기 힘들다.
- 업무중에 통계 수집은 절대 하면 안된다.(*)
CBO에서만 사용가능한 기능
- 테이블 및 인덱스의 파티셔닝
- 인덱스 구성 테이블 (IOT)
- Reverse key 인덱스
- optimizer 확장가능
- Function Based Index (FBI)
- 병렬 execution(실행, SQL) and 병렬 DML
- Star transformations and Star joins
- materialized views 를 이용한 Query rewrite
- Hash join
- Bitmap indexes 과 Bitmap Join 인덱스
- SAMPLE clauses in a SELECT statement (Select 문장에서 Sample 절)
의도적인 Optimizer 동작 조정
OPTIMIZER가 알아서 동작하지만 의도적으로 조정이 가능하다.
OPTIMIZER_MODE 파라미터
| key | 의미 |
| CHOOSE | - 10gR2버전에 없음 - |
| ROLE | - 10gR2버전에 없음 - 통계여부과 관계 없이 무조건 RBO 사용 |
| ALL_ROWS | - CBO 에만 적용 - 전체 처리율 최적화 |
| FIRST_ROWS[_n] | - CBO 에만 적용 - 특정 행까지 출력되는 시간을 줄이기 위한 최적화 |
인스턴스 레벨
- 초기화 파라미터를 수정하여 조정한다.(spfile 또는pfile)
세션 레벨
- alter system session 을 이용하여 조정한다.
단일 SQL 레벨
- 힌트를 이용하여 조정한다.
/*+ 파라미터 */
예>
SELECT /*+ ALL_ROWS */ COLUMN... FROM TABLE_NAME;
- 어떤 방식으로 SQL 문을 실행 할 것인지를 정하는 계획이다. [본문으로]