ITPub博客

首页 > 数据库 > Oracle > [20160224]绑定变量的分配长度.txt

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

原创 Oracle 作者:lfree 时间:2016-02-24 10:12:39 0 删除 编辑

[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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2470
  • 访问量
    6277365