[20160307]绑定变量的分配长度3.txt

[20160307]绑定变量的分配长度3.txt

--如果绑定变量中字符串分配占用空间的长度变化，oracle会建立子光标。

--参考连接：

<a href="http://blog.itpub.net/267265/viewspace-1993495/">http://blog.itpub.net/267265/viewspace-1993495/</a>

<a href="http://blog.itpub.net/267265/viewspace-2024389/">http://blog.itpub.net/267265/viewspace-2024389/</a>

--oracle 可以通过一个10503事件设置大的缓存：

$ oerr ora 10503

10503, 00000, "enable user-specified graduated bind lengths"

// *Cause:

// *Action:

--oracle 这样设置，一定有一定的道理，估计跟内存的使用有关。

1.环境：

SCOTT@book&gt; @  &amp;r/ver1

PORT_STRING                    VERSION        BANNER

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

x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

create table t (c1 varchar2(4000));

-- ALTER SESSION SET EVENTS '10503 trace name context level &lt;buffer length&gt;, forever';

-- ALTER SESSION SET EVENTS '10503 trace name context level 2000, forever';

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;

/

commit;

--确定sql_id='9mrd273576n14'

SCOTT@book&gt; @ &amp;r/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 2016-03-07 17:06:10 VARCHAR2(32)    X

                         1 YES :INSTRING                     1        128 2016-03-07 17:06:10 VARCHAR2(128)   X

                         2 YES :INSTRING                     1       2000 2016-03-07 17:06:10 VARCHAR2(2000)  X

                         3 YES :INSTRING                     1       4000 2016-03-07 17:06:10 VARCHAR2(4000)  X

$ cat shp4.sql

column N0_6_16 format 99999999

SELECT DECODE (kglhdadr,

               kglhdpar, '父游标句柄地址',

               '子游标句柄地址')

          text,

       kglhdadr,

       kglhdpar,

       substr(kglnaobj,1,40) c40,

       kglobhd0,

       kglobhd6,

       kglobhs0,kglobhs6,kglobt16,

       kglobhs0+kglobhs6+kglobt16 N0_6_16,

           kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20,

           kglnahsh,

           kglobt03 ,

           kglobt09

  FROM x$kglob

WHERE kglobt03 = '&amp;1'  or kglhdpar='&amp;1' or kglhdadr='&amp;1' or KGLNAHSH= &amp;1;

SYS@book&gt; @ &amp;r/sharepool/shp4 9mrd273576n14

old  17:  WHERE kglobt03 = '&amp;1'  or kglhdpar='&amp;1' or kglhdadr='&amp;1'

new  17:  WHERE kglobt03 = '9mrd273576n14'  or kglhdpar='9mrd273576n14' or kglhdadr='9mrd273576n14'

TEXT           KGLHDADR         KGLHDPAR         C40                                      KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09

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

子游标句柄地址 00000000858419B8 0000000085935A28 select count(*) from t where c1=:instrin 000000007CAD9978 000000007D0D0770       4528       8088       5570     18186      18186 3396554788 9mrd273576n14          0

子游标句柄地址 000000007CA388A0 0000000085935A28 select count(*) from t where c1=:instrin 000000007C9ACF48 000000007D0D0BD8       4528       8088       5570     18186      18186 3396554788 9mrd273576n14          1

子游标句柄地址 000000007CAF20C0 0000000085935A28 select count(*) from t where c1=:instrin 000000007CBE3660 000000007D39A188       8600      12144       5570     26314      26314 3396554788 9mrd273576n14          2

子游标句柄地址 000000007CACE500 0000000085935A28 select count(*) from t where c1=:instrin 000000007CB1F3E0 000000007D39A4D0       8600      12144       5570     26314      26314 3396554788 9mrd273576n14          3

父游标句柄地址 0000000085935A28 0000000085935A28 select count(*) from t where c1=:instrin 000000007CB497E0 00                     8816          0          0      8816       8816 3396554788 9mrd273576n14      65535

--KGLOBT09表示child_number,观察2，3的子光标，可以发现堆0，堆6 消耗的空间很大。oracle这样分级应该从节约共享内存的需要考虑。

--从v$sql视图也可以发现问题。

SYS@book&gt; select  sql_id,sql_text,SHARABLE_MEM,child_number from  v$sql where sql_id='9mrd273576n14';

SQL_ID        SQL_TEXT                                   SHARABLE_MEM CHILD_NUMBER

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

9mrd273576n14 select count(*) from t where c1=:instring         18186            0

9mrd273576n14 select count(*) from t where c1=:instring         18186            1

9mrd273576n14 select count(*) from t where c1=:instring         26314            2

9mrd273576n14 select count(*) from t where c1=:instring         26314            3

--另外今天才知道KGLOBT09=65535就是父游标。我以前的判断是kglhdadr=KGLHDPAR相等就是父游标。