图灵社区按
TEAP是什么?TEAP是Turingbook Early Access Program的简称,即早期试读,它公布的是图灵在途新书未经编辑的内容。一本书的翻译周期约为3到6个月,如果在翻译过程中,译者就能与读者进行沟通和交流,对整本书的翻译品质是有帮助的。通过TEAP,读者可以提前阅读将来才能出版的内容,译者也能收获宝贵的反馈意见,改进翻译,提高质量。

本书原名为Expert PLSQL Practices,中文暂定名为《Oracle PL/SQL实战》,本文选自第6章 批量SQL操作第7节

我的社区ID博客

真正的好处:客户端批量处理
正如我在本章开始时提到的,我反复在五金店内折返降低了我的效率。不过,有时事情更糟糕得多:我支付了一个商品,开车回家,然后在车里意识到我需要回去,然后开车回五金店,再购买别的东西。(为敏感的读者着想,我不准备提及当发生这种情况时,我妻子使用的“钟爱”一词!)

本章描述了在PL/SQL中使用批量操作来访问数据的效率,但这相当于已经在五金店里。从外部的客户端应用程序中访问数据库(网络往返),并采用一次一行的方式处理数据的代价相当于来回驾驶汽车去商店。这个代价是可以用一些简单的演示量化的。
首先,我将构建(使用PL/SQL,当然!)许多用3GL代码实现的客户端应用程序的一个副本,即一个程序在表上打开一个游标,而另一个程序一次获取一行。下面是在bulk_network_1.sql中的代码:

SQL> create or replace    
  2  package PKG1 is    
  3    
  4   procedure open_cur(rc in out sys_refcursor);    
  5    
  6   procedure fetch_cur(rc in out sys_refcursor, p_row out hardware.item%type);    
  7    
  8  end;    
  9  /    
Package created.    

SQL> create or replace    
  2  package body PKG1 is    
  3    
  4   procedure open_cur(rc in out sys_refcursor) is    
  5   begin    
  6     open rc for select item from hardware;    
  7   end;    
  8    
  9   procedure fetch_cur(rc in out sys_refcursor, p_row out hardware.item%type) is    
 10   begin    
 11     fetch rc into p_row;    
 12   end;    
 13    
 14  end;    
 15  /    
Package body created.    

SQL Plus将足以作为客户端应用程序调用这个包。当然SQL Plus本身即是一个真正的SQL客户端,你能明确控制它每次获取的行数这个属性,所以上面的演示可以写成这样:

set arraysize n  (n=1 for single row fetch, n=1000 for multirow fetch)    
select item from HARDWARE;    

但我想模拟3GL应用程序做的工作,即包含用显式调用方式打开游标,再从它重复获取,然后将其关闭。要打开游标,然后反复获取结果集,直到最后一行,我可以使用脚本bulk_single_fetch_100000.sql,它执行以下步骤:

variable rc refcursor    
exec pkg1.open_cur(:rc)    
variable n number    
exec     pkg1.fetch_cur(:rc,:n);    
exec     pkg1.fetch_cur(:rc,:n);    
[repeat 100000 times]    

为了无需滚动100,000行输出来查看演示所消耗的时间,我关掉终端输出并记录之前和之后的时间戳。

SQL> variable rc refcursor    
SQL> exec pkg1.open_cur(:rc)    
SQL> select to_char(systimestamp,'HH24:MI:SS.FF') started from dual;    
STARTED    
------------------    
12:11:18.779000    

SQL> set termout off    
SQL> @bulk_single_fetch_1000.sql   -- 包含 1000 次获取调用    
[重复 100 次]    

SQL> set termout on    
SQL> select to_char(systimestamp,'HH24:MI:SS.FF') ended from dual;    

    ENDED    
------------------    
12:12:47.270000    

你可以看到,100,000次到数据库的往返大约用了90秒。我启用会话跟踪,再次运行演示并检查TKPROF格式化后的结果文件。下面是结果:

SELECT ITEM    
FROM   HARDWARE    
call     count       cpu    elapsed       disk      query    current        rows    
------- ------  -------- ---------- ---------- ---------- ----------  ----------    
Parse        1      0.00       0.05          0          1          0           0    
Execute      1      0.00       0.00          0          0          0           0    
Fetch   100000      1.17       0.94          0     100003          0      100000    
------- ------  -------- ---------- ---------- ---------- ----------  ----------    
total   100002      1.17       1.00          0     100004          0      100000    

