11G R2中的並行執行，dbms_parallel_execute

在以前我們處理大資料量的時候，一般機器硬體都足夠強悍，單執行緒的處理怎麼也消耗不了多少的系統資源，而整個處理的過程又比較慢，這個時候並行就出現了。 並行可以透過ORACLE的PARALLEL來指定和執行，而在11G R2版本中，這個功能得到了加強，ORACLE推出了一個dbms_parallel_execute包，專門來處理這些內容。這個包其實原理也比較簡 單：首先是把資料分成一個個CHUNK，然後指定並行度來同時執行這些任務。就類似手工的按照主鍵、ROWID或者資料的日期等等進行並行處理類似。下面 來看看這個咚咚怎麼使用[@more@]





使用dbms_parallel_execute一般分為3個步驟：建立一個TASK；然後建立CHUNK把資料進行分批；最後是執行這個TASK。下面來做一個演示，演示更新一個表的過程：

1、首先建立一個TASK，名稱為update sql:
exec dbms_parallel_execute.create_task('update sql');

這時可以透過檢視來檢視任務的建立情況：
SQL> select task_name,chunk_type,status from dba_parallel_execute_tasks;

TASK_NAME CHUNK_TYPE STATUS
--------------------------------------- ------------ -------------------
update sql UNDELARED CREATED

說明CHUNK還沒有建立，所以CHUNK TYPE未知；狀態是剛建立完成狀態

2、把將要更新的表按照ROWID進行分批，分到各個CHUNK中：
exec dbms_parallel_execute.create_chunks_by_rowid(task_name => 'update sql',table_owner => 'CGZHANG',table_name => 'BORECEIVEDLOG',by_row => false,chunk_size => 10000);

BY_ROW：分CHUNK的型別。如果為TRUE，則後面的CHUNK_SIZE表示是行；如果是FALSE，則後面的CHUNK_SIZE表示的是BLOCK。
CHUNK_SIZE：CHUNK大小。如果BY_ROW為TRUE，表示多少行分為一個CHUNK；如果BY_ROW為FALSE，則表示多少塊分為一個CHUNK。

注意：這裡的TABLE_OWNER和TABLE_NAME是隻能用大寫的，不知道是否是BUG，小寫的時候會報ORA-29491錯誤。

分好後的CHUNK可以使用檢視檢視：
SQL> select chunk_id,task_name,status,start_rowid,end_rowid from dba_parallel_execute_chunks where rownum<=2;

CHUNK_ID TASK_NAME STATUS START_ROWID END_ROWID
---------- -------------------- -------------------- ------------------ ------------------
112627 update sql UNASSIGNED AAASKjAAUAAAHiAAAA AAASKjAAUAAAH1/CcP
112628 update sql UNASSIGNED AAASKkAAUAAAH2AAAA AAASKkAAUAAAIJ/CcP
這裡可以看到分好的每個CHUNK的ID，還有TASK_NAME，以及每個CHUNK的狀態，每個CHUNK從哪個ROWID開始，到哪個ROWID結束。在資料真正開始處理的時候，也可以透過這個檢視來檢視每個CHUNK的執行情況以及還有多少CHUNK沒有執行，大概可以評估出整個任務的進度。

3、執行並行任務，這裡更新其中一個欄位為原先的10倍：
EXEC DBMS_PARALLEL_EXECUTE.run_task(task_name => 'update sql',sql_stmt => 'update /*+ ROWID(e) */ cgzhang.boreceivedlog e SET e.bankid = e.bankid * 10 WHERE rowid BETWEEN :start_id AND :end_id',language_flag => DBMS_SQL.native,parallel_level => 4);

start_id和end_id是兩個佔位符，用來標識CHUNK的開始和結束；PARALLEL表示平行度；LANGUAGE_FLAG意義如下：
V6 (or 0) specifies version 6 behavior
NATIVE (or 1) specifies normal behavior for the database to which the program is connected
V7 (or 2) specifies Oracle database version 7 behavior

執行結果可以透過檢視檢視：
SQL> select task_name,status,job_prefix,sql_stmt,language_flag from dba_parallel_execute_tasks;

TASK_NAME STATUS JOB_PREFIX SQL_STMT LANGUAGE_FLAG
----------- ------------------- ------------- -------------------------------------------------- -------------
update sql FINISHED_WITH_ERROR TASK$_145 update /*+ ROWID(e) */ cgzhang.boreceivedlog e SET 1
可以看到任務的狀態時帶錯誤的完成。其中JOB_PREFIX表示後來進行並行時候所開啟的JOB的名稱的字首。那麼下面可以透過檢視CHUNK相關檢視來檢視具體的錯誤資訊：
SQL> select chunk_id,task_name,status,start_rowid,end_rowid,job_name,start_ts,end_ts,error_code,error_message from dba_parallel_execute_chunks where chunk_id in (112110,112211);

