领导要求测试单条记录insert到oracle数据库的速度,之前测试的结果不是很理想,
并且因为之前做过一个把数据导出成文本的测试,发现实际上oracle本身执行的速度
是很快的,但是大量的时间花费在了客户端与服务器的通信上,我想这个insert
应该和那个导出也是类似的,所以这次采用bulk的方式
测试了下,速度确实快了很多。
它的实现原理其实就是实现了批量,减小了客户端和服务器间交互的时间。
另外,我这里是用plsql的方式来实现的,oci,pro*c,java等不同方式应该
都有类似的实现,不同场景不同情况下性能可能略有出入,但偏差应该不是很大。
但是对bulk的这种执行还不是非常熟悉,也是参考了网上的一些资料,具体的可
以看文档PL/SQL Language Reference 11g Release 2 (11.2)中bulk的部分
另外,删除的时候也可以应用这种批量的形式
| 普通方式 | bulk方式 |
构造数据 | 100s | 47s |
从现有告警表取数据 | 102s | 55s |
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 3 08:46:33 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn test4/test4;
Connected.
SQL> show user
USER is "TEST4"
构造数据
普通方式
SQL> select count(*) from t2;
COUNT(*)
----------
1000000
SQL> truncate table t2;
Table truncated.
SQL> select count(*) from t2;
COUNT(*)
----------
0
SQL> set time on
08:47:35 SQL>
08:47:35 SQL>
08:47:46 SQL>
08:47:46 SQL> declare
08:47:47 2 i number:=0;
08:47:47 3 begin
08:47:47 4 for i in 1..1000000 loop
08:47:47 5 insert into t2 values (6, i,11,11,11,11,
08:47:47 6 10,10,10,10,10,10,10,10,10,10,
08:47:47 7 10,10,10,10,10,10,10,10,10,10,
08:47:47 8 10,10,10,10,10,10,10,10,10,10,
08:47:47 9 10,10,10,10,10,10,10,10,10,10,
08:47:47 10 10,10,10,10,10,10,10,10,10,10,
08:47:47 11 10,10,10,10,10,10,10,10,10,10,
08:47:47 12 10,10,10,10,10,10,10,10,10,10,
08:47:47 13 10,10,10,10,10,10,10,10,10,10,
08:47:47 14 10,10,10,10,10,10,10,10,10,10,
08:47:47 15 10,10,10,10,10,10,10,10,10,10,
08:47:47 16 10,10,10,10,10,10,10,10,10,10,
08:47:47 17 10,10,10,10,10,10,10,10,10,10,
08:47:47 18 10,10,10,10,10,10,10,10,10,10,
08:47:47 19 10,10,10,10,10,10,10,10,10,10,
08:47:47 20 10,10,10,10,10,10,10,10,10,10,
08:47:47 21 10,10,10,10,10,10,10,10,10,10,
08:47:47 22 10,10,10,10,10,10,10,10,10,10,
08:47:47 23 10,10,10,10,10,10,10,10,10,10,
08:47:47 24 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:47:48 25 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:47:48 26 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:47:48 27 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:47:48 28 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:47:48 29 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:47:48 30 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:47:48 31 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:47:48 32 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:47:48 33 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:47:48 34 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:47:48 35 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:47:48 36 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:47:48 37 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:47:48 38 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:47:48 39 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:47:48 40 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:47:48 41 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:47:48 42 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:47:48 43 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:47:48 44 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
08:47:48 45 end loop;
08:47:48 46 end;
08:47:49 47 /
PL/SQL procedure successfully completed.
08:49:29 SQL> COMMIT;
Commit complete.
08:49:57 SQL> select count(*) from t2;
COUNT(*)
----------
1000000
08:50:17 SQL> truncate table t2;
Table truncated.
bulk方式
08:51:21 SQL> declare
08:51:23 2 TYPE NumTab IS TABLE OF t2.id%TYPE INDEX BY PLS_INTEGER;
08:51:23 3 pnums NumTab;
08:51:23 4 iterations CONSTANT PLS_INTEGER := 1000000;
08:51:23 5 begin
08:51:23 6 FOR j IN 1..iterations LOOP -- populate collections
08:51:23 7 pnums(j) := j;
08:51:23 8 end loop;
08:51:23 9 FORALL i IN 1..iterations
08:51:23 10 insert into t2 values (pnums(i),11,11,11,11,11,
08:51:23 11 10,10,10,10,10,10,10,10,10,10,
08:51:23 12 10,10,10,10,10,10,10,10,10,10,
08:51:23 13 10,10,10,10,10,10,10,10,10,10,
08:51:23 14 10,10,10,10,10,10,10,10,10,10,
08:51:23 15 10,10,10,10,10,10,10,10,10,10,
08:51:23 16 10,10,10,10,10,10,10,10,10,10,
08:51:23 17 10,10,10,10,10,10,10,10,10,10,
08:51:23 18 10,10,10,10,10,10,10,10,10,10,
08:51:23 19 10,10,10,10,10,10,10,10,10,10,
08:51:23 20 10,10,10,10,10,10,10,10,10,10,
08:51:23 21 10,10,10,10,10,10,10,10,10,10,
08:51:23 22 10,10,10,10,10,10,10,10,10,10,
08:51:23 23 10,10,10,10,10,10,10,10,10,10,
08:51:23 24 10,10,10,10,10,10,10,10,10,10,
08:51:23 25 10,10,10,10,10,10,10,10,10,10,
08:51:23 26 10,10,10,10,10,10,10,10,10,10,
08:51:23 27 10,10,10,10,10,10,10,10,10,10,
08:51:23 28 10,10,10,10,10,10,10,10,10,10,
08:51:23 29 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:51:23 30 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:51:23 31 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:51:23 32 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:51:23 33 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:51:23 34 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:51:23 35 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:51:23 36 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:51:23 37 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:51:23 38 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:51:23 39 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:51:23 40 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:51:23 41 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:51:23 42 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:51:23 43 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:51:23 44 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:51:23 45 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:51:23 46 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:51:23 47 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:51:23 48 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
08:51:23 49 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
08:51:23 50 end;
08:51:24 51 /
PL/SQL procedure successfully completed.
08:52:11 SQL> commit;
Commit complete.
08:52:25 SQL> select count(*) from t2;
COUNT(*)
----------
1000000
从现有告警表插入
普通方式
09:08:37 SQL> truncate table t1;
Table truncated.
09:08:44 SQL>
09:08:45 SQL> DECLARE
09:08:51 2 BEGIN
09:08:51 3 FOR cur IN (SELECT * FROM test.TFA_ALARM_ACT1 where rownum<1000001) LOOP
09:08:51 4 INSERT INTO t1 VALUES cur;
09:08:51 5 END LOOP;
09:08:51 6 END;
09:08:51 7 /
PL/SQL procedure successfully completed.
09:10:33 SQL> commit;
Commit complete.
09:10:39 SQL> select count(*) from t1;
COUNT(*)
----------
1000000
09:10:45 SQL> truncate table t1;
Table truncated.
09:13:40 SQL> select count(*) from t1;
COUNT(*)
----------
0
bulk方式
09:14:12 SQL>
09:14:12 SQL> DECLARE
09:14:13 2 CURSOR cur IS
09:14:13 3 SELECT * FROM test.TFA_ALARM_ACT1 where rownum<1000001;
09:14:13 4 TYPE rec IS TABLE OF test.TFA_ALARM_ACT1%ROWTYPE;
09:14:13 5 recs rec;
09:14:13 6 BEGIN
09:14:13 7 OPEN cur;
09:14:13 8 WHILE (TRUE) LOOP
09:14:13 9 FETCH cur BULK COLLECT
09:14:13 10 INTO recs LIMIT 100;
09:14:13 11 FORALL i IN 1 .. recs.COUNT
09:14:13 12 INSERT INTO t1 VALUES recs (i);
09:14:13 13 EXIT WHEN cur%NOTFOUND;
09:14:13 14 END LOOP;
09:14:13 15 CLOSE cur;
09:14:13 16 END;
09:14:13 17 /
PL/SQL procedure successfully completed.
09:15:08 SQL> commit;
Commit complete.
09:15:25 SQL> select count(*) from t1;
COUNT(*)
----------
1000000
参考资料
Tips: AUL License, DBA Tools, 新浪微博
Oracle » http://www.anysql.net/oracle/plsql_bulk_operation.html
2007-02-26
在Pro*C中可以为SQL中的绑定变量传入一个数组, 进行批量处理, 减少User Call和Execute Count, 尤其是在客户端服务器的情况下, 这个性能提升十份明显. 在Java中也有类似的executeBatch调用, 最新版本的Perl中也可以用数组来绑定一个变量. Oracle在9i的PL/SQL中正式支持了批量操作, 下面是一个批量删除的例子:
DECLARE
TYPE ARRROWID IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
tbrows ARRROWID;
row PLS_INTEGER;
cursor delete_table is select row_id from testobj_rowid;
BEGIN
open delete_table;
loop
fetch delete_table bulk collect into tbrows limit 250;
FORALL row IN 1 .. tbrows.count()
DELETE TEST.TESTOBJ WHERE rowid = tbrows(row);
commit;
-- dbms_lock.sleep(1);
exit when delete_table%notfound;
end loop;
close delete_table;
END;
/
致于批量的插入或更新角本, 照着这个改改应当很容易出来的. 写在这儿, 也是为了方便我自已抄写.
=======
在Oracle数据库中,不是提交越频繁越好。恰恰相反,批量提交可以得到更好的性能。这篇文章给大家简单展示一下在Oracle数据库中逐行提交于批量提交两者之间的性能差别。最后再给出一种可以极大改变性能的方法。
1.创建表t_ref,并初始化880992条数据,用于后面的。实验
sec@ora10g> create table t_ref as select * from all_objects;
sec@ora10g>insertinto t_ref select * from t;
220248 rows created.
sec@ora10g> /
sec@ora10g> /
sec@ora10g> /
sec@ora10g> /
sec@ora10g> /
440496 rows created.
2.编写一个简单的PL/SQL块来模拟逐行提交的情况,注意观察执行时间。
我们的目标是将t_ref表中的数据全部插入到t中。
sec@ora10g> set timing on
sec@ora10g> DECLARE
2 BEGIN
3 FOR cur IN (SELECT * FROM t_ref) LOOP
4 INSERT INTO t VALUES cur;
5 COMMIT;
6 END LOOP;
7 END;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:03:12.77
逐行提交的情况下,一共用时3分12秒。
3.再来模拟批量提交的情况。
sec@ora10g> truncate table t;
Table truncated.
sec@ora10g> DECLARE
2 v_count NUMBER;
3 BEGIN
4 FOR cur IN (SELECT * FROM t_ref) LOOP
5 INSERT INTO t VALUES cur;
6 v_count := v_count + 1;
7 IF v_count >= 100 THEN
8 COMMIT;
9 END IF;
10 END LOOP;
11 COMMIT;
12 END;
13 /
PL/SQL procedure successfully completed.
Elapsed: 00:01:27.69
此时共用时1分27秒,大约是逐行提交方法一半的时间。由此可见,对于Oracle应该尽量以批量提交的方式来完成工作。
4.最后我们使用Oracle更高级的方法完成上面的任务,体验一下极限速度。
sec@ora10g> DECLARE
2 CURSOR cur IS
3 SELECT * FROM t_ref;
4 TYPE rec IS TABLE OF t_ref%ROWTYPE;
5 recs rec;
6 BEGIN
7 OPEN cur;
8 WHILE (TRUE) LOOP
9 FETCH cur BULK COLLECT
10 INTO recs LIMIT 100;
11 FORALL i IN 1 .. recs.COUNT
12 INSERT INTO t VALUES recs (i);
13 COMMIT;
14 EXIT WHEN cur%NOTFOUND;
15 END LOOP;
16 CLOSE cur;
17 END;
18 /
Elapsed: 00:00:09.75
此时我们仅仅使用了不到10秒的时间就完成了曾经需要几分钟才能完成的任务。
5.小结
在Oracle数据库中,频繁的COMMIT会引起大量Redo Log的物理I/O,会极大的限制的性能。因此,为提高数据库性能,尽可能的批量提交。数据库
Oracle有很多优秀的方法值得尝试。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28952551/viewspace-763271/,如需转载,请注明出处,否则将追究法律责任。