可以看到,这90秒中,实际上花在数据库工作上的只有一秒钟。其他89秒的时间都用在网络来回跳跃上了。数据库实际上是无所事事,而从客户端应用程序的角度来看,它是在等待数据库。用开车来打比方,我的车轮在转动但哪儿也没去。我目前的工作场所,每当我们中间层服务器上遇到出现这种行为的3GL程序,我们称之为“中间件失败”。以这种方式来标识质量差的代码,确实能够引起开发团队的关注,效果好得出奇!

现在,让我们采用这个新发现的批量处理方法从数据库中批量获取数据,并把数据批量地传回客户端。下面新实现的代码(bulk_network_2.sql)的性能会好很多:

SQL> create or replace    
  2  package PKG2 is    
  3    
  4   type t_num_list is table of hardware.item%type index by pls_integer;    
  5    
  6   procedure open_cur(rc in out sys_refcursor);    
  7    
  8   procedure fetch_cur(rc in out sys_refcursor, p_rows out t_num_list);    
  9    
 10  end;    
 11  /    
Package created.    
SQL> create or replace    
  2  package body PKG2 is    
  3    
  4   procedure open_cur(rc in out sys_refcursor) is    
  5   begin    
  6     open rc for select item from hardware;    
  7   end;    
  8    
  9   procedure fetch_cur(rc in out sys_refcursor, p_rows out t_num_list) is    
 10   begin    
 11     fetch rc bulk collect into p_rows limit 1000;    
 12   end;    
 13    
 14  end;    
 15  /    
Package body created.    

重新运行演示。每次将有1,000行被批量从数据库中收集并传回客户端。由于SQL Plus本身并不理解返回来的数组,数据将被简单地追加到一个大的VARCHAR2变量(the_data)来模拟客户端接收数组的数据。这里就是一个例子(bulk_multi_fetch_in_bulk.sql):

SQL> variable rc refcursor    
SQL> exec pkg2.open_cur(:rc)    
PL/SQL procedure successfully completed.    
SQL> select to_char(systimestamp,'HH24:MI:SS.FF') started from dual;    
STARTED    
------------------    
12:39:09.704000    

SQL> variable the_data varchar2(4000);    
SQL> set termout off    
SQL> declare    
  2    n pkg2.t_num_list;    
  3  begin    
  4    :the_data := null;    
  5    pkg2.fetch_cur(:rc,n);    
  6    for i in 1 .. n.count loop    
  7      :the_data := :the_data||n(i);    
  8    end loop;    
  9  end;      
[repeated 100 times]    

SQL> select to_char(systimestamp,'HH24:MI:SS.FF') ended from dual;    
ENDED    
------------------    
12:39:09.837000    

差异是惊人的。周转时间立即从90秒下降至0.13秒。
启用跟踪重新运行演示程序,跟踪信息揭示了在数据库往返(次数)上的减少。

    SELECT ITEM      
    FROM HARDWARE      
    call     count       cpu    elapsed       disk      query    current        rows      
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------      
    Parse        1      0.00       0.00          0          0          0           0      
    Execute      1      0.00       0.00          0          0          0           0      
    Fetch      100      0.04       0.04          0        256          0      100000      
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------      
    total      102      0.04       0.04          0        256          0      100000      

演示程序仍然获取了100,000行(最右列),但只调用了100次获取。即使只有这个简单的演示,它所展示的好处已经好得不能再好了。经常见到Oracle的客户花费大笔资金,通过各种优化手段的实施,比如高速缓存、创建索引、SQL调优等,或许能使得他们的系统性能得到额外的百分之10-15%的提高,有时候,客户甚至购买更多的硬件,并支付增加的相关许可证费用。但考虑本演示中的获得的性能好处:

从90秒到0.13秒,几乎提高了700倍。

作为应用程序开发人员,如果你修改代码,使(程序)速度快上几百倍,那将会使你的应用更加成功,而你更受欢迎!能否降低一个客户端应用程序与数据库交互中的网络往返次数,很大程度上取决于开发人员对他们的客户程序所用语言工具的运用能力,应用的设计是否能够充分利用一种智能的方式将数据传递到数据库,还有开发人员的努力。但作为一个数据库开发人员,如果你能确保你的PL/SQL到数据库的接口都配备允许客户端应用程序传递和接收大容量数据(的功能),那么你更接近实现良好的应用程序性能。