h1.비용

  • I/O :I/O 비용모델
  • CPU :CPU 비용모델
  • CHOOSE : 시스템 통계가 있으면 CPU 비용 모델 없으면 I/O 비용 모델 (opfimizer_mode를 choose로 설정했을 때 오브젝트 통계가 있으연 CBO, 없으면 EBO로 동작하는 것과 같은 개념)
  • 기본 값은 choose
  • 9i은 관리자가 시스템통계를 생성해 줄때만 CPU 비용모델 사용
  • 10g 에서는 NoWorkload 시스템통계 도입 ( 관리자가 따로 시스템통계를 생성하지않아도 CPU 기반모델 사용)
    -쿼리 레벨로도 비용 모댈을 선택 - cpu_costing , no_cpu_costing

h3.(1) I/O 비용 모델

  • I/O 비용 모델에서의 비용은 디스크 I/O Call 횟수(논리적/물리적으로 읽은 블록 개수가 아닌 I/0 Call 횟수))

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


-- 인텍스를 경유한 테이블 액세스 시 에는 Single Block 1/0 방식 이 사용
-- 디스크에서한 블록을 읽을 때 마다 한 번의 I/O Call을 일으키는 방식이므로 읽게 될 물리적 블록 개수가 액세스 비용 = I/O Call 횟쉬과 일치한다

create table t as select * from all_objects;

Table T이(가) 생성되었습니다.

create index t_owner_idx on t(owner) ;

Index T_OWNER_IDX이(가) 생성되었습니다.

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

PL/SQL 프로시저가 성공적으로 완료되었습니다.

alter session set "_optimizer_cost_model" = io;

Session이(가) 변경되었습니다.

set autotrace traceonly explain;

Traceonly 옵션은 현재 지원되지 않습니다. 

EXPLAIN PLAN FOR 
select /* + index (t) */* from t where owner = 'SYS' ;
select * from table(dbms_xplan.display)

---------------------------------------------------------------------------                                                                                                                                                                                                                                 
| Id  | Operation                   | Name        | Rows  | Bytes | Cost  |                                                                                                                                                                                                                                 
---------------------------------------------------------------------------                                                                                                                                                                                                                                 
|   0 | SELECT STATEMENT            |             |  1292 |   113K|    37 |                                                                                                                                                                                                                                 
|   1 |  TABLE ACCESS BY INDEX ROWID| T           |  1292 |   113K|    37 |                                                                                                                                                                                                                                 
|*  2 |   INDEX RANGE SCAN          | T_OWNER_IDX |  1292 |       |     4 |                                                                                                                                                                                                                                 
---------------------------------------------------------------------------  

==========================================================================================

index scan 단계에서 cost 4 발생
table access 단계에서 37 - 4(index scan) = 34 single block 발생  
row가 1292인데 I/O call 이 34번인건 클러스트링 팩터가 비용계산식에 고려 되어있기때문




비용 = blevel +                                  -- 인텍스 수직적 탐색 비용
      (리프 블록 수 × 유효 인텍스 선택도) +     -- 인텍스 수평적 탐색 비용
      (클러스터링 팩터 × 유효 태이블 선택도)    -- 테이블 Random 액세스 비용

* blevel : 브랜치 레벨을 의미하며 
* 유효 인텍스 선택도 : 전체 인텍스 레코드 중에서 조건절을 만족하는 레코드를 찾기 위해 스캔할 것으로 예상되는 비율(%)
* 유효 테이블 선택도 : 전체 레묘드 중에서 인텍스 스캔을 완료하고서 최종적으로 테이블을 방문할 것으로 예상되는 비율(%)

select i.blevel , i.leaf_blocks, c .num_distinct, i.clustering_factor
     , 1 + (i.leaf_blocks * 1 / c.num_distinct) "인텍스 스캔 비용"
     , 1 + (i.leaf_blocks * 1 / c.num_distinct)
     + (i.clustering_factor * 1 / c.num_distinct) "총 테이블 액세스 비용"
 from user_indexes i , user_tab_col_statistics c
 where i.index_name  = 'T_OWNER_IDX'
   and c.table_name  = i.table_name
   and c.column_name = 'OWNER' ;


    BLEVEL            LEAF_BLOCKS              NUM_DISTINCT        CLUSTERING_FACTOR         인텍스 스캔 비용                    총 테이블 액세스 비용
