Bind peeking/Adaptive cursor sharing의 Parameter 변경과 영향

Knowledge/Perfomance Tuning

Bind peeking/Adaptive cursor sharing의 Parameter 변경과 영향

neo-orcl 2016. 8. 8. 18:43

■ 개요

성능고도화나 이번에 나온 트러블슈팅 오라클 퍼포먼스나 오라클 공식 교재의 SQL tuning workshop등에서 adaptive cursor sharing에 대해 효과를 설명하고 있습니다.

몇몇 파라미터를 조정시에 기능이 제대로 사용되는지 확인해보려 합니다.

테스트 계기는 사이트에서 DB 파라미터를 보다 보면 아래 파라미터들이 조정되어 있는 경우를 볼 수 있었기 때문입니다.

_optim_peek_user_binds=false (기본값 true)

_optimizer_extended_cursor_sharing_rel=none (10g 없음. 11g 기본 simple)

_optimizer_extended_cursor_sharing=none (10g,11g 기본 udo)

_optimizer_adaptive_cursor_sharing=false (10g 없음, 11g 기본 true)

버그 때문에 안정화를 위해 조정해두는 경우가 있는데, 위 파라미터들이 모두 조정되었을 경우, 혹은 한개라도 조정되었을 경우 adaptive cursor sharing이 동작 하는지 알기 위해 간단히 테스트합니다.

■ 테스트 준비

1. 현재 버전, 파라미터 확인

SQL> select version from v$instance;

VERSION
-----------------
11.2.0.4.0

--여기 적지 않았지만 PSU도 11.2.0.4.5까지 적용되어 있습니다.

SQL> select a.ksppinm, b.ksppstvl
2 from x$ksppi a, x$ksppsv b
3 where a.indx=b.indx
4 and ksppinm like '%&parametername%';
Enter value for parametername: optim_peek
old 4: and ksppinm like '%&parametername%'
new 4: and ksppinm like '%optim_peek%'

KSPPINM KSPPSTVL
---------------------------------------- ----------------------------------------
_optim_peek_user_binds TRUE

SQL>
SQL> select a.ksppinm, b.ksppstvl
2 from x$ksppi a, x$ksppsv b
3 where a.indx=b.indx
4 and ksppinm like '%&parametername%';
Enter value for parametername: cursor_sharing
old 4: and ksppinm like '%&parametername%'
new 4: and ksppinm like '%cursor_sharing%'

KSPPINM                                  KSPPSTVL
---------------------------------------- ----------------------------------------
_disable_cursor_sharing                  FALSE
cursor_sharing                           EXACT
_optimizer_extended_cursor_sharing       UDO
_optimizer_extended_cursor_sharing_rel   SIMPLE
_optimizer_adaptive_cursor_sharing       TRUE

2. 테스트 테이블, 인덱스 생성, histogram도 수집하게 하여 통계정보 수집

SQL> create table tb1(id int, col2 char(4)) tablespace users;

Table created.

SQL> create index tb1_idx on tb1(id);

Index created.

SQL> insert into tb1 select 1, 'name' from all_objects where rownum <= 100000;

13757 rows created.

SQL> insert into tb1 values(2, 'name');

1 row created.

SQL> commit;

Commit complete.

--테이블의 id 컬럼에 1의 데이터를 13757건, 2의 데이터를 1건 입력

SQL> exec dbms_stats.gather_table_stats(user, 'tb1', estimate_percent=>100, method_opt=>'for columns size 2 id', cascade=>true);

PL/SQL procedure successfully completed.

--통계정보 수집. histogram도 수집

--bind peeking이나 adaptive cursor sharing 모두 histogram이 없으면 동작하지 않기에 반드시 histogram도 수집

■ 테스트

1. Default

SQL> set line 180
SQL> col IBS for a3
SQL> col IBA for a3
SQL> col sql_text for a42
SQL> var id number;
SQL> alter system flush shared_pool;

System altered.

--커서를 비우기 위해 수행

SQL> exec :id := 1;

PL/SQL procedure successfully completed.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
13757

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID SQL_TEXT IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id Y N 1364719694

--bind peeking에 의해 full table scan 실행계획이 생성

SQL> exec :id := 2;

PL/SQL procedure successfully completed.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
1

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID SQL_TEXT IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id Y N 1364719694

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
1

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID        SQL_TEXT                                   IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id Y   N        1364719694
4q3p69cucf7m4 select count(col2) from tb1 where id = :id Y   Y         589386258

--두번째 실행에서 새로운 실행계획이 생성. index scan

SQL> exec :id := 1;

PL/SQL procedure successfully completed.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
13757

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

정상 동작은 위와 같습니다.(bind peeking -> adaptive cursor sharing)

2. _optim_peek_user_binds=false

SQL> alter session set "_optim_peek_user_binds"=false;

Session altered.

SQL> var id number;

SQL> alter system flush shared_pool;

System altered.

SQL> exec :id := 1;

PL/SQL procedure successfully completed.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
13757

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID SQL_TEXT IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id N N 1364719694

SQL> exec :id := 2;

