SQL語句是一種方便的語言，同樣也是一種“迷惑性”的語言。這個主要體現在它的集合操作特性上。無論資料表資料量是1條，還是1億條，更新的語句都是完全相同。但是，實際執行結果（或者能否出現結果）卻是有很大的差異。

筆者在開發DBA領域的一個理念是：作為開發人員，對資料庫、對資料要有敬畏之心，一個語句發出之前，起碼要考慮兩個問題：目標資料表的總資料量是多少（投產之後）？你這個操作會涉及到多大的資料量？不同的回答，處理的方案其實是不同的。

更新大表資料，是我們在開發和運維，特別是在資料遷移領域經常遇到的一種場景。上面兩個問題的回答是：目標資料表整體就很大，而且更新範圍也很大。一個SQL從理論上可以處理。但是在實際中，這種方案會有很多問題。

本篇主要介紹幾種常見的大表處理策略，並且分析出他們的優劣。作為我們開發人員和DBA，選取的標準也是靈活的：根據你的操作型別（運維操作還是系統日常作業）、程式執行環境（硬體環境是否支援並行）和程式設計環境（是否可以完全獨佔所有資源）來綜合考量決定。

首先，我們需要準備出一張大表。

1、環境準備

我們選擇Oracle 11.2版本進行試驗。

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE     11.2.0.1.0     Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 – Production

準備一張大表。

SQL> create table t as select * from dba_objects;

Table created

SQL> insert into t select * from t;

72797 rows inserted

SQL> insert into t select * from t;

145594 rows inserted

（篇幅原因，中間過程略……）

SQL> commit;

Commit complete

SQL> select bytes/1024/1024/1024 from dba_segments where owner='SYS' and segment_name='T';

BYTES/1024/1024/1024

--------------------

 1.0673828125

SQL> select count(*) from t;

COUNT(*)

----------

 9318016

Executed in 14.711 seconds

資料表T作為資料來源，一共包括9百多萬條記錄，合計空間1G左右。筆者實驗環境是在虛擬機器上，一顆虛擬CPU，所以後面進行並行Parallel操作的方案就是示意性質，不具有代表性。

下面我們來看最簡單的一種方法，直接update。

2、方法1：直接Update

最簡單，也是最容易出問題的方法，就是“不管三七二十一”，直接update資料表。即使很多老程式設計師和DBA，也總是選擇出這樣的策略方法。其實，即使結果能出來，也有很大的僥倖成分在其中。

我們首先看筆者的實驗，之後討論其中的原因。先建立一張實驗資料表t_target。

SQL> create table t_targettablespace users as select * from t;

Table created

SQL> update t_target set owner=to_char(length(owner));

（長時間等待……）

在等待期間，筆者發現如下幾個現象：

ü  資料庫伺服器執行速度奇慢，很多連線操作速度減緩，一段時間甚至無法登陸；

ü  後臺會話等待時間集中在資料讀取、log space buffer、空間分配等事件上；

ü  長期等待，作業系統層面開始出現異常。Undo表空間膨脹；

ü  日誌切換頻繁；

此外，選擇這樣策略的朋友還可能遇到：前臺錯誤丟擲異常、客戶端連線被斷開等等現象。

筆者遇到這樣的場景也是比較糾結，首先，長時間等待（甚至一夜）可能最終沒有任何結果。最要命的是也不敢輕易的撤銷操作，因為Oracle要進行update操作的回滾動作。一個小時之後，筆者放棄。

updatet_target set owner=to_char(length(owner))

ORA-01013: 使用者請求取消當前的操作

（接近一小時未完成）

之後就是相同時間的rollback等待，通常是事務執行過多長時間，回滾進行多長時間。期間，可以透過x$ktuxe後臺內部表來觀察、測算回滾速度。這個過程中，我們只有“乖乖等待”。

SQL> select KTUXESIZ from x$ktuxe where KTUXESTA<>'INACTIVE';

  KTUXESIZ

----------

     62877

（……）

SQL> select KTUXESIZ from x$ktuxe where KTUXESTA<>'INACTIVE';

  KTUXESIZ

----------

       511

