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 삽입 위치가 중간에 들어오는 들어오는 경향)

{CODE:SQL}
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.

{CODE}

{CODE:SQL}

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 |

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

{CODE:SQL}

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

{CODE}

> 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

{CODE:SQL}
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 |

{CODE}
{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 가 높아지는 경우가 있다.

{CODE:SQL}

정상적인 경우.

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

-

{CODE}