海量資料處理，是很多系統開發人員，有時候甚至是運維人員，經常面對的需求。介面海量資料檔案載入入庫、批次資料更新、階段資料歸檔刪除是我們經常遇到的應用需求。針對不同的實際情況，包括軟硬體、運維環境、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兩種執行方法。

使用11g dbms_parallel_execute執行並行更新（上）

相關文章