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

约定不等于承诺〃 2022-07-10 17:54 148阅读 0赞

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;

5   id\_table id\_table\_type;

6   name\_table name\_table\_type;

7   start\_time  number(10);

8   end\_time number(10);

10  begin

12  for i in 1..5000  loop

13     id\_table(i):=i;

14     name\_table(i):='name'||to\_char(i);

15  end loop;

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;

5   id\_table id\_table\_type;

6   name\_table name\_table\_type;

7   start\_time  number(10);

8   end\_time number(10);

10  begin

12  for i in 1..5000  loop

13     id\_table(i):=i;

14     name\_table(i):='name'||to\_char(i);

15  end loop;

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;

6   cursor cur\_testa is

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

9   id\_table id\_table\_type;

10   name\_table name\_table\_type;

11   testa\_table testa\_table\_type;

13   v\_id number(6);

14   v\_name varchar2(10);

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))  ;

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;

6   id\_table id\_table\_type;

7   name\_table name\_table\_type;

8   testa\_table testa\_table\_type;

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))  ;

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变量主要是用来存储大量数据的数据库字段