[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.探究这个太复杂,放弃!!