[20250729]建立绑定变量的字符串分配长度测试脚本.txt

[20250729]建立绑定变量的字符串分配长度测试脚本.txt

--//正好别人问关于绑定变量的字符串分配长度问题，重新整理以前写的测试脚本：

$ cat txt/bind_varchar_len.txt
--//用于测试PLSQL字符串分配分界点

--// create table t (a varchar2(4000));
declare
v_a varchar2(&&1);
  begin
   for i in 1..&&1 loop
     v_a := rpad('X',i,'X');
     execute immediate 'select /*+ find_me &&1 */ count(*) from t where a=:v_a' using v_a ;
     --//execute immediate 'select /*+ find_me &&1 */ :v_a from dual' using v_a ;
   end loop;
end;
/

column sql_id new_value v_sql_id
SELECT sql_text
      ,sql_id
      ,child_number
      ,executions
      ,parse_calls
      ,loads
      ,invalidations
  FROM v$sql
 WHERE sql_text LIKE '%find_me &&1%' AND sql_text NOT LIKE '%v$sql%' AND sql_text NOT LIKE 'declare%';

select sql_id ,CHILD_NUMBER,DATATYPE_STRING from v$sql_bind_capture WHERE   sql_id = '&v_sql_id' order by 2 ;

--//测试如下：

1.环境：
SCOTT@book01p> @ ver2
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 21.0.0.0.0
BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

create table t (a varchar2(4000));

2.测试1：
--//注意PLSQL支持32767的字符串长度。
--//alter system flush shared_pool;

SCOTT@book01p> @ txt/bind_varchar_len.txt 32767
PL/SQL procedure successfully completed.

SQL_TEXT                                                     SQL_ID        CHILD_NUMBER EXECUTIONS PARSE_CALLS      LOADS INVALIDATIONS
------------------------------------------------------------ ------------- ------------ ---------- ----------- ---------- -------------
select /*+ find_me 32767 */ count(*) from t where a=:v_a     8yjbtkhkgm33x            0         32           1          1             0
select /*+ find_me 32767 */ count(*) from t where a=:v_a     8yjbtkhkgm33x            1         96           0          1             0
select /*+ find_me 32767 */ count(*) from t where a=:v_a     8yjbtkhkgm33x            2       1872           0          1             0
select /*+ find_me 32767 */ count(*) from t where a=:v_a     8yjbtkhkgm33x            3       2000           0          1             0
select /*+ find_me 32767 */ count(*) from t where a=:v_a     8yjbtkhkgm33x            4       4192           0          1             0
select /*+ find_me 32767 */ count(*) from t where a=:v_a     8yjbtkhkgm33x            5       8194           0          1             0
select /*+ find_me 32767 */ count(*) from t where a=:v_a     8yjbtkhkgm33x            6      16381           0          1             0
7 rows selected.

SQL_ID        CHILD_NUMBER DATATYPE_STRING
------------- ------------ ------------------------------
8yjbtkhkgm33x            0 VARCHAR2(32)
8yjbtkhkgm33x            1 VARCHAR2(128)
8yjbtkhkgm33x            2 VARCHAR2(2000)
8yjbtkhkgm33x            3 VARCHAR2(4000)
8yjbtkhkgm33x            4 VARCHAR2(8192)
8yjbtkhkgm33x            5 VARCHAR2(16386)
8yjbtkhkgm33x            6 VARCHAR2(32767)
7 rows selected.

--//从执行次数上可以看出字符串长度变化而导致的子光标情况。
32
32+96 = 128
32+96+1872 = 2000
32+96+1872+2000 = 4000
32+96+1872+2000+4192 = 8192
32+96+1872+2000+4192+8194 = 16386
32+96+1872+2000+4192+8194+16381 = 32767

3.测试2：
--//PLSQL字符串分配有一个分界点，如果定义字符串长度小于等于1000时，按照定义的长度分配，比如定义为varchar2(1000),分配的空
--//间是1000字符分配。这样的情况就不会产生前面测试出现子光标的情况。

