oracle性能提高---批量绑定

author:skate

time:2010-05-04

---

在我们的系统里，大家在写pl/sql时，处理多条记录时，几乎都是通过游标来完成的，这样是非常影响性能的。我们可以用批量绑定可以大大的改善。

 

批量绑定是oracle9i增加的特性，是指执行单次sql操作能传递所有集合元素的数据。通过绑定绑定变量可以极大的提高数据处理速度，提高应用程序的速度。批处理可以用与select，update，delete，insert语句上进行批量数据的处理。

 

在我们写pl/sql的时候，oracle会为select和dml语句分配上下文区（这个步骤是非常耗资源的，oracle对于太频繁的切换，都换用其它方式代替，例如spin），游标就是上下文区的指针。所以在我们日常coding时，尽量少用cursor，虽然cursor使用很简单，但也带来很大的性能问题，我们现在系统里的游标就非常多。

批量绑定是使用bulk collect和forall语句来完成的。

 

bulk collect：用与取得批量数据，只能用户，select，fetch和dml返回字句

forall：适用于批量的dml

下面简单介绍下使用批量绑定和不使用批量绑定的性能对比的样例,一共两个例子：

测试表：

create table TESTA

(

  ID   NUMBER(6) primary key not null ,

  NAME VARCHAR2(10)

)

**********************************************************************************

例子1：

Forall：

使用批量绑定：

SQL> declare

  2   type id_table_type is table of number(6) index by binary_integer;

  3   type name_table_type is table of varchar2(10) index by binary_integer;

  4 

  5   id_table id_table_type;

  6   name_table name_table_type;

  7   start_time  number(10);

  8   end_time number(10);

  9 

 10  begin

 11 

 12  for i in 1..5000  loop

 13     id_table(i):=i;

 14     name_table(i):='name'||to_char(i);

 15  end loop;

 16 

 17  start_time:=dbms_utility.get_time;

 18  for i in 1..id_table.count loop

 19   insert into testa values(id_table(i),id_table(i))  ;

 20  end loop;

 21  end_time:=dbms_utility.get_time;

 22  dbms_output.put_line('总的执行时间(s)：'|| (end_time-start_time)/100) ;

 23  end;

 24  /

 

总的执行时间(s)：.32

 

PL/SQL procedure successfully completed

 

Executed in 0.344 seconds

 

未使用批量绑定：

SQL> declare

  2   type id_table_type is table of number(6) index by binary_integer;

  3   type name_table_type is table of varchar2(10) index by binary_integer;

  4 

  5   id_table id_table_type;

  6   name_table name_table_type;

  7   start_time  number(10);

  8   end_time number(10);

  9 

 10  begin

 11 

 12  for i in 1..5000  loop

 13     id_table(i):=i;

 14     name_table(i):='name'||to_char(i);

 15  end loop;

 16 

 17  start_time:=dbms_utility.get_time;

 18  --for i in 1..id_table.count loop

 19  forall i in 1..id_table.count

 20   insert into testa values(id_table(i),id_table(i))  ;

 21  --end loop;

 22  end_time:=dbms_utility.get_time;

 23  dbms_output.put_line('总的执行时间(s)：'|| (end_time-start_time)/100) ;

 24  end;

 25  /

 

总的执行时间(s)：.02

 

PL/SQL procedure successfully completed

 

Executed in 0.047 seconds

 

SQL>

对比：

批量绑定  ：Executed in 0.344 seconds

非批量绑定：Executed in 0.047 seconds

结果：性能提高近8倍

**************************************************************************************************************

例子2：

bulk collect：

未批量绑定（取100条记录）：

SQL> declare

  2   type testa_table_type is table of testa%rowtype index by binary_integer;

  3   type id_table_type is table of number(6) index by binary_integer;

  4   type name_table_type is table of varchar2(10) index by binary_integer;

  5 

  6   cursor cur_testa is

  7   select *  from testa where rownum <=&num;

  8 

  9   id_table id_table_type;

 10   name_table name_table_type;

 11   testa_table testa_table_type;

 12 

 13   v_id number(6);

 14   v_name varchar2(10);

 15 

 16  begin

 17  for i in 1..5000  loop

 18     id_table(i):=i;

 19     name_table(i):='name'||to_char(i);

 20  end loop;

 21  forall i in 1..id_table.count

 22   insert into testa values(id_table(i),id_table(i))  ;

 23 

 24  -- select *  bulk collect into testa_table from testa where rownum <=&num;

 25  open cur_testa;

 26  loop

 27  fetch cur_testa into v_id,v_name;

 28  exit when cur_testa%notfound;

 29   --for i in 1..testa_table.count loop

 30      dbms_output.put_line('name is :'||v_name) ;

 31  end loop;

 32  close cur_testa;

 33  end;

 34  /

 

name is :2162

...

name is :2261

 

PL/SQL procedure successfully completed

 

Executed in 0.047 seconds

 

批量绑定（取100条记录）：

 

SQL> declare

  2   type testa_table_type is table of testa%rowtype index by binary_integer;

  3   type id_table_type is table of number(6) index by binary_integer;

  4   type name_table_type is table of varchar2(10) index by binary_integer;

  5 

  6   id_table id_table_type;

  7   name_table name_table_type;

  8   testa_table testa_table_type;

  9 

 10  begin

 11  for i in 1..5000  loop

 12     id_table(i):=i;

 13     name_table(i):='name'||to_char(i);

 14  end loop;

 15  forall i in 1..id_table.count

 16   insert into testa values(id_table(i),id_table(i))  ;

 17 

 18   select *  bulk collect into testa_table from testa where rownum <=&num;

 19   for i in 1..testa_table.count loop

 20      dbms_output.put_line('name is :'||testa_table(i).name) ;

 21   end loop;

 22  end;

 23  /

 

name is :2162

..

name is :2261

 

PL/SQL procedure successfully completed

 

Executed in 0.031 seconds

 

对比：

批量绑定  ：Executed in 0.031 seconds

非批量绑定：Executed in 0.047 seconds

 

结果：性能提高近2倍

*****************************************************************************

我这里只是简单介绍下批量绑定，具体使用方法就需要大家自己练习了。

 

和批量绑定有关的知识如下：

 

一．oracle的数据类型分为几大类：标量（scalar），复合（composite），引用（reference）和lob

 

1.标量（scalar）

合法的标量类型与数据库的列所使用的类型相同，也就是我常用的数据类型，

它又分为七个组：数字、字符、行、日期、行标识、布尔和可信。

 

2.复合（composite）

标量类型是经过预定义的，利用这些类型可以衍生出一些复合类型（集合）。主要有记录、表。

 

3.引用（reference）

它指向一个对象，例如游标就是引用

 

4.lob

LOB变量主要是用来存储大量数据的数据库字段

 

二．在处理复合类型的变量，oracle有对应的方法

 

oracle在pl/sql里处理数据的方法：

单行单列：用标量来存储

单行多列：用pl/sql记录来存储

多行单列：用pl/sql集合（索引表，嵌套表，varrary）

多行多列：pl/sql记录表

 

 

 

 ------end-----