oracle 绑定变量的作用和应用

关键词:

绑定变量(binding variable),共享池(shared buffer pool), SGA(system global area);

在开发一个数据库系统前,有谁对Oracle 系统了解很多,尤其是它的特性,好象很少吧;对初学者来讲,这更是不可能的事情;仅仅简单掌握了SQL的写法,就开始了数据库的开发,其结果只能是开发一个没有效率,也没有可扩展的系统;
因此,我写这个主题也是希望让大家更多地掌握Oracle数据库的特性,从而在架构一个新系统时,能考虑系统的可扩展,可伸缩性,也兼顾系统的效率和稳定;

使用绑定变量是Oracle数据库的特性之一;于是大家要问,为什么使用,怎样使用,它的使用限制条件是什么?我会按照这样的想法去解答大家的疑问,我也会以举例子的方式来回答这些问题;

1. 为什么使用绑定变量?
这是解决Oracle应用程序可伸缩性的一个关键环节;而Oracle的共享池就决定了开发人员必须使用绑定变量;如果想要Oracle 运行减慢,甚至完全终止,那就可以不用绑定变量;
这里举例说明上述问题;
为了查询一个员工代号是123,你可以这样查询:
select * from emp where empno=’123’;
你也可以这样查询:
select * from emp where empno=:empno;

象我们往常一样,你查询员工’123’一次以后,有可能再也不用;接着你有可能查询员工’456’,然后查询’789’等等;如果查询使用象第一个查询语句,你每次查询都是一个新的查询(我们叫它硬编码的查询方法);因此,Oracle每次必须分析,解析,安全检查, 优化等等;
第二个查询语句提供了绑定变量:empno,它的值在查询执行时提供,查询经过一次编译后,查询方案存储在共享池中,可以用来检索和重用;在性能和伸缩性方面,这两者的差异是巨大的,甚至是惊人的;通俗点讲,就不是一个级别;
第一个查询使用的频率越高,所消耗的系统硬件资源越大,从而降低了用户的使用数量;它也会把优化好的其它查询语句从共享池中踢出;就象一个老鼠坏了一锅汤似的,系统的整体性能降低; 而执行绑定变量,提交相同对象的完全相同的查询的用户(这句话,大家听起来比较难理解,随后我会给出详细的解释),一次性使用就可重复使用,其效率不言耳语; 打个形象的比喻来说,第一个查询就象一次性使用的筷子,而第二个查询象是铁筷子,只要洗干净,张三李四都能用,合理有效地使用了资源;

下面举例子去详细论证上述的问题,不使用绑定变量为生病状况:

这是一个未使用的绑定变量(吃药前):

set echo on;(把执行结果显示出来)
alter system flush shared_pool;
这条语句是清空共项池,每次都必须使用,确保共享池是空的,以提高执行效率;
set timing on(打开记时器.)

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;
/
PL/SQL 过程已成功完成。

执行时间: 已用时间: 00: 00: 07.03

这是一个使用的绑定变量(吃药后):
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;
PL/SQL 过程已成功完成。

执行时间: 已用时间: 00: 00: 00.75

大家自己比较结果,相差就是一个数量级;使用绑定变量不仅仅是运行快,而且允许多个用户同时使用;

上述的环境是在数据库Oracle 8.1.7, 操作系统: Windows Server 2003,内存 1G , CPU: P4 3.4GHZ ; 电脑配置不同,执行的结果是有差异的;

2. 怎样使用绑定变量?
下面举例说明:
2.1.让Oracle自己绑定变量(也叫静态绑定变量)

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;
/
在上面的情况，Oracle会自己绑定变量，即，如果参数保存在一个数组中，select语句放在一个循环中，
select 语句只会编译一次。

2.2 .动态绑定变量
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的绑定变量使用
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;

这里强烈推荐使用静态绑定变量,有兴趣的话可以自己比较;

3. 我怎样知道正在使用绑定变量的方法;
下面举例说明;
创建一个Table;
Create table t (xx int);
执行下面的语句;
Begin
For I in 1..100 loop
Execute immediate’insert into t values(‘|| t ||’)’;
End loop;
end;

