[20171019]关于光标共享问题.txt-白红宇

[20171019]关于光标共享问题.txt

--//如果sql语句光标不能共享,查看v$sql_shared_cursor视图.

--//别人问的问题,如果存在两个因素是否显示2个原因.自己还是测试看看.

1.环境:

SCOTT@test01p> @ver1

PORT_STRING VERSION BANNER CON_ID

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

IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0

SCOTT@test01p> create table t (c1 varchar2(4000));

Table created.

2.测试:

--//参考链接: http://blog.itpub.net/267265/viewspace-1993495/

SCOTT@test01p> variable instring varchar2(32)

SCOTT@test01p> exec :instring := rpad('X',1);

PL/SQL procedure successfully completed.

SCOTT@test01p> Select count(*) from t where c1=:instring;

COUNT(*)

----------

--//最好执行多次,确定sql_id=9msm2r8u8fv55.

SCOTT@test01p> show parameter optimizer_index_cost_adj

NAME TYPE VALUE

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

optimizer_index_cost_adj integer 100

SCOTT@test01p> alter session set optimizer_index_cost_adj=99;

Session altered.

SCOTT@test01p> variable instring varchar2(200)

SCOTT@test01p> exec :instring := rpad('X',33);

PL/SQL procedure successfully completed.

SCOTT@test01p> Select count(*) from t where c1=:instring;

COUNT(*)

----------

--//最好执行多次.

SCOTT@test01p> select sql_id, child_number, executions from v$sql where sql_id = '9msm2r8u8fv55';

SQL_ID CHILD_NUMBER EXECUTIONS

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

9msm2r8u8fv55 0 7

9msm2r8u8fv55 1 5

--//可以发现产生2个子光标.

3.查询为什么不能共享原因:

SCOTT@test01p> @ share 9msm2r8u8fv55

old 15: and q.sql_id like ''&1''',

new 15: and q.sql_id like ''9msm2r8u8fv55''',

SQL_TEXT = Select count(*) from t where c1=:instring

SQL_ID = 9msm2r8u8fv55

ADDRESS = 000007FF1FD516B0

CHILD_ADDRESS = 000007FF12E68290

CHILD_NUMBER = 0

REASON = <ChildNode><ChildNumber>0</ChildNumber><ID>3</ID><reason>Optimizer mismatch(12)</reason><size>2x312</size><optimizer_index_cost_adj> 100 99 </optimizer_index_cost_adj></ChildNode>

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

SQL_TEXT = Select count(*) from t where c1=:instring

SQL_ID = 9msm2r8u8fv55

ADDRESS = 000007FF1FD516B0

CHILD_ADDRESS = 000007FF1268F890

CHILD_NUMBER = 1

OPTIMIZER_MISMATCH = Y

REASON =

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

PL/SQL procedure successfully completed.

SCOTT@test01p> @ bind_cap 9msm2r8u8fv55 ''

C200

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

Select count(*) from t where c1=:instring

SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING

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

9msm2r8u8fv55 0 YES :INSTRING 1 32 2017-10-19 21:19:19 VARCHAR2(32) X

1 YES :INSTRING 1 2000 2017-10-19 21:20:07 VARCHAR2(2000) X

--//可以发现这样不共享原因的原因是OPTIMIZER_MISMATCH.并没有包括BIND_LENGTH_UPGRADEABLE.

--//可以参考http://blog.itpub.net/267265/viewspace-746524/.

4.继续测试:

SCOTT@test01p> alter system flush shared_pool;

System altered.

declare

v_c1 varchar2(4000);

begin

for i in 1..4000 loop

v_c1 := rpad('X',i);

execute immediate 'select count(*) from t where c1=:instring' using v_c1 ;

end loop;

end;

SCOTT@test01p> @ share 9mrd273576n14

SQL_TEXT = select count(*) from t where c1=:instring

SQL_ID = 9mrd273576n14

ADDRESS = 000007FF11975C18

CHILD_ADDRESS = 000007FF12A095D0

CHILD_NUMBER = 0

REASON = <ChildNode><ChildNumber>0</ChildNumber><ID>40</ID><reason>Bind

mismatch(22)</reason><size>4x4</size><bind_position>0</bind_position><original_oacflg>19</original_oacflg><original_oacmxl>32</original_oacmxl><upgradeable_new_oacmxl>128</upgradeable_new_oacmxl></ChildNode>

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

SQL_TEXT = select count(*) from t where c1=:instring

SQL_ID = 9mrd273576n14

ADDRESS = 000007FF11975C18

CHILD_ADDRESS = 000007FF126C6028

CHILD_NUMBER = 1

BIND_LENGTH_UPGRADEABLE = Y

REASON = <ChildNode><ChildNumber>1</ChildNumber><ID>40</ID><reason>Bind

