|
[20181229]关于字符串的分配问题.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下的.
--//突然想起我以前的测试,可以验证11g改动分界点,实际上是1001个字符.当时测试的链接如下:
--//http://blog.itpub.net/267265/viewspace-746524/? => [20160224]绑定变量的分配长度.txt
--//http://blog.itpub.net/267265/viewspace-1993495/ => [20121016]字符串长度与绑定变量的子光标.txt
--//里面提到1个情况，我当时没搞清楚，看完上面的链接一下明白过来,我通过重复测试来说明问题。
1.环境：
[email?protected]> @ ver1
PORT_STRING????????? VERSION??? BANNER?????????????????????????????????????????????????????????????????????? CON_ID
-------------------- ---------- ---------------------------------------------------------------------------- ------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production????? 0
[email?protected]> 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 1000
--//执行完成后确定sql_id=4mv1hkjru31tp
[email?protected]> 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
[email?protected]> @ 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 2018-12-30 20:18:38 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.测试二:
[email?protected]> alter system flush shared_pool ;
System altered.
--//修改参数1001,执行 @ len.txt 1001
[email?protected]> @ len.txt 1001
PL/SQL procedure successfully completed.
--//确定sql_id=as5nq40yutw9t
[email?protected]> select sql_text,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 as5nq40yutw9t??????????? 1???????? 96?????????? 0???? 1???????????? 0
select /*+ find_me 1001 */ count(*) from t where a=:instring as5nq40yutw9t??????????? 2??????? 872?????????? 0???? 1???????????? 0
[email?protected]> @ 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 2018-12-30 20:30:47 VARCHAR2(32)??? X
???????????????????????? 1 YES :INSTRING??????? 1??????? 128 2018-12-30 20:30:47 VARCHAR2(128)?? XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
???????????????????????? 2 YES :INSTRING??????? 1?????? 2000 2018-12-30 20:30:47 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确定).
3.继续测试:
--//面前的测试在PL/SQL进行的,在sqlplus测试看看.
[email?protected]> alter system flush shared_pool;
System altered.
variable instring varchar2(1000)
exec :instring := rpad(‘X‘,1);
Select /*+ find_me */ count(*) from t where a=:instring;
--//确定sql_id=383pcxarzpwbg.
[email?protected]> @ bind_cap 383pcxarzpwbg ‘‘
C200
-------------------------------------------------------
Select /*+ find_me */ count(*) from t where a=:instring
SQL_ID??????? CHILD_NUMBER WAS NAME?????????????????? POSITION MAX_LENGTH LAST_CAPTURED?????? DATATYPE_STRING VALUE_STRING
------------- ------------ --- -------------------- ---------- ---------- ------------------- --------------- ------------------
383pcxarzpwbg??????????? 0 YES :INSTRING???????????????????? 1?????? 2000 2018-12-30 20:42:17 VARCHAR2(2000)? X
[email?protected]> alter system flush shared_pool;
System altered.
variable instring varchar2(1001)
exec :instring := rpad(‘X‘,1);
Select /*+ find_me 1001x */ count(*) from t where a=:instring;
--//确定sql_id=fd4dr46guv82z
[email?protected]> @ bind_cap fd4dr46guv82z ‘‘C200-------------------------------------------------------------Select /*+ find_me 1001x */ count(*) from t where a=:instringSQL_ID??????? CHILD_NUMBER WAS NAME?????????????????? POSITION MAX_LENGTH LAST_CAPTURED?????? DATATYPE_STRING VALUE_STRING------------- ------------ --- -------------------- ---------- ---------- ------------------- --------------- --------------------fd4dr46guv82z??????????? 0 YES :INSTRING???????????????????? 1?????? 2000 2018-12-30 20:44:41 VARCHAR2(2000)? X--//可以看出sqlplus就不是这样,按照定义分配.而仅仅PL/sql比较特殊.存在1个1001分界点.4.上面文档是来自10g的官方文档.要找一个10g的版本重复测试看看,验证是否是2000.--//等上班找个10g的环境来测试看看.5.附上bind_cap.sql的脚本.$ cat bind_cap.sqlset verify offcolumn value_string format a50column datatype_string format a15break on sql_id on child_number? skip 1select? 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
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