綜合這種策略的結果通常是：同業抱怨（影響了他們的作業執行）、提心吊膽（不知道執行到哪裡了）、資源耗盡（CPU、記憶體或者IO佔到滿）、勞而無功（最後還是被rollback）。如果是正式投產環境，還要承擔影響業務生產的責任。

我們詳細分析一下這種策略的問題：

首先，我們需要承認這種方式的優點，就是簡單和片面的高效。相對於在本文中其他介紹的方法，這種方式程式碼量是最少的。而且，這種方法一次性的將所有的任務提交給資料庫SQL引擎，可以最大程度的發揮系統一個方面（CPU、IO或者記憶體）的能力。

如果我們的資料表比較小，經驗值在幾萬一下，這種方法是比較合適的。我們可以考慮使用。

另一方面，我們要看到Oracle Update的另一個方面，就是Undo、Redo和程式工作負載的問題。熟悉Oracle的朋友們知道，在DML操作的時候，Undo和Redo是非常重要的方面。當我們在Update和Delete資料的時候，資料塊被修改之前的“前映象”就會儲存在Undo Tablespace裡面。注意：Undo Tablespace是一種特殊的表空間，需要儲存在磁碟上。Undo的存在主要是為了支援資料庫其他會話的“一致讀”操作。只要事務沒有被commit或者rollback，Undo資料就會一直保留在資料庫中，而且不能被“覆蓋”。

Redo記錄了進行DML操作的“後映象”，Redo生成是和我們修改的資料量相關。現實問題要修改多少條記錄，生成的Redo總量是不變的，除非我們嘗試nologging選項。Redo單個日誌成員如果比較小，Oracle應用生成Redo速度比較大。Redo Group切換頻度高，系統中就面臨著大量的日誌切換或者Log Space Buffer相關的等待事件。

如果我們選擇第一種方法，Undo表空間就是一個很大的瓶頸。大量的前映象資料儲存在Undo表空間中不能釋放，繼而不斷的引起Undo檔案膨脹。如果Undo檔案不允許膨脹（autoextend=no），Oracle DML操作會在一定時候報錯。即使允許進行膨脹，也會伴隨大量的資料檔案DBWR寫入動作。這也就是我們在進行大量update的時候，在event等待事件中能看到很多的DBWR寫入。因為，這些寫入中，不一定都是更新你的資料表，裡面很多都是Undo表空間寫入。

同時，長時間的等待操作，觸動Oracle和OS的負載上限，很多奇怪的事情也可能出現。比如程式僵死、連線被斷開。

這種方式最大的問題在於rollback動作。如果我們在長時間的事務過程中，發生一些異常報錯，通常是由於資料異常，整個資料需要回滾。回滾是Oracle自我保護，維持事務完整性的工具。當一個長期DML update動作發生，中斷的時候，Oracle就會進入自我的rollback階段，直至最後完成。這個過程中，系統是比較執行緩慢的。即使重啟伺服器，rollback過程也會完成。

所以，這種方法在處理大表的時候，一定要慎用！！起碼要評估一下風險。

3、方法2：PL/SQL匿名塊

上面方法1的最大問題在於“一次性瞬間壓力”大。無論是Undo量、還是Rollback量，都是有很大的問題。即使我們的系統能夠支援這樣的操作，如果update過程中存在其他的作業，必然受到影響。

PL/SQL匿名塊的原則在於平穩負載，分批的進行處理。這個過程需要使用bulk collect批次操作，進行遊標操作。

我們首先還原實驗環境。

SQL> truncate table t_target;

Table truncated

SQL> insert /*+append*/into t_target select * from t;

9318016 rows inserted

SQL> commit;

Commit complete

程式碼片段。

SQL> set timing on;

SQL> declare

  2    type rowid_list is table of urowid index by binary_integer;

  3    rowid_infosrowid_list;

  4    i number;

  5    cursor c_rowids is select rowid from t_target;

6  begin

  7    open c_rowids;

  8 

  9    loop

 10      fetch c_rowidsbulk collect into rowid_infos limit 2000;

 11 

 12      foralli in 1..rowid_infos.count

 13         update t_target set owner=to_char(length(owner)+1)

 14             where rowid=rowid_infos(i);

 15 

 16      commit;

 17      exit when rowid_infos.count<2000;

 18    end loop;

 19    close c_rowids;

