手机版
你好,游客 登录 注册 搜索
背景:
阅读新闻

PL/SQL中的forall简单测试

[日期:2015-07-15] 来源:Linux社区  作者:jeanron100 [字体: ]

之前写过一篇bulk collect的文章,只是对于bulk collect做了简单的实例。http://www.linuxidc.com/Linux/2015-07/119948.htm
其实不光是bulk collect,forall对于pl/sql的性能的提升也是相当大的。
 可以参见下面的两个图,可以看到其实在pl/sql中,可能很多时候我们所写的pl/sql代码会在sql引擎和plsql引擎建进行上下文的切换,这个过程还是很耗费时间的。

 而forall却是相反,是提供一次上下文切换,会在forall的过程中进行数据的包装处理。一次发送给sql执行器去处理,大大减少了上下文切换时间。

 对于此,可以想象,如果cursor中的结果集很庞大,就很可能进行大量的上下文切换,导致执行速度骤降。
 我们来做一个简单的实例来说明一下。

 我们创建一个表test_data,里面大概有7万多的数据量。
n1@TEST11G> create table test_data as select *from all_objects;
 Table created.

 n1@TEST11G> select count(*)from test_data;
  COUNT(*)
 ----------
      71659
 1 row selected

n1@TEST11G> create unique index inx_test_data_pk on test_data(object_id);
Index created.
Elapsed: 00:00:00.48

然后就开始执行存储过程
[ora11g@oel1 plsql]$ cat a.sql
create or replace procedure test_proc as 
  cursor test_cur is select *from test_data;
  i number;
begin
  i:=1;
  for cur in test_cur
  loop
  update test_data set object_name=cur.object_name
where object_id=cur.object_id;
  dbms_output.put_line('this is a test');
  i:=i+1;
  end loop;
end;
/

 exec test_proc;

执行的过程中会看到进程占用了大量的cpu资源。可见进行了大量的上下文切换。其实一个主要的信息点就是可以看到输出了大量的日志内容,最后还因为缓存的原因退出了。

......
this is a test
 this is a test
 this is a test
 this is a test
 this is a test
 this is a test
 this is a test
 this is a test
 this is a test
 this is a test
 this is a test
 this is a test
 this is a test
 this is a test
 this is a test
 BEGIN test_proc; END;

 *
 ERROR at line 1:
 ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
 ORA-06512: at "SYS.DBMS_OUTPUT", line 32
 ORA-06512: at "SYS.DBMS_OUTPUT", line 97
 ORA-06512: at "SYS.DBMS_OUTPUT", line 112
 ORA-06512: at "N1.TEST_PROC", line 10
 ORA-06512: at line 1

Elapsed: 00:00:13.73

同样的要求,如果使用forall的形式,使用的代码如下。
[ora11g@oel1 plsql]$ cat b.sql
create or replace procedure test_proc as 
  cursor test_cur is select *from test_data;
  type rec_type is table of test_cur%rowtype index by binary_integer;
  recs rec_type;
begin
  open test_cur;
  fetch test_cur bulk collect into recs;
  close test_cur;
  forall i in 1..recs.COUNT
  update test_data set object_name=recs(i).object_name
where object_id=recs(i).object_id;
  dbms_output.put_line('this is a test');
end;
/

这种效果就好得多,可以看到日志中只输出了一次日志信息,意味着只进行了一次上下文切换,这种方法明显要好很多。
n1@TEST11G> exec test_proc;
this is a test
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.67
对于大批量的数据处理还是很值得推荐的。后续会使用dbms_profiler来对此测试一下,可以看出在一些实现点中还是存在着很大的不同。

Oracle数据库之PL/SQL程序基础设计  http://www.linuxidc.com/Linux/2015-06/119013.htm

PL/SQL Developer实用技巧分享 http://www.linuxidc.com/Linux/2014-09/107391.htm

更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址http://www.linuxidc.com/Linux/2015-07/119949.htm

linux
相关资讯       PL/SQL 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款