• _OPTIMIZER_COST_MODEL 파라미터
• IO : I/O 비용 모델 ( 예상되는 I/O 요청 횟수만을 쿼리 수행 비용 )
• CPU : CPU 비용 모델( IO + 시간 개념 )
• CHOOSE : 시스템 통계가 있으면 CPU , 없으면 I/O ( 기본값 )
• CPU_COSTING : 힌트 쿼리 레벨 비용모델 가능
• NO_CPU_COSTING : 힌트 쿼리 레벨 비용모델 가능

(1) I/O 비용 모델

• 디스크 I/O CALL 횟수 (논리적 / 물리적으로 읽은 블록 개수가 아닌 I/O CALL 횟수 )

인덱스를 경유한 테이블 액세스 비용

• Single Block I/O 방식
• 블록 개수 = I/O CALL 횟수
  {CODE:SQL}

DROP TABLE T PURGE;
CREATE TABLE T AS SELECT * FROM ALL_OBJECTS;
CREATE INDEX T_OWNER_IDX ON T( OWNER ) ;

begin
dbms_stats.gather_table_stats( user, 'T', method_opt=> 'for all columns size 1' );
end;
/

ALTER SESSION SET "_OPTIMIZER_COST_MODEL" = io;

SET AUTOTRACE TRACEONLY EXP;

SQL> SELECT /*+ INDEX( T ) */ * FROM T WHERE OWNER = 'SYS';

Predicate Information (identified by operation id):

2 - access("OWNER"='SYS')

Note

• cpu costing is off (consider enabling it)
  ;

{CODE}

• 유효 인덱스 선택도 : 인덱스 Access Predicate
• 유효 테이블 선택도 : 인덱스 Access Predicate와 Filter Predicate에 의해 결정됨
• (최종) 테이블 선택도 : 테이블 Filter Predicate 까지 포함한 모든 조건절에 의해 결정됨

• 비용 = blevel + (리프 블록 수 * 유효 인덱스 선택도 ) +(클러스터링 팩터 * 유효 테이블 선택도 )

• blevel은 브랜치 레벨을 의미하며 리프 블록에 도달하기 전에 릭게 될 브랜치 블록 개수임
• 유효 인덱스 선택도 : 전체 인덱스 레코드 중에서 조건절 만족하는 레코드를 찾기 위해 스캔할 것으로 예상되는 비율, 리프 블록에는 인덱스 레코드가 정렬된 상태로 저장되므로이 비율이 곧 방문할 리프블록 비율, 인덱스 Access Predicate에 의해 결정

• 유효 테이블 선택도 : 전체 레코드 중에서 인덱스 스캔을 완료하고서 최종적으로 테이브을 방문할 것으로 예상되는 비율,클러스터링 팩터는 인덱스를 경유해 전체 로우를 액세스할때 읽힐 것으로 예상되는 테이블 블록 개수이므로 여기는 선택도를 곱함으로 조건절에 대해 읽힐 것으로 예상되는 테이블 블록 개수를 구할 수 있음. 인덱스 Access Predicate와 Filter Predicae에 의해 결정

Full Scan에 의한 테이블 엑세스 비용

: HWN아래쪽 블록을 순차적으로 읽어 들이는 과정에서 발생하는 I/OCALL 횟수로 비용을 계산한다.
FULL SCAN할 때는 한번여 여러 BLOCK을 읽어들이는 Multiblock I/O방식을 사용 하므로 총 블럭수 / db_file_multiblock_read_count = I/O CALL이 발생을 하지만 내부적 조정된 값으로 비용을 계산 하기 때문에 차이가 발생 한다.

I/O비용 모델의 비현실적인 가정

디스크 I/OCALL 횟수로 테이블 엑세스 비용을 평가 할경우

1. Single Block I/O와 Multiblock I/o 비용은 같다
2. 캐싱 효과를 전혀 고려 하지 않는다.

optimzer_index_cost_adj

: 인덱스 탐색 비용을 조정 하고자 할때 사용
설정 범위값은 1~10,000
기본값이 100이란 수치는 한 번의 I/O CALL을 통해 Single Block Read 방식으로 한 블록을 읽는 비용과
Multiblock Read 방식으로 여러 블록을 읽는 비용을 같게 평가 하라는 의미

낮게 설정 할수록 옵티마이저는 테이블 스캔보다 인덱스를 이용한 테이블 엑세스를 선호

optimzer_index_caching

:NL 조인시 INNER 테이블 쪽을 매번 디스크에 읽는가정 하지만 이는 비현실적이므로
NL조인에서 inner쪽 인덱스 블록이 캐싱돼 있을 가능성을 옵티마이저에게 알려주는 파라미터이다.
값의 범위는 0~100이며 값이 높게 설정 할 수록 옵티마이저는 인덱스를 이용한NL 조인을 선호

(2) CPU 비용 모델

• 데이터 베이스 오퍼레이션은 CPU를 사용 하므로 경우에 따라 I/O보다 큰영향을 미친다.

I/O이 소량임에도 불구하고 쿼리 수행 시간이 오래걸리는 경우

• 해시 조인할 때, 해시 체인에 달린 레코드가 많아 해시 체인을 스캔하는 부하가 심할 때
• 캐싱된 블록을 반복적으로 읽는데, 한 블록 내에서 매번 비효율적으로 많은 레코드를 스캔할 때 ( NL조인 inner쪽 인덱스 선두 컬럼이 between 조건일 때)
• 버퍼를 Pin한 상태에서 같은 블록을 반복 액세스할 때
• 다량의 레코드를 읽으면서 건건이 여러 개의 사용자 정의 함수를 반복 호출할 때
• 메모리 소트를 반복할 때

CPU 사용량이 다소 증가 하는 경우

• 조건절 개수가 아주 많을 때
• 조건절이나 select-list에 연산 집약적인 작업을 많이 포함할 때