在oracle中绑定变量的使用

不使用绑定变量而使用硬编码是oracle性能问题的主要原因和可伸缩性的主要障碍

而且这种障碍是除了使用绑定变量以外很难改变的！

 

下面根据一个简单的试验来查看硬编码与使用绑定变量对性能的影响：

在一个查询中我们可以使用两种方式：
比如查询个人编号：
select * from ac01 where aac001=123;
另外，也可以查询：
select * from ac01 where aac001=:grbh;

 

在典型的系统中，查询员工123 一次，可能以后也不会查询，以后将查询员工456，然后查询员工789，等
对于这个查询方式，我们看到，对于每一次的查询来说都是新的查询，即数据库中没有过的查询。每次的查询都要经过分析、限定(名称解析)、安全检查、优化等等，简单的说，执行的每条语句在每次执行时都将必须经过编译。这样的结果会造成share pool，即SGA中的共享池迅速的填满，而oracle不得不花费大量的时间来整理，这也违背了share pool的设计思想。

然而，第二个查询使用了绑定变量：grbh。查询经过一个编译后，查询方案存储在共享池中，只保留这一个副本，可以用来检索和重用。
在性能和可伸缩性方面，这两者的差异是巨大的，甚至是惊人的。

看看这个例子：

 

SQL>  alter system flush shared_pool
  2  ;

System altered.

SQL> set timing on
SQL> declare
  2  type rc is ref cursor;
  3  l_rc rc;
  4  l_dummy all_objects.object_name%type;
  5  l_start number default dbms_utility.get_time;
  6  begin
  7     for i in 1..1000
  8     loop
  9             open l_rc for
 10             'select object_name from all_objects where object_id ='||i;
 11             fetch l_rc into l_dummy;
 12             close l_rc;
 13     end loop;
 14     dbms_output.put_line(round((dbms_utility.get_time-l_start)/100,2)||
 15     'seconds ..');
 16  end;
 17  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:11.09

注意，没有使用绑定变量方式，花费了11秒
SQL> declare
  2  type rc is ref cursor;
  3  l_rc rc;
  4  l_dummy all_objects.object_name%type;
  5  l_start number default dbms_utility.get_time;
  6  begin
  7     for i in 1..1000
  8     loop
  9             open l_rc for
 10             'select object_name from all_objects where object_id=:x'
 11             using i;
 12             fetch l_rc into l_dummy;
 13             close l_rc;
 14     end loop;
 15     dums_output.put_line
 16     (round((dbms_utility.get_time-l_start)/100,2)||
 17     'seconds..');
 18  end;
 19  /
        dums_output.put_line
        *
ERROR at line 15:
ORA-06550: line 15, column 2:
PLS-00201: identifier 'DUMS_OUTPUT.PUT_LINE' must be declared
ORA-06550: line 15, column 2:
PL/SQL: Statement ignored

Elapsed: 00:00:00.08
SQL> edit
Wrote file afiedt.buf

  1  declare
  2  type rc is ref cursor;
  3  l_rc rc;
  4  l_dummy all_objects.object_name%type;
  5  l_start number default dbms_utility.get_time;
  6  begin
  7     for i in 1..1000
  8     loop
  9             open l_rc for
 10             'select object_name from all_objects where object_id=:x'
 11             using i;
 12             fetch l_rc into l_dummy;
 13             close l_rc;
 14     end loop;
 15     dbms_output.put_line
 16     (round((dbms_utility.get_time-l_start)/100,2)||
 17     'seconds..');
 18* end;
SQL> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.18

而使用绑定变量的方式，只用了不到一秒的时间！

从而可以看出，对于绑定变量的使用，可以提高系统性能的极大提高！