使用11g dbms_parallel_execute執行並行更新（下）
上篇我們討論了dbms_parallel_execute的工作方法、使用流程和特點。本篇繼續來討論其他兩種劃分Chunk方式。說明：對每種劃分策略執行過程中，筆者都進行了不同的實驗，來說明其工作特點。
4、By Number Col劃分Chunk方法
應該說，使用rowid進行資料表劃分可以帶來很多好處。每個chunk資料獲取過程，本質上就是執行一個範圍Range操作。對於rowid而言，直接透過範圍檢索的效率是相當高的。
與Rowid方法對應兩種策略都是依據“資料表列範圍”進行chunk劃分。By Number Col的方法顧名思義，需要我們指定出一個數字型別列名稱。Oracle會依據這個列取值進行劃分。每個chunk實際上都是透過數字型別檢索到的結果集合進行處理。
當然，這個過程必然伴隨著我們對於“地勢”條件的依賴。每次從上千萬條記錄中，FTS的檢索出一個chunk資料顯然是很費力的操作過程。最直接的最佳化手段就是索引和分割槽。注意：如果我們沒有特殊的條件進行chunk劃分輔助，一定要考慮by number col方式是否適合。
SQL> create index idx_t_id on t(object_id);
Index created
Executed in 107.282 seconds
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,degree => 2);
PL/SQL procedure successfully completed
Executed in 87.453 seconds
修改的指令碼如下：
SQL> declare
2 vc_task varchar2(100);
3 vc_sql varchar2(1000);
4 n_try number;
5 n_status number;
6 begin
7 --Define the Task
8 vc_task := 'Task 2: By Number Col';
9 dbms_parallel_execute.create_task(task_name => vc_task);
10
11 --Define the Spilt
12 dbms_parallel_execute.create_chunks_by_number_col(task_name => vc_task,
13 table_owner => 'SYS',
14 table_name => 'T',
15 table_column => 'OBJECT_ID',
16 chunk_size => 1000); --定義chunk
17
18 vc_sql := 'update /*+ ROWID(dda) */t set DATA_OBJECT_ID=object_id+1 where object_id between :start_id and :end_id';
19 --Run the task
20 dbms_parallel_execute.run_task(task_name => vc_task,
21 sql_stmt => vc_sql,
22 language_flag => dbms_sql.native,
23 parallel_level => 1);
24
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 /
從執行流程上看，上面指令碼和by rowid方式沒有顯著地差異。最大的區別在於定義chunk時呼叫的方法，引數包括指定的資料表、列名和chunk size。注意：我們這裡定義了chunk size是1000，但是在執行過程中，我們不能保證每個chunk的大小是1000。這個結論我們在後面的闡述實驗中可以證明。
執行指令碼的速度顯著的比by rowid的慢了很多。但是我們也能發現很多技術細節。首先，我們會有一個時期，在chunk檢視中沒有結果返回。
SQL> select task_name, chunk_type, status from user_parallel_execute_tasks;
TASK_NAME CHUNK_TYPE STATUS
------------------------- ------------ -------------------
Task 2: By Number Col NUMBER_RANGE CHUNKING
Executed in 0.61 seconds
SQL> select status, count(*) from user_parallel_execute_chunks group by status;
STATUS COUNT(*)
-------------------- ----------
在之後，我們才能檢視到chunk處理情況。
SQL> select task_name, chunk_type, status from user_parallel_execute_tasks;
TASK_NAME CHUNK_TYPE STATUS
------------------------- ------------ -------------------
Task 2: By Number Col NUMBER_RANGE PROCESSING
SQL> select status, count(*) from user_parallel_execute_chunks group by status;
STATUS COUNT(*)
-------------------- ----------
ASSIGNED 1
UNASSIGNED 1557
PROCESSED 13
這個現象說明：對dbms_parallel_execute包處理過程來說，包括兩個重要的步驟，Chunk分塊步驟和Chunk處理步驟。無論是哪種分塊方法，Oracle都是首先依據分割原則，將任務拆分開來，規劃在任務檢視裡面。之後再進行分作業Job的Processing處理過程。
同by rowid方式中的rowid Range資訊一樣，我們在chunk檢視中也是可以看到數字列範圍的資訊。
SQL> select task_name, status, start_id, end_id, job_name from user_parallel_execute_chunks where rownum<5;
TASK_NAME STATUS START_ID END_ID JOB_NAME
------------------------- -------------------- ---------- ---------- ------------------------------
Task 2: By Number Col PROCESSED 25002 26001 TASK$_5_2
Task 2: By Number Col ASSIGNED 26002 27001 TASK$_5_1
Task 2: By Number Col ASSIGNED 27002 28001 TASK$_5_2
Task 2: By Number Col UNASSIGNED 28002 29001
注意：我們此處看到的chunk範圍是1000，由於資料準備過程，範圍1000絕對不意味著每個chunk的大小是1000。所以，我們也就可以推斷出，呼叫方法中的chunk size在number col方式中，是取值範圍的大小。
直觀的想，Oracle選取這樣的策略也是有依據的：Oracle可以直接選取一個最小和最大的資料列值，依次chunk取值範圍進行分割。這樣做可減少對資料檢索的壓力。
在執行過程中，我們跟蹤了執行會話的SQL語句，從shared pool中抽取出執行計劃。
SQL> select * from table(dbms_xplan.display_cursor(sql_id=>'f2z147unc1n3q'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID f2z147unc1n3q, child number 0
-------------------------------------
update /*+ ROWID(dda) */t set DATA_OBJECT_ID=object_id+1 where
object_id between :start_id and :end_id
Plan hash value: 538090111
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 74397 (100)| |
| 1 | UPDATE | T | | | | |
|* 2 | FILTER | | | | | |
|* 3 | INDEX RANGE SCAN| IDX_T_ID | 48375 | 472K| 197 (1)| 00:00:03 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:START_ID<=:END_ID)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
3 - access("OBJECT_ID">=:START_ID AND "OBJECT_ID"<=:END_ID)
匿名塊執行完畢。
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 11350.421 seconds
完成時間大大增加，摺合3個小時左右。這個實驗告訴我們：在三種方法選取如果不合適，效能會大大降低。
下面我們來看最後一種方法by SQL。
5、by SQL方法進行chunk劃分
By SQL方法是使用者自己定義SQL語句，獲取column的start id和end id作為劃分chunk的內容。程式碼如下：
SQL> declare
2 vc_task varchar2(100);
3 vc_sql varchar2(1000);
4 vc_sql_mt varchar2(1000);
5 n_try number;
6 n_status number;
7 begin
8 --Define the Task
9 vc_task := 'Task 3: By SQL';
10 dbms_parallel_execute.create_task(task_name => vc_task);
11
12 --Define the Spilt
13 vc_sql_mt := 'select distinct object_id, object_id from t';
14 dbms_parallel_execute.create_chunks_by_SQL(task_name => vc_task,
15 sql_stmt => vc_sql_mt,
16 by_rowid => false);
17
18 vc_sql := 'update /*+ ROWID(dda) */t set DATA_OBJECT_ID=object_id+1 where object_id between :start_id and :end_id';
19 --Run the task
20 dbms_parallel_execute.run_task(task_name => vc_task,
21 sql_stmt => vc_sql,
22 language_flag => dbms_sql.native,
23 parallel_level => 2);
24
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 /
在定義chunk的過程中，我們指定出單獨的SQL語句來確定start id和end id。這也就讓我們不需要定義所謂的chunk size了。
執行過程依然進行chunking和processing過程。相關檢視資訊如下：
--chunking過程
SQL> select task_name, chunk_type, status from user_parallel_execute_tasks;
TASK_NAME CHUNK_TYPE STATUS
------------------------- ------------ -------------------
Task 3: By SQL NUMBER_RANGE CHUNKING
--Processing過程
SQL> select task_name, chunk_type, status from user_parallel_execute_tasks;
TASK_NAME CHUNK_TYPE STATUS
------------------------- ------------ -------------------
Task 3: By SQL NUMBER_RANGE PROCESSING
SQL> select status, count(*) from user_parallel_execute_chunks group by status;
STATUS COUNT(*)
-------------------- ----------
ASSIGNED 2
UNASSIGNED 75559
PROCESSED 25
--執行作業情況
SQL> select saddr, sid, serial#, PROGRAM from v$session where username='SYS' and status='ACTIVE' and osuser='oracle';
SADDR SID SERIAL# PROGRAM
-------- ---------- ---------- ------------------------------------------------
35ECE400 31 103 oracle@SimpleLinux.localdomain (J000)
35EA8300 45 29 oracle@SimpleLinux.localdomain (J001)
在chunk範圍資訊中，我們可以印證對於chunk size的理解。
SQL> select chunk_id, task_name, status, start_id, end_id from user_parallel_execute_chunks where rownum<10;
CHUNK_ID TASK_NAME STATUS START_ID END_ID
---------- ------------------------- -------------------- ---------- ----------
20052 Task 3: By SQL PROCESSED 17427 17427
20053 Task 3: By SQL PROCESSED 17439 17439
20054 Task 3: By SQL PROCESSED 17442 17442
20055 Task 3: By SQL PROCESSED 17458 17458
20056 Task 3: By SQL PROCESSED 37321 37321
20057 Task 3: By SQL PROCESSED 37322 37322
20058 Task 3: By SQL PROCESSED 17465 17465
20059 Task 3: By SQL PROCESSED 37323 37323
20060 Task 3: By SQL PROCESSED 17468 17468
9 rows selected
由於條件的限制，本次執行時間較長。
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 47522.328 seconds
總執行時間為13個小時。
6、結論
從上面的實驗，我們可以瞭解dbms_parallel_execute新功能包的使用和功能特點。比較顯著的就是區別與傳統的並行設定，parallel_execute包的方法是依託於10g以來的job schedule機制。並行、多執行緒轉化為多個後臺作業自主執行完成。
應該說，這樣的策略讓並行變的更加簡單易用。我們將關注點轉移到如何進行chunk劃分和設定多少並行度的問題上。Chunk的劃分影響到的是每次處理的資料量，而並行度取決於實際系統的資源富裕程度。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17203031/viewspace-1080376/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 使用11g dbms_parallel_execute執行並行更新（上）Parallel並行
- 【oracle】使用DBMS_PARALLEL_EXECUTE並行更新表OracleParallel並行
- 11G R2中的並行執行，dbms_parallel_execute並行Parallel
- 使用DBMS_PARALLEL_EXECUTE進行快速更新Parallel
- 使用dbms_parallel_execute來完成DML的並行Parallel並行
- 並行執行並行
- Oracle11.2新特性之使用DBMS_PARALLEL_EXECUTE包實現並行OracleParallel並行
- 使用並行執行——資料倉儲手冊並行
- Linux下編譯生成SO並進行呼叫執行Linux編譯
- Java中使用ThreadPoolExecutor並行執行獨立的單執行緒任務Javathread並行執行緒
- 在LINUX下編寫程式並執行Linux
- DBMS_PARALLEL_EXECUTE 11GR2新特性，並行訂正大資料Parallel並行大資料
- 26、多執行緒與並行執行緒並行
- 11g DBMS_PARALLEL_EXECUTEParallel
- IOS下使用多執行緒iOS執行緒
- 多執行緒並行執行，然後彙總結果執行緒並行
- Oracle“並行執行”——監控檢視Oracle並行
- 課程 3: 執行緒與並行執行緒並行
- 如何取得Oracle並行執行的traceOracle並行
- 9i並行執行的限制並行
- 利用並行提高sql執行效率（轉）並行SQL
- Docker部署並執行ElasticsearchDockerElasticsearch
- DM7使用dmrestore執行並行對映檔案還原REST並行
- PostgreSQL官方並行更新時間表SQL並行
- laravel建立定時任務並在windows下執行LaravelWindows
- 使用docker構建jenkins映象並執行容器DockerJenkins
- PHP多程式並行執行php指令碼PHP並行指令碼
- 並行執行任務的Fork/Join框架並行框架
- 並行執行的學習與測試並行
- 強制SQL Server執行計劃使用並行提升在複雜查詢語句下的效能SQLServer並行
- 如何使用 appium+pytest 進行多機並行執行不同 case 檔案APP並行
- 配置並執行Activiti Explorer
- oracle建立job並執行jobOracle
- 搭建go環境並執行Go
- sql更新是如何執行的？SQL
- Oracle並行操作——淺議使用並行的時機Oracle並行
- mac下安裝xctool並簡單執行xctool來打包Mac
- oracle sqlprofile 固定執行計劃，並遷移執行計劃OracleSQL