[20160307]綁定變量的分配長度3.txt

--如果綁定變量中字符串分配占用空間的長度變化，oracle會建立子光標。
--參考連接：
http://blog.itpub.net/267265/viewspace-1993495/
http://blog.itpub.net/267265/viewspace-2024389/

--oracle 可以通過一個10503事件設置大的緩存：
$ oerr ora 10503
10503, 00000, "enable user-specified graduated bind lengths"
// *Cause:
// *Action:

--oracle 這樣設置，一定有一定的道理，估計跟內存的使用有關。

1.環境：

SCOTT@book> @? &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 <buffer length>, 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> @ &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 = '&1'? or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &1;

SYS@book> @ &r/sharepool/shp4 9mrd273576n14
old? 17:? WHERE kglobt03 = '&1'? or kglhdpar='&1' or kglhdadr='&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> 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相等就是父游標。

總結

以上是生活随笔為你收集整理的[20160307]绑定变量的分配长度3.txt的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。