eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 无尽的爱(Endless Love) - 美丽的神话 | Blog首页 | NetAPP + Linux iSCSI配置step-by-step »

批量绑定(bulk binds):FOR循环与FORALL的性能比较

通常在SQL语句中给PL/SQL变量赋值叫做绑定(Binding),一次绑定一个完整的集合称为批量绑定(Bulk Binding)。

批量绑定(Bulk binds)可以通过减少在PL/SQL和SQL引擎之间的上下文切换(context switches )提高了性能.

批量绑定(Bulk binds)包括:
(i) Input collections, use the FORALL statement,一般用来改善DML(INSERT、UPDATE和DELETE) 操作的性能
(ii) Output collections, use BULK COLLECT clause,一般用来提高查询(SELECT)的性能

FORALL的语法如下:

FORALL index IN lower_bound..upper_bound sql_statement;

下面是一个简单测试,用以说明FORALL与FOR循环的性能差异。
SQL> drop table blktest;

Table dropped.

Elapsed: 00:00:00.13
SQL> 
SQL> CREATE TABLE blktest (num NUMBER(20), name varchar2(50));

Table created.

Elapsed: 00:00:00.08
SQL> 
SQL> CREATE OR REPLACE PROCEDURE bulktest
  2  IS
  3     TYPE numtab IS TABLE OF NUMBER (20)
  4        INDEX BY BINARY_INTEGER;
  5  
  6     TYPE nametab IS TABLE OF VARCHAR2 (50)
  7        INDEX BY BINARY_INTEGER;
  8  
  9     pnums    numtab;
 10     pnames   nametab;
 11     t1       NUMBER;
 12     t2       NUMBER;
 13     t3       NUMBER;
 14  BEGIN
 15     FOR j IN 1 .. 1000000
 16     LOOP
 17        pnums (j)         := j;
 18        pnames (j)        := 'Seq No. ' || TO_CHAR (j);
 19     END LOOP;
 20  
 21     SELECT DBMS_UTILITY.get_time
 22       INTO t1
 23       FROM DUAL;
 24  
 25     FOR i IN 1 .. 1000000
 26     LOOP
 27        INSERT INTO blktest
 28             VALUES (pnums (i), pnames (i));
 29     END LOOP;
 30  
 31     SELECT DBMS_UTILITY.get_time
 32       INTO t2
 33       FROM DUAL;
 34  
 35     FORALL i IN 1 .. 1000000
 36        INSERT INTO blktest
 37             VALUES (pnums (i), pnames (i));
 38  
 39     SELECT DBMS_UTILITY.get_time
 40       INTO t3
 41       FROM DUAL;
 42  
 43     DBMS_OUTPUT.put_line ('Execution Time (hsecs)');
 44     DBMS_OUTPUT.put_line ('---------------------');
 45     DBMS_OUTPUT.put_line ('FOR loop: ' || TO_CHAR (t2 - t1));
 46     DBMS_OUTPUT.put_line ('FORALL:   ' || TO_CHAR (t3 - t2));
 47  END;
 48  /

Procedure created.

Elapsed: 00:00:01.46
SQL> exec bulktest;
Execution Time (hsecs)
---------------------
FOR loop: 30361
FORALL:   4792

PL/SQL procedure successfully completed.

Elapsed: 00:06:32.92

我们可以看到FORALL较FOR循环性能大大提高。

历史上的今天...
    >> 2008-11-13文章:
    >> 2006-11-13文章:

无觅

By eygle on 2005-11-13 20:28 | Comments (6) | SQL.PLSQL | 528 |

6 Comments

我的测试怎么这样啊?

SQL> CREATE TABLE blktest (num NUMBER(20), name varchar2(50));

Table created

SQL>
SQL> CREATE OR REPLACE PROCEDURE bulktest
2 IS
3 TYPE numtab IS TABLE OF NUMBER (20)
4 INDEX BY BINARY_INTEGER;
5
6 TYPE nametab IS TABLE OF VARCHAR2 (50)
7 INDEX BY BINARY_INTEGER;
8
9 pnums numtab;
10 pnames nametab;
11 t1 NUMBER;
12 t2 NUMBER;
13 t3 NUMBER;
14 BEGIN
15 FOR j IN 1 .. 1000000
16 16 LOOP
17 pnums (j) := j;
18 pnames (j) := 'Seq No. ' || TO_CHAR (j);
19 END LOOP;
20
21 SELECT DBMS_UTILITY.get_time
22 INTO t1
23 FROM DUAL;
24
25 FOR i IN 1 .. 1000000
26 LOOP
27 INSERT INTO blktest
28 VALUES (pnums (i), pnames (i));
29 END LOOP;
30
31 SELECT DBMS_UTILITY.get_time
32 INTO t2
33 FROM DUAL;
34
35 FORALL i IN 1 .. 1000000
36 INSERT INTO blktest
37 VALUES (pnums (i), pnames (i));
38
39 SELECT DBMS_UTILITY.get_time
40 INTO t3
41 FROM DUAL;
42
43 DBMS_OUTPUT.put_line ('Execution Time (hsecs)');
44 DBMS_OUTPUT.put_line ('---------------------');
45 DBMS_OUTPUT.put_line ('FORALL: ' || TO_CHAR (t3 - t2));
46 DBMS_OUTPUT.put_line ('FOR loop: ' || TO_CHAR (t2 - t1));
47 END;
48 /
Procedure created

SQL> exec bulktest;

Execution Time (hsecs)
---------------------
FORALL: 10181
FOR loop: 7272

查一下其他方面,看是不是日志切换或其他因素影响了结果.

或者多测试几次看看结果.

您好,刚看完您讲的oracle之路和数据库优化,想问个问题:
我执行你上面的例子,怎么看不到DBMS_OUTPUT.put_line输出的信息?怎么设置?多谢了。呵呵


set serveroutput on

我的结果,环境 win2003 oracle 10g
scott@ORA10G>exec bulktest;
Execution Time (hsecs)
---------------------
FOR loop: 12199
FORALL: 9027

PL/SQL 过程已成功完成。

我在xp,oracle9i,用pl/sql developer执行结果:
Execution Time (hsecs)
---------------------
FOR loop: 3186
FORALL: 568


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com