[20160224]繫結變數的分配長度.txt

lfree發表於2016-02-24

[20160224]繫結變數的分配長度.txt

--如果繫結變數中字串分配佔用空間的長度變化,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

SCOTT@book> create table t (c1 varchar2(4000));
Table created.

2.建立指令碼:

--這個測試指令碼來自tom。
declare
v_c1 varchar2(4000);
  begin
   for i in 1..4000 loop
     v_c1 := rpad('X',i);
     execute immediate 'insert into t values (:instring) ' using v_c1 ;
    end loop;
end;
/
commit;

--找到sql_id ,過程略。sql_id=''7v2jg1nahvkzn'.

SCOTT@book> select sql_id, child_number, executions  from v$sql where sql_id = '7v2jg1nahvkzn';
SQL_ID        CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
7v2jg1nahvkzn            0         32
7v2jg1nahvkzn            1         96
7v2jg1nahvkzn            2       1872
7v2jg1nahvkzn            3       2000

SCOTT@book> @ &r/share 7v2jg1nahvkzn
SQL_TEXT                       = insert into t values (:instring)
SQL_ID                         = 7v2jg1nahvkzn
ADDRESS                        = 0000000063C72778
CHILD_ADDRESS                  = 00000000677CDED8
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                       = insert into t values (:instring)
SQL_ID                         = 7v2jg1nahvkzn
ADDRESS                        = 0000000063C72778
CHILD_ADDRESS                  = 0000000062245EE8
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                       = insert into t values (:instring)
SQL_ID                         = 7v2jg1nahvkzn
ADDRESS                        = 0000000063C72778
CHILD_ADDRESS                  = 00000000850FF490
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                       = insert into t values (:instring)
SQL_ID                         = 7v2jg1nahvkzn
ADDRESS                        = 0000000063C72778
CHILD_ADDRESS                  = 000000006B315AD8
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.

--很明顯,不能使用同一個游標的原因是BIND_LENGTH_UPGRADEABLE.
--字串長度變化是32,32+96=128,32+96+1872=2000.也就是分4個段 1-32,33-128,129-2000,2001-4000.

3.繼續測試:
--我前面的測試字串長度從小到大。如果反過來呢?
--指令碼修改如下
SCOTT@book> alter system flush shared_pool;
System altered.

declare
v_c1 varchar2(4000);
  begin
   for i in REVERSE 1..4000 loop
     v_c1 := rpad('X',i);
     execute immediate 'insert into t (c1) values (:instring) ' using v_c1 ;
    end loop;
end;
/
commit;

--為了避免前面的sql語句,我對sql語句進行了修改。另外for迴圈使用了REVERSE。也就是先插入字串最大的情況。
--確定sql_id='0v70rn71pdtcj'.

SCOTT@book> select sql_id, child_number, executions  from v$sql where sql_id = '0v70rn71pdtcj';
SQL_ID        CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
0v70rn71pdtcj            0       4000

--很明顯這個時候僅僅1個子游標。因為一開始分很大的空間對於V_C1變數。

4.但是如果改用select呢?

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> select sql_id, child_number, executions  from v$sql where sql_id = '9mrd273576n14';
SQL_ID        CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
9mrd273576n14            0         32
9mrd273576n14            1         96
9mrd273576n14            2       1872
9mrd273576n14            3       2000

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-02-24 09:22:54 VARCHAR2(32)    X
                         1 YES :INSTRING                     1        128 2016-02-24 09:22:54 VARCHAR2(128)   X
                         2 YES :INSTRING                     1       2000 2016-02-24 09:22:55 VARCHAR2(2000)  X

--從這個檢視也可以看出字串長度變化,沒有包括CHILD_NUMBER=3.這個我同事問的第一個問題?為什麼沒有看到CHILD_NUMBER=3的情況。
--很簡單受隱含引數_cursor_bind_capture_area_size大小控制。

