Learning to bind variables

 

Learn this interesting thing.

Variable binding can effectively reduce hard parsing. Here you need to query several views: V $ mystat, V $ statname, V $ sqlarea.

First, check the number of existing resolutions in the system:

 
SelectName, Value
FromV $ mystat A, V $ statname B
WhereA. Statistic #=B. Statistic #
AndB. NameLike 'Parse %';
 

Parse time CPU	0
Parse time elapsed	0
Parse count (total)	31
Parse count (hard)	2
Parse count (failures)	0
Parse count (describe)	0

We can see that the hard Parsing is 2, which should be caused by the above query.

Write a stored procedure:

 Create   Or   Replace   Procedure Data_creatorIs 
Var1 Number ;
Var2 Number ;
Sqlstr Varchar2 ( 200 );
 Begin 
 For I In   1 .. 3000 Loop
Var1: = Seq1.nextval;
Var2: = Seq1.currval;
Sqlstr: =   '  Insert into Test2 values (: X,: Y)  ' ;
 Execute Immediate sqlstr
Using var1, var2;
 End Loop;
 Commit ;
 End ;

This table inserts 3000 data records and is generated by sequences. That is to say, the SQL statements are different during each execution. Execute this stored procedure:

 
ExecuteData_creator;
 

Then query the hard parsing:

SQL _text	SQL _fulltext	Executions	Parse_cils
SQL	SQL	1	1

It is executed only once and parsed once. The effect is obvious.

 

Replace it with insert:

 Insert IntoTest1Values(1,2);

Commit;
 

The number is changed 10 times.

Perform the preceding query parsing and hard parsing, which are 55 and 2 respectively. Parse time CPU and parse time elapsed are both 0.

After execution, the parsing and hard parsing are as follows:
99 and 5.Parse time CPU andAll parse time elapsed values are 3.

It seems that if no variable is bound, a large amount of SQL will put a great deal of pressure on the CPU.