[20190102]关于字符串的分配问题(10g).txt

[20190102]关于字符串的分配问题(10g).txt --//链接：http://www.itpub.net/thread-2107534-1-1.html提到的问题，里面一段英文读起来很绕口： --//百度找到如下内容：https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/datatypes.htm VARCHAR2 Datatype You use the VARCHAR2 datatype to store variable-length character data. How the data is represented internally depends on the database character set. The VARCHAR2 datatype takes a required parameter that specifies a maximum size up to 32767 bytes. The syntax follows: VARCHAR2(maximum_size [CHAR  |  BYTE]) You cannot use a symbolic constant or variable to specify the maximum size; you must use an integer literal in the range 1 .. 32767. Small VARCHAR2 variables are optimized for performance, and larger ones are optimized for efficient memory use. The cutoff point is 2000 bytes. For a VARCHAR2 that is 2000 bytes or longer, PL/SQL dynamically allocates only enough memory to hold the actual value. For a VARCHAR2 variable that is shorter than 2000 bytes, PL/SQL preallocates the full declared length of the variable. For example, if you assign the same 500-byte value to a VARCHAR2(2000 BYTE) variable and to a VARCHAR2(1999 BYTE) variable, the former takes up 500 bytes and the latter takes up 1999 bytes. --//里面的内容来之这里.简单点就是小的varchar2变量优化为性能,大的varchar2变量优化内存使用.分界点在2000字节. --//贴一段金山词霸的翻译: 小的VARCHAR 2变量是为了性能而优化的，较大的变量是为了高效的内存使用而优化的。截止点是2000字节。对于2000字节或更长的 VARCHAR 2，PL/SQL动态分配的内存仅足以容纳实际值。对于小于2000字节的VARCHAR 2变量，PL/SQL将分配变量的完整声明长度。例如， 如果将相同的500字节值分配给VARCHAR 2(2000字节)变量和VARCHAR 2(1999字节)变量，则前者占500个字节，后者占1999年字节。 If you specify the maximum size in bytes rather than characters, a VARCHAR2(n) variable might be too small to hold n multibyte characters. To avoid this possibility, use the notation VARCHAR2(n CHAR) so that the variable can hold n characters in the database character set, even if some of those characters contain multiple bytes. When you specify the length in characters, the upper limit is still 32767 bytes. So for double-byte and multibyte character sets, you can only specify 1/2 or 1/3 as many characters as with a single-byte character set. Although PL/SQL character variables can be relatively long, you cannot insert VARCHAR2 values longer than 4000 bytes into a VARCHAR2 database column. You can insert any VARCHAR2(n) value into a LONG database column because the maximum width of a LONG column is 2147483648 bytes or two gigabytes. However, you cannot retrieve a value longer than 32767 bytes from a LONG column into a VARCHAR2(n) variable. Note that the LONG datatype is supported only for backward compatibility; see "LONG and LONG RAW Datatypes" more information. When you do not use the CHAR or BYTE qualifiers, the default is determined by the setting of the NLS_LENGTH_SEMANTICS initialization parameter. When a PL/SQL procedure is compiled, the setting of this parameter is recorded, so that the same setting is used when the procedure is recompiled after being invalidated. --//注意该文档是10gR2下的.放假前在12c上测试过,分界点是1001个字符. --//补充测试10g的情况: --//参考链接如下: --//http://blog.itpub.net/267265/viewspace-746524/  => [20160224]绑定变量的分配长度.txt  --//http://blog.itpub.net/267265/viewspace-1993495/ => [20121016]字符串长度与绑定变量的子光标.txt  --//http://blog.itpub.net/267265/viewspace-2287070/=> [20181229]关于字符串的分配问题.txt 1.环境： SCOTT@test> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- ---------------------------------------------------------------- x86_64/Linux 2.4.xx            10.2.0.4.0     Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi SCOTT@test> create table t (a varchar2(4000)); Table created. --//分析略. --//建立脚本len.txt declare instring varchar2(&&1);   begin    for i in 1..1000 loop      instring := rpad('X',i,'X');      execute immediate 'select /*+ find_me &&1 */ count(*) from t where a=:instring' using instring ;     end loop; end; / 2.测试一： --//执行 @ len.txt 1999 SCOTT@test> @ len.txt 1999 PL/SQL procedure successfully completed. --//执行完成后确定sql_id=4yqsjj26q8s1s SCOTT@test> select sql_text,sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id='4yqsjj26q8s1s'; SQL_TEXT                                                     SQL_ID        CHILD_NUMBER EXECUTIONS PARSE_CALLS      LOADS INVALIDATIONS ------------------------------------------------------------ ------------- ------------ ---------- ----------- ---------- ------------- select /*+ find_me 1999 */ count(*) from t where a=:instring 4yqsjj26q8s1s            0         32           1          1             0 select /*+ find_me 1999 */ count(*) from t where a=:instring 4yqsjj26q8s1s            1         96           0          1             0 select /*+ find_me 1999 */ count(*) from t where a=:instring 4yqsjj26q8s1s            2        872           0          1             0 --//很明显10g关于这个问题的分界点也不是1999或者2000. SCOTT@test> @ len.txt 1000 PL/SQL procedure successfully completed. --//确定sql_id=4mv1hkjru31tp SCOTT@test> select sql_text,sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id='4mv1hkjru31tp'; SQL_TEXT                                                     SQL_ID        CHILD_NUMBER EXECUTIONS PARSE_CALLS      LOADS INVALIDATIONS ------------------------------------------------------------ ------------- ------------ ---------- ----------- ---------- ------------- select /*+ find_me 1000 */ count(*) from t where a=:instring 4mv1hkjru31tp            0       1000           1          1             0 SCOTT@test> @ &r/bind_cap 4mv1hkjru31tp '' C200 ------------------------------------------------------------ select /*+ find_me 1000 */ count(*) from t where a=:instring SQL_ID        CHILD_NUMBER WAS NAME                   POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING ------------- ------------ --- -------------------- ---------- ---------- ------------------- --------------- ------------------- 4mv1hkjru31tp            0 YES :INSTRING                     1       2000 2019-01-02 08:50:28 VARCHAR2(2000)  X --//你可以发现查询v$sql_bind_capture视图,里面DATATYPE_STRING记录的是VARCHAR2(2000),而我定义的大小是varchar2(1000). --//当然oracle按照定义不会分配2000空间,而是最大1000. --//许多人都知道,如果字符串绑定变量长度变化会产生子光标. --//通过测试可以知道字符串的长度变化是32，32+96=128,32+96+1872=2000.也就是分4个段 1-32,33-128,129-2000,2001-4000. --//参考链接:http://blog.itpub.net/267265/viewspace-746524/ --//如果开始分配的字符串空间是按照实际使用大小来分配的,就会出现至少3个子光标的情况.而现在仅仅出现1个,说明oracle在开始执行就 --//分配1000个字符空间. 3.测试二: SCOTT@test01p> alter system flush shared_pool ; System altered. --//修改参数1001,执行 @ len.txt 1001 SCOTT@test> @ len.txt 1001 PL/SQL procedure successfully completed. --//确定sql_id=as5nq40yutw9t SCOTT@test> select sql_text,sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id='as5nq40yutw9t'; SQL_TEXT                                                     SQL_ID        CHILD_NUMBER EXECUTIONS PARSE_CALLS      LOADS INVALIDATIONS ------------------------------------------------------------ ------------- ------------ ---------- ----------- ---------- ------------- select /*+ find_me 1001 */ count(*) from t where a=:instring as5nq40yutw9t            0         32           1          1             0 select /*+ find_me 1001 */ count(*) from t where a=:instring                          1         96           0          1             0 select /*+ find_me 1001 */ count(*) from t where a=:instring                          2        872           0          1             0 SCOTT@test> @ &r/bind_cap as5nq40yutw9t '' C200 ------------------------------------------------------------ select /*+ find_me 1001 */ count(*) from t where a=:instring SQL_ID        CHILD_NUMBER WAS NAME                   POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING ------------- ------------ --- -------------------- ---------- ---------- ------------------- --------------- -------------------------------------------------- as5nq40yutw9t            0 YES :INSTRING                     1         32 2019-01-02 08:53:38 VARCHAR2(32)    X                          1 YES :INSTRING                     1        128 2019-01-02 08:53:38 VARCHAR2(128)   XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX                          2 YES :INSTRING                     1       2000 2019-01-02 08:53:38 VARCHAR2(2000)  XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX                                                                                                               XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX                                                                                                               XXXXXXXXXXXXXXXXXXXXXXXXXXXXX --//我仅仅修改字符串的定义varchar2(1001),就导致相似的语句产生3个子光标。 --//说明一个问题当字符串长度大于1000时,oracle字符串的分配按需来分配,这样就会出现3个子光标的情况. --//从执行次数上可以看出长度变化1-32, 33- 128(32+96),129-2000(2000可以从v$sql_bind_capture视图的DATATYPE_STRING确定). --//可以看出10g与11g,12c版本一样,分界点是1001.估计oracle文档更新不及时. 4.附上bind_cap.sql的脚本. $ 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 and name=nvl('&&2',name)  order by child_number,was_captured,position; break on sql_id on child_number  skip 0