SCOTT@book01p> @ txt/bind_varchar_len.txt 1000
PL/SQL procedure successfully completed.

SQL_TEXT                                                     SQL_ID        CHILD_NUMBER EXECUTIONS PARSE_CALLS      LOADS INVALIDATIONS
------------------------------------------------------------ ------------- ------------ ---------- ----------- ---------- -------------
select /*+ find_me 1000 */ count(*) from t where a=:v_a      62cyz2kd2qu1a            0       1000           1          1             0

SQL_ID        CHILD_NUMBER DATATYPE_STRING
------------- ------------ ------------------------------
62cyz2kd2qu1a            0 VARCHAR2(2000)

--//如果定义字符串长度大于1000时，不是按照定义的长度分配，而且按照实际需求分配使用空间，比如定义为varchar2(1001),如果
--//实际的字符串长度为33，实际占用空间是33.这样的情况就会产生前面测试出现子光标的情况。
--//注意：我前面提到的情况没有考虑字符集的情况，字符串定义单位byte。

SCOTT@book01p> @ txt/bind_varchar_len.txt 1001
PL/SQL procedure successfully completed.

SQL_TEXT                                                     SQL_ID        CHILD_NUMBER EXECUTIONS PARSE_CALLS      LOADS INVALIDATIONS
------------------------------------------------------------ ------------- ------------ ---------- ----------- ---------- -------------
select /*+ find_me 1001 */ count(*) from t where a=:v_a      gwm6t1hu54cuc            0         32           1          1             0
select /*+ find_me 1001 */ count(*) from t where a=:v_a      gwm6t1hu54cuc            1         96           0          1             0
select /*+ find_me 1001 */ count(*) from t where a=:v_a      gwm6t1hu54cuc            2        873           0          1             0


SQL_ID        CHILD_NUMBER DATATYPE_STRING
------------- ------------ ------------------------------
gwm6t1hu54cuc            0 VARCHAR2(32)
gwm6t1hu54cuc            1 VARCHAR2(128)
gwm6t1hu54cuc            2 VARCHAR2(2000)


--//如果再次执行@ txt/bind_varchar_len.txt 1001，执行次数各个子光标的情况如何呢？

SCOTT@book01p> @ txt/bind_varchar_len.txt 1001
PL/SQL procedure successfully completed.


SQL_TEXT                                                     SQL_ID        CHILD_NUMBER EXECUTIONS PARSE_CALLS      LOADS INVALIDATIONS
------------------------------------------------------------ ------------- ------------ ---------- ----------- ---------- -------------
select /*+ find_me 1001 */ count(*) from t where a=:v_a      gwm6t1hu54cuc            0         32           1          1             0
select /*+ find_me 1001 */ count(*) from t where a=:v_a      gwm6t1hu54cuc            1         96           0          1             0
select /*+ find_me 1001 */ count(*) from t where a=:v_a      gwm6t1hu54cuc            2       1874           1          1             0

SQL_ID        CHILD_NUMBER DATATYPE_STRING
------------- ------------ ------------------------------
gwm6t1hu54cuc            0 VARCHAR2(32)
gwm6t1hu54cuc            1 VARCHAR2(128)
gwm6t1hu54cuc            2 VARCHAR2(2000)

--//第2次执行次数是1001，实际上全部使用的是chile_number=2的执行计划，873+1001=1874,说明在探查子光标时先遇到
--//chile_number=2，满足需求，直接使用。估计在父游标堆0保持一个列表或者数组，记录各个子光标的情况。
--//如果child_number=0,1消失，再有新的子光标产生，如果还是字符串长度的原因，长度应该大于2000，会占用child_number=0,1的位
--//置,这种情况测试比较困难，放弃。
--//CHILD_NUMBER=2,DATATYPE_STRING=VARCHAR2(2000).这就好比大房子合适不会选择小房子。