20  end;

21  /

PL/SQL procedure successfully completed

Executed in 977.081 seconds

我們在977s完成了整個操作。這個方法有幾個特點：首先是批次的獲取bulk collect指定數量更新批次。第二個是使用forall的方法批次更新，減少引擎之間的切換。第三是更新一批之後，commit操作。

這樣的好處在於平穩化undo使用。如果資料量少，這種程式碼方法可能比直接update要慢。但是如果資料量大，特別是海量資料情況下，這種方法是可以支援非常大的資料表更新的。

程式碼中update操作，使用rowid，如果有其他業務方面的主鍵也可以使用替換。

在程式設計實踐中，有時候我們可能不能使用PL/SQL程式碼片段，只能使用SQL語句。這種時候就需要結合業務方面有沒有特點可以使用？這種時候往往也就考驗開發人員對業務特點的理解了。

在使用forall的時候，要注意一批更新的數量。根據一些Oracle文獻透露，內部SQL引擎在update的時候，也是分批進行的，每批大約1000條記錄。經驗告訴我們每批數量在1-5萬比較合適。

這是第二種方法，下面我們介紹一種簡單、可行的手段，比較方便。

4、方法3：insert append方法

從Undo和Redo的角度看，我們更喜歡insert，特別是生成較少redo的nologging和append插入。Update和Delete操作，都會生成Undo記錄，在我們看來，都是可以想辦法減少的方法。

這種方法的思路是：利用insert，將原來的資料表插入到一個新建立的資料表。在insert過程中，整理column的取值，起到update相同的效果。

下面是實驗過程。我們先建立資料表，注意可以設定nologging屬性。

SQL> create tablet_renamenologging as select * from t_target where 1=0;

Table created

Executed in 0.889 seconds

在這個過程中，我們建立的是一個空表。之後就可以插入資料，這種方法比較麻煩的地方，就是需要在insert指令碼中列出所有的資料列。當然，借用一些工具技巧，這個過程也可以很簡單。

SQL> insert /*+append*/into t_rename

2  selectto_char(length(owner)) owner,

3  OBJECT_NAME,

4  SUBOBJECT_NAME,

5  OBJECT_ID,

6  DATA_OBJECT_ID,

7  OBJECT_TYPE,

8  CREATED,

9  LAST_DDL_TIME,

10  TIMESTAMP,

11  STATUS,

12  TEMPORARY,

13  GENERATED,

14  SECONDARY,

15  NAMESPACE,

16  EDITION_NAME from t_target;

9318016 rows inserted

Executed in 300.333 seconds

使用append操作，可以減少redo log的生成。從結果看，一共執行了300s左右，效率應該是比較好的。

之後，提交事務。將原來的資料表刪除，將新資料表rename成原有資料表名稱。

SQL> commit;

Commit complete

Executed in 0.031 seconds

SQL> drop table t_target purge;

Table dropped

Executed in 1.467 seconds

SQL> rename t_rename to t_target;

Table renamed

Executed in 0.499 seconds

SQL> select count(*) from t_target;

COUNT(*)

----------

   9318016

Executed in 14.336 seconds

最後，可以將nologging屬性修改回來，將資料表約束新增上。

SQL> alter table t_target logging;

Table altered

Executed in 0.015 seconds

這種方法的好處在於效率，在資料量維持中高的情況下，這種方法速度是比較吸引人的。但是，這種方式也要消耗更多的儲存空間。在儲存空間允許的情況下，可以用這種方法。

如果資料量更大，達到海量的程度，比如幾十G的資料表，這種方法就值得考量一下了。需要結合硬體環境和執行環境完成。另外，這種方法涉及到資料表的建立等運維工作特性，故不適合在應用程式中使用，適合在運維人員過程中使用。

還有，就是這種方法的實際對備份的影響。由於我們使用了nologging+append選項，生成的redo log數量是不足以進行還原的，所以如果要實現完全恢復的話，資料庫實際上是失去了連續還原的依據。因此，如果真正使用了這個方法在生產環境下，之後需要進行資料庫全備份操作。

