The characteristics and application of binding variables in Oracle database

Binding variable characteristics and application of Oracle database "-----" reprinted from https://www.cnblogs.com/rootq/(original address) Keywords:

Bind variable (binding variable), shared pool (gkfx buffer pool), SGA (System global Area);

Before the development of a database system, who knows a lot about the Oracle system, especially its characteristics, it seems to be very few, for beginners, this is more impossible things, simply mastered the wording of SQL, began the development of the database, the results can only be developed a no efficiency, there is no extensible system;
Therefore, I write this topic also want to let people more grasp the characteristics of Oracle database, so in the framework of a new system, can consider the scalability of the system, scalability, but also take into account the efficiency and stability of the system;

The use of bound variables is one of the characteristics of an Oracle database; so we ask, why use, how to use it, what are the limitations of its use? I will answer the questions in this way, and I will respond to them by example.

1. Why are binding variables used?
This is a key step in solving the scalability of Oracle applications, while Oracle's shared pool determines that the developer must use bound variables, and if you want Oracle to slow down or even terminate completely, you can not bind the variables;
The above questions are illustrated here;
In order to query an employee code is 123, you can query:
SELECT * from emp where empno= ' 123 ';
You can also check this:
SELECT * from EMP where empno=:empno;

As we always do, you may not use it again after you query employees ' 123 ', then you may query employees ' 456 ', then query ' 789 ' and so on; If the query uses a query like the first one, you are a new query every time (we call it hard-coded query method); Oracle must analyze, parse, check, optimize and so on every time.
The second query statement provides a binding variable: Empno, whose value is provided when the query is executed, the query scheme is stored in a shared pool once compiled, and can be used for retrieval and reuse; In terms of performance and scalability, the difference is huge or even astonishing; in layman's words, it is not a level;
The higher the frequency of the first query, the greater the system hardware resources consumed, thereby reducing the number of users used; it would also kick out the other query statements that were optimized from the shared pool; As a mouse broke a pot of soup, the overall performance of the system decreased; Instead of executing the binding variable, submit the same object to the user of the exact same query (this sentence, everyone sounds more difficult to understand, then I will give a detailed explanation), disposable use can be reused, its efficiency does not whisper; To make an image of the analogy, the first query is like a disposable chopsticks, and the second query like Iron chopsticks, as long as the Wash, Dick and Harry can use, reasonable and effective use of resources;

Here is an example to illustrate the above problem in detail, do not use the binding variable for the illness condition:

This is an unused binding variable (before the pill):

Set echo on; (shows the execution result)
alter system flush Shared_pool;
This statement clears the pooled pool and must be used each time to ensure that the shared pool is empty to improve execution efficiency;
Set timing on (opens the timer.)

Declare
Type RC is REF CURSOR;
L_RC RC;
L_dummy All_objects.object_name%type;
L_start number default dbms_utility.get_time;
Begin
For I in 1.. 1000
Loop
Open L_RC for
' Select object_name
From All_objects
where object_id = ' | | I
Fetch L_RC into l_dummy;
Close L_RC;
End Loop;
Dbms_output.put_line
(Round ((dbms_utility.get_time-l_start)/100, 2) | |
' seconds ... ');
End
/
The PL/SQL process has completed successfully.

Execution time: Time spent: 00:00:07.03

This is a binding variable to use (after taking medicine):
Set echo on

alter system flush Shared_pool;
Declare
Type RC is REF CURSOR;
L_RC RC;
L_dummy All_objects.object_name%type;
L_start number default dbms_utility.get_time;
Begin
For I in 1.. 1000
Loop
Open L_RC for
' Select object_name
From All_objects
WHERE object_id =: X '
Using I;
Fetch L_RC into l_dummy;
Close L_RC;
End Loop;
Dbms_output.put_line
(Round ((dbms_utility.get_time-l_start)/100, 2) | |
' seconds ... ');
End
The PL/SQL process has completed successfully.

Execution time: Time spent: 00:00:00.75

We compare the result, the difference is an order of magnitude; using bound variables is not only fast, but also allows multiple users to use simultaneously;

The above environment is in the database Oracle 8.1.7, operating system: Windows Server 2003, Memory 1G, CPU:P4 3.4GHZ; Different Computer configuration, the results of the implementation of the difference;

2. How do I use bound variables?
The following examples illustrate:
2.1. Let Oracle bind its own variable (also called a static binding variable)

