[TEAP早期試讀]真正的好處：客戶端批量處理

lt發表於2012-03-14
客戶端

圖靈社群按：
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到資料庫的介面都配備允許客戶端應用程式傳遞和接收大容量資料（的功能），那麼你更接近實現良好的應用程式效能。

相關文章