如果資料庫版本為11.2以上，我們可以使用Oracle的一個新特性dbms_parallel_execute包，進行資料表並行更新。詳見下面介紹。

5、方法3：dbms_paralle_execute並行包使用

其他最佳化手段都用上的時候，並行是可以嘗試的方法。並行parallel就是利用多個process同時進行處理，從而提高處理效率的方法。Parallel的使用有一些前提，也有一些不良反應。並行的前提是硬體支援，並行技術本身要消耗很多的資源，相當於是將伺服器資源“榨乾”來提速。在規劃並行策略的時候，首先要看硬體資源是不是支援，單核CPU情況下，也就不需要使用這個技術了。

使用並行之後，必然對其他正在執行程式、作業有影響。所以，筆者的經驗是：一般應用不要考慮並行的事情，如果發現特定場景存在並行的需要，可以聯絡DBA或者運維人員確定可控的技術方案。

在11.2之前，使用並行稍微複雜一些，很多朋友在使用的時候經常是“有名無實”，看似設定了並行，但是實際還是單程式執行。11.2之後，Oracle提供了新的並行操作介面dbms_parallel_execute，這讓並行更加簡單。

說明：本篇不是專門介紹dbms_parallel_execute介面，只作為介紹。詳細內容參見筆者專門介紹這個介面的文章。

dbms_parallel_execute工作採用作業task方式，後臺執行。首先是按照特定的原則進行資料分割，將工作資料集合分割為若干chunk。之後啟動多個後臺job進行工作。在劃分工作集合的問題上，Oracle提供了三種方法，rowid、column_value和SQL，分別按照rowid、列值和特定SQL語句進行分割。

注意：使用dbms_parallel_execute介面包有一個前提，就是job_queue_process引數必須設定非空。如果為0，則我們的程式執行之後被阻塞掛起。

恢復資料環境。

SQL> create table t_targettablespace users as select * from t where 1=0;

Table created

Executed in 0.078 seconds

SQL> insert /*+append*/into t_target select * from t;

9318016 rows inserted

Executed in 64.974 seconds

SQL> commit;

Commit complete

Executed in 0.109 seconds

引數環境。

SQL> show parameter job_queue

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

job_queue_processes                  integer     1000

在這個任務中，我們選擇使用create_chunks_by_rowid方法。這種方法通用型強，執行計劃穩定性好。

SQL> set serveroutput on;

SQL> declare

  2    vc_sqlvarchar2(1000);

  3    n_try number;

  4    n_status number;

5  begin

  6    --create a task

  7    dbms_parallel_execute.create_task(task_name => 'Huge_Update');

  8 

  9    --By Rowid

 10    dbms_parallel_execute.create_chunks_by_rowid(task_name => 'Huge_Update',

 11     table_owner => 'SYS',table_name => 'T_TARGET',by_row =>true,chunk_size => 10000);

 12 

 13    vc_sql := 'update /*+rowid(dda)*/t_target set owner=to_char(length(owner)) where rowid between :start_id and :end_id';

 14 

 15    dbms_parallel_execute.run_task(task_name => 'Huge_Update',sql_stmt =>vc_sql,language_flag =>dbms_sql.native,parallel_level => 3);

 16  --防止失敗後重啟

 17    n_try := 0;

 18    n_status := dbms_parallel_execute.task_status('Huge_Update');

 19    while (n_try<2 and (n_status != dbms_parallel_execute.FINISHED)) loop

 20       n_try := n_try + 1;

 21       dbms_parallel_execute.resume_task('Huge_Update');

 22       n_status := dbms_parallel_execute.task_status('Huge_Update');

 23    end loop;

 24 

 25    dbms_output.put_line(''||n_try);

 26    dbms_parallel_execute.drop_task('Huge_Update');

27  end;

28  /

0

PL/SQL procedure successfully completed

Executed in 1177.106 seconds

在程式碼中，需要注意start_id和end_id兩個繫結變數。這兩個範圍值是介面固定的。這種方法使用了1177s來完成工作。

在執行過程中，我們也有很多方法來監督執行過程。Oracle提供了兩個檢視，關於parallel_execute介面的。Dba_parallel_execute_tasks表示了提交的任務，裡面我們可以看到狀態資訊。

