The bound variable hierarchy refers to Oracle stay plsql According to the definition length of text binding variables, these text binding variables are divided into 4 Level .
a、 Define the length in 32 Text bound variables within bytes are classified at the first level
b、 The length is in 33-128 Between bytes is the second level
c、 The length is in 129-2000 The third level is between bytes
d、 The length is in 2000 Bytes above are divided into a fourth level
The fourth level is the memory space allocated by text binding variables , Depending on the size of the actual bound variable value passed in by the corresponding text line bound variable . To be specific ： If the actual value of the passed in binding variable is less than or equal to 2000 byte , be Oracle Distribution for them 2000 Byte memory space , If it is greater than 2000 byte , Then it is allocated 4000 Byte memory space .
Bound variable rating Only for text bound variables , It means Oracle I don’t know about numerical types number Type of binding variable to do binding variable grading .
about plsql The target of the text binding variable is used in the code SQL Come on , as long as SQL The definition length of text binding variable in text changes , The size of the allocated memory space may also change , So once Oracle The amount of memory allocated for these binding variables has changed , Then the SQL Previously stored child cursor The parse tree and execution plan in can’t be reused . as a result of child cursor In addition to storing targets SQL The parse tree and execution plan of , It also stores the SQL The type and length of the binding variable used , It means that even if it’s time to SQL Of SQL There is no change in the text , As long as it SQL The definition length of text bound variables in text has changed , Then the SQL When it is executed again, it may still be hard parsing .
The following example is a hierarchical operation of binding variables :
Create a table t, Two , Column n by number, Column v by varchar2(3000), And then insert five pieces of data in different bytes , among v The fields are varchar2(32),varchar2(33),varchar2(129),varchar2(2001),varchar2(32767)
The query results are as follows
SQL> select * from t; N V---------- ------------------------------ 1 didu1 2 didu2 3 didu3 4 didu4 5 didu5 SQL>
Due to the insertion of the second 4,5 When you have data v The value is only 5 Bytes , So actually Oracle It will only be allocated 2000 Byte memory space , That is to say, execution paradigm plsql Code 4 and 5 Can use soft parsing , Soft analysis , Because it will continue to use 3 Parse tree and execution plan of code .
Check the target SQL It’s the same as parent cursor：
SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like ‘insert into t%’;
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
———————————————————— ————————– ————- ———-
insert into t values(:n,:v) 21mycdpm39kzv 3 5
That goal SQL Do it 5 Time , Corresponding parent cursor There are three of them child cursor
SQL> select address,bind_name,position,datatype,max_length from v$sql_bind_metadata where address=’00007FF771A1DE68′ order by position;
ADDRESS BIND_NAME POSITION DATATYPE MAX_LENGTH
—————- ———————————————————— ———- ———- ———-
00007FF771A1DE68 N 1 2 22
00007FF771A1DE68 V 2 1 32
SQL> select address,bind_name,position,datatype,max_length from v$sql_bind_metadata where address=’00007FF774A51260′ order by position;
ADDRESS BIND_NAME POSITION DATATYPE MAX_LENGTH
—————- ———————————————————— ———- ———- ———-
00007FF774A51260 N 1 2 22
00007FF774A51260 V 2 1 128
SQL> select address,bind_name,position,datatype,max_length from v$sql_bind_metadata where address=’00007FF771359A60′ order by position;
ADDRESS BIND_NAME POSITION DATATYPE MAX_LENGTH
—————- ———————————————————— ———- ———- ———-
00007FF771359A60 N 1 2 22
00007FF771359A60 V 2 1 2000
In terms of the results ,child cursor 0 Chinese text binding variables v It was assigned 32 Byte memory space ,child cursor 1 Bound variable v Was assigned 128 Byte memory space ,child cursor 2 Bound variable v Was assigned 2000 Byte memory space , But numeric binding variables are also assigned 22 Byte memory space .
SQL> declare 2 n number(10); 3 v varchar2(2002); 4 begin 5 n := 6; 6 v := rpad('didu6',2002,'6'); 7 execute immediate 'insert into t values (:n,:v)' using n,v; 8 commit; 9 end; 10 / PL/SQL Process completed successfully . SQL> select n,length(v) from t; N LENGTH(V)---------- ---------- 1 5 2 5 3 5 4 5 5 5 6 2002
The above example code is executed as before SQL sentence , It’s just the actual incoming v The length of the value of has changed , Due to the insertion of v The value is greater than 2000, therefore Oracle Will bind variables for v Distribute 4000 Byte memory space , So this insertion will use hard parsing , So target SQL Corresponding parent cursor There should be 4 individual child cursor
Verify as follows ：
SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like ‘insert into t%’;
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
———————————————————— ————————– ————- ———-
insert into t values(:n,:v) 21mycdpm39kzv 4 6
And then take a look at this extra child cursor Specific information ：
SQL> select sql_id,child_number,child_address from v$sql where sql_id=’21mycdpm39kzv’;
SQL_ID CHILD_NUMBER CHILD_ADDRESS
————————– ———— —————-
21mycdpm39kzv 0 00007FF771A1DE68
21mycdpm39kzv 1 00007FF774A51260
21mycdpm39kzv 2 00007FF771359A60
21mycdpm39kzv 3 00007FF775992650
SQL> select address,bind_name,position,datatype,max_length from v$sql_bind_metadata where address=’00007FF775992650′ order by position;
ADDRESS BIND_NAME POSITION DATATYPE MAX_LENGTH
—————- ———————————————————— ———- ———- ———-
00007FF775992650 N 1 2 22
00007FF775992650 V 2 1 4000
From the above results we can see that ,child cursor 3 Chinese bound variables v It was assigned 4000 Byte memory space .
So come to the conclusion ：
To avoid unnecessary hard parsing , stay plsql The code deals with targets with text bound variables SQL when , The definition length of these text bound variables should be kept at the same level , Of course , The best definition here is uniform length , such as varchar2(4000).