Set serverout on;
Set timing on;
Declare
L_sql VARCHAR2 (2000);
L_count number;
L_PARAM1 varchar2 (100);
L_PARAM2 varchar2 (100);
Begin
L_param1:= ' a ';
L_param2:= ' B ';
Select COUNT (*) into L_count from table1 where col_1=l_param1 and col_2=l_param2;
Dbms_output.put_line (L_count);
End
/
In the above case, Oracle binds the variable itself, that is, if the parameter is stored in an array, the SELECT statement is placed in a loop,
The SELECT statement is compiled only once.

2.2. Dynamically bound variables
Set serverout on;
Set timing on;
Declare
L_sql VARCHAR2 (2000);
L_count number;
L_PARAM1 varchar2 (100);
L_PARAM2 varchar2 (100);
Begin
L_param1:= ' a ';
L_param2:= ' B ';
L_sql:= ' SELECT COUNT (*) into:x from table1 where col_1=:y and Col_2=:z ';
Execute Immediate l_sql into L_count using L_param1,l_param2;
Dbms_output.put_line (L_count);
End
/

2.3. Dbms_output Binding variable Use
Set echo on;
Set serveroutput on;
Set timming on;
Declare
cursor_id integer;
I number;
XSQL VARCHAR2 (200);
Xout VARCHAR2 (200);
L_start number default dbms_utility.get_time;
Xrow integer;
Begin
Cursor_id:=dbms_sql.open_cursor;
For I in 1..1000 Loop
Dbms_sql.parse (cursor_id, ' insert INTO t values (: username,:user_id,sysdate) ', Dbms_sql. V7);
Dbms_sql.bind_variable (cursor_id, ' username ', ' Test ' | | To_char (i));
Dbms_sql.bind_variable (cursor_id, ' user_id ', i);
Xrow:=dbms_sql.execute (cursor_id);
--insert into t values (' Test ' | | To_char (i), i,sysdate);
--xsql:= ' insert into t values (: username,:user_id,sysdate) ';
--execute immediate XSQL using ' Test ' | | To_char (i), I;
End Loop;
Dbms_sql.close_cursor (cursor_id);
Dbms_output.put_line (Round ((dbms_utility.get_time-l_start)/100,2) | | ' Seconds ... ');
--xout:=to_char (Round ((Dbms_utility.get_time-l_start)/100,2)) | | ' Seconds ... ';
--xout:= ' seconds ... ';
--return xout;
End

It is strongly recommended to use static binding variables, which are interesting to compare themselves;

3. How do I know how to use bound variables;
The following examples illustrate;
Create a table;
Create table T (xx int);
Execute the following statement;
Begin
For I in 1..100 loop
Execute immediate ' insert into t values (' | | | t | | ') ';
End Loop;
End

Now that the script is ready, start creating a function that removes the constant in a string that uses the SQL statement as:
INSERT into t values (' Hello ', 55);
INSERT into t values (' world ', 56);
Convert it to
INSERT into T values (' # ', @);
All the same statements are obviously visible (using bound variables); The two unique insert statements are transformed into the same statements; The completed conversion functions are:
Create or Replace function remove_constants (p_query in varchar2) return VARCHAR2 as
L_query long;
L_char varchar2 (1);
L_in_quates Boolean default false;
Begin
For I in 1..length (p_query)
Loop
L_char:=substr (p_query,i,1);
If l_char= "and L_in_quates Then
L_in_quates:=false;
elsif l_char= "and not" l_in_quates then
Then
L_in_quates:=true;
l_query=:l_query| | ' #‘;
End If

If not l_in_quates then
l_query=:l_query| | L_char;
End If;
End Loop;