SQL> col task_name for a15;

SQL> select task_name, status from dba_parallel_execute_tasks;

TASK_NAME           STATUS

------------------- -------------------

Huge_Update         PROCESSING

SQL> select task_name, JOB_PREFIX from dba_parallel_execute_tasks;

TASK_NAME            JOB_PREFIX

-------------------- ------------------------------

Huge_Update          TASK$_655

執行中，我們從v$session中可能看到後臺的程式會話。

SQL> select sid, serial#, status, PROGRAM, SQL_ID, event from v$session where action like 'TASK$_655%';

       SID    SERIAL# STATUS   PROGRAM                                          SQL_ID        EVENT

---------- ---------- -------- ------------------------------------------------ ------------- ----------------------------------------------------------------

        35        507 ACTIVE   oracle@bspdev.localdomain (J003)                 d7xw8z3nzh5cg db file scattered read

        38        119 ACTIVE   oracle@bspdev.localdomain (J001)                 d7xw8z3nzh5cg log buffer space

        45       6612 ACTIVE   oracle@bspdev.localdomain (J000)                 d7xw8z3nzh5cg Data file init write

另一個檢視更有用dba_parallel_execute_chunks，其中包括了所有的chunk物件。Parallel Execute執行的原則就是資料的劃分，這個檢視中，可以看到哪些chunk已經完成，哪些沒有完成。

SQL> select status, count(*) from dba_parallel_execute_chunks group by status;

STATUS                 COUNT(*)

-------------------- ----------

ASSIGNED                      3

UNASSIGNED                 2315

PROCESSED                   571

ASSIGNED狀態表示多程式狀態正在處理，我們設定了三個後臺程式執行。UNASSIGNED表示沒有完成，正等待處理。PROCESSED表示已經處理完。

這種方法應該是目前找到比較好的方法。缺點就是程式碼量比較大。優點是處理使用並行，如果物理條件支援，執行效率是很高的。而且，在海量資料表的情況下，這種策略是很值得推薦的。

6、結論

更新大量的資料，是我們常見的一種問題場景。無論對開發人員，還是運維人員，都有不同的挑戰。筆者本篇要強調的是：沒有絕對好的策略，都是針對特別的場景和背景，選取最適合的策略。從而更好地完成任務。盲目的執行SQL語句，是一種典型不負責任的行為，需要避免杜絕。

海量資料處理，是很多系統開發人員，有時候甚至是運維人員，經常面對的需求。介面海量資料檔案載入入庫、批次資料更新、階段資料歸檔刪除是我們經常遇到的應用需求。針對不同的實際情況，包括軟硬體、運維環境、SLA視窗期要求，我們需要採用不同的策略和方法進行問題解決。

在筆者之前文章《Oracle中如何更新一張大表記錄》（http://blog.itpub.net/17203031/viewspace-1061065/）中，介紹了以Oracle資料庫端為中心，進行大表資料處理過程中的一些方法和考慮因素。簡單的說，海量資料處理難點不在語句層面，而在如何平衡各種需求因素。比較常見的因素有如下：

ü  業務系統正常生產衝擊。大資料操作絕大多數場景是在生產環境。在7*24可用性需求日益強化的今天，業務系統一個SQL執行之後，影響減慢核心操作速度，嚴重甚至系統崩潰，絕對不是我們運維人員希望見到的；

ü  操作視窗期長短。在相同的業務操作量的情況下，平緩化操作負載一定是以增加操作時間作為前提的。增加延長操作時間是否能夠在維護視窗內完成，也是需要考量的問題；

ü  對資料一致性的影響。一些“流言”方法（如nologging），雖然可以減少操作負載，但是潛在會給系統備份連續性帶來災難影響；

此外，SQL語句本身最佳化，操作策略也會有一些可以提高的空間。但是，一些問題還是需要單純的大量資料處理。當其他常規手段出盡的時候，在硬體條件允許下，並行、併發操作往往是不錯的選擇。

在11gR2中，Oracle為海量資料處理提供了很多方便的支援。工具包dbms_parallel_execute可以支援將海量資料分拆為獨立的chunk任務，並行執行作業。本篇就詳細介紹這個新特性的使用。

1、環境準備

