测试游标 FOR 循环和 FORALL 的性能差异

原创文章，转载请注明出处：http://blog.itpub.net/30168575/viewspace-1465287/

通过 FORALL 加速 DML
BULK COLLECT 对查询加速，而 FORALL 会对增删改以及合并加速（Oracle 11g 才支持 FORALL 的合并）。FORALL 告诉PL/SQL引擎要先把一个或者多个集合的所有成员都绑定到SQL语句中，然后再把语句发送给SQL引擎。


FORALL 语句的语法
虽然 FORALL 语句带有一个迭代模式，但它不是一个 FOR 循环。语法如下：
FORALL index_row IN 
  [ lower_bound .. upper_bound | 
    INDICES OF indexing_collection |
    VALUES OF indexing_collection ]
  [ SAVE EXCEPTIONS ]
  sql_statements;
其中：
  index_row 是一个整数，由Oracle隐式声明，并且被定义做集合的索引值。
  lower_bound 操作开始的索引值（行或者是集合元素）
  upper_bound 操作结束的索引值（行或者是集合元素）
  sql_statements 将对每一个元素执行的SQL语句
  indexing_collection 这是一个pl/sql集合，是一个指向sql_statements所使用的绑定数组的索引的集合；INDICES OF 和 VALUES OF 是从Oracle 10g才有的。
  SAVE EXCEPTIONS 这是一个可选的子句，告诉 FORALL 处理全部行，不过把发生的任何异常都保存下来。


测试表中有超过100万数据，处理逻辑为简单的从一个表复制数据到另一个表

-- 测试表：

点击(此处)折叠或打开

1. DROP TABLE data_test;
   
2. CREATE TABLE data_test(
   
3.   hi_card_nbr number,
   
4.   hi_card_str VARCHAR2(4000),
   
5.   hi_card_ts Timestamp
   
6. );
   
7. /
   
8. DECLARE
   
9.   counter NUMBER;
   
10. BEGIN
    
11.   EXECUTE IMMEDIATE \'TRUNCATE TABLE data_test\';
    
12.   INSERT INTO data_test
    
13.   SELECT t.cpu_time, t.sql_text, t.first_load_time FROM v$sql t
    
14.   UNION ALL
    
15.   SELECT t.object_id, t.object_name, t.timestamp FROM DBA_objects t;
    
16.   
    
17.   counter := SQL%ROWCOUNT;
    
18.   
    
19.   WHILE counter < 1000000 LOOP
    
20.     INSERT INTO data_test SELECT * FROM data_test;
    
21.     counter := counter + SQL%ROWCOUNT;
    
22.   END LOOP;
    
23. END;
    
24. /
    
25. COMMIT;

测试逻辑代码单元

点击(此处)折叠或打开

1. CREATE OR REPLACE PACKAGE my_test IS
   
2.   PROCEDURE copydata_test;
   
3.   PROCEDURE copydata_test_forall;
   
4. END my_test;
   
5. /
   
6. CREATE OR REPLACE PACKAGE BODY my_test IS
   
7.   PROCEDURE copydata_test IS
   
8.     CURSOR cur IS
   
9.       SELECT * from data_test;
   
10.   BEGIN
    
11.     FOR rec IN cur LOOP
    
12.       INSERT INTO data_test_into VALUES(rec.hi_card_nbr, rec.hi_card_str, rec.hi_card_ts);
    
13.     END LOOP;
    
14.   END copydata_test;
    
15. 
    
16. 
    
17. 
    
18. 
    
19.   PROCEDURE copydata_test_forall IS
    
20.     CURSOR cur IS
    
21.       SELECT * from data_test;
    
22.     TYPE rec_t IS TABLE OF cur%ROWTYPE INDEX BY binary_integer;
    
23.     rec rec_t;
    
24.   BEGIN
    
25.     OPEN cur;
    
26.     LOOP
    
27.     FETCH cur BULK COLLECT INTO rec LIMIT 100;
    
28.     FORALL indx IN rec.FIRST .. rec.LAST
    
29.       INSERT INTO data_test_into VALUES rec(indx);
    
30. 
    
31. 
    
32.     EXIT WHEN cur%NOTFOUND;
    
33.     END LOOP;
    
34.     CLOSE cur;
    
35.   END copydata_test_forall;
    
36. END my_test;
    
37. /

测试过程

点击(此处)折叠或打开

1. -- 先调用两遍预热缓存
   
2. BEGIN
   
3.   my_test.copydata_test;
   
4.   ROLLBACK;
   
5.   my_test.copydata_test_forall;
   
6.   ROLLBACK;
   
7. END;
   
8. /
   
9. BEGIN
   
10.   my_test.copydata_test;
    
11.   ROLLBACK;
    
12.   my_test.copydata_test_forall;
    
13.   ROLLBACK;
    
14. END;
    
15. /
    
16. ROLLBACK;

 测试两种方法的性能
SQL> SET timing ON
SQL> BEGIN
  2    my_test.copydata_test;
  3  END;
  4  /
  
PL/SQL procedure successfully completed
 
Executed in 608.966 seconds
SQL> ROLLBACK;
 
Rollback complete
 
Executed in 81.277 seconds
SQL> BEGIN
  2    my_test.copydata_test_forall;
  3  END;
  4  /
  
 
PL/SQL procedure successfully completed
 
Executed in 43.665 seconds
SQL> ROLLBACK;
 
Rollback complete
 
Executed in 4.477 seconds

-- 测试结果：
-- 很容易看出，使用游标 FOR 循环时，处理耗时608.966s，而改用了 FORALL 之后，处理时间减少到 43.665s。这个提升是非常值得尝试的。

FORALL 语句将 DML 批量从 PL/SQL引擎提交给 SQL引擎，从而减少了程序执行过程中上下文切换的次数。而上下文切换的开销是十分昂贵的。

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

转载于:http://blog.itpub.net/30168575/viewspace-1465287/