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

Controlling Index Scan Cost

Controlling Index Scan Cost

Index Cost Parameter ( Index 를 사용하다 보면 Cost 를 조정할 일이 있다. )

optimizer_index_caching
optimizer_index_cost_adj

optimizer_index_caching (default 0)

index가 Memory 에 Caching 되어 있을 확률을 의미. 즉 기본값인 0 은 모든 Cost 계산은 Physical I/O 라는 가정.
그러나 현실적으로는 Index Scan 이 Physical read 보다는 Logical read 발생할 확률이 높음.
(Sigle block I/O 인 경우 LRU 삽입 위치가 중간에 들어오는 들어오는 경향)


SQL> create table t1(c1 char(10), c2 char(10));
SQL> create table t2(c1 char(10), c2 char(10));
SQL> insert into t1
  2  select level, 'x'
  3  from dual
  4  connect by level <= 10000
  5  ;

10000 rows created.

SQL> insert into t2
  2  select level, 'x'
  3  from dual
  4  connect by level <= 10000
  5  ;

10000 rows created.



SQL> alter session set "_optimizer_cost_model"=io; 
 
SQL> alter session set optimizer_index_cost_adj = 100;
SQL> alter session set optimizer_index_caching = 0;

SQL> explain plan for
  2  select /*+ use_nl(t1 t2) */ *
  3  from t1, t2
  4  where t1.c1 = t2.c1
  5  ;


optimizer_index_caching = 0;

---------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       | 10000 |   429K| 20006 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2    |     1 |    22 |     2 |
|   2 |   NESTED LOOPS              |       | 10000 |   429K| 20006 |
|   3 |    TABLE ACCESS FULL        | T1    | 10000 |   214K|     6 |
|*  4 |    INDEX RANGE SCAN         | T2_N1 |     1 |       |     1 |
---------------------------------------------------------------------

000 % |   0 | SELECT STATEMENT            |       | 10000 |   429K| 20006 |
025 % |   0 | SELECT STATEMENT            |       | 10000 |   429K| 10006 |
050 % |   0 | SELECT STATEMENT            |       | 10000 |   429K| 10006 |
075 % |   0 | SELECT STATEMENT            |       | 10000 |   429K| 10006 |
100 % |   0 | SELECT STATEMENT            |       | 10000 |   429K| 10006 |


SQL> explain plan for
  2  select /*+ index(t1) */ *
  3  from t1 where c1 > ' '
  4  ;

---------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       | 10000 |   214K|  1938 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    | 10000 |   214K|  1938 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 | 10000 |       |    32 |
---------------------------------------------------------------------

000 |   0 | SELECT STATEMENT            |       | 10000 |   214K|  1938 |
025 |   0 | SELECT STATEMENT            |       | 10000 |   214K|  1938 |
050 |   0 | SELECT STATEMENT            |       | 10000 |   214K|  1938 |
075 |   0 | SELECT STATEMENT            |       | 10000 |   214K|  1938 |
100 |   0 | SELECT STATEMENT            |       | 10000 |   214K|  1938 |

이 Parameter 에 비례해서 Cost 값이 결정되지 않는 점을 확인.



SQL>alter session set "_optimizer_cost_model" = CPU;

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       | 10000 |   429K| 20031   (1)| 00:01:41 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2    |     1 |    22 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |       | 10000 |   429K| 20031   (1)| 00:01:41 |
|   3 |    TABLE ACCESS FULL        | T1    | 10000 |   214K|    12   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | T2_N1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |       | 10000 |   429K| 10022   (1)| 00:00:51 |
|   0 | SELECT STATEMENT            |       | 10000 |   429K| 10022   (1)| 00:00:51 |

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       | 10000 |   214K|  1940   (1)| 00:00:10 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    | 10000 |   214K|  1940   (1)| 00:00:10 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 | 10000 |       |    32   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |       | 10000 |   214K|  1940   (1)| 00:00:10 |
|   0 | SELECT STATEMENT            |       | 10000 |   214K|  1940   (1)| 00:00:10 |

Nested Loops Join , In List Operation Cost 영향을 준다.
Cost 값이 이 수치에 비례 해서 증가하지 않는다고 판단
OLTP 환경에서는 영향이 매우 크다고 보임

optimizer_index_cost_adj (default 100)

Index Cost 를 조정하는 Parameter
Default 100% 기본값을 100% 그대로 적용 한다는 의미.
1회 Single block I/O Cost = Muliti Block Cost


SQL> alter session set "_optimizer_cost_model" = io;

SQL> alter session set optimizer_index_caching = 0;

SQL> alter session set optimizer_index_cost_adj = 100;

SQL> explain plan for
  2  select /*+ use_nl(t1 t2) */ *
  3  from t1, t2
  4  where t1.c1 = t2.c1
  5  ;

---------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       | 10000 |   429K| 20006 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2    |     1 |    22 |     2 |
|   2 |   NESTED LOOPS              |       | 10000 |   429K| 20006 |
|   3 |    TABLE ACCESS FULL        | T1    | 10000 |   214K|     6 |
|*  4 |    INDEX RANGE SCAN         | T2_N1 |     1 |       |     1 |
---------------------------------------------------------------------
075 |   0 | SELECT STATEMENT            |       | 10000 |   429K| 15006 |
050 |   0 | SELECT STATEMENT            |       | 10000 |   429K| 10006 |
025 |   0 | SELECT STATEMENT            |       | 10000 |   429K|  5006 |
015 |   0 | SELECT STATEMENT            |       | 10000 |   429K|  3006 |
001 |   0 | SELECT STATEMENT            |       | 10000 |   429K|   206 |