實驗環境為11.2.0.3。

SQL> select * from v$version;

BANNER

------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 – Production

構造一張大表。說明：條件所限，筆者環境比較簡單，一些效能方面的優勢比較難體現出來。先建立出一個單獨表空間。

SQL> create tablespace test datafile size 2G autoextend on

  2  extent management local uniform size 1m

  3  segment space management auto;

Tablespace created

SQL> create table t as select * from dba_objects;

Table created

SQL> insert into t select * from t;

75586 rows inserted

（一系列的insert操作……）

SQL> commit;

Commit complete

資料表T包括大約2千萬條記錄，佔用空間體積在2G左右。

SQL> select count(*) from t;

  COUNT(*)

----------

  19350016

SQL> select bytes/1024/1024/1024, tablespace_name from dba_segments where owner='SYS' and segment_name='T';

BYTES/1024/1024/1024 TABLESPACE_NAME

-------------------- ------------------------------

        2.0986328125 TEST

Dbms_parallel_execute並不是傳統的多程式並行操作，本質上是透過作業管理器Schedule來完成系列作業的（在後文中會詳細證明）。所以前提要求job_queue_processes引數設定不能為0。

SQL> show parameter job

NAME                                 TYPE        VALUE

-------------------- ------------------------------

job_queue_processes                  integer     1000

2、dbms_parallel_execute包執行介紹

Dbms_parallel_execute是Oracle 11g推出的一個全新並行操作介面。它的原理為：當Oracle需要處理一個大量資料處理，特別是update操作的時候，可以將其拆分為若干各chunk分塊，以多程式作業（Schedule Job）分塊執行操作。從而降低一次性undo的使用，更進一步的便於斷點續作。

Dbms_parallel_execute包使用要滿足兩個條件：

ü  執行程式使用者需要擁有create job系統許可權；

ü  Dbms_parallel_execute程式包執行中需要呼叫dbms_sql包的一些方法，所以也需要該程式包執行許可權；

並行包的執行有兩個問題需要呼叫者確定：chunk分割方法和並行作業程式個數。

傳統的單執行緒執行策略中，無論任務多大，都是對應一個Server Process進行處理。如果呼叫了並行，會有對應的協調程式和工作程式存在（v$px_process）。

如果啟用了並行執行，一個關鍵問題在於如何劃分任務，將一個資料表更新操作劃分為多個小資料集合操作。Dbms_parallel_execute包支援三種任務劃分方法。

ü  By_rowid方法：依據rowid將運算元據進行劃分；

ü  By_number_col方法：輸入定義一個數字列名稱，依據這個列的取值進行劃分；

ü  By_SQL語句方法：給一個SQL語句，使用者可以幫助定義出每次chunk的起始和終止id取值；

在三種方法中，筆者比較推薦rowid方法，理由是條件要求低、操作速度快。如果操作過程中沒有明確的對資料表作業，這種策略是首選。具體比較可以從下面的實驗中看出。

確定了劃分方法，還要確定每個chunk的大小。注意：這個chunk設定大小並不一定是每個chunk運算元據行的數量。針對不同的分割槽型別，有不同的策略。這個在下面實驗中筆者也會給出明確的解析。

並行程式個數表示的是當“一塊”任務被劃分為“一堆”相互獨立的任務集合之後，準備多少個工作程式進行工作。這個是並行包使用的關鍵，類似於並行度，是需要依據實際軟硬體資源負載情況綜合考慮。

長時間作業存在一個問題，就是呼叫使用者希望隨時瞭解執行情況。Oracle提供了兩個資料檢視user_parallel_execute_tasks和user_parallel_execute_chunks，分別檢視Task執行情況和各個chunk執行完成情況。

在Oracle官方文件中，給出了呼叫dbms_parallel_execute包的方法流程，本文使用的也就是這個指令碼的變種，特此說明。下面，我們先看第一種by rowid方法。

3、By Rowid劃分chunk方法

Oracle中的rowid是資料實際物理位置的表示。藉助rowid直接定位資料，是目前Oracle獲取資料最快的方法。所以在RBO中，第一執行計劃被確定為rowid訪問方式。