L_query:=tranlate (l_query, ' 0123456789 ', ');
For I in 1..8 loop
L_query:=replace (L_query,lpad (' @ ', 10-i, ' @ '), ' @ ');
L_query:=replace (L_query,lpad (', 10-i, '), ');

End Loop;
return Upper (L_query);
End
/

Then we set up a temporary table to save the statements in the V$sqlarea, and all the SQL execution results are written here;
establishment of temporary tables;
Create global temporary table sql_area_tmp on commit preserve rows as
Select Sql_text,sql_text sql_text_wo_constants from
V$sqlarea where 1=0;

Save the data to the temp table;
Insert into Sql_area_tmp (sql_text) select Sql_text from V$sqlarea;

Update the data in the temporary table, delete the constant;
Update sql_area_tmp Set sql_text_wo_constants= remove_constants (Sql_text);

Now we're going to find a bad query.
Select Sql_text_wo_constants,count (*) from sql_area_tmp
GROUP BY Sql_text_wo_constants
Having Count (*) >10
Order by 2;

Sql_text_wo_constants COUNT (*)
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
INSERT into T VALUES (@) 100

In addition, set the following parameters
Alter session Set Sql_trace=true;
Alter session Set Timed_statictics=true;
Alter session Set Events ' 10046 Trace name context forever,level <N> ';
Here the ' N ' represents the 1,4,8,12, please refer to the relevant documentation for details
Alter session Set Events ' 10046 Trace name context off ';
You can use the TKPROF tool to view the results of the binding top variable execution, more details please check the relevant documents;

4. The use of bound variables in the application development environment;
4.1 Use in vb.net or ASP. NET and VB
It is recommended to use the OracleClient DB connection method, OLE DB does not support;

Example of the use of the OracleClient connection (this code execution takes only 2 seconds);
Begin
Dim Cn01 as New oracleconnection
Dim CMD01 as New OracleCommand

Dim CMD as New OleDbCommand
Dim I as Integer
Try
<add key= "DBCONN_SFCFA" value= "User id=sfcfa;password=sfcfa;data source=cim;"/>
Xconnstr = System.Configuration.ConfigurationSettings.AppSettings ("DBCONN_SFCFA")
' Cn01. ConnectionString ()
Cn01. ConnectionString = Xconnstr
Cn01. Open ()

TextBox1.Text = Now
Application.doevents ()

XSQL = "INSERT INTO t values (: username,:userid,sysdate)"
For i = 1 to 1000
CMD01 = New Oracleclient.oraclecommand (XSQL, Cn01)
Cmd01.commandtype = CommandType.Text
CMD01. Parameters.Add ("username", "test" + CStr (i))
CMD01. Parameters.Add ("userid", i)
CMD01. ExecuteNonQuery ()
CMD01. Parameters.clear ()
Next I

TextBox2.Text = Now

Catch ex as OleDbException
MsgBox (ex. Message)
Catch ex as Exception
MsgBox (ex. HelpLink + ex. Message)

End Try
End.

OLE DB (vb,asp, etc.) does not support binding variables, or I do not find a better way to implement it; it has the concept of variables but does not support binding; On the network, there are many posts said; he implemented the binding variable with VB or ASP; I tried it the way they did, and I found that they were no different from simple arguments, Please look at the following content;

OLE DB (This execution takes 5 seconds;):
Dim Xconnstr, xSql as String
Dim Cn as New OleDbConnection

Dim Cn01 as New oracleconnection
Dim CMD01 as New OracleCommand

Dim CMD as New OleDbCommand
Dim I as Integer
Try
<add key= "DBCONN_SFCFA" value= "provider=msdaora.1; User id=sfcfa;password=sfcfa;data Source=cim; " />
Xconnstr = System.Configuration.ConfigurationSettings.AppSettings ("DBCONN_SFCFA")
' Cn.connectionstring ()
cn.connectionstring = Xconnstr
Cn.open ()

TextBox1.Text = Now
Application.doevents ()

XSQL = "INSERT into t values (?,?, sysdate)"
For i = 1 to 1000
CMD = New OleDbCommand (XSQL, Cn)
Cmd.commandtype = CommandType.Text
CMD.PARAMETERS.ADD ("username", "test" + CStr (i))
CMD.PARAMETERS.ADD ("userid", i)
Cmd.executenonquery ()
Cmd.Parameters.Clear ()
Next I

TextBox2.Text = Now

Catch ex as OleDbException
MsgBox (ex. Message)
Catch ex as Exception
MsgBox (ex. HelpLink + ex. Message)

End Try

VB or ASP (time is also about 5 seconds ...):

Private Sub Command1_Click ()
Dim sconn as String
Dim BVCS_CN as ADODB. Connection
' Dim Bvcs as ADODB.
Dim XCMD as Adodb.command
Dim Xpre as ADODB. Parameter
Dim XSQL as String
Dim XSql01 as String
Dim XRS as ADODB. Recordset

On Error GoTo 1

Setdbconnection = True
Set BVCS_CN = New ADODB. Connection

' Bvcs_cn. Provider = "Msdaora"
' sconn = ' DATA source= ' & ServerName & '; '
sconn = "provider=msdaora.1; PASSWORD=SFCFA; User id=sfcfa;data Source=cim; "

With BVCS_CN
. Open sconn
End with


If BVCS_CN. State = 0 Then
MsgBox "DB Connection is Error"
Exit Sub
End If

Text1.Text = Now
DoEvents

Set XCMD = New Adodb.command

Dim XTest as String


Set xpre = New ADODB. Parameter

' BVCS_CN

For i = 1 to 1000

With XCMD

. ActiveConnection = Bvcs_cn

. CommandText = "Insert into TT (Username,userid) VALUES (?,?)"
. CommandType = adCmdText
. Parameters.Append. CreateParameter ("username", Adbstr, adParamInput, "test" + CStr (i))
. Parameters.Append. CreateParameter ("userid", Adinteger, adParamInput, 4, i)
. Prepared = True
. Execute
End with

XCMD.Parameters.Delete 1
XCMD.Parameters.Delete 0

Next I

Set XCMD = Nothing

Text2.text = Now

Exit Sub
1:
Set XCMD = Nothing

MsgBox error$
For each objerr in BVCS_CN. Errors
MsgBox objerr.description
Next
Bvcs_cn. Errors.clear
Exit Sub
Resume Next
End Sub

4.2 The use of Delphi;
Specifically, the Borland Delphi 4.0 version has already begun to fully support the concept of bound variables, so it performs a database query more efficiently than other development tools; The results of execution are less than 2 seconds;
Procedure Tform1.button1click (Sender:tobject);
Var
I:integer;
Begin

Edit1.text:=datetimetostr (now);
For I: = 1 to
Begin
With Query1 do
Begin
Close
Sql.clear;
Sql.add (' Insert into T Values (: username,:user_id,sysdate) ');
Parambyname (' username '). Asstring: = ' test ';
Parambyname (' user_id '). Asinteger: =i;
Execsql;
End;
End

Edit2.text:=datetostr (now);
Edit2.text:=datetimetostr (now);
End

4.3. Using binding variables in Java

String v_id = ' xxxxx ';
String v_sql = ' Select name from table_a where id =? ‘; Embedding binding variables
stmt = Con.preparestatement (V_sql);
Stmt.setstring (1, v_id); Assigning a value to a bound variable
Stmt.executequery ();
In Java, combined with the Setxxx series method, you can assign values to binding variables of different data types, which greatly optimizes the performance of SQL statements.

4.4 C # with vb.net, here is not repeating;

5. What are the restrictions on binding variable usage?
In order not to parse the same SQL statements repeatedly, after the first resolution, Oracle stores the SQL statements in memory. This is a shared pool located in the system global area of the SGA (total buffer pool) Memory can be shared by all database users. So, when you execute an SQL statement (sometimes referred to as a cursor), if it is exactly the same as the previous executed statement, Oracle can quickly get the parsed statement and the best execution path. This feature of Oracle greatly improves the performance of SQL and saves memory usage. Unfortunately, Oracle only provides caching for simple tables (cache buffering), which does not apply to multi-table connection queries (which is not entirely credible and interesting to ponder) .
The database administrator must set the appropriate parameters for this region in Init.ora, and when the memory area is larger, more statements can be kept, and the likelihood of sharing is greater.
When you submit an SQL statement to Oracle, Oracle will first look for the same statement in this memory. It is important to note that Oracle has a strict match for both, and the SQL statement must be identical (including spaces, newline, and so on) in order to achieve a share.
A shared statement must meet three conditions:

A. Character-level comparisons:
The statements that are currently executed and those in the shared pool must be identical.
For example:
SELECT * from EMP;
It's different from every one of the following.
SELECT * from EMP;
Select * from EMP;
SELECT * from EMP;


B. The object that the two statement refers to must be exactly the same:
For example:
How user object names are accessed
Jack sal_limit private synonym
work_city public synonym
Plant_detail public synonym

Jill sal_limit private synonym
work_city public synonym
Plant_detail table Owner

Consider whether the following SQL statements can be shared between these two users.


Why SQL can be shared
Select Max (sal_cap) from Sal_limit; Not every user has a private synonym-sal_limit, they are different objects
Select count (*0 from work_city where Sdesc like ' new% '; Can two users access the same object public synonym-work_city
Select A.sdesc,b.location from Work_city A, plant_detail b where a.city_id = b.city_id cannot be user jack through private synonym access plant _detail while Jill is the owner of the table, the objects are different.



C. Binding variables in two SQL statements that must use the same name (bind variables)

For example:

The first group of two SQL statements are the same (can be shared), while two statements in the second group are different (even at run time, the same values are assigned to different binding variables)
A.
Select PIN, name from people where pin =: blk1.pin;
Select PIN, name from people where pin =: blk1.pin;

B.
Select PIN, name from people where pin =: blk1.ot_ind;
Select PIN, name from people where pin =: blk1.ov_ind;

6. Summary
Do not use the binding variable is to do die, use the binding variable does not necessarily die; no good medicine will cure all ills, so in how to use the binding variables reasonably and effectively still need to explore;

[end of full text]..

Related literature:
1. [Oracle advanced expert programming] Author: Thomas kytes Xianqing Yong, Zhang Yuqui compilation; Tsinghua University Press;
2. ORACLE SQL Performance Optimization series from http://www.dbasupport.com/
3. Oracle binding variable usage from the Sword of Kings (http://www.albertsong.com/)

The characteristics and application of binding variables in Oracle database