All about optimizer(2)

4) 비용(Cost)
        : 쿼리를 수행하는데 소요되는 일량 또는 시간

        - 옵티마이저 비용 모델
            a. I/O 비용 모델 : 예상되는 I/O Call 횟수만을 쿼리 수행 비용을 간주해 실행계획 작성
            b. CPU 비용 모델 : I/O 비용 모델에서 시간 개념을 더해 비용 산정

          오라클에서는 _optimizer_cost_model 파라미터로 비용 모델 변경 가능


        A. 인덱스를 경유한 테이블 액세스 비용
            Disk I/O Call 횟수(논리적/물리적으로 읽은 블록 개수가 아닌 I/O call 횟수)
         
            인덱스를 경유한 테이블 액세스 시 Single Block I/O 방식 사용,
            디스크에서 한 블록을 읽을 때마다 한번의 I/O Call을 일으키는 방식이므로,
            읽게 될 물리적 블록 개수가 I/O Call 횟수가 일치함.

인덱스를 이용한 테이블 액세스 비용 =
blevel +                                  <--- 인덱스 수직적 탐색 비용
( leaf block 수 * 유효 인덱스 선택도 ) +  <--- 인덱스 수평적 탐색 비용
( cluster factor * 유효 테이블 선택도 )   <--- 테이블 random 액세스 비용, 조건절에 대해 읽힐 것으로 예상되는 테이블 블록 개수

- blevel : 브렌치 레벨, leaf block에 도달하기 전에 읽게 브랜치 블록 개수임.
- 유효 인덱스 선택도 : 전체 인덱스 레코드 중에서 조건절을 만족하는 레코드를 찾기 위해 스캔할 것으로 예상되는 비율(%)
- 유효 테이블 선택도 : 전체 테이블 레코드 중에서 인덱스 스캔을 완료하고서 최종적으로 테이블을 방문할 것으로 예상되는 비율(%)

 

# 클러스터팩터란? -  인덱스경유해전체테이블레코드를액세스할때access할것으로예상되는논리적인블록개수 -  군집성계수(= 데이터가모여있는정도)   # 클러스터링팩터조회   create table t as select * from all_objects order by object_id;   create index t_object_id_idx on t(object_id);   create index t_object_name_idx on t(object_name);   exec dbms_stats.gather_table_stats(user,'T');     select i.index_name, t.blocks table_blocks, i.num_rows, i.clustering_factor from user_tables t, user_indexes i where t.table_name = 'T' and i.table_name = t.table_name;     INDEX_NAME                     TABLE_BLOCKS   NUM_ROWS CLUSTERING_FACTOR ------------------------------ ------------ ---------- ----------------- T_OBJECT_ID_IDX                        1125      77438              1099 T_OBJECT_NAME_IDX                      1125      77438             41468     CLUSTERING_FACTOR가TABLE_BLOCKS에가까울수록데이터가잘정렬돼있음을의미, 레코드개수(NUM_ROWS)에가까울수록흩어져있음을의미함.   # 클러스터계산방식 1)     Counter 변수를하나선언 2)     인덱스리프블록을처음부터끝까지스캔하면서인덱스rowid로부터블록번호취함 3)     현재읽고있는인덱스레코드의블록번호가바로직전에읽은레코드의블록번호와다를때마다counter 변수값을1씩증가 4)     스캔을완료하고서, 최종counter변수값을clustering_factor로서인덱스통계에저장

 

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;   select /*+ index(t) */ * from t where owner = 'SYS';   ------------------------------------------------------------------------------------------- | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |             |  1490 |   141K|    44   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| T           |  1490 |   141K|    44   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN          | T_OWNER_IDX |  1490 |       |     4   (0)| 00:00:01 | -------------------------------------------------------------------------------------------       옵티마이저는 Index range scan 단계에서4번, table access 단계에서 40번 Single Block I/O Call이 발생할 것으로 예상함. index range scan단계에서 Rows(=Cardinality)가 1490인데, I/O Call이40번뿐 인 것은 Cluster factor가 비용 계산식에 고려됨.

 

 

     

  B. Full scan에 의한 테이블 액세스 비용
            테이블 전체를 순차적으로 읽어 들이는 과정에서 발생하는 I/O Call 횟수로 비용 계산

            ex) 100블록을 8개씩 나누어 읽는다면 13번의 I/O Call이 발생하고, I/O Call 횟수로써 Full scan 비용 추정
          
            Multiblock I/O 단위가 증가할 수 록 I/O Call 횟수가 줄고 예상 비용도 준다.

 