依據Oracle文件提供的PL/SQL匿名塊，修改處我們第一個rowid範圍查詢。

declare

  vc_task varchar2(100);

  vc_sql varchar2(1000);

  n_try number;

  n_status number;

begin

  --Define the Task

  vc_task := 'Task 1: By Rowid';  --Task名稱

  dbms_parallel_execute.create_task(task_name => vc_task); --手工定義一個Task任務；

  --Define the Spilt

  dbms_parallel_execute.create_chunks_by_rowid(task_name => vc_task,

                                               table_owner => 'SYS',

                                               table_name => 'T',

                                               by_row => true,

                                               chunk_size => 1000); --定義Chunk

  vc_sql := 'update /*+ ROWID(dda) */t set DATA_OBJECT_ID=object_id+1 where rowid between :start_id and :end_id';

  --Run the task

  dbms_parallel_execute.run_task(task_name => vc_task,

                                 sql_stmt => vc_sql,

                                 language_flag => dbms_sql.native,

                                 parallel_level => 2); --執行任務，確定並行度

  --Controller

  n_try := 0;

  n_status := dbms_parallel_execute.task_status(task_name => vc_task);

  while (n_try<2 and n_status != dbms_parallel_execute.FINISHED) loop

     dbms_parallel_execute.resume_task(task_name => vc_task);

     n_status := dbms_parallel_execute.task_status(task_name => vc_task);

  end loop;        

  --Deal with Result

  dbms_parallel_execute.drop_task(task_name => vc_task);                                   

end;

/

從呼叫過程來看，這個並行操作包括下面幾個步驟：

ü  定義Task；

ü  確定chunk劃分方法，定義每個chunk的範圍資訊；

ü  執行作業，確定並行作業程式數量；

這個呼叫過程和我們常見的並行方式有很大差異，類似於Oracle的Job Schedule機制。由於執行過程比較長，我們可以有比較從容的檢視並行執行包的情況。

從user_parallel_execute_tasks中，看到當前作業的關鍵資訊。注意：chunk_type表示的是採用什麼樣的劃分方法。JOB_PREFIX對應的則是Schedule中的內容。

SQL> select task_name, chunk_type, JOB_PREFIX from user_parallel_execute_tasks;

TASK_NAME            CHUNK_TYPE   JOB_PREFIX

-------------------- ------------ ------------------------------

Task 1: By Rowid     ROWID_RANGE  TASK$_4

在user_parallel_execute_chunks中，作業的所有chunk劃分，每個chunk對應的一行資料。其中包括這個chunk的起始和截止rowid。對應的chunk取值對應的就是每個chunk的資料行數。

SQL> select chunk_id, task_name, status, start_rowid, end_rowid from user_parallel_execute_chunks where rownum<10;

  CHUNK_ID TASK_NAME            STATUS               START_ROWID        END_ROWID

---------- -------------------- -------------------- ------------------ ------------------

         1 Task 1: By Rowid     PROCESSED            AAATLKAAHAAAACAAAA AAATLKAAHAAAACxCcP

         2 Task 1: By Rowid     PROCESSED            AAATLKAAHAAAACyAAA AAATLKAAHAAAADjCcP

         3 Task 1: By Rowid     PROCESSED            AAATLKAAHAAAADkAAA AAATLKAAHAAAAD/CcP

         4 Task 1: By Rowid     PROCESSED            AAATLKAAHAAAAEAAAA AAATLKAAHAAAAExCcP

         5 Task 1: By Rowid     PROCESSED            AAATLKAAHAAAAEyAAA AAATLKAAHAAAAFjCcP

         6 Task 1: By Rowid     PROCESSED            AAATLKAAHAAAAFkAAA AAATLKAAHAAAAF/CcP

         7 Task 1: By Rowid     PROCESSED            AAATLKAAHAAAAGAAAA AAATLKAAHAAAAGxCcP

         8 Task 1: By Rowid     PROCESSED            AAATLKAAHAAAAGyAAA AAATLKAAHAAAAHjCcP

         9 Task 1: By Rowid     PROCESSED            AAATLKAAHAAAAHkAAA AAATLKAAHAAAAH/CcP

9 rows selected

