Oracle variable binding

Bind peeking is a new feature introduced in Oracle 9i and continues until Oracle 10g R2. It is used to view the value of the current SQL predicate during hard Analysis of SQL statements.
To generate the best execution plan. In versions earlier than Oracle 9i, Oracle only makes execution plans based on statistics.

I. Variable binding
Use the value at the first SQL run to generate an execution plan. The first execution plan is used to run the SQL statement again.
Affected Versions: Oracle 9i, Oracle 10g
Binding variables to special values in variable columns or non-even distribution columns may lead to the selection and execution of inefficient execution plans.

Note that bind peeking only occurs during hard analysis, that is, when the SQL is executed for the first time, the subsequent variables will not be peeking. We can see that bind
Peeking does not ultimately solve the problem of selecting different execution plans due to different predicates. It can only make the Execution Plan Selection more accurate at the first execution of SQL statements and cannot help OLAP
The system solves the problem of incorrect Execution Plan Selection caused by variable binding. This is one reason why OLAP should not bind variables.

More specifically, the Bind Variable Snoop is in the Physical Phase of SQL parsing. The query optimizer will snoop the value of the Bind Variable and use it as a literal. That is, the first Oracle resolution
In SQL, the actual values of the variables are substituted into the execution plan, and the first survival execution plan is used for all SQL statements using the bound variable. So? What is the performance?
The result is that the execution plan is not optimal. This problem was solved in Oracle 11g.
See: Oracle adaptive shared cursor

Ii. Example of variable binding
1. Create a demo Environment

SQL> select * from V $ version where rownum <2; --> view the current database version </P> <p> banner <br/> release <br/> Oracle Database 10g Release 10.2.0.3.0-64bit production </P> <p> SQL> Create Table T (ID, owner, object_id) as --> Create test table t <br/> 2 select rownum, owner, object_id from all_objects where rownum <= 1000; </P> <p> SQL> ALTER TABLE t add constraint t_pk primary key (ID ); --> Add a primary key for table t </P> <p> SQL> begin --> collect statistics, no histogram information is generated here <br/> 2 dbms_stats.gather_table_stats (<br/> 3 ownname => 'Scott ', <br/> 4 tabname => 'T ', <br/> 5 estimate_percent => 100, <br/> 6 method_opt => 'for all columns size 1'); <br/> 7 end; <br/> 8/</P> <p> PL/SQL procedure successfully completed. </P> <p> SQL> select count (ID), count (distinct ID), min (ID), max (ID) from T; --> View the distribution of values </P> <p> count (ID) count (distinctid) min (ID) max (ID) <br/> ---------- ------------------- ---------- <br/> 1000 1000 1 1000 <br/>

2. Execution Plan of the SQL statement without binding Variables