mismatch(22)</reason><size>4x4</size><bind_position>0</bind_position><original_oacflg>19</original_oacflg><original_oacmxl>128</original_oacmxl><upgradeable_new_oacmxl>32</upgradeable_new_oacmxl></ChildNode>

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

SQL_TEXT = select count(*) from t where c1=:instring

SQL_ID = 9mrd273576n14

ADDRESS = 000007FF11975C18

CHILD_ADDRESS = 000007FF117CF2B8

CHILD_NUMBER = 2

BIND_LENGTH_UPGRADEABLE = Y

REASON = <ChildNode><ChildNumber>2</ChildNumber><ID>40</ID><reason>Bind

mismatch(22)</reason><size>4x4</size><bind_position>0</bind_position><original_oacflg>19</original_oacflg><original_oacmxl>2000</original_oacmxl><upgradeable_new_oacmxl>4000</upgradeable_new_oacmxl></ChildNode>

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

SQL_TEXT = select count(*) from t where c1=:instring

SQL_ID = 9mrd273576n14

ADDRESS = 000007FF11975C18

CHILD_ADDRESS = 000007FF12901EA0

CHILD_NUMBER = 3

BIND_LENGTH_UPGRADEABLE = Y

REASON = <ChildNode><ChildNumber>3</ChildNumber><ID>40</ID><reason>Bind

mismatch(22)</reason><size>4x4</size><bind_position>0</bind_position><original_oacflg>19</original_oacflg><original_oacmxl>4000</original_oacmxl><upgradeable_new_oacmxl>2000</upgradeable_new_oacmxl></ChildNode>

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

PL/SQL procedure successfully completed.

SCOTT@test01p> alter session set optimizer_index_cost_adj=90;

Session altered.

declare

v_c1 varchar2(4000);

begin

for i in 1..4000 loop

v_c1 := rpad('X',i);

execute immediate 'select count(*) from t where c1=:instring' using v_c1 ;

end loop;

end;

SCOTT@test01p> @ share 9mrd273576n14

SQL_TEXT = select count(*) from t where c1=:instring

SQL_ID = 9mrd273576n14

ADDRESS = 000007FF11975C18

CHILD_ADDRESS = 000007FF12A095D0

CHILD_NUMBER = 0

REASON = <ChildNode><ChildNumber>0</ChildNumber><ID>40</ID><reason>Bind

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

SQL_TEXT = select count(*) from t where c1=:instring

SQL_ID = 9mrd273576n14

ADDRESS = 000007FF11975C18

CHILD_ADDRESS = 000007FF126C6028

CHILD_NUMBER = 1

BIND_LENGTH_UPGRADEABLE = Y

REASON = <ChildNode><ChildNumber>1</ChildNumber><ID>40</ID><reason>Bind

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

SQL_TEXT = select count(*) from t where c1=:instring

SQL_ID = 9mrd273576n14

ADDRESS = 000007FF11975C18

CHILD_ADDRESS = 000007FF117CF2B8

CHILD_NUMBER = 2

BIND_LENGTH_UPGRADEABLE = Y

REASON = <ChildNode><ChildNumber>2</ChildNumber><ID>40</ID><reason>Bind

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

SQL_TEXT = select count(*) from t where c1=:instring

SQL_ID = 9mrd273576n14

ADDRESS = 000007FF11975C18

CHILD_ADDRESS = 000007FF12901EA0

CHILD_NUMBER = 3

BIND_LENGTH_UPGRADEABLE = Y

REASON = <ChildNode><ChildNumber>3</ChildNumber><ID>3</ID><reason>Optimizer mismatch(12)</reason><size>2x312</size><optimizer_index_cost_adj> 100 90

</optimizer_index_cost_adj></ChildNode><ChildNode><ChildNumber>3</ChildNumber><ID>40</ID><reason>Bind

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

SQL_TEXT = select count(*) from t where c1=:instring

SQL_ID = 9mrd273576n14

ADDRESS = 000007FF11975C18

CHILD_ADDRESS = 000007FF1199A5B0

CHILD_NUMBER = 4

OPTIMIZER_MISMATCH = Y

REASON = <ChildNode><ChildNumber>4</ChildNumber><ID>40</ID><reason>Bind

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

SQL_TEXT = select count(*) from t where c1=:instring

SQL_ID = 9mrd273576n14

ADDRESS = 000007FF11975C18

CHILD_ADDRESS = 000007FF11462050

CHILD_NUMBER = 5

OPTIMIZER_MISMATCH = Y

BIND_LENGTH_UPGRADEABLE = Y

REASON = <ChildNode><ChildNumber>5</ChildNumber><ID>40</ID><reason>Bind

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

SQL_TEXT = select count(*) from t where c1=:instring

SQL_ID = 9mrd273576n14

ADDRESS = 000007FF11975C18

CHILD_ADDRESS = 000007FF1197B738