现在准备好了脚本,开始创建一个字符串中删除常数的一个函数,它采用的是SQL语句为:
insert into t values(‘hello’,55);
insert into t values(‘world’,56);
将其转换为
insert into t values(‘#’,@);
所有相同的语句很显然是可见的(使用绑定变量);上述两个独特的插入语句经过转换后变成同样的语句; 完成的转换函数为:
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;
/

接着我们建立一个临时表去保存V$SQLAREA里的语句,所有 Sql的执行结果都写在这里;
建立临时表;
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;

保存数据到临时表上;
insert into sql_area_tmp(sql_text) select sql_text from v$sqlarea;

对临时表中的数据进行更新;删除掉常数;
Update sql_area_tmp set SQL_TEXT_WO_CONSTANTS= remove_constants(sql_text);

现在我们要找到哪个糟糕的查询
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

另外, 设定如下参数
Alter session set sql_trace=true;
Alter session set timed_statictics=True;
Alter session set events ‘10046 trace name context forever,level <N>’;
这里的’N’ 表示的是1,4,8,12,详细内容请参考相关文档
Alter session set events ‘10046 trace name context off’;
可以用 TKPROF 工具查看绑顶变量执行的结果,更详细内容请查看相关文档;

4. 绑定变量在应用开发环境下的使用;
4.1 在VB.Net or ASP.NET and VB中的的使用
建议用Oracleclient DB的连接方法,OleDB不支持;下面是使用

OracleClient连接的使用例子(这个代码执行只需要2秒不到);
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.

OleDB(VB,ASP等)不支持绑定变量,或者我没有找到更好的方法去实现它;它有变量的概念但不支持绑定;网络上,有很多帖子说;他实现了绑定变量用VB or ASP;我按照他们的方法去试,发现他们与单纯传参数没有什么区别,请看下面的内容;

OleDB(这个执行需要5秒;) :
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(耗时也是5秒左右…):

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, 30, "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 在Delphi中的使用情况;
这里特殊说明, Borland Delphi 4.0以上的版本已经开始完全支持绑定变量的概念,因此,它执行数据库的查询效率要好于其他开发工具;执行的结果不到2秒;
procedure TForm1.Button1Click(Sender: TObject);
Var
i :Integer;
begin

edit1.text:=DatetimeToStr(now);
For i := 1 to 1000 do
//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. 在Java中的使用绑定变量

String v_id = 'xxxxx';
String v_sql = 'select name from table_a where id = ? '; //嵌入绑定变量
stmt = con.prepareStatement( v_sql );
stmt.setString(1, v_id ); //为绑定变量赋值
stmt.executeQuery();
在Java中，结合使用setXXX 系列方法，可以为不同数据类型的绑定变量进行赋值，从而大大优化了SQL 语句的性能。

4.4 C#同VB.NET ,这里不在赘述;

5. 绑定变量使用限制条件是什么?
为了不重复解析相同的SQL语句,在第一次解析之后, ORACLE将SQL语句存放在内存中.这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享. 因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同, ORACLE就能很快获得已经被解析的语句以及最好的执行路径. ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用.可惜的是ORACLE只对简单的表提供高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询(这句并不完全可信,有兴趣的可以自己琢磨).
数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了.
当你向ORACLE 提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句.这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须完全相同(包括空格,换行等).
共享的语句必须满足三个条件:

A. 字符级的比较:
当前被执行的语句和共享池中的语句必须完全相同.
例如:
SELECT * FROM EMP;
和下列每一个都不同
SELECT * from EMP;
Select * From Emp;
SELECT * FROM EMP;


B. 两个语句所指的对象必须完全相同:
例如:
用户 对象名 如何访问
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

考虑一下下列SQL语句能否在这两个用户之间共享.


SQL 能否共享 原因
select max(sal_cap) from sal_limit; 不能 每个用户都有一个private synonym - sal_limit , 它们是不同的对象
select count(*0 from work_city where sdesc like 'NEW%'; 能 两个用户访问相同的对象public synonym - work_city 
select a.sdesc,b.location from work_city a , plant_detail b where a.city_id = b.city_id 不能 用户jack 通过private synonym访问plant_detail 而jill 是表的所有者,对象不同.



C. 两个SQL语句中必须使用相同的名字的绑定变量(bind variables)

例如：

第一组的两个SQL语句是相同的(可以共享),而第二组中的两个语句是不同的(即使在运行时,赋于不同的绑定变量相同的值)
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. 总结
不使用绑定变量是做着等死,使用绑定变量不一定不会死;没有任何的良药会包治百病,所以在如何合理有效地使用绑定变量仍就需要大家去摸索;

[全文完]..

相关文献:
1. [Oracle 高级专家编程] 作者:Thomas Kytes 袁勤勇,张玉魁编译;清华大学出版社;
2. ORACLE SQL性能优化系列 from http://www.dbasupport.com/
3. Oracle 绑定变量的用法 from 王者之剑（http://www.albertsong.com/）