Oracle Binding Variable Usage Summary __oracle

In Oracle, there are two alternative parsing processes, hard parsing and soft parsing, for a committed SQL statement.

A hard resolution needs to be parsed, the execution path developed, and the access plan optimized. Hard parsing not only consumes a lot of CPU, but more importantly, it occupies an important latch (latch) resource. The only way Oracle can reuse the execution plan is by binding variables. The essence of a binding variable is to use a variable instead of a constant in the SQL statement. A binding variable can make every SQL statement that is committed exactly the same.

1. Use variable in Sqlplus to define

sql> select * from t where id=1;

        ID NAME------------------------------------------1 Test sql> select * from t where id=2;
ID NAME------------------------------------------2 test2 sql> variable i number;

sql> exec:i: = 1;

Pl/sql procedure successfully completed.

        Sql> select * from t where id=:i;

ID NAME------------------------------------------1 Test sql> exec:i: = 2;

Pl/sql procedure successfully completed.

        Sql> select * from t where id=:i; ID NAME------------------------------------------2 test2 sql> select Sql_text,parse_calls from V$sql where

Sql_text like ' select * from t where id=% ';
Sql_text--------------------------------------------------------------------------------parse_calls----------- SELECT * FROM t where id=2 1 select * to T where id=1 1 select * from T where id=:i--you can see this SQL being tuned Used two times, this two-time use includes a soft parse 

2. (misunderstanding) in the sqlplus through the define definition is not a variable, but only the word constants quantity, define definition, and then through the & or && reference when you do not need to enter again, Oracle automatically replaces the defined values at the time of execution, and this is not the binding variable.

sql> define A=1
sql> define define           = "_date" (CHAR)
30-oct-16 define = "_connect_identifier" (CHAR)
DEFINE _user           = "SYS" (char)
DEFINE _privilege      = "as SYSDBA" (char)
DEFINE _sqlplus_release = "1102000400 "(char)
DEFINE _editor         =" Ed "(char)
DEFINE _o_version      =" Oracle Database 11g Enterprise Edition-Rel Ease 11.2.0.4.0-64bit Production
with the partitioning, OLAP, Data Mining and real Application Testing options "(CH AR)
DEFINE _o_release      = "1102000400" (char)
DEFINE A               = "1" (char)
sql> select * from t where I d=&a;
Old   1:select * from T where id=&a
new   1:select * from T where id=1

        ID NAME
--------------- ---------------------------
         1 Test

&& and & Functions, but && replace once after the need to enter, can be replaced multiple times.

Sql> select * from t where id=&b;
Enter value for B:2
old   1:select * from T where id=&b
new   1:select * from T where id=2

        ID name< c6/>------------------------------------------
         2 test2

sql> select * from t where id=&b; 
Enter value for B:2
old   1:select * from T where id=&b
new   1:select * from T where id=2

        ID NAME
------------------------------------------
         2 test2

sql> select * from t where id=&&b;< C18/>enter value for B:2
old   1:select * from T where id=&&b
new   1:select * from T where id= 2

        ID NAME
------------------------------------------
         2 test2

sql> select * from t where id= &&b;
Old   1:select * from T where id=&&b
new   1:select * from T where id=2

        ID NAME
---------- --------------------------------
         2 Test2

In addition, if define defines a character type, you need to enclose it in a quote

Sql> select * from t where name=&c;
Old   1:select * from T where name=&c
new   1:select * from t where name=test
select * from t where name =test
                           *
ERROR at line 1:
ORA-00904: "Test": Invalid identifier


sql> select * from t where name= ' &c ' ;
Old   1:select * from t where name= ' &c '
new   1:select * from T where name= ' test '

        ID name
----- -------------------------------------
         1 Test

As you can see, Oracle is automatically replaced when executing SQL

Sql> Select Sql_text from V$sql where Sql_text like ' select * from t where name=% ';

Sql_text
--------------------------------------------------------------------------------
SELECT * From T where name= ' test '

3. Oracle resolves SQL by converting variables defined in Plsql into binding variables

Sql> CREATE TABLE TT (ID int,name varchar2 ());

Table created.

Sql> alter session set Sql_trace=true;

Session altered.

Sql> declare
  2  begin
  3 for  i in 1 loop
  4  inserts into TT values (i, ' test ');
  5 end  Loop;
  6  commit;
  7 End  ;
  8  /

Pl/sql procedure successfully completed.

Sql> alter session set Sql_trace=false;

Session altered.

Trace file Contents:

2016-10-30 12:11:22.815 Close
#140170997623912: c=0,e=6,dep=0,type=0,tim=1477800682815427
============ =========
parsing in CURSOR #140170997623912 len=92 dep=0 uid=0 oct=47 lid=0 tim=1477800682817922 hv=218581220 ad= ' 8c 89d9b0 ' sqlid= ' 6pdgqjs6hfk74 '
declare
begin
for I in 1. Loop inserts into
TT values (i, ' test ');
End Loop;
commit;
End;
End of STMT
PARSE #140170997623912: c=1999,e=2431,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim= 1477800682817921
=====================
parsing in CURSOR #140170996439488 len=34 dep=1 uid=0 oct=2 lid=0 tim= 1477800682818383 hv=1299226876 ad= ' 86bc23a8 ' sqlid= ' 9j06ydd6r187w '
INSERT into TT VALUES (: B1, ' Test ') end of
STMT

The growth from hard parsing can also be seen:

Sql> Select A.*,b.name 

  2   from V$sesstat A, v$statname b
  3   where a.statistic#=b.statistic#
  4 and   a.sid= (select distinct SID from V$mystat)
  5 and   b.name like '%parse% ';

sql> col name format A30
sql>/

       SID statistic#      VALUE name