CHILD_NUMBER = 6

OPTIMIZER_MISMATCH = Y

BIND_LENGTH_UPGRADEABLE = Y

REASON = <ChildNode><ChildNumber>6</ChildNumber><ID>40</ID><reason>Bind

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

SQL_TEXT = select count(*) from t where c1=:instring

SQL_ID = 9mrd273576n14

ADDRESS = 000007FF11975C18

CHILD_ADDRESS = 000007FF148FDB10

CHILD_NUMBER = 7

OPTIMIZER_MISMATCH = Y

BIND_LENGTH_UPGRADEABLE = Y

REASON = <ChildNode><ChildNumber>7</ChildNumber><ID>40</ID><reason>Bind

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

PL/SQL procedure successfully completed.

--//注意看:CHILD_NUMBER = 4,OPTIMIZER_MISMATCH= Y.视乎于CHILD_NUMBER = 3进行比较.

--//看CHILD_NUMBER = 3的REASON

REASON = <ChildNode><ChildNumber>3</ChildNumber><ID>3</ID><reason>Optimizer mismatch(12)</reason><size>2x312</size><optimizer_index_cost_adj> 100 90

</optimizer_index_cost_adj></ChildNode><ChildNode><ChildNumber>3</ChildNumber><ID>40</ID><reason>Bind

SCOTT@test01p> @ bind_cap 9mrd273576n14 ''

C200

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

select count(*) from t where c1=:instring

SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING

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

9mrd273576n14 0 YES :INSTRING 1 32 2017-10-19 21:34:51 VARCHAR2(32) X

1 YES :INSTRING 1 128 2017-10-19 21:34:51 VARCHAR2(128) X

2 YES :INSTRING 1 2000 2017-10-19 21:34:51 VARCHAR2(2000) X

4 YES :INSTRING 1 32 2017-10-19 21:36:02 VARCHAR2(32) X

5 YES :INSTRING 1 128 2017-10-19 21:36:02 VARCHAR2(128) X

6 YES :INSTRING 1 2000 2017-10-19 21:36:02 VARCHAR2(2000) X

--//缺少CHILD_NUMBER=3,主要超出了抓取字符串限制.

--//还是看不出什么规律,放弃.

--//附上脚本:

$ cat share.sql

SET serveroutput on size 1000000;

DECLARE

c NUMBER;

col_cnt NUMBER;

col_rec DBMS_SQL.desc_tab;

col_value VARCHAR2 (4000);

ret_val NUMBER;

BEGIN

c := DBMS_SQL.open_cursor;

DBMS_SQL.parse

(c,

'select q.sql_text, s.*

from v$sql_shared_cursor s, v$sql q

where s.sql_id = q.sql_id

and s.child_number = q.child_number

and q.sql_id like ''&1''',

DBMS_SQL.native

);

DBMS_SQL.describe_columns (c, col_cnt, col_rec);

FOR idx IN 1 .. col_cnt

LOOP

DBMS_SQL.define_column (c, idx, col_value, 4000);

END LOOP;

ret_val := DBMS_SQL.EXECUTE (c);

WHILE (DBMS_SQL.fetch_rows (c) > 0)

LOOP

FOR idx IN 1 .. col_cnt

LOOP

DBMS_SQL.COLUMN_VALUE (c, idx, col_value);

IF col_rec (idx).col_name IN

('SQL_ID', 'ADDRESS', 'CHILD_ADDRESS', 'CHILD_NUMBER',

'SQL_TEXT','REASON')

THEN

DBMS_OUTPUT.put_line ( RPAD (col_rec (idx).col_name, 30)

|| ' = '

|| col_value

);

ELSIF col_value = 'Y'

THEN

DBMS_OUTPUT.put_line ( RPAD (col_rec (idx).col_name, 30)

|| ' = '

|| col_value

);

END IF;

END LOOP;

DBMS_OUTPUT.put_line

('--------------------------------------------------');

END LOOP;

DBMS_SQL.close_cursor (c);

END;

SET serveroutput off;

$cat bind_cap.sql

set verify off

column value_string format a50

column datatype_string format a15

break on sql_id on child_number skip 1

select replace(sql_fulltext,chr(13),'') c200 from v$sql where sql_id='&1' and rownum<=1;

SELECT sql_id,

child_number,

was_captured,

name,

position,

max_length,

last_captured,

datatype_string,

DECODE (

datatype_string,

'DATE', TO_CHAR (TO_DATE (value_string, 'mm/dd/yy hh24:mi:ss'),

'yyyy/mm/dd hh24:mi:ss'),

value_string)

value_string

FROM v$sql_bind_capture

WHERE sql_id = '&1' and was_captured='YES' and DUP_POSITION is null and name=nvl('&&2',name)

order by child_number,was_captured,position;

break on sql_id on child_number skip 0