[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

[20181229]關於字串的分配問題.txt

相關文章