[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://blog.itpub.net/267265/viewspace-2024389/
http://blog.itpub.net/267265/viewspace-2050886/
http://blog.itpub.net/267265/viewspace-2056695/

--本测试看看2个绑定变量能产生多少子光标,按照道理应该产生4*4=16个子光标。实际上别人的测试并不会产生16个子光标,
--出于好奇没事,我自己也测试看看。

1.环境:
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

SCOTT@test01p> create table t (c1 varchar2(4000),c2 varchar2(4000),c3 varchar2(4000),c4 varchar2(4000),c5 varchar2(4000));
Table created.

--//前面我的测试
--字符串长度变化是32,32+96=128,32+96+1872=2000.也就是分4个段 1-32,33-128,129-2000,2001-4000.
--这样如果2个字段varchar2(4000),理论讲可以出现4*4=16个子光标,测试看看是否正确。

SYS@test> alter system set "_cursor_bind_capture_area_size"=8000 scope=memory;
alter system set "_cursor_bind_capture_area_size"=8000 scope=memory
*
ERROR at line 1:
ORA-00068: invalid value 8000 for parameter _cursor_bind_capture_area_size, must be between 0 and 3999

SYS@test> alter system set "_cursor_bind_capture_area_size"=3999 scope=memory;
System altered.
--//改变 _cursor_bind_capture_area_size,保证能捕获绑定变量的值。

2.测试脚本一:

SCOTT@test01p> alter system flush shared_pool;
System altered.

declare
v_c1 varchar2(4000);
v_c2 varchar2(4000);
begin
   v_c1 := rpad('0',32);
   v_c2 := rpad('0',32);
   execute immediate 'select count(*) from t where c1=:instring1 and c2=:instring2' using v_c1,v_c2 ;

   v_c1 := rpad('1',32);
   v_c2 := rpad('1',128);
   execute immediate 'select count(*) from t where c1=:instring1 and c2=:instring2' using v_c1,v_c2 ;

   v_c1 := rpad('2',128);
   v_c2 := rpad('2',32);
   execute immediate 'select count(*) from t where c1=:instring1 and c2=:instring2' using v_c1,v_c2 ;

   v_c1 := rpad('3',128);
   v_c2 := rpad('3',128);
   execute immediate 'select count(*) from t where c1=:instring1 and c2=:instring2' using v_c1,v_c2 ;
end;
/
commit;
--确定sql_id='2z6faqbzrf9jg'.

SCOTT@test01p> select sql_id, child_number, executions  from v$sql where sql_id = '2z6faqbzrf9jg';
SQL_ID        CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
2z6faqbzrf9jg            0          1
                         1          1
                         2          2
--仅仅3个子光标,why?

SCOTT@test01p> @ bind_cap 2z6faqbzrf9jg
C200
------------------------------------------------------------
select count(*) from t where c1=:instring1 and c2=:instring2

SQL_ID        CHILD_NUMBER WAS NAME                   POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING
------------- ------------ --- -------------------- ---------- ---------- ------------------- --------------- ---------------------------------
2z6faqbzrf9jg            0 YES :INSTRING1                    1         32 2016-10-01 19:42:46 VARCHAR2(32)    0
                           YES :INSTRING2                    2         32 2016-10-01 19:42:46 VARCHAR2(32)    0

                         1 YES :INSTRING1                    1         32 2016-10-01 19:42:46 VARCHAR2(32)    1
                           YES :INSTRING2                    2        128 2016-10-01 19:42:46 VARCHAR2(128)   1

                         2 YES :INSTRING1                    1        128 2016-10-01 19:42:46 VARCHAR2(128)   2
                           YES :INSTRING2                    2        128 2016-10-01 19:42:46 VARCHAR2(128)   2
6 rows selected.

--//会是因为第2次执行时已经赋值v_c2 := rpad('1',128)。

3.建立脚本方便测试:
D:\tools\rlwrap> cat bbb.sql
declare
v_c1 varchar2(4000);
v_c2 varchar2(4000);
begin
   execute immediate 'select count(*) from t where c1=:instring1 and c2=:instring2' using rpad('1',&&1),rpad('1',&&2);
end;
/
--quit;

alter system flush shared_pool;
alter system flush shared_pool;

@ bbb.sql 1 1
@ bbb.sql 1 33
@ bbb.sql 33 1
@ bbb.sql 33 33

SCOTT@test01p> @bind_cap 2z6faqbzrf9jg
C200
------------------------------------------------------------
select count(*) from t where c1=:instring1 and c2=:instring2

SQL_ID        CHILD_NUMBER WAS NAME                   POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING
------------- ------------ --- -------------------- ---------- ---------- ------------------- --------------- ---------------
2z6faqbzrf9jg            0 YES :INSTRING1                    1         32 2016-10-01 21:12:53 VARCHAR2(32)    1
                           YES :INSTRING2                    2         32 2016-10-01 21:12:53 VARCHAR2(32)    1

                         1 YES :INSTRING1                    1         32 2016-10-01 21:12:53 VARCHAR2(32)    1
                           YES :INSTRING2                    2        128 2016-10-01 21:12:53 VARCHAR2(128)   1

                         2 YES :INSTRING1                    1        128 2016-10-01 21:12:53 VARCHAR2(128)   1
                           YES :INSTRING2                    2        128 2016-10-01 21:12:53 VARCHAR2(128)   1

--//依旧是3个子光标。

alter system flush shared_pool;
@ bbb.sql 1 1
@ bbb.sql 33 1
@ bbb.sql 1 33
@ bbb.sql 33 33

SCOTT@test01p> @bind_cap 2z6faqbzrf9jg
C200
------------------------------------------------------------
select count(*) from t where c1=:instring1 and c2=:instring2

SQL_ID        CHILD_NUMBER WAS NAME                   POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING
------------- ------------ --- -------------------- ---------- ---------- ------------------- --------------- --------------------
2z6faqbzrf9jg            0 YES :INSTRING1                    1         32 2016-10-01 21:15:07 VARCHAR2(32)    1
                           YES :INSTRING2                    2         32 2016-10-01 21:15:07 VARCHAR2(32)    1

                         1 YES :INSTRING1                    1        128 2016-10-01 21:15:07 VARCHAR2(128)   1
                           YES :INSTRING2                    2         32 2016-10-01 21:15:07 VARCHAR2(32)    1

                         2 YES :INSTRING1                    1        128 2016-10-01 21:15:07 VARCHAR2(128)   1
                           YES :INSTRING2                    2        128 2016-10-01 21:15:07 VARCHAR2(128)   1

SCOTT@test01p> select sql_id, child_number, executions  from v$sql where sql_id = '2z6faqbzrf9jg';
SQL_ID        CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
2z6faqbzrf9jg            0          1
                         1          1
                         2          2

--视乎从上面的测试得到一个规律,文字比较难描述。
--当执行@ bbb.sql 1 33,如果没有其他子光标的情况下应该如下:

SCOTT@test01p> @bind_cap 2z6faqbzrf9jg
C200
------------------------------------------------------------
select count(*) from t where c1=:instring1 and c2=:instring2

SQL_ID        CHILD_NUMBER WAS NAME                   POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING
------------- ------------ --- -------------------- ---------- ---------- ------------------- --------------- ----------------
2z6faqbzrf9jg            0 YES :INSTRING1                    1         32 2016-10-01 21:34:50 VARCHAR2(32)    1
                           YES :INSTRING2                    2        128 2016-10-01 21:34:50 VARCHAR2(128)   1

--而因为存在子光标CHILD_NUMBER=1
SQL_ID        CHILD_NUMBER WAS NAME                   POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING
------------- ------------ --- -------------------- ---------- ---------- ------------------- --------------- --------------------
2z6faqbzrf9jg            1 YES :INSTRING1                    1        128 2016-10-01 21:15:07 VARCHAR2(128)   1
                           YES :INSTRING2                    2         32 2016-10-01 21:15:07 VARCHAR2(32)    1

--这样生成新的子光标DATATYPE_STRING都是VARCHAR2(128),也就是生成的DATATYPE_STRING要大于前面的子光标。

4.根据这个规律,在排列组合看看:

alter system flush shared_pool;
@bbb.sql 1 1
@bbb.sql 1 33
@bbb.sql 33 1
@bbb.sql 33 33

@bbb.sql 1 129
@bbb.sql 33 129
@bbb.sql 129 1
@bbb.sql 129 33
@bbb.sql 129 129

@bbb.sql 1 2001
@bbb.sql 33 2001
@bbb.sql 129 2001
@bbb.sql 2001 1
@bbb.sql 2001 33
@bbb.sql 2001 129
@bbb.sql 2001 2001

--按照这个组合应该是7个子光标。

SCOTT@test01p> @bind_cap 2z6faqbzrf9jg

C200
------------------------------------------------------------
select count(*) from t where c1=:instring1 and c2=:instring2

SQL_ID        CHILD_NUMBER WAS NAME                   POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING
------------- ------------ --- -------------------- ---------- ---------- ------------------- --------------- ---------------------------------
2z6faqbzrf9jg            0 YES :INSTRING1                    1         32 2016-10-01 21:53:03 VARCHAR2(32)    1
                           YES :INSTRING2                    2         32 2016-10-01 21:53:03 VARCHAR2(32)    1

                         1 YES :INSTRING1                    1         32 2016-10-01 21:53:03 VARCHAR2(32)    1
                           YES :INSTRING2                    2        128 2016-10-01 21:53:03 VARCHAR2(128)   1

                         2 YES :INSTRING1                    1        128 2016-10-01 21:53:03 VARCHAR2(128)   1
                           YES :INSTRING2                    2        128 2016-10-01 21:53:03 VARCHAR2(128)   1

                         3 YES :INSTRING1                    1        128 2016-10-01 21:53:03 VARCHAR2(128)   1
                           YES :INSTRING2                    2       2000 2016-10-01 21:53:03 VARCHAR2(2000)  1

                         4 YES :INSTRING1                    1       2000 2016-10-01 21:53:03 VARCHAR2(2000)  1
                           YES :INSTRING2                    2       2000 2016-10-01 21:53:03 VARCHAR2(2000)  1

                         5 YES :INSTRING1                    1       2000 2016-10-01 21:53:03 VARCHAR2(2000)  1
                           YES :INSTRING2                    2       4000 2016-10-01 21:53:03 VARCHAR2(4000)  1

                         6 YES :INSTRING1                    1       4000 2016-10-01 21:53:03 VARCHAR2(4000)  1
                           YES :INSTRING2                    2       4000 2016-10-01 21:53:03 VARCHAR2(4000)  1
14 rows selected.

SCOTT@test01p> select sql_id, child_number, executions  from v$sql where sql_id = '2z6faqbzrf9jg';
SQL_ID        CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
2z6faqbzrf9jg            0          1
                         1          1
                         2          2
                         3          2
                         4          3
                         5          3
                         6          4
7 rows selected.

--总结:
--大家可以尝试各种组合,我的测试最大就是7个。

alter system flush shared_pool;
@bbb.sql 1 1
@bbb.sql 1 33
@bbb.sql 1 129
@bbb.sql 1 2001

@bbb.sql 33 1
@bbb.sql 33 33
@bbb.sql 33 129
@bbb.sql 33 2001

@bbb.sql 129 1
@bbb.sql 129 33
@bbb.sql 129 129
@bbb.sql 129 2001

@bbb.sql 2001 1
@bbb.sql 2001 33
@bbb.sql 2001 129
@bbb.sql 2001 2001

SCOTT@test01p> @bind_cap 2z6faqbzrf9jg
C200
------------------------------------------------------------
select count(*) from t where c1=:instring1 and c2=:instring2

SQL_ID        CHILD_NUMBER WAS NAME                   POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING
------------- ------------ --- -------------------- ---------- ---------- ------------------- --------------- --------------------------------
2z6faqbzrf9jg            0 YES :INSTRING1                    1         32 2016-10-01 22:00:32 VARCHAR2(32)    1
                           YES :INSTRING2                    2         32 2016-10-01 22:00:32 VARCHAR2(32)    1

                         1 YES :INSTRING1                    1         32 2016-10-01 22:00:32 VARCHAR2(32)    1
                           YES :INSTRING2                    2        128 2016-10-01 22:00:32 VARCHAR2(128)   1

                         2 YES :INSTRING1                    1         32 2016-10-01 22:00:32 VARCHAR2(32)    1
                           YES :INSTRING2                    2       2000 2016-10-01 22:00:32 VARCHAR2(2000)  1

                         3 YES :INSTRING1                    1         32 2016-10-01 22:00:32 VARCHAR2(32)    1
                           YES :INSTRING2                    2       4000 2016-10-01 22:00:32 VARCHAR2(4000)  1

                         4 YES :INSTRING1                    1        128 2016-10-01 22:00:32 VARCHAR2(128)   1
                           YES :INSTRING2                    2       4000 2016-10-01 22:00:32 VARCHAR2(4000)  1

                         5 YES :INSTRING1                    1       2000 2016-10-01 22:00:33 VARCHAR2(2000)  1
                           YES :INSTRING2                    2       4000 2016-10-01 22:00:33 VARCHAR2(4000)  1

                         6 YES :INSTRING1                    1       4000 2016-10-01 22:00:33 VARCHAR2(4000)  1
                           YES :INSTRING2                    2       4000 2016-10-01 22:00:33 VARCHAR2(4000)  1
14 rows selected.

SCOTT@test01p> select sql_id, child_number, executions  from v$sql where sql_id = '2z6faqbzrf9jg';
SQL_ID        CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
2z6faqbzrf9jg            0          1
                         1          1
                         2          1
                         3          1
                         4          4
                         5          4
                         6          4
7 rows selected.

时间: 2024-09-20 08:33:19

[20161001]绑定变量的分配长度5.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支持字串

[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

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

[20160224]绑定变量的分配长度.txt --如果绑定变量中字符串分配占用空间的长度变化,oracle会建立子光标. --昨天被别人问一个问题,通过例子来说明: 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------

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]& 代替冒号绑定变量.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