CHUNK_ID TASK_NAME STATUS START_ROWID END_ROWID JOB_NAME START_TS END_TS ERROR_CODE ERROR_MESSAGE
---------- ------------- -------------------- ------------------ ------------------ -------------- -------------------------------- -------------------------------- ---------- -------------------------------------
112211 update sql PROCESSED_WITH_ERROR AAASKVAAIAAD9eAAAA AAASKVAAIAAD9x/CcP TASK$_145_3 11-1月 -10 03.20.37.647108 下午 11-1月 -10 03.20.37.930320 下午 -1438 ORA-01438: 值大於為此列指定的允許精度
112110 update sql PROCESSED AAASKRAANAABZaAAAA AAASKRAANAABZt/CcP TASK$_145_4 11-1月 -10 03.20.40.640925 下午 11-1月 -10 03.20.40.680616 下午
這裡可以看到每個CHUNK開始和結束的時間，以及每個CHUNK完成的狀態，以及有錯誤的CHUNK的錯誤原因。

注意：這裡的每個CHUNK是在單獨一個事務中提交的。所以某幾個CHUNK發生錯誤後，可以修正錯誤，並使用下面的方法來繼續未完成的CHUNK的任務：
exec dbms_parallel_execute.resume_task(task_name => 'update sql',sql_stmt => 'update /*+ ROWID(e) */ cgzhang.boreceivedlog e SET e.bankid = e.bankid * 10 WHERE rowid BETWEEN :start_id AND :end_id',language_flag => 1);
執行完後，檢視檢視：
SQL> select task_name,status,job_prefix,sql_stmt,language_flag from dba_parallel_execute_tasks;

TASK_NAME STATUS JOB_PREFIX SQL_STMT LANGUAGE_FLAG
----------- ------------------- -------------- -------------------------------------------------------------------------------- -------------
update sql FINISHED TASK$_145 update /*+ ROWID(e) */ cgzhang.boreceivedlog e SET e.bankid = e.bankid * 10 WHER 1
狀態已經變成完成，而且CHUNK檢視中的所有的CHUNK狀態已經變成PROCESSED。



4、最後可以刪除完成的任務：
exec dbms_parallel_execute.drop_task(task_name => 'update sql');
刪除任務時，任務對應的CHUNK也會被刪除

5、如果中途想停止任務，可以使用：
exec dbms_parallel_execute.stop_task('update sql');




除了按照ROWID來進行分CHUNK外，ORACLE還提供了按照列來CHUNK以及按照SQL執行的結果來進行CHUNK。

按照列來進行CHUNK的時候，跟手工按照主鍵或者其他值來進行分割槽的方法類似，示例如下：
exec dbms_parallel_execute.create_chunks_by_number_col(task_name => 'update sql',table_owner => 'CGZHANG',table_name => 'BORECEIVEDLOG',table_column => 'BANKID',chunk_size => 100000);
這個時候的CHUNK是先查詢列的一個MAX和MIN值，然後按照CHUNK的粒度來進行CHUNK。
START_ID END_ID
--------------------------- ---------------------------
min_id_val min_id_val+1*chunk_size-1
min_id_val+1*chunk_size min_id_val+2*chunk_size-1
… …
min_id_val+i*chunk_size max_id_val
注意：ORACLE並不是那麼智慧的，所以如果進行CHUNK的列中如果有異常資料導致某一兩個值特別大，那麼就會因此產生很多很多空的CHUNK，導致整個CHUNK過程需要很長時間。而且，接下來的RUN_TASK的時候，傳入的START_ID和END_ID將是這裡CHUNK使用的值，那麼如果這個值上沒有索引或者索引的可選擇性不高，那整個執行過程就是噩夢。



之前的都是針對全表進行操作的，如果只需要對錶中的部分資料，或者基於某個查詢的結果集進行並行操作，那根據SQL結果進行CHUNK就派上用場了：
exec dbms_parallel_execute.create_chunks_by_SQL(task_name => 'update sql',sql_stmt => 'SELECT bankid,bankid FROM CGZHANG.BORECEIVEDLOG where bankorderresult=1',by_rowid => false);
這是隻針對bankorderresult為1的，按照BANKID進行CHUNK，每個BANKID是一個單獨的CHUNK，然後START_ID和END_ID都根據單獨的BANKID去批次操作。

exec dbms_parallel_execute.create_chunks_by_SQL(task_name => 'update sql',sql_stmt => 'SELECT rowid,rowid FROM CGZHANG.BORECEIVEDLOG where bankorderresult=1',by_rowid => true);
這是根據ROWID進行CHUNK劃分，每行是一個單獨的CHUNK

exec dbms_parallel_execute.create_chunks_by_SQL(task_name => 'update sql',sql_stmt => 'SELECT MIN(DEALID), MAX(DEALID) FROM (SELECT ROW_NUMBER() OVER(ORDER BY DEALID) RW, DEALID FROM CGZHANG.BORECEIVEDLOG WHERE BANKORDERRESULT = 1) GROUP BY CEIL(RW / 100000)',by_rowid => false);
這個是把BANKORDERRESULT = 1的記錄中，按照DEALID去進行CHUNK，然後CHUNK的大小為100000，得到的結果如下：
SQL> SELECT CHUNK_ID,TASK_NAME,STATUS,START_ROWID,END_ROWID,START_ID,END_ID FROM DBA_PARALLEL_EXECUTE_CHUNKS;