SQL> explain plan for
  2  select /*+ index(t1) */ *
  3  from t1 where c1 > ' '
  4  ;

---------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       | 10000 |   214K|  1938 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    | 10000 |   214K|  1938 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 | 10000 |       |    32 |
---------------------------------------------------------------------

075 |   0 | SELECT STATEMENT            |       | 10000 |   214K|  1454 |
050 |   0 | SELECT STATEMENT            |       | 10000 |   214K|   969 |
025 |   0 | SELECT STATEMENT            |       | 10000 |   214K|   485 |
015 |   0 | SELECT STATEMENT            |       | 10000 |   214K|   291 |
001 |   0 | SELECT STATEMENT            |       | 10000 |   214K|    20 |

{NOTE}

모든 INDEX COST에 영향을 미친다.
Single I/O 의 COST 를 낮춤으로써 Single Block I/O 와 MultiBlock I/O Cost 의 불균형을 해소하는 역활.
{NOTE}

Parameter Vs. System Statistics

Parameter

Oracle 에서 Default 제공 하는 값은 적합하게 설정되어 있지 않다고 보여는 값이며,
대부분의 시스템, 특히 OLTP System dptj 추천되는 값은 다음과 같다.

optimizer_index_caching (80~95%)
optimizer_index_cost_adj (5~10)

( RBO > CBO 로 전환 할 경우 Opimizer 를 RBO 의 index 성향 보정으로 좋아 보인다.)
하지만 최적의 값은 시스템마다 다를 것이다.

System Statistics

seradtim Single block I/O 평균 수행 시간
mreadtim Multi block I/O 평균 수행 시간
bmrc Optimizer Multiblock_read_count

적절히 시스템 통계 정보가 수집 되어 있으면 Parameter 의 default 값 보다 더 효율적 운영에 도움.
System Statistics 에 자세한 정보는 1장 참조.

Bug 5578791

Combination of optimizer_index_caching and optimizer_index_cost_adj increases Cost


This issue is fixed in 	10.2.0.5 (Server Patch Set)
                        11.1.0.6 (Base Release)

optimizer_index_cost_adj,optimizer_index_caching 를 같이 사용하면 NL JOIN 의 COST 가 높아지는 경우가 있다.


 - 정상적인 경우.


alter session set optimizer_index_cost_adj = 100;
alter session set optimizer_index_caching = 0;

---------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       | 10000 |   429K| 20006 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2    |     1 |    22 |     2 |
|   2 |   NESTED LOOPS              |       | 10000 |   429K| 20006 |
|   3 |    TABLE ACCESS FULL        | T1    | 10000 |   214K|     6 |
|*  4 |    INDEX RANGE SCAN         | T2_N1 |     1 |       |     1 |
---------------------------------------------------------------------

alter session set optimizer_index_cost_adj = 100;
alter session set optimizer_index_caching = 100;

---------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       | 10000 |   429K| 10006 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2    |     1 |    22 |     1 |
|   2 |   NESTED LOOPS              |       | 10000 |   429K| 10006 |
|   3 |    TABLE ACCESS FULL        | T1    | 10000 |   214K|     6 |
|*  4 |    INDEX RANGE SCAN         | T2_N1 |     1 |       |       |
---------------------------------------------------------------------

alter session set optimizer_index_cost_adj = 75;
alter session set optimizer_index_caching = 100;

---------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       | 10000 |   429K|  7506 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2    |     1 |    22 |     1 |
|   2 |   NESTED LOOPS              |       | 10000 |   429K|  7506 |
|   3 |    TABLE ACCESS FULL        | T1    | 10000 |   214K|     6 |
|*  4 |    INDEX RANGE SCAN         | T2_N1 |     1 |       |     1 |
---------------------------------------------------------------------
alter session set optimizer_index_cost_adj = 50;
alter session set optimizer_index_caching = 100;
---------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       | 10000 |   429K|  5006 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2    |     1 |    22 |     1 |
|   2 |   NESTED LOOPS              |       | 10000 |   429K|  5006 |
|   3 |    TABLE ACCESS FULL        | T1    | 10000 |   214K|     6 |
|*  4 |    INDEX RANGE SCAN         | T2_N1 |     1 |       |     1 |
---------------------------------------------------------------------

alter session set optimizer_index_cost_adj = 25;
alter session set optimizer_index_caching = 100;
---------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       | 10000 |   429K|  2506 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2    |     1 |    22 |     1 |
|   2 |   NESTED LOOPS              |       | 10000 |   429K|  2506 |
|   3 |    TABLE ACCESS FULL        | T1    | 10000 |   214K|     6 |
|*  4 |    INDEX RANGE SCAN         | T2_N1 |     1 |       |     1 |
---------------------------------------------------------------------
alter session set optimizer_index_cost_adj = 5;
alter session set optimizer_index_caching = 100;

---------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       | 10000 |   429K|   506 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2    |     1 |    22 |     1 |
|   2 |   NESTED LOOPS              |       | 10000 |   429K|   506 |
|   3 |    TABLE ACCESS FULL        | T1    | 10000 |   214K|     6 |
|*  4 |    INDEX RANGE SCAN         | T2_N1 |     1 |       |     1 |
---------------------------------------------------------------------