Oracle绑定变量如何提升相关效率

当我们把一个sql语句提交完后，Oracle绑定变量会检查一下在共享缓冲池（shared pool）中有无与之完全相同的相关语句，如果有的话只须执行软分析即可，否则就得进行硬分析。以下就是文章的具体内容介绍。
而唯一使得oracle 能够重复利用执行计划的方法就是采用绑定变量。绑定变量的实质就是用于替代sql语句中的常量的替代变量。绑定变量能够使得每次提交的sql语句都完全一样。

在字段(包括字段集)建有索引，且字段(集)的集的势非常大（也就是有个值在字段中出现的比例特别的大）的情况下，使用绑定变量可能会导致查询计划错误，因而会使查询效率非常低。这种情况最好不要使用绑定变量。

但是并不是任何情况下都需要使用绑定变量，下面是两种例外情况：

1．对于隔相当一段时间才执行一次的SQL语句，这是利用绑定变量的好处会被不能有效利用优化器而抵消

2．数据仓库的情况下。

绑定变量不能当作嵌入的字符串来使用，只能当作语句中的变量来用。不能用Oracle绑定变量来代替表名、过程名、字段名等.

从效率来看，由于Oracle10G放弃了RBO，全面引入CBO，因此，在10G中使用绑定变量效率的提升比9i中更为明显。

举例：

普通sql语句：

SELECT fname, lname, pcode FROM cust WHERE id = 674;

SELECT fname, lname, pcode FROM cust WHERE id = 234;

SELECT fname, lname, pcode FROM cust WHERE id = 332;

含绑定变量的sql 语句：

SELECT fname, lname, pcode FROM cust WHERE id = :cust_no;

Sql*plus 中使用Oracle绑定变量：

SQL> set timing on

SQL> variable x number;

SQL> exec 😡 :=8

PL/SQL 过程已成功完成。

已用时间: 00: 00: 00.03

SQL> select * from A;

ID

3

5

已用时间: 00: 00: 00.06

SQL> insert into A values(:x);

已创建 1 行。

已用时间: 00: 00: 00.01

SQL> select * from A;

ID

3

8

5

已用时间: 00: 00: 00.01

PL/SQL很多时候都会自动绑定变量而无需编程人员操心，即很多你写得sql语句都会自动利用Oracle绑定变量，如下例所示：

SQL> Set timing on

SQL> declare

2 I NUMBER;

3 BEGIN

4 FOR I IN 1..1000 LOOP

5 INSERT INTO A VALUES(I);

6 end loop;

7 end;

8 /

PL/SQL 过程已成功完成。

已用时间: 00: 00: 00.12

这段代码是不需要使用Oracle绑定变量的方法来提高效率的，Oracle会自动将其中的变量绑定。

SQL> create table D ( id varchar(10));

表已创建。

已用时间: 00: 00: 00.50

SQL> declare

2 i number;

3 sqlstr varchar(2000);

4 begin

5 for i in 1..1000 loop

6 sqlstr :=’ insert into d values(‘||to_char(i)||’)’;

7 execute immediate sqlstr;

8 end loop;

9 end;

10 /

PL/SQL 过程已成功完成。

已用时间: 00: 00: 00.68

这段代码同样是执行了1000条insert语句，但是每一条语句都是不同的，因此Oracle会把每条语句硬解析一次，其效率就比前面那段就低得多了。如果要提高效率，不妨使用绑定变量将循环中的语句改为

SQL> declarev

2 i number;

3 sqlstr varchar(2000);

4 begin

5 for i in 1..1000 loop

6 sqlstr :=’ insert into d values(:i)’;

7 execute immediate sqlstr using i;

8 end loop;

9 end;

10 /

PL/SQL 过程已成功完成。

已用时间: 00: 00: 00.18

这样执行的效率就高得多了。

在PL/SQL中，引用变量即是引用绑定变量。但是在pl/sql中动态sql并不是这样。在vb，java以及其他应用程序中都得显式地利用Oracle绑定变量。对于绑定变量的支持不仅仅限于Oracle,其他RDBMS向SQL SERVER也支持这一特性。
 

【编辑推荐】

1. Oracle reports工具的使用
2. 配置Oracle ebs的oaf开发环境的操作步骤
3. Oracle中join用法的演示
4. Oracle DBA速成法的潜规则
5. Oracle Names服务的实际操作