CHUNK_ID TASK_NAME STATUS START_ROWID END_ROWID START_ID END_ID
---------- ------------ -------------------- ------------------ ------------------ ---------- ----------
1775907 update sql UNASSIGNED 3337739000 3349852900
1775908 update sql UNASSIGNED 3252487000 3273417000
1775909 update sql UNASSIGNED 3294196300 3316557400
1775910 update sql UNASSIGNED 3273417000 3294196000
1775905 update sql UNASSIGNED 3007610000 3252486600
1775906 update sql UNASSIGNED 3316558300 3337738800
可以看到，這裡是以START_ID和END_ID進行CHUNK的，所以在RUN_TASK的時候，傳入的WHERE條件要是DEALID，示例如下：
EXEC DBMS_PARALLEL_EXECUTE.run_task(task_name => 'update sql',sql_stmt => 'update cgzhang.boreceivedlog e SET e.bankid = e.bankid * 10 WHERE dealid BETWEEN to_char(:start_id) AND to_char(:end_id)',language_flag => DBMS_SQL.native,parallel_level => 4);
這時因為DEALID為VARCHAR型別，雖然裡面存的是NUMBER數值，所以要對START_ID和END_ID變數進行TO_CHAR轉換，使得整個更新任務可以正確的使用到DEALID上的索引。


exec dbms_parallel_execute.create_chunks_by_SQL(task_name => 'update sql',sql_stmt => 'SELECT MIN(ROWID), MAX(ROWID) FROM (SELECT ROW_NUMBER() OVER(ORDER BY ROWID) RW, ROWID FROM CGZHANG.BORECEIVEDLOG WHERE BANKORDERRESULT = 1) GROUP BY CEIL(RW / 100000)',by_rowid => true);
這個是按照ROWID進行CHUNK（因為BY_ROWID引數為TRUE），每個CHUNK大小為100000行，得到的CHUNK結果如下：
SQL> SELECT CHUNK_ID,TASK_NAME,STATUS,START_ROWID,END_ROWID,,START_ID,END_ID FROM DBA_PARALLEL_EXECUTE_CHUNKS;

CHUNK_ID TASK_NAME STATUS START_ROWID END_ROWID START_ID END_ID
---------- --------------- -------------------- ------------------ ------------------ ---------- ----------
1775901 update sql UNASSIGNED AAASKVAAIAAD/dwAAE AAASKVAAIAAEELeAAB
1775902 update sql UNASSIGNED AAASKVAAIAAENxeAAC AAASKVAAIAAESlOAAJ
1775903 update sql UNASSIGNED AAASKVAAIAAEI+0AAC AAASKVAAIAAENxeAAA
1775904 update sql UNASSIGNED AAASKVAAIAAEELeAAC AAASKVAAIAAEI+0AAB
1775899 update sql UNASSIGNED AAASKVAAIAAD6qWAAA AAASKVAAIAAD/dwAAD
1775900 update sql UNASSIGNED AAASKVAAIAAESlPAAA AAASKVAAIAAEVh7AAI
這裡是按照START_ROWID和END_ROWID進行CHUNK的，所以START_ID和END_ID欄位都為空，這是RUN_TASK的時候要使用ROWID作為WHERE條件



測試過程中發現如果CHUNK很小，則導致分出來的CHUNK過多，那麼CHUNK_ID是使用一個SEQUENCE叫 DBMS_PARALLEL_EXECUTE_SEQ$來生成的，而這個SEQUENCE的預設CACHE值只有20，可以透過加大這裡的CACHE值解決一部分效能問題。




總結：
DMBS_PARALLEL_EXECUTE使用步驟基本是：建立任務、把資料進行CHUNK、執行任務三大步驟。

其中CHUNK中的BY_ROWID和BY_COL都比較容易理解，BY_SQL是不大容易理解也是最靈活的方式。其實BY_SQL就是執行一個查詢，但最重要的是查詢返回的結果要是能夠進行CHUNK的區間，這裡怎麼寫這個SQL就是非常頭疼的問題了（這裡感謝PUB的newkid為了提供的SQL），這個CHUNK寫好了，後面的問題就簡單了。

除了對自己進行資料更新外，也可以進行資料的並行遷移（只要把UPDATE改成INSERT另一個表就好了）等等許多複雜的任務，而且DBMS_PARALLEL_EXECUTE提供了set_chunk_status、 get_rowid_chunk等多種更靈活的方式來控制整個任務執行的過程，從而實現複雜任務的並行執行。




UPDATE:
“逆襲的W”網友提出了一個很好的問題，就是使用BY_SQL進行CHUNK的時候，如果資料不是唯一的，那麼使用ROW_NUMBER來進行CHUNK就會出現CHUNK之間有重疊，比如我10000條記錄一個CHUNK，結果裡面有10萬條都是相同的，那就會有這樣的問題存在。
使用RANK替代ROW_NUMBER可以解決這個問題，因為RANK會過濾掉重複的資料。

這也正說明了一點，CHUNK是並行操作很重要的一個步驟，而且CHUNK完成後，最好對CHUNK的結果進行校驗，保證資料不多不少。