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

[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

时间: 2024-10-13 20:23:45

[20160224]绑定变量的分配长度.txt的相关文章

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

[20171019]绑定变量的分配长度7.txt --//如果绑定变量中字符串分配占用空间的长度变化,oracle会建立子光标. --//参考连接: http://blog.itpub.net/267265/viewspace-1993495/ --//oracle 可以通过一个10503事件设置大的缓存,测试看看: $ oerr ora 10503 10503, 00000, "enable user-specified graduated bind lengths" // *Cau

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

[20160313]绑定变量的分配长度4.txt --如果绑定变量中字符串分配占用空间的长度变化,oracle会建立子光标. --参考连接: http://blog.itpub.net/267265/viewspace-1993495/ http://blog.itpub.net/267265/viewspace-2024389/ http://blog.itpub.net/267265/viewspace-2050886/ --12c支持更长的字符串,顺便测试看看: --关于设置12c支持字串

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

[20161001]绑定变量的分配长度5.txt --如果绑定变量中字符串分配占用空间的长度变化,oracle会建立子光标. --一般如果绑定变量有多个字段在分配占用空间时长度变化,这样生成的子光标会增加. --我以前的测试字符串长度变化是32,32+96=128,32+96+1872=2000.也就是分4个段 1-32,33-128,129-2000,2001-4000. --相关链接: http://blog.itpub.net/267265/viewspace-1993495/ http:

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

[20160302]绑定变量的分配长度2.txt --如果绑定变量中字符串分配占用空间的长度变化,oracle会建立子光标. --参考连接: http://blog.itpub.net/267265/viewspace-1993495/ --oracle 可以通过一个10503事件设置大的缓存,测试看看: $ oerr ora 10503 10503, 00000, "enable user-specified graduated bind lengths" // *Cause: //

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

[20161002]绑定变量的分配长度6.txt --如果绑定变量中字符串分配占用空间的长度变化,oracle会建立子光标. --一般如果绑定变量有多个字段在分配占用空间时长度变化,这样生成的子光标会增加. --我以前的测试字符串长度变化是32,32+96=128,32+96+1872=2000.也就是分4个段 1-32,33-128,129-2000,2001-4000. --相关链接: http://blog.itpub.net/267265/viewspace-1993495/ http:

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

[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

Oracle绑定变量分级(Bind Graduation)

Oracle绑定变量分级(Bind Graduation) 绑定变量分级(Bind Graduation)是指Oracle在PL/SQL代码中会根据文本型绑定变量的定义长度而将这些文本型绑定变量分为四个等级,如下所示: l 定义长度小于等于32字节(Byte)的文本型绑定变量被分在第一个等级,Oracle为其分配32字节的内存空间. l 定义长度在[33,128]字节之间的被分在第二个等级,Oracle为其分配128字节的内存空间. l 定义长度在[129,2000]字节之间的文本型绑定变量被分

[20170929]&amp; 代替冒号绑定变量.txt

[20170929]& 代替冒号绑定变量.txt --//我昨天看链接,http://orasql.org/2017/09/27/ampersand-instead-of-colon-for-bind-variables/ --//重复测试: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -

[20150812]关于抓取绑定变量.txt

[20150812]关于抓取绑定变量.txt --通过视图v$sql_bind_capture以及DBA_HIST_SQLBIND可以抓取到sql语句的绑定变量.受到一些参数的限制,曾经写过一篇: [20130410]v$sql_bind_capture和隐含参数_bind_capture_area_size.txt http://blog.itpub.net/267265/viewspace-758175/ SCOTT@test> @ver1 PORT_STRING