[20181229]關於字元串的分配問題.txt--//鏈接：http://www.itpub.net/thread-2107534-1-1.html提到的問題，裡面一段英文讀起來很繞口：--//百度找到如下內容：https://docs.oracle.com/cd/B19306_01/appdev ...
[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.環境：
SCOTT@test01p> @ 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
SCOTT@test01p> 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
SCOTT@test01p> 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@test01p> @ 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.測試二:
SCOTT@test01p> alter system flush shared_pool ;
System altered.
--//修改參數1001,執行 @ len.txt 1001
SCOTT@test01p> @ len.txt 1001
PL/SQL procedure successfully completed.
--//確定sql_id=as5nq40yutw9t
SCOTT@test01p> 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 as5nq40yutw9t 1 96 0 1 0
select /*+ find_me 1001 */ count(*) from t where a=:instring as5nq40yutw9t 2 872 0 1 0
SCOTT@test01p> @ 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測試看看.
SCOTT@test01p> 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.
SCOTT@test01p> @ 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
SCOTT@test01p> 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
SCOTT@test01p> @ bind_cap fd4dr46guv82z ''
C200
-------------------------------------------------------------
Select /*+ find_me 1001x */ count(*) from t where a=:instring
SQL_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.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