作為user_parallel_execute_chunks，一個很重要的欄位就是status狀態列，用於標註每個chunk的處理情況。我們可以依據這個欄位來判斷任務完成情況。

SQL> select status, count(*) from user_parallel_execute_chunks group by status;

STATUS                 COUNT(*)

-------------------- ----------

ASSIGNED                      2

UNASSIGNED                 5507

PROCESSED                   938

（過一會之後…….）

SQL> select status, count(*) from user_parallel_execute_chunks group by status;

STATUS                 COUNT(*)

-------------------- ----------

ASSIGNED                      2

UNASSIGNED                 5441

PROCESSED                  1004

從status欄位，我們可以分析出並行作業工作的原理。每一個chunk記錄在劃分之後，都是設定為unassiged狀態，包括起始和終止的id資訊（rowid或者column_range）。每次處理的chunk是assigned狀態，實驗程式中我們設定parallel_level為2，所以每次都是2個chunk是assigned狀態。處理結束之後，設定為processed狀態。

海量資料更新最大的問題在於undo擴充的量，我們檢查一下執行過程中的undo size情況。

SQL> select sum(bytes)/1024/1024 from dba_undo_extents where status='ACTIVE';

SUM(BYTES)/1024/1024

--------------------

SQL> select sum(bytes)/1024/1024 from dba_undo_extents where status='ACTIVE';

SUM(BYTES)/1024/1024

--------------------

                  16

SQL> select sum(bytes)/1024/1024 from dba_undo_extents where status='ACTIVE';

SUM(BYTES)/1024/1024

--------------------

                  10

每次的資料量都不大，說明每次都是一小塊chunk的操作。也確定使用parallel執行的過程，是分步小塊commit的過程。在job檢視中，我們也可以明確的看出作為作業的資訊。

SQL> select owner, job_name, JOB_ACTION, SCHEDULE_TYPE, state, last_start_date from dba_scheduler_jobs where job_name like 'TASK$_4%';

OWNER   JOB_NAME  JOB_ACTION                                 SCHEDULE_TYPE STATE           LAST_START_DATE

------- ----------- ------------------------------------------ ------------- --------------- -----------------------------------

SYS     TASK$_4_2  DBMS_PARALLEL_EXECUTE.RUN_INTERNAL_WORKER  IMMEDIATE     RUNNING         10-2月 -14 01.48.34.947417 下午 PRC

SYS     TASK$_4_1  DBMS_PARALLEL_EXECUTE.RUN_INTERNAL_WORKER  IMMEDIATE     RUNNING         10-2月 -14 01.48.34.730487 下午 PRC

注意：傳統的並行程式v$px_process中沒有看到資料資訊，說明並行程式包並不是Oracle傳統的資料庫並行方案。

SQL> select * from v$px_process;

SERVER_NAME STATUS           PID SPID                            SID    SERIAL#

----------- --------- ---------- ------------------------ ---------- ----------

執行結束資訊：

25    --Controller

 26    n_try := 0;

 27    n_status := dbms_parallel_execute.task_status(task_name => vc_task);

 28    while (n_try<2 and n_status != dbms_parallel_execute.FINISHED) loop

 29       dbms_parallel_execute.resume_task(task_name => vc_task);

 30       n_status := dbms_parallel_execute.task_status(task_name => vc_task);

 31    end loop;

 32 

 33    --Deal with Result

 34    dbms_parallel_execute.drop_task(task_name => vc_task);

 35  end;

 36  /

PL/SQL procedure successfully completed

Executed in 944.453 seconds

更新2G資料一共使用945s，合計約16分鐘。

從上面的資料檢視和呼叫過程，我們可以得到如下結論：

對dbms_parallel_execute執行包而言，透過確定chunk方法和chunk size，可以將一個很大的資料集合劃分為若干各小chunk集合，分步進行操作處理。程式碼中設定的parallel_level，體現在設定Job的個數上。啟動作業任務後，Oracle並不是啟動傳統的並行機制，而是在Job Schedule的基礎上建立parallel_level個數的作業，型別為立即執行。多個作業分別執行各個chunk的小塊工作。使用Job Schedule的一個好處在於可以方便的進行作業resume和start過程。

下面我們討論by number col和by SQL兩種執行方法。