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

I/O 비용 모델
(2) CPU 비용 모델

I/O 횟수: I/O(CALL) 호출 횟수
CPU : I/O (CALL) 횟수 + 시간
CHOOSE : 시스템 통계 있다면 CPU 모델, 없으면 I/O비용모델

기본값은 CHOOSE : ORACLE 9i 에서는 통계를 가지고 있지 않으므로 통계를 생성하면 CPU모델방식이 되며 ORACLE 10g에서는 CPU모델이 선택 되도록 NoWorkload 시스템 통계를 추가 도입
NoWorkload : 오라클의 내부적인 설정을 기준으로 한 통계 정보 생성

힌트를 이용 하여 쿼리 레벨로도 비용 모델을 선택 할 수 있다.
- cpu_costing
- no_cpu_costing

I/O 비용 모델

:디스크 I/O CALL 횟수(논리 물리가 아닌 입출력 CALL횟수)를 나타낸다.

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

:Single Block I/O방식을 사용(디스크에서 한 블록을 읽을때마다 한 번의 I/O을 일으키는 방식)하므로 물리적 블록 개수와 일치한다.


SQL> Create table t as select * from all_objects;

테이블이 생성되었습니다.

SQL> create index t_owner_idx on t(owner);
인덱스가 생성되었습니다.

SQL> begin
       dbms_stats.gather_table_stats(user, 't', method_opt=>'for all columns size 1');
    end;
    /
PL/SQL 처리가 정상적으로 완료되었습니다.


SQL> alter session set "_optimizer_cost_model"=io;
세션이 변경되었습니다.

SQL> set autotrace traceonly exp;

SQL> select /*+ index(t) */ * from t where owner='SYS';


Execution Plan
----------------------------------------------------------
Plan hash value: 910642575

---------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |  2368 |   215K|    69 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T           |  2368 |   215K|    69 |
|*  2 |   INDEX RANGE SCAN          | T_OWNER_IDX |  2368 |       |     6 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

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

Note
-----
   - cpu costing is off (consider enabling it)

분석

인덱스 엑세스는 6개 발생
테이블 엑스스 + 인덱스 엑세스 = 69개
테이블 ACCESS : 69 - 6 = 63개가 발생
63개의 Single Block I/O가 발생 함.
인덱스 단계를 살펴 보면 총 ROW가 2368개가 발생 이 되었으나 I/O 63 뿐인 이유는 클레스터링 팩터가 비용 계산식에 고려되었기 때문이다.


비용 = blevel  +   --수직 탐색 비용 
     (리프 블록의 수 X 유효 인덱스 선택도) +  --수평탐색 비용
     (클러스터링 팩터 X 유효 테이블 선택도) + --테이블 Random Access 발생 비용
    :데이터의 군집성

blevel : 리프 블록에 도달 하기 전의 읽게될 블랜치 블록의 개수
유효 인덱스 선택도: 조건절에 만족하는 레코드를 찾기 위한 예상 스캔 비율 리프 블록은 인덱스 레코드 정렬된 상태로 저장 되므로 다시 말해 방문 비율을 의미
유효 테이블 선택도: 최종적으로 테이블을 방문할 것으로 예상되는 비율

유효 인덱스 선택도를 Predicate로 연결 해보자
유효 인덱스 선택도: Access Predicate에 의해 결정
유효 테이블 선택도: Access Predicate 와 Filter Preidcate에 의해 결정
최종 선택도 : 테이블 Filter predicate까지 포함한 모든 조건절

Access Predicate 제외

lower(owner) = 'sys': 좌변 컬럼 가공
OBJECT_NAME like '%_CD%':양쪽의 %를 사용
OBJECT_NAME BETWEEN A AND Z:같은 컬럼에 조건절이 두개 이상

Access Predicate:인덱스 스캔 범위를 결정 하는데 영향을 미치는 조건절)
Filter Preidcate: 테이블의 엑세스 여부를 결정 짓는 조건절


SELECT i.blevel		AS BL
      ,i.leaf_blocks	AS LF_BLKS
      ,c.num_distinct	AS NUM_DISNT --선택도
      ,i.clustering_factor AS CLUS_FACTR
      ,1 + (i.leaf_blocks * 1/c.num_distinct) AS "IDX_SCAN_COST"
      ,1 + (i.leaf_blocks * 1/c.num_distinct) 
       + (i.clustering_factor * 1/c.num_distinct) AS "TOT_TAB_ACC_COST"
FROM USER_INDEXES i, USER_TAB_COL_STATISTICS c
WHERE i.index_name = 'T_OWNER_IDX'
AND   i.table_name = c.table_name
AND   c.column_name= 'OWNER';
      

        BL    LF_BLKS  NUM_DISNT CLUS_FACTR IDX_SCAN_COST TOT_TAB_ACC_COST
---------- ---------- ---------- ---------- ------------- ----------------
         1        115         21       1322    6.47619048       69.4285714

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

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



--EX> T 테이블의 총블럭이 703
SQL> SELECT BLOCKS FROM USER_TABLES WHERE TABLE_NAME = 'T';

    BLOCKS
----------
       703

--블럭 계수 조절
SQL> ALTER SESSION SET db_file_multiblock_read_count = 10;
세션이 변경되었습니다.


SQL> select /*+ full(t) */ *  from t where owner = 'SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2368 |   215K|    94 |
|*  1 |  TABLE ACCESS FULL| T    |  2368 |   215K|    94 |
----------------------------------------------------------


--20 :ALTER SESSION SET db_file_multiblock_read_count = 20;
----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2368 |   215K|    60 |
|*  1 |  TABLE ACCESS FULL| T    |  2368 |   215K|    60 |
----------------------------------------------------------


--30::ALTER SESSION SET db_file_multiblock_read_count = 30;
----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2368 |   215K|    46 |
|*  1 |  TABLE ACCESS FULL| T    |  2368 |   215K|    46 |
----------------------------------------------------------

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

디스크 I/OCALL 횟수로 테이블 엑세스 비용을 평가 할경우
- 1. Single Block I/O와 Multiblock I/o 비용은 같다
- 2. 캐싱 횩4ㅘ를 전혀 고려 하지 않는다.

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에 연산 집약적인 작업을 많이 포함할 때