by-nc-sa     개발자, 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 |

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

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 |
---------------------------------------------------------------------

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.