[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(*)
----------
         0

--//最好执行多次,确定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(*)
----------
         0
--//最好执行多次.

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
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>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
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>
--------------------------------------------------
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
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                  = 000007FF11462050
CHILD_NUMBER                   = 5
OPTIMIZER_MISMATCH             = Y
BIND_LENGTH_UPGRADEABLE        = Y
REASON                         = <ChildNode><ChildNumber>5</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                  = 000007FF1197B738
CHILD_NUMBER                   = 6
OPTIMIZER_MISMATCH             = Y
BIND_LENGTH_UPGRADEABLE        = Y
REASON                         = <ChildNode><ChildNumber>6</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                  = 000007FF148FDB10
CHILD_NUMBER                   = 7
OPTIMIZER_MISMATCH             = Y
BIND_LENGTH_UPGRADEABLE        = Y
REASON                         = <ChildNode><ChildNumber>7</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.

--//注意看: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
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>

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

时间： 2024-10-19 12:34:26

[20171019]关于光标共享问题.txt的相关文章

[20160407]光标共享TOP_LEVEL_RPI_CURSOR

[20160407]光标共享问题TOP_LEVEL_RPI_CURSOR.txt --以前做测试出现,光标不能共享TOP_LEVEL_RPI_CURSOR,不知道什么意思,链接: -- http://blog.itpub.net/267265/viewspace-765072/ --今天看blog,终于明白表示什么意思?参考链接: -- http://www.peasland.net/2016/04/05/vsql_shared_cursor-top_level_rpi_cursor/ --为了

[20131210]linux screen 命令共享会话.txt

[20131210]linux screen 命令共享会话.txt 工作需要,要在两个用户之间共享linux的会话,看了一下screen的文档,加上一些google,做一些记录: 方法1:--使用script,没有想到script本来是记录操作内容的,通过管道文件也可以实现这种功能. --会话1.mkfifo /tmp/sessionscript -f /tmp/session --会话2cat /tmp/session --这样会话2能看会话1的操作,但是会话2不能做任何操作.会话1 exit

[20170904]11Gr2 查询光标为什么不共享脚本

[20170904]11Gr2 查询光标为什么不共享脚本.txt --//参考链接下面的注解脚本: https://carlos-sierra.net/2017/09/01/poors-man-script-to-summarize-reasons-why-cursors-are-not-shared/ --//做一个记录. SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ---------

[20170621]Session Cursor Caching 2.txt

[20170621]Session Cursor Caching 2.txt --//当sql执行时,第一次要经历硬分析,第二次软分析,如果session_cached_cursors设置的化,还可以绕过软分析,也有人叫"软软分析". --//摘录链接的一段话: --//www.toadworld.com/platforms/oracle/b/weblog/archive/2017/04/12/session-cursor-caching-part-one When a SQL sta

[20161219]关于LANGUAGE_MISMATCH.txt

[20161219]为什么光标不共享(LANGUAGE_MISMATCH).txt --生产系统看看那种情况出现比较多,写一个脚本: select sum(decode(UNBOUND_CURSOR,'Y',1,0))    UNBOUND_CURSOR, sum(decode(SQL_TYPE_MISMATCH,'Y',1,0))    SQL_TYPE_MISMATCH, sum(decode(OPTIMIZER_MISMATCH,'Y',1,0))    OPTIMIZER_MISMATC

Win7无线共享批处理

  新建记事本"Wifi共享.txt",然后更改为"Wifi共享.bat",要启动双击即可. 文件内容如下: [plain] @echo off rem 添加虚拟无线网络 netsh wlan set hostednetwork mode=allow ssid="Wifi_Name" key=12345678 keyUsage=persistent rem 启动虚拟无线网络 netsh wlan start hostednetwork rem &

javascript语法

     alert(new Date().toLocaleString());       varnum = new Number();       num =11;      alert(num);       var a= "a";      alert(a);       var b= 'b';      alert(b);       var a= "123";       if(isNaN(a)) {      alert("是一个数字&quo

[20160516]SQL共享光标的测试疑问.txt

[20160516]SQL共享光标的测试疑问.txt --昨天我看了链接http://blog.itpub.net/17203031/viewspace-754994/,感觉他的测试有问题,不可能相同的sql语句,而sql_id会不一样 --的.我自己测试看看. 1.环境: SYS@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ ------

[20160713]为什么光标不能共享.txt

[20160713]为什么光标不能共享.txt --这个是生产系统遇到的问题,有1条语句产生的子光标很多,但是直接查询v$sql仅仅有1个(CHILD_NUMBER很大). --检查为什么不能共享的原因是ROLL_INVALID_MISMATCH. ROLL_INVALID_MISMATCH reason indicates that, after a fresh statistics, using dbms_stats.auto_invalidate value for the paramet