优化你的应用--请使用绑定变量

首先清空shared pool,确保测试数据库处于干净状态.
SQL> alter system flush SHARED_POOL;

系统已更改。

我们选取ALL_OBJECTS表做测试.它有29542条纪录.

[@more@]

首先清空shared pool,确保测试数据库处于干净状态.
SQL> alter system flush SHARED_POOL;

系统已更改。

我们选取ALL_OBJECTS表做测试.它有29542条纪录.
SQL> DESC ALL_OBJECTS
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 OWNER                                     NOT NULL VARCHAR2(30)
 OBJECT_NAME                               NOT NULL VARCHAR2(30)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                 NOT NULL NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(18)
 CREATED                                   NOT NULL DATE
 LAST_DDL_TIME                             NOT NULL DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)

SQL> select count(*) from all_objects;

  COUNT(*)
----------
     29542
    

没有使用绑定变量的情况.
我写了一个简单测试程序实现这样一个查询统计执行1000次的时间.
形如:
SELECE object_name FROM all_objects WHERE object_id=1;
SELECE object_name FROM all_objects WHERE object_id=2;
.
.
.
.
SELECE object_name FROM all_objects WHERE object_id=1000;
可以看到在没有使用绑定变量的情况下执行时间为12.9 seconds (12.9秒)
pl/sql 1
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
 11              from all_objects
 12             where object_id = ' || i;
 13           fetch l_rc into l_dummy;
 14           close l_rc;
 15       end loop;
 16       dbms_output.put_line
 17       ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
 18         ' seconds...' );
 19   end;
 20  /
12.9 seconds...

PL/SQL 过程已成功完成。

使用绑定变量的情况.
我写了一个简单测试程序实现这样一个查询统计执行1000次的时间.
形如:
SELECE object_name FROM all_objects WHERE object_id=:object_id;
SELECE object_name FROM all_objects WHERE object_id=:object_id;
.
.
.
.
SELECE object_name FROM all_objects WHERE object_id=:object_id;
可以看到使用绑定变量的情况下执行时间为1.32 seconds... (1.32秒)
pl/sql 2
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
 11               from all_objects
 12              where object_id = :x'
 13            using i;
 14            fetch l_rc into l_dummy;
 15            close l_rc;
 16        end loop;
 17        dbms_output.put_line
 18        ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
 19          ' seconds...' );
 20    end;
 21  /
1.32 seconds...

PL/SQL 过程已成功完成。

原因:
执行sql需要在shared_pool中生成执行计划.如果没有使用绑定变量,会使硬分析的次数增加.
一次硬分析的代价是很高的,包括这条sql的语法分析,语义分析,执行权限分析.而当Oracle在职执行分析个过程中又要获得latch(latch 一种低级串行锁,保护shared_pool内存征用),latch的资源对于一个繁忙的OLTP系统也是非常宝贵的.

没有执行pl/sql 1时,硬分析统计.
SQL> select name,value from v$sysstat where name like '%parse%';
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse time cpu                                                         4948
parse time elapsed                                                     4468
parse count (total)                                                  170148
parse count (hard)                                                     1619  (硬分析次数)
parse count (failures)                                                   80

执行pl/sql 1后,硬分析统计.
SQL> select name,value from v$sysstat where name like '%parse%';
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse time cpu                                                         4948
parse time elapsed                                                     4468
parse count (total)                                                  170148
parse count (hard)                                                     2619  (硬分析次数)
parse count (failures)                                                   80
 

提高应用程序非常重要的手段之一就是采用PrepareStatement代替普通的Statement。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/76065/viewspace-789124/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/76065/viewspace-789124/