한번의I/O Call로 읽어들이는Block수가 증가할 수록Cost는 낮아짐.   SQL> select blocks from user_tables where table_name = 'T';   BLOCKS ---------- 1126   SQL> set autotrace traceonly exp; SQL> alter session set db_file_multiblock_read_count = 2;   Session altered.   SQL> select /*+ full(t) */ * from t where owner = 'SYS';   Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873   ---------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost  | ---------------------------------------------------------- |   0 | SELECT STATEMENT  |      |  1490 |   141K|   427 | |*  1 |  TABLE ACCESS FULL| T    |  1490 |   141K|   427 | ----------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------   1 - filter("OWNER"='SYS')   Note ----- - cpu costing is off (consider enabling it)     SQL> alter session set db_file_multiblock_read_count = 4;   Session altered.   SQL> SQL> select /*+ full(t) */ * from t where owner = 'SYS';   Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873   ---------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost  | ---------------------------------------------------------- |   0 | SELECT STATEMENT  |      |  1490 |   141K|   271 | |*  1 |  TABLE ACCESS FULL| T    |  1490 |   141K|   271 | ----------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------   1 - filter("OWNER"='SYS')   Note ----- - cpu costing is off (consider enabling it)   SQL> alter session set db_file_multiblock_read_count = 8;   Session altered.   SQL> SQL> select /*+ full(t) */ * from t where owner = 'SYS';   Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873   ---------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost  | ---------------------------------------------------------- |   0 | SELECT STATEMENT  |      |  1490 |   141K|   173 | |*  1 |  TABLE ACCESS FULL| T    |  1490 |   141K|   173 | ----------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------   1 - filter("OWNER"='SYS')   Note ----- - cpu costing is off (consider enabling it)   SQL> alter session set db_file_multiblock_read_count = 16;   Session altered.   SQL> SQL> select /*+ full(t) */ * from t where owner = 'SYS';   Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873   ---------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost  | ---------------------------------------------------------- |   0 | SELECT STATEMENT  |      |  1490 |   141K|   110 | |*  1 |  TABLE ACCESS FULL| T    |  1490 |   141K|   110 | ----------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------   1 - filter("OWNER"='SYS')   Note ----- - cpu costing is off (consider enabling it)   SQL> alter session set db_file_multiblock_read_count = 32;   Session altered.   SQL> SQL> select /*+ full(t) */ * from t where owner = 'SYS';   Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873   ---------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost  | ---------------------------------------------------------- |   0 | SELECT STATEMENT  |      |  1490 |   141K|    70 | |*  1 |  TABLE ACCESS FULL| T    |  1490 |   141K|    70 | ----------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------   1 - filter("OWNER"='SYS')   Note ----- - cpu costing is off (consider enabling it)   SQL> alter session set db_file_multiblock_read_count = 64;   Session altered.   SQL> SQL> select /*+ full(t) */ * from t where owner = 'SYS';   Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873   ---------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost  | ---------------------------------------------------------- |   0 | SELECT STATEMENT  |      |  1490 |   141K|    45 | |*  1 |  TABLE ACCESS FULL| T    |  1490 |   141K|    45 | ----------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------   1 - filter("OWNER"='SYS')   Note ----- - cpu costing is off (consider enabling it)   SQL> alter session set db_file_multiblock_read_count = 128;   Session altered.   SQL> SQL> select /*+ full(t) */ * from t where owner = 'SYS';   Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873   ---------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost  | ---------------------------------------------------------- |   0 | SELECT STATEMENT  |      |  1490 |   141K|    29 | |*  1 |  TABLE ACCESS FULL| T    |  1490 |   141K|    29 | ----------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------   1 - filter("OWNER"='SYS')   Note ----- - cpu costing is off (consider enabling it)

 

알티베이스도 DBFILE_MULTIPAGE_READ_COUNT 수치 증가에 따라 Full scan 성능 좋아진다.

NOK 페이지 참조 : http://nok.altibase.com/display/rnd/20111205+-+MPR%28MultiPageRead%29+Performance

 

 

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

1) Single Block I/O와 MultiBlock I/O 비용이 같다.
2) Buffer 캐싱 효과를 전혀 고려하지 않는다.


위 2가지 가정을 보정하기 위한 2가지 오라클 파라미터
1) optimizer_index_cost_adj
2) optimizer_index_caching



2. CPU 비용 모델

I/O 시간과 CPU 연산 시간을 더한 시간 개념을 빌어 쿼리 수행 비용 평가


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

1) Hash join시 Hash chain에 달린 record가 많아 Hash chain을 스캔하는 부하가 심할 때
2) 캐싱된 블록을 반복적으로 읽는데, 한 블록 내에서 매번 비효율적으로 많은 레코드를 스캔할 때
    ( ex. NL join시 Inner족 인덱스 선두 컬럼이 between 조건일 때 )
3) Buffer를 pin한 상태에서 같은 블록을 반복 액세스할 때
4) 다량의 레코드를 읽으면서 건건이 여러 개의 사용자 정의 함수를 반복 호출할 때
5) 메모리 소트를 반복할 때


아래의 경우에도 CPU usag가 다소 증가함.
1) 조건절 개수가 아주 많을 때
2) 조건절이나 select-list에 연산 집약적인 작업을 많이 포함할 때



CPU 비용 모델의 산식

Cost = ( ( #SRDs * sreadtim ) + ( #MRds * mreadtim ) + ( #CPUCycles / cpuspeed ) ) / sreadtim

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





# I/O 비용

( #SRDs * sreadtim ) + ( #MRds * mreadtim )   <---- I/O 일량


#  CPU 비용

 ( #CPUCycles / cpuspeed )     <---- CPU 비용


sreadtim으로 나눈 것은 CPU 비용 모델에서는 쿼리의 예상 총 수행 시간을 Single Block I/O 시간 단위로 표현한 것이다.
Cost가 10이라면 10번의 Single Block I/O를 수행하는 만큼의 시간이 걸릴 것임을 의미