---------- ----------------------- ------------------------ -------------------------   ---------------------- ---------------------------------------
         1                     44                      14                        452           4.1428571428571                    36.428571428571428571


h5.Full Scan에 의한 테이블 액세스 비용


-- 테이블을 Full Scan할 때는 HWM 아래쪽 블록을 순차적으로 읽어 들이는 과정에서 발생하는 I/O Call 횟수로 비용을 계산
-- Full Scan할 때는 한번의 I/O Call로써 여러 블록을 읽어 들이는 Multiblock I/O 방식을 사용
-- 총블럭수 / db_file_multiblock_read_count  = I/O call  <= 가 일치하진 않는다


select blocks from user_tables where table_name = 'T' ;

    BLOCKS
----------
       242

alter session set db_file_multiblock_read_count = 2; -- 멀티블록을 두개씩 읽어라

Session이(가) 변경되었습니다.

EXPLAIN PLAN FOR 
select /*+ full(t) */ * from t where owner = 'SYS' ;
select * from table(dbms_xplan.display);

----------------------------------------------------------                                                                                                                                                                                                                                                  
| Id  | Operation         | Name | Rows  | Bytes | Cost  |                                                                                                                                                                                                                                                  
----------------------------------------------------------                                                                                                                                                                                                                                                  
|   0 | SELECT STATEMENT  |      |  1292 |   113K|    93 |                                                                                                                                                                                                                                                  
|*  1 |  TABLE ACCESS FULL| T    |  1292 |   113K|    93 |                                                                                                                                                                                                                                                  
----------------------------------------------------------     

db_file_multiblock_read_count = 4
----------------------------------------------------------                                                                                                                                                                                                                                                  
| Id  | Operation         | Name | Rows  | Bytes | Cost  |                                                                                                                                                                                                                                                  
----------------------------------------------------------                                                                                                                                                                                                                                                  
|   0 | SELECT STATEMENT  |      |  1292 |   113K|    60 |                                                                                                                                                                                                                                                  
|*  1 |  TABLE ACCESS FULL| T    |  1292 |   113K|    60 |                                                                                                                                                                                                                                                  
----------------------------------------------------------   

db_file_multiblock_read_count = 8
----------------------------------------------------------                                                                                                                                                                                                                                                  
| Id  | Operation         | Name | Rows  | Bytes | Cost  |                                                                                                                                                                                                                                                  
----------------------------------------------------------                                                                                                                                                                                                                                                  
|   0 | SELECT STATEMENT  |      |  1292 |   113K|    38 |                                                                                                                                                                                                                                                  
|*  1 |  TABLE ACCESS FULL| T    |  1292 |   113K|    38 |                                                                                                                                                                                                                                                  
----------------------------------------------------------  

h5.I/O 비용 모델의 비현실적인 가정
디스크 I/O Call 횟수로써 테이블 액세스 비용을 평가 의미

  • Single Block I10와 Multiblock I/O 는 비용이 같다.
  • 캐싱 효과를 전혀 고려하지 않는다.

위의 가정을 보정하는 오라클 파라미터
http://wiki.gurubee.net/pages/viewpage.action?pageId=1507599

h3.(2) CPU 비용 모델

블록 I/O가 소량인데도 쿼리 수행 시간이 상당히 오래 걸리는 경우

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

CPU 비용 모댈에서의 비용계산식
Cost = ( #SRds * sreadtim +
#MRds * mreadtim +
#CPUCycles / cpuspeed
) / sreadtim

  • #SRds - Single Block I/0 요청 횟수
  • #MRds - Multiblock I/O 요청 횟수
  • #CPUCycles - 쿼리 수행에 필요한 CPU 사이클수
  • sreadtim - Single Block i/O 에 소요되는 시간(m잉
  • mreadtim - Multiblock I/O 에 소요되는 시간(ms)
  • cpuspeed- 초당 처리할 수 있는 CPU 사이클 수