SCOTT@book> @ &r/dpcx 9mrd273576n14 '' 3
argment : typical all advanced partition predicate remote note parallel projection alias peeked_binds outline adaptive
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  9mrd273576n14, child number 3
-------------------------------------
select count(*) from t where c1=:instring
Plan hash value: 2966233522
----------------------------------------------------------------------------
| Id  | Operation          | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |       |   785 (100)|          |
|   1 |  SORT AGGREGATE    |      |      1 |  2002 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |     64 |   125K|   785   (1)| 00:00:10 |
----------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
   1 - (VARCHAR2(30), CSID=852, Not Captured)
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("C1"=:INSTRING)
--從這裡看出沒有capture。

SYS@book> @ &r/hide _cursor_bind_capture_area_size
NAME                           DESCRIPTION                                   DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
------------------------------ --------------------------------------------- ------------- ------------- -------------
_cursor_bind_capture_area_size maximum size of the cursor bind capture area  TRUE          400           400

--因為capture area size是400,這樣0-32 是CHILD_NUMBER=0,33-128 是CHILD_NUMBER=1,129-2000 ,CHILD_NUMBER=2,這樣超出400無法在抓取。

SYS@book> alter system set "_cursor_bind_capture_area_size"=2002 scope=memory;
System altered.

SCOTT@book> 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;
/
commit;

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-02-24 09:41:05 VARCHAR2(32)    X
                         1 YES :INSTRING                     1        128 2016-02-24 09:41:05 VARCHAR2(128)   X
                         2 YES :INSTRING                     1       2000 2016-02-24 09:41:05 VARCHAR2(2000)  X
                         3 YES :INSTRING                     1       4000 2016-02-24 09:41:05 VARCHAR2(4000)  X
--這樣第1個問題解決。

4.sqlplus測試問題:
SCOTT@book> alter system flush shared_pool;
System altered.

SCOTT@book> variable instring varchar2(4000)
SCOTT@book> exec :instring := rpad('X',1);

PL/SQL procedure successfully completed.

SCOTT@book> select count(*) from t where c1=:instring;
  COUNT(*)
----------
         2

SCOTT@book> exec :instring := rpad('X',33);
PL/SQL procedure successfully completed.

SCOTT@book> select count(*) from t where c1=:instring;
  COUNT(*)
----------
         2

SCOTT@book> select sql_id, child_number, executions  from v$sql where sql_id = '9mrd273576n14';
SQL_ID        CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
9mrd273576n14            0          2

--為什麼在sqlplus進行類似的測試問題消失呢?如果看看繫結變數的捕獲相關檢視:

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       4000 2016-02-24 09:47:59 VARCHAR2(4000)  X

--可以發現在sqlplus執行,型別長度是其中定義的長度。已經是4000.這樣類似的測試就不會出現子游標問題了。
--而在plsql中雖然定義是varchar2(4000),oracle在執行時還是分成了4段,也許為了節約記憶體的需要。

--如果換成如下測試,問題就可以再現了:

SCOTT@book> variable instring varchar2(32)
SCOTT@book> exec :instring := rpad('X',1);
PL/SQL procedure successfully completed.

SCOTT@book> Select count(*) from t where c1=:instring;
  COUNT(*)
----------
         2

SCOTT@book> variable instring varchar2(200)
SCOTT@book> exec :instring := rpad('X',33);

PL/SQL procedure successfully completed.

SCOTT@book> Select count(*) from t where c1=:instring;
  COUNT(*)
----------
         2

--確定sql_id=9msm2r8u8fv55.
SCOTT@book> select sql_id, child_number, executions  from v$sql where sql_id = '9msm2r8u8fv55';
SQL_ID        CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
9msm2r8u8fv55            0          1
                         1          1


SCOTT@book> @ &r/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 2016-02-24 09:51:40 VARCHAR2(32)    X
                         1 YES :INSTRING                     1       2000 2016-02-24 09:52:02 VARCHAR2(2000)  X

--還可以看出我定義variable instring varchar2(200),實際上在v$sql_bind_capture支援的資料型別VARCHAR2(2000)。
--oracle根據分配長度根據佔用的空間,選擇最大的32,128,2000,4000空間。

--最後附上bind_cap指令碼:
# 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
order by child_number,was_captured,position;
break on sql_id on child_number  skip 0

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-1993495/,如需轉載,請註明出處,否則將追究法律責任。

相關文章