[20221103]繫結變數的分配長度11.txt

[20221103]繫結變數的分配長度11.txt

--//https://nenadnoveljic.com/blog/bind_variable_length/
--//很奇怪作者的測試結果,重複測試看看.

1.環境:
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

$ env | grep -i nls
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
NLS_TIME_TZ_FORMATx=HH24.MI.SSXFF TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT=YYYY-MM-DD HH24:MI:SS.FF TZH:TZM
NLS_TIMESTAMP_FORMAT=YYYY-MM-DD HH24:MI:SS.FF
NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS

--//$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

2.測試1:
SCOTT@book> create table t (c varchar2(4000));
Table created.

var v varchar2(20)
exec :v := 'AAAA'
select * from t where c=:v ;

var v varchar2(90)
exec :v := 'AAAA'
select * from t where c=:v ;

SCOTT@book> select datatype_string from v$sql_bind_capture where  sql_id = '5a8bp2fr4v6w9' ;
DATATYPE_STRING
------------------------------
VARCHAR2(128)
VARCHAR2(32)

--//我並沒有做出作者的測試結果,我估計與字符集有關.修改環境變數NLS_LANG字符集看看.

3.測試2:

$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

--//重啟資料庫,重複測試:

var v varchar2(20)
exec :v := 'AAAA'
select * from t where c=:v ;

var v varchar2(33)
exec :v := 'AAAA'
select * from t where c=:v ;

SCOTT@book> select datatype_string from v$sql_bind_capture where  sql_id = '5a8bp2fr4v6w9' ;
DATATYPE_STRING
---------------
VARCHAR2(128)

--// 128/4 = 32
--// 128/2 = 64

var v varchar2(65)
exec :v := 'AAAA'
select * from t where c=:v ;

SCOTT@book> select datatype_string from v$sql_bind_capture where  sql_id = '5a8bp2fr4v6w9' ;
DATATYPE_STRING
------------------------------------------------------------------------------------------
VARCHAR2(128)

SCOTT@book> select datatype_string from v$sql_bind_capture where  sql_id = '5a8bp2fr4v6w9' ;
DATATYPE_STRING
------------------------------------------------------------------------------------------
VARCHAR2(2000)
VARCHAR2(128)

--//可以發現在sqlplus下設定varchar2(65),出現第2個子游標.

4.使用10046跟蹤看看.
--//退出,重新登入執行:
@ 10046on 12
var v varchar2(65)
exec :v := 'AAAA'
select * from t where c=:v ;
@ 10046off

=====================
PARSING IN CURSOR #139902619393960 len=26 dep=0 uid=83 oct=47 lid=83 tim=1667437617687128 hv=643001901 ad='7ce9b0e8' sqlid='c9uhnw4m56vjd'
BEGIN :v := 'AAAA'; END;
END OF STMT
PARSE #139902619393960:c=0,e=103,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,plh=0,tim=1667437617687121
BINDS #139902619393960:
 Bind#0
  oacdty=01 mxl=2000(130) mxlc=00 mal=00 scl=00 pre=00
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  oacflg=03 fl2=1000000 frm=01 csi=852 siz=2000 off=0
  kxsbbbfp=7f3d9dee8f90  bln=2000  avl=00  flg=05
WAIT #139902619393960: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=407 tim=1667437617687401
EXEC #139902619393960:c=0,e=190,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=3,plh=0,tim=1667437617687433

*** 2022-11-03 09:07:01.094
WAIT #139902619393960: nam='SQL*Net message from client' ela= 3407381 driver id=1650815232 #bytes=1 p3=0 obj#=407 tim=1667437621094893
CLOSE #139902619393960:c=0,e=24,dep=0,type=0,tim=1667437621095029

--//摘要原始連結 :https://nenadnoveljic.com/blog/bind_variable_length/
--//注意對方的定義是var v varchar2(90)
In fact, we're dealing with two sizes. One is the actual size of the passed data, which is 4 for the string "AAAA". It's
stored in the field avl, as documented in Cary Millsap's book The Method R Guide to MASTERING ORACLE TRACE DATA. We
won't consider the actual size further, because it's irrelevant for the optimizer. The value of interest is mxl, which
possibly stands for "maximum length". Unlike avl, mxl contains the bind vaiable length, and it is relevant for the
optimizer. It consists of two values: 2000(270). 270 determines in which bucket the bind variable value will be placed.
2000 is the upper limit of the bucket. Simply put, 270 belongs to the bucket with the upper limit 2000.

事實上，我們要處理的是兩種尺寸的問題。一個是傳遞資料的實際大小，字串"AAAA"為4。它儲存在avl領域中，正如卡里·米爾薩普的
書《掌握甲骨文跟蹤資料的方法R指南》中所記錄的那樣。我們不會進一步考慮實際大小，因為它與最佳化器無關。利息值為mxl，可能代表
最大長度。與avl不同，mxl包含繫結可訪問的長度，並且它與最佳化器相關。它由兩個值組成：2000(270)。270確定將繫結變數值放置在哪
個桶中。2000年是該桶的上限。簡單地說，270屬於上限2000。

The question is: where did the value 270 come from? The variable length in declation, i.e. 90, was multiplied by 3
(90*3=270). Optimizer uses 270 for deciding in which bucket to place the bind variable. To understand where the
multiplication by 3 had came from, I did an experiment using the Oracle Call Interface (OCI) with C. In C we have the
highest degree of control of what's being sent to the database.