PL/SQL procedure successfully completed.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
1

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID SQL_TEXT IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id N N 1364719694

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
1

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID SQL_TEXT IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id N N 1364719694

SQL> exec :id := 1;

PL/SQL procedure successfully completed.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
13757

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID SQL_TEXT IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id N N 1364719694

SQL> alter session set "_optim_peek_user_binds"=true;

Session altered.

_optim_peek_user_binds 를 false로 설정하면 adaptive cursor sharing도 사용되지 않는 다는 것을 알 수 있습니다.

사실 위 테스트로는 bind peeking이 제대로 동작 안한건지 확인할 수가 없습니다. 왜냐면 bind peeking을 안써도 bind변수를 썼기에 optimizer는 col2 컬럼이 2개의 NDV로 가정하기 때문에 full table scan을 선택하거든요.

그래서 이번 테스트만 bind 변수 순서를 바꾸어서 한번 더 해봅니다.

SQL> alter session set "_optim_peek_user_binds"=false;

Session altered.

SQL> alter system flush shared_pool;

System altered.

SQL> exec :id := 2;

PL/SQL procedure successfully completed.

--id=2 로 먼저 시도해봅니다.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
1

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID SQL_TEXT IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id N N 1364719694

SQL> exec :id := 1;

PL/SQL procedure successfully completed.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
13757

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID SQL_TEXT IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id N N 1364719694

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
13757

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID SQL_TEXT IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id N N 1364719694

SQL> exec :id := 2;

PL/SQL procedure successfully completed.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
1

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID SQL_TEXT IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id N N 1364719694

SQL> alter session set "_optim_peek_user_binds"=true;

Session altered.

--다시 bind peeking을 키고 id=2 일때 다시 수행해봅니다.

SQL> alter system flush shared_pool;

System altered.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
1

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID SQL_TEXT IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id Y N 589386258

--예상하던대로 플랜이 달라졌습니다.

3. _optimizer_extended_cursor_sharing_rel=none

SQL> alter session set "_optimizer_extended_cursor_sharing_rel"=none;

Session altered.

SQL> var id number;

SQL> alter system flush shared_pool;

System altered.

SQL> exec :id := 1;

PL/SQL procedure successfully completed.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
13757

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID SQL_TEXT IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id N N 1364719694

SQL> exec :id := 2;

PL/SQL procedure successfully completed.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
1

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID SQL_TEXT IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id N N 1364719694

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
1

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID SQL_TEXT IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id N N 1364719694

SQL> exec :id := 1;

PL/SQL procedure successfully completed.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
13757

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID SQL_TEXT IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id N N 1364719694

SQL> alter session set "_optimizer_extended_cursor_sharing_rel"='simple';

Session altered.

_optimizer_extended_cursor_sharing_rel 를 none으로 설정하면 adaptive cursor sharing도 사용되지 않는 다는 것을 알 수 있습니다.

4. _optimizer_extended_cursor_sharing=none

SQL> alter session set "_optimizer_extended_cursor_sharing"=none;

Session altered.

SQL> var id number;

SQL> alter system flush shared_pool;

System altered.

SQL> exec :id := 1;

PL/SQL procedure successfully completed.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
13757

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID SQL_TEXT IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id Y N 1364719694

SQL> exec :id := 2;

PL/SQL procedure successfully completed.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
1

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID SQL_TEXT IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id Y N 1364719694

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
1

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL> exec :id := 1;

PL/SQL procedure successfully completed.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
13757

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL> alter session set "_optimizer_extended_cursor_sharing"='UDO';

Session altered.

_optimizer_extended_cursor_sharing 을 none으로 변경해도 adaptive cursor sharing은 동작합니다.

5. _optimizer_adaptive_cursor_sharing=false

SQL> alter session set "_optimizer_adaptive_cursor_sharing"=false;

Session altered.

SQL> var id number;

SQL> alter system flush shared_pool;

System altered.

SQL> exec :id := 1;

PL/SQL procedure successfully completed.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
13757

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID SQL_TEXT IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id Y Y 1364719694

SQL> exec :id := 2;

PL/SQL procedure successfully completed.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
1

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID        SQL_TEXT                                   IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id Y   Y        1364719694
4q3p69cucf7m4 select count(col2) from tb1 where id = :id Y   Y         589386258

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
1

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID        SQL_TEXT                                   IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id Y   Y        1364719694
4q3p69cucf7m4 select count(col2) from tb1 where id = :id Y   Y         589386258

SQL> exec :id := 1;

PL/SQL procedure successfully completed.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
13757

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID        SQL_TEXT                                   IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id Y   Y        1364719694
4q3p69cucf7m4 select count(col2) from tb1 where id = :id Y   Y         589386258

예상하기론 optimizer_adaptive_cursor_sharing을 false로 변경하면 adaptive cursor sharing 기능이 사용되지 않을 줄 알았는데 오히려 더 적극적으로 사용됩니다.

마치 bind_aware 힌트를 사용한 것과 같은 효과를 가집니다.

저작자표시 비영리 변경금지