adaptive cursor sharing

: 쿼리 수행시 바인드 변수에 따라 유연하게 cursor sharing 하는 개념. Tibero6 부터 추가된 기능.

bind peeking 의 단점을 보완한다.




_ADAPTIVE_CURSOR_SHARING 파라미터로 제어 (기본값: Y, Static )




* 테스트 버전




Tibero 6 (DB 6.0 FS02)

AIX iu 3 5 0006BC92D900 version (big-endian)




Patch files (none)

Compiled with "cc_r -D_HAVENOT_LINUX_AIO -D_TAC -D_XOPEN_SOURCE_EXTENDED=1 -D_ISOC99_SOURCE -D_ALL_SOURCE -qcpluscmt -qlanglvl=extc99 -g -O3 -D_OPT_COMPILED -D_XOPEN_SOURCE_EXTENDED=1 -D_ISOC99_SOURCE -D_ALL_SOURCE -D_WTHR_STACK_DONT_USE_MMAP -D_USE_VIP -qsrcmsg -DHAVE_CONFIG_H -D_WORDSIZE=64 -D_OS_AIX -D_AIX_64 -D_PPC -DUSE_ASSERT -DTSAM -DTSAM_NO_ESDS_SEQUENCE -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -D_USE_FSYNC -D_CHECK_LICENSE -DLGWR_DONT_USE_AIO -qchars=signed -D_STBLOG -D_TAC -D_TAC_CCD -D_AC_FRAME -D_AC_CCD -D_AC_CCN -D_LARGE_FILES -D_GNU_SOURCE"

Compiled at Sep 3 2015 13:57:22







* 테스트 스크립트




create table t1(a varchar(100),b number,c varchar(100))

;




create index t1_idx1 on t1 (a,b);




insert into t1

select 'a',level as b, level||'c'

from dual

connect by level <= 100;




insert into t1

select 'b',level as b, level||'c'

from dual

connect by level <= 1000000;




insert into t1

select 'c',level as b, level||'c'

from dual

connect by level <= 10000000;




commit;




* 케이스 설명 bind 변수 값에 따른 플랜 변화를 확인 한다.




set autot on

var v_aa varchar(100);

exec :v_aa :='a';




select /* test2 */ sum(b)

from t1

where a = :v_aa

;




exec :v_aa :='c';

select /* test2 */ sum(b)

from t1

where a = :v_aa

;
















* no adaptive cursor sharing (_ADAPTIVE_CURSOR_SHARING=N)

SQL> var v_aa varchar(100);

SQL> exec :v_aa :='a';




PSM completed.




SQL> select /* test2 */ sum(b)

2 from t1

3 where a = :v_aa

4 ;




SUM(B)

----------

5050




1 row selected.




SQL ID: 5cu783dgzqcum

Child number: 51

Plan hash value: 2104760400




Execution Plan

--------------------------------------------------------------------------------

1 COLUMN PROJECTION (Cost:1309, %%CPU:0, Rows:1)

2 SORT AGGR (Cost:1309, %%CPU:0, Rows:1)

3 INDEX (RANGE SCAN): T1_IDX1 (Cost:1302, %%CPU:0, Rows:503511)







Predicate Information

--------------------------------------------------------------------------------

3 - access: ("T1"."A" = :0) (0.046)







NAME VALUE

------------------------------ ----------

db block gets 0

consistent gets 65

physical reads 11

redo size 60

sorts (disk) 0

sorts (memory) 2

rows processed 258




SQL> exec :v_aa :='c';




PSM completed.




SQL>

SQL> select /* test2 */ sum(b)

2 from t1

3 where a = :v_aa

4 ;







SUM(B)

----------

5E+13




1 row selected.




SQL ID: 5cu783dgzqcum

Child number: 51

Plan hash value: 2104760400




Execution Plan

--------------------------------------------------------------------------------

1 COLUMN PROJECTION (Cost:1309, %%CPU:0, Rows:1)

2 SORT AGGR (Cost:1309, %%CPU:0, Rows:1)

3 INDEX (RANGE SCAN): T1_IDX1 (Cost:1302, %%CPU:0, Rows:503511)







Predicate Information

--------------------------------------------------------------------------------

3 - access: ("T1"."A" = :0) (0.046)







NAME VALUE