問題是：270的值從何而來？在解密過程中的可變長度，即90，乘以3(90*3=270)。最佳化器使用270來決定將bind變數放入哪個桶中。為了
理解3的乘法從何而來，我使用Oracle Call介面(OCI)做了一個實驗。在C語言中，我們對傳送到資料庫的內容有最高程度的控制。

--//我的測試是65*2=130.看下劃線mxl=2000(130),超過128限制.

5.繼續:
--//退出會話,重新設定NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

alter system flush shared_pool;
alter system flush shared_pool;
alter system flush shared_pool;

var v varchar2(65)
exec :v := 'AAAA'
select * from t where c=:v ;

SCOTT@book> select datatype_string from v$sql_bind_capture where  sql_id = '5a8bp2fr4v6w9' ;
DATATYPE_STRING
------------------------------
VARCHAR2(128)
--//說明確實與字符集相關.不過我的測試是*2,不知道對方如何定義NLS_LANG.

6.補充測試PL/SQL的情況:
--//設定NLS_LANG=AMERICAN_AMERICA.AL32UTF8
$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
--//重啟資料庫略.

$ cat aa.txt
declare
instring varchar2(&&1);
  begin
   for i in 1..4000 loop
     instring := rpad('X',i,'X');
     execute immediate 'select /*+ find_me &&1 */ count(*) from t where c=:instring' using instring ;
   end loop;
end;
/

SCOTT@book> @ aa.txt 4000
PL/SQL procedure successfully completed.

SCOTT@book> select sql_text c100 ,sql_id from v$sqlarea where sql_text like '%find_me 4000%' and sql_text not like 'declare%';
C100                                                                                                 SQL_ID
---------------------------------------------------------------------------------------------------- ---------------------------------------
select /*+ find_me 4000 */ count(*) from t where c=:instring                                         7j53xh0dks9aq
select sql_text c100 ,sql_id from v$sqlarea where sql_text like '%find_me 4000%' and sql_text not li cdd1wn4wkhy7d
ke 'declare%'
--//sql_id=7j53xh0dks9aq

SCOTT@book> @ bind_cap 7j53xh0dks9aq ''
SQL_ID        CHILD_NUMBER WAS_CAPTU NAME      POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING
------------- ------------ --------- ------------------ ---------- ------------------- --------------- --------------------------------------------------
7j53xh0dks9aq            0 YES       :INSTRING        1         32 2022-11-03 09:21:29 VARCHAR2(32)    X

                         1 YES       :INSTRING        1        128 2022-11-03 09:21:29 VARCHAR2(128)   XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

                         2 YES       :INSTRING        1       2000 2022-11-03 09:21:29 VARCHAR2(2000)  XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
                                                                                                       XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
                                                                                                       XXXXXXXXXXXXXXXXXXXXXXXXXXXXX

                         3 YES       :INSTRING        1       4000 2022-11-03 09:21:29 VARCHAR2(4000)  XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
                                                                                                         ...
                                                                                                       X
SCOTT@book> select executions,child_number from v$sql where sql_id='7j53xh0dks9aq';
EXECUTIONS CHILD_NUMBER
---------- ------------
        32            0
        96            1
      1872            2
      2000            3
--//可以看出PL/sql的字串分配與sqlplus的不同.PL/SQL按照實際大小分配的.注意我定義的instring varchar2(4000),按實際使用分配.
--//如果定義instring varchar2(1000),PL/SQL按照1000單位來分配的;
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.
--// I think cutoff point is 1000,not 2000.

--//我開始以為是char,byte的區別,不過我的測試不對.

$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
SCOTT@book> var v varchar2(65 bytes)
--//注故意寫錯.
Usage: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
                    VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
                    NVARCHAR2 (n) | CLOB | NCLOB | BLOB | BFILE
                    REFCURSOR | BINARY_FLOAT | BINARY_DOUBLE ] ]

--//存在兩種單位,char,byte.

SCOTT@book> var v varchar2(65 byte)
SCOTT@book> exec :v := 'BBBB'
PL/SQL procedure successfully completed.
SCOTT@book> Select * from t where c=:v ;
no rows selected

SCOTT@book> @ hash
HASH_VALUE SQL_ID                                  CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID
---------- --------------------------------------- ------------ ---------- --------------- ---------- ------------------- -----------
 843118768 64pdsa8t41y5h                                      0      63664      1601196873  3240f8b0  2022-11-03 09:30:55    16777216

SCOTT@book> select datatype_string from v$sql_bind_capture where  sql_id = '64pdsa8t41y5h' ;
DATATYPE_STRING
------------------------------------------------------------------------------------------
VARCHAR2(2000)

SCOTT@book> var v varchar2(65 char)
SCOTT@book> SElect * from t where c=:v ;
no rows selected

SCOTT@book> @ hash
HASH_VALUE SQL_ID                                  CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID
---------- --------------------------------------- ------------ ---------- --------------- ---------- ------------------- -----------
3433558012 bf4kz0b6agvzw                                      0     126972      1601196873  cca7effc  2022-11-03 09:31:52    16777216

SCOTT@book> select datatype_string from v$sql_bind_capture where  sql_id = 'bf4kz0b6agvzw' ;
DATATYPE_STRING
------------------------------------------------------------------------------------------
VARCHAR2(2000)

--//都是一開始分配2000.探究這個太複雜,放棄!!