SQL> select sum (object_id) from t where ID <900; --> Publish an SQL query statement </P> <p> sum (object_id) <br/> -------------- <br/> 446549 </P> <p> SQL> select * from table (dbms_xplan.display_cursor (); --> according to its execution plan, the current SQL statement uses full table scan <br/> /************************** * **********************/<br/>/* Author: robinson Cheng */<br/>/* blog: http://blog.csdn.net/robinson_0612 */<br/>/* MSN: robinson_0612@hotmail.com */<br/>/* QQ: 645746311 */<br/> /******************************** * ****************/</P> <p> plan_table_output <br/> preview <br/> SQL _id bz6h6fdsxgjka, child number 0 <br/> ----------------------------------- <br/> select sum (object_id) from t where ID <900 </P> <p> plan hash value: 2966233522 </P> <p> --------------------------------------------------------------------------- <br/> | ID | operation | Name | rows | bytes | cost (% CPU) | time | <br/> --------------------------------------------------------------------------- <br/> | 0 | SELECT statement | 3 (100) | <br/> | 1 | sort aggregate | 1 | 8 | <br/> | * 2 | table access full | T | 900 | 7200 | 3 (0) | 00:00:01 | <br/> --------------------------------------------------------------------------- </P> <p> predicate information (identified by Operation ID ): <br/> ----------------------------------------------- </P> <p> 2-filter ("ID" <900) </P> <p> SQL> select sum (object_id) from t where ID <10; --> publish another SQL query statement </P> <p> SQL> select * from table (dbms_xplan.display_cursor ()); --> at this time, the execution plan generated by the query goes through the index range scan <br/> --> because the literal quantity is different, therefore, two SQL statements generate different SQL _id and execution plan <br/> plan_table_output <br/> values <br/> SQL _id 6y2280pyvacfq, child number 0 <br/> ----------------------------------- <br/> select sum (object_id) from t where ID <10 </P> <p> plan hash value: 4270555908 </P> <p> bytes <br/> | ID | operation | Name | rows | bytes | cost (% CPU) | time | <br/> latency <br/> | 0 | SELECT statement | 3 (100) | <br/> | 1 | sort aggregate | 1 | 8 | <br/> | 2 | table access by index rowid | T | 9 | 72 | 3 (0) | 00:00:01 | <br/> | * 3 | index range scan | t_pk | 9 | 2 (0) | 00:00:01 | <br/> identified </P> <p> predicate information (identified by Operation ID ): <br/> ------------------------------------------------- </P> <p> 3-access ("ID" <10) <br/>

3. Execution Plan with variable binding

SQL> variable v_id number; --> defines the binding variable <br/> SQL> Exec: v_id: = 900; --> assign values to the bound variable </P> <p> PL/SQL procedure successfully completed. </P> <p> SQL> select sum (object_id) from t where ID <: v_id; </P> <p> sum (object_id) <br/> -------------- <br/> 446549 </P> <p> SQL> select * from table (dbms_xplan.display_cursor ()); --> at this time, the last SQL statement went through the full table scan, and its SQL _id was 7qcp6urq1_d2j </P> <p> plan_table_output <br/> limit <br/> SQL _id 7qcp6urq1_d2j, child number 0 <br/> ----------------------------------- <br/> select sum (object_id) from t where ID <: v_id </P> <p> plan hash value: 2966233522 </P> <p> --------------------------------------------------------------------------- <br/> | ID | operation | Name | rows | bytes | cost (% CPU) | time | <br/> --------------------------------------------------------------------------- <br/> | 0 | SELECT statement | 3 (100) | <br/> | 1 | sort aggregate | 1 | 8 | <br/> | * 2 | table access full | T | 900 | 7200 | 3 (0) | 00:00:01 | <br/> --------------------------------------------------------------------------- </P> <p> predicate information (identified by Operation ID ): <br/> ------------------------------------------------- </P> <p> 2-filter ("ID" <: v_id) --> the predicate information indicates that the binding variable is used. </P> <p> SQL> Exec: v_id: = 10; --> assign a value to the bound variable </P> <p> PL/SQL procedure successfully completed. </P> <p> SQL> select sum (object_id) from t where ID <: v_id; --> execute the SQL statement again </P> <p> sum (object_id) <br/> -------------- <br/> 254 </P> <p> SQL> select * from table (dbms_xplan.display_cursor ()); --> in this case, full table scan is still selected in the execution plan <br/> --> its SQL _id is the same as the SQL statement that was run the same time, this means that the full sharing is achieved. <br/> plan_table_output --> If id <10 is not used to bind a variable, the index range is used for scanning. <br/> --------------------------------- --> therefore, not the best execution plan executed <br/> SQL _id 7qcp6urq1_d2j, Child number 0 <br/> ------------------------------------- <br/> select sum (object_id) from t where ID <: v_id </P> <p> plan hash value: 2966233522 </P> <p> --------------------------------------------------------------------------- <br/> | ID | operation | Name | rows | bytes | cost (% CPU) | time | <br/> --------------------------------------------------------------------------- <br/> | 0 | SELECT statement | 3 (100) | <br/> | 1 | sort aggregate | 1 | 8 | <br/> | * 2 | table access full | T | 900 | 7200 | 3 (0) | 00:00:01 | <br/> --------------------------------------------------------------------------- </P> <p> predicate information (identified by Operation ID ): <br/> ----------------------------------------------- </P> <p> 2-filter ("ID" <: v_id) </P> <p> SQL> alter system flush shared_pool; --> clear the shared pool. The shared parent cursor and child cursor are all released </P> <p> SQL> Print v_id; </P> <p> v_id <br/> ---------- <br/> 10 </P> <p> SQL> select round (AVG (object_id )) from t where ID <: v_id; --> use ID <10 to execute the SQL statement </P> <p> round (AVG (object_id )) <br/> --------------------- <br/> 28 </P> <p> SQL> select * from table (dbms_xplan.display_cursor ()); --> at this time, the SQL statement uses the best execution plan, that is, scanning by index range </P> <p> plan_table_output <br/> limit <br/> SQL _id 0bx53mgt4qqnt, child number 0 <br/> ----------------------------------- <br/> select round (AVG (object_id) from t where ID <: v_id </P> <p> plan hash value: 4270555908 </P> <p> bytes <br/> | ID | operation | Name | rows | bytes | cost (% CPU) | time | <br/> latency <br/> | 0 | SELECT statement | 3 (100) | <br/> | 1 | sort aggregate | 1 | 8 | <br/> | 2 | table access by index rowid | T | 9 | 72 | 3 (0) | 00:00:01 | <br/> | * 3 | index range scan | t_pk | 9 | 2 (0) | 00:00:01 | <br/> identified </P> <p> predicate information (identified by Operation ID ): <br/> ----------------------------------------------- </P> <p> 3-access ("ID" <: v_id) </P> <p> SQL> Exec: v_id: = 900; --> new value for the variable </P> <p> PL/SQL procedure successfully completed. </P> <p> SQL> select round (AVG (object_id) from t where ID <: v_id; </P> <p> round (AVG (object_id )) <br/> --------------------- <br/> 497 </P> <p> SQL> select * from table (dbms_xplan.display_cursor ()); --> the SQL statement that runs this time should have been used for full table scan. At this time, index range scan is selected </P> <p> plan_table_output <br/> limit <br/> SQL _id 0bx53mgt4qqnt, child number 0 <br/> ----------------------------------- <br/> select round (AVG (object_id) from t where ID <: v_id </P> <p> plan hash value: 4270555908 </P> <p> bytes <br/> | ID | operation | Name | rows | bytes | cost (% CPU) | time | <br/> latency <br/> | 0 | SELECT statement | 3 (100) | <br/> | 1 | sort aggregate | 1 | 8 | <br/> | 2 | table access by index rowid | T | 9 | 72 | 3 (0) | 00:00:01 | <br/> | * 3 | index range scan | t_pk | 9 | 2 (0) | 00:00:01 | <br/> identified </P> <p> predicate information (identified by Operation ID ): <br/> ----------------------------------------------- </P> <p> 3-access ("ID" <: v_id) </P> <p> SQL> drop table t; <br/>

Iii. Summary
As shown in the preceding demo, due to the ability to bind variables to snoop, not only the first generated execution plan is applied for the subsequent execution plan, but also the row, bytes,
Cost (% CPU) is the same as the first survival execution plan. Therefore, although variable binding can be used to solve the problem of repeated SQL parsing in OLTP systems. But bind the variable
This may cause the SQL statement to select an unoptimal execution plan. Especially for columns with data skew, and generating a histogram is not suitable for variable binding. In Oracle 11g
To some extent, this feature solves the problems caused by variable binding.

Iv. Extension reference
Oracle adaptive shared cursor
Bind variables and their advantages and disadvantages

Parent cursor, child cursor, and shared cursor

Use of the display_cursor function of dbms_xplan

Use of the display function of dbms_xplan

Description of each field module in the execution plan