------------------------------ ----------

db block gets 0

consistent gets 26466

physical reads 26457

redo size 0

sorts (disk) 0

sorts (memory) 0

rows processed 1




SQL> SQL>

SQL>

SQL>

SQL>

SQL> /




SUM(B)

----------

5E+13




1 row selected.




SQL ID: 5cu783dgzqcum

Child number: 51

Plan hash value: 2104760400




Execution Plan

--------------------------------------------------------------------------------

1 COLUMN PROJECTION (Cost:1309, %%CPU:0, Rows:1)

2 SORT AGGR (Cost:1309, %%CPU:0, Rows:1)

3 INDEX (RANGE SCAN): T1_IDX1 (Cost:1302, %%CPU:0, Rows:503511)







Predicate Information

--------------------------------------------------------------------------------

3 - access: ("T1"."A" = :0) (0.046)







NAME VALUE

------------------------------ ----------

db block gets 0

consistent gets 26460

physical reads 0

redo size 0

sorts (disk) 0

sorts (memory) 0

rows processed 1




SQL> /




SUM(B)

----------

5E+13




1 row selected.




SQL ID: 5cu783dgzqcum

Child number: 51

Plan hash value: 2104760400




Execution Plan

--------------------------------------------------------------------------------

1 COLUMN PROJECTION (Cost:1309, %%CPU:0, Rows:1)

2 SORT AGGR (Cost:1309, %%CPU:0, Rows:1)

3 INDEX (RANGE SCAN): T1_IDX1 (Cost:1302, %%CPU:0, Rows:503511)







Predicate Information

--------------------------------------------------------------------------------

3 - access: ("T1"."A" = :0) (0.046)







NAME VALUE

------------------------------ ----------

db block gets 0

consistent gets 26460

physical reads 0

redo size 0

sorts (disk) 0

sorts (memory) 0

rows processed 1







* adaptive cursor sharing (_ADAPTIVE_CURSOR_SHARING=Y)




동일 sql_id 에 대해 plan 및 child number가 변경됨을 확인한다.




SQL> set autot on

SQL> var v_aa varchar(100);

SQL> exec :v_aa :='a';




PSM completed.




SQL>

SQL> select /* test2 */ sum(b)

2 from t1

3 where a = :v_aa

4 ;




SUM(B)

----------

5050




1 row selected.




SQL ID: 5cu783dgzqcum

Child number: 53

Plan hash value: 2104760400




Execution Plan

--------------------------------------------------------------------------------

1 COLUMN PROJECTION (Cost:1309, %%CPU:0, Rows:1)

2 SORT AGGR (Cost:1309, %%CPU:0, Rows:1)

3 INDEX (RANGE SCAN): T1_IDX1 (Cost:1302, %%CPU:0, Rows:503511)







Predicate Information

--------------------------------------------------------------------------------

3 - access: ("T1"."A" = :0) (0.046)







NAME VALUE

------------------------------ ----------

db block gets 0

consistent gets 71

physical reads 11

redo size 0

sorts (disk) 0

sorts (memory) 2

rows processed 258




SQL>

SQL>

SQL>

SQL> exec :v_aa :='c';




PSM completed.




SQL>

SQL> exec :v_aa :='c';




PSM completed.




SQL>

SQL> select /* test2 */ sum(b)

2 from t1

3 where a = :v_aa

4 ;




SUM(B)

----------

5E+13




1 row selected.




SQL ID: 5cu783dgzqcum

Child number: 62

Plan hash value: 4177583




Execution Plan

--------------------------------------------------------------------------------

1 COLUMN PROJECTION (Cost:12704, %%CPU:2, Rows:1)

2 SORT AGGR (Cost:12704, %%CPU:2, Rows:1)

3 FILTER (Cost:12628, %%CPU:1, Rows:5005758)

4 INDEX (FAST FULL SCAN): T1_IDX1 (Cost:12448, %%CPU:0, Rows:11018540)



from TmaxSoft Technical network











Predicate Information

--------------------------------------------------------------------------------

3 - filter: ("T1"."A" = :0) (0.454)







NAME VALUE

------------------------------ ----------

db block gets 356

consistent gets 29203

physical reads 2900

redo size 0

sorts (disk) 0

sorts (memory) 3

rows processed 260




SQL>