---------------------------------- --------------------------        264          0 ADG parselock X get attempts        265          0 ADG Parselock X get successes        622          4 Parse Time CPUs        623          8 Parse time elapsed        624        238 Parse Count (total)        625        parse count (hard)        626          0 Parse count ( Failures)        627          0 Parse count (describe)

8 rows selected.

The hard resolution before execution is 155

Sql> declare
  2  begin
  3 for  i in 1 loop
  4  inserts into TT values (i, ' test ');
  5 end  Loop;
  6  commit;
  7 End  ;
  8  /

Pl/sql procedure successfully completed.

Sql> Select A.*,b.name
  2   from V$sesstat A, v$statname b
  3   where a.statistic#=b.statistic#
  4 and   a.sid= (select distinct SID from V$mystat)
  5 and   b.name like '%parse% ';

       SID statistic#      VALUE NAME
------------------------------------------------------------        264          0 ADG parselock x get attempts        265          0 ADG parselock X get successes          4 par Se time CPU        623          8 Parse time elapsed        624        242 Parse count (total)        625< C44/>157 parse count (hard)        626          0 Parse count (failures)        627          0 Parse count ( Describe)

8 rows selected.

After the execution of 157, only two, and if you do not use the binding variable, the hard parse number is definitely more than two 4. Parameters in stored procedures are automatically converted to bound variables

sql> Create or Replace procedure proc_test (p_id int,p_name varchar2)
  2  is
  3  begin
  4  Insert into TT values (p_id,p_name);
  5  commit;
  6 end  ;
  7  /

Procedure created.

Sql> alter session set Sql_trace=true;

Session altered.

sql> exec proc_test (' Test ');

Pl/sql procedure successfully completed.

Sql> alter session set Sql_trace=false;

Session altered.

Trace file Contents:

2016-10-31 04:11:23.421 Close
#140585231805712: c=0,e=6,dep=0,type=0,tim=1477858283421964
============ =========
parsing in CURSOR #140585231805712 len=35 dep=0 uid=0 oct=47 lid=0 tim=1477858283423073 hv=526484776 ad= ' 86 b57878 ' sqlid= ' asc6yd8gq3198 '
BEGIN proc_test (' Test '); End;
End of STMT
PARSE #140585231805712: c=999,e=1047,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1477858283423072
=====================
Parsing in CURSOR #140585233135112 len=32 dep=1 uid=0 oct=2 lid=0 tim=1477858283423304 hv=1422618771 ad= ' 8697d9b8 ' sqlid= ' 1yqc845acqw4m '
INSERT into TT VALUES (: B2,: B1) End of
STMT
PARSE #140585233135112: c=0,e=100,p=0,cr=0,cu= 0,mis=1,r=0,dep=1,og=1,plh=0,tim=1477858283423304

In fact, the call process from the stored procedure can also be seen using the binding variable

Begin
-Call
the procedure proc_test (p_id =>:p _id, p_name =>
:p _name);
End

5. Using binding variables in dynamic SQL

A. Direct use of value stitching in cursors

[Oracle@centos6 scripts]$ cat Sql_parse1.sql
declare
cursor test_cur is select Id,name from TT;
Begin for
i-Test_cur loop
execute immediate ' INSERT INTO TT values (' | | i.id| | ', ' | | Chr (39) | | i.name| | Chr (+) | | ') ';
End Loop;
commit;
End

This directly uses the value stitching in the cursor to belong to the unbound variable, for hard parsing

Sql> alter system flush Shared_pool;

System altered.

sql> @sql_parse1. SQL Pl/sql procedure successfully completed. Sql> set linesize sql> col hash_value format 9999999999 sql> Col sql_id format sql> col for Mat sql> Col version_count format sql> Col sql_text format A40 sql> Col parse_calls format 999 sql> Sele CT hash_value,sql_id,child_latch,version_count,sql_text,parse_calls from V$sqlarea where Sql_text like ' inserts into tt%
';  hash_value sql_id        Child_latch VERSION_COUNT SQL_TEXT  & nbsp;                                          parse_calls--------------------------------------------------------------------------- ---------------------------------- 3161064081 196c4s2y6n0nj           0              1 INSERT INTO TT values (, ' Test ')                                1  3718124844 6hfpagbftw59c           0              1 INSERT INTO TT values (m, ' test ')                                1  4046592725 c5txty7sm46qp            0             1 INSERT into TT values (             &NB, ' test ')sp;               1   961289967 4n0n3dnwns7rg           0              1 INSERT INTO TT values (' Test ')                                1  2124685404 g70mmhxza882w           0              1 INSERT INTO TT values (+, ' test ')                                1   608576974 3nm07v4k4c9ff            0             1 Insert into TT values (' Test ')                               1  3770952793 2xcry8ghc8b2t           0              1 INSERT INTO TT values (1, ' Test ')                           
      1

B. Binding variable notation

[Oracle@centos6 scripts]$ cat Sql_parse2.sql
declare
cursor test_cur is select Id,name from TT;
Begin for
i-Test_cur loop
execute immediate ' INSERT INTO TT values (: a,:b) ' using I.id,i.name;
End Loop;
commit;
End;
/

sql> alter system flush Shared_pool;

System altered.

sql> @sql_parse2. SQL

Pl/sql procedure successfully completed.

Sql> Select Hash_value,sql_id,child_latch,version_count,sql_text,parse_calls from V$sqlarea where Sql_text like ' insert into tt% ';

 Hash_value sql_id        child_latch version_count sql_text                                           parse_calls
--------------------------------- ----------------------------------------------------------------------------
 2034333845 gbkazctwn2y4p           0             1 insert INTO TT values (: a,:b)                                 1