这篇文章将为大家详细讲解有关Oracle11.2中怎样使用DBMS_PARALLEL_EXECUTE包实现并行，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。
使用DBMS_PARALLEL_EXECUTE包实现并行
该包支持insert、update、delete、merge、匿名包自动以scheduler job 方式并行执行。
支持的chunk方式包括：
CREATE_CHUNKS_BY_NUMBER_COL Procedure
CREATE_CHUNKS_BY_ROWID Procedure
CREATE_CHUNKS_BY_SQL Procedure
测试：
SQL> create table employees
2 as
3 select * from dba_objects;
Table created
SQL> select count(*) from mh.employees;
COUNT(*)
———-
72787
SQL>
SQL> select count(*) from mh.employees where object_id=data_object_id;
COUNT(*)
———-
7253
DECLARE
l_sql_stmt VARCHAR2(1000);
l_try NUMBER;
l_status NUMBER;
BEGIN
— Create the TASK
DBMS_PARALLEL_EXECUTE.CREATE_TASK ('mytask');
— Chunk the table by ROWID
DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID('mytask', 'MH', 'EMPLOYEES', true, 100);
— Execute the DML in parallel
l_sql_stmt := 'update /*+ ROWID (dda) */ EMPLOYEES e
SET e.object_id = e.object_id + 10
WHERE rowid BETWEEN :start_id AND :end_id';
DBMS_PARALLEL_EXECUTE.RUN_TASK('mytask', l_sql_stmt, DBMS_SQL.NATIVE,
parallel_level => 10);
— If there is an error, RESUME it for at most 2 times.
L_try := 0;
L_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS('mytask');
WHILE(l_try < 2 and L_status != DBMS_PARALLEL_EXECUTE.FINISHED)
LOOP
L_try := l_try + 1;
DBMS_PARALLEL_EXECUTE.RESUME_TASK('mytask');
L_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS('mytask');
END LOOP;
— Done with processing; drop the task
DBMS_PARALLEL_EXECUTE.DROP_TASK('mytask');
END;
/
执行期间在另一个session中查询：
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as mh@boclink
SQL>
SQL> select * from user_parallel_execute_chunks;
CHUNK_ID TASK_NAME STATUS START_ROWID END_ROWID START_TS END_TS
——– ——— ———- —————— —————— – ——————- ——————-
1 mytask PROCESSED AAAVBvAAGAAAAEgAAA AAAVBvAAGAAAAEnCcP 05-1月 -14 02.27.39 05-1月 -14 02.27.39
2 mytask PROCESSED AAAVBvAAGAAAAEoAAA AAAVBvAAGAAAAEvCcP 05-1月 -14 02.27.39 05-1月 -14 02.27.39
3 mytask PROCESSED AAAVBvAAGAAAAEwAAA AAAVBvAAGAAAAE3CcP 05-1月 -14 02.27.39 05-1月 -14 02.27.39
4 mytask PROCESSED AAAVBvAAGAAAAE4AAA AAAVBvAAGAAAAE/CcP 05-1月 -14 02.27.39 05-1月 -14 02.27.39
5 mytask PROCESSED AAAVBvAAGAAAAFAAAA AAAVBvAAGAAAAFHCcP 05-1月 -14 02.27.39 05-1月 -14 02.27.39
6 mytask PROCESSED AAAVBvAAGAAAAFIAAA AAAVBvAAGAAAAFPCcP 05-1月 -14 02.27.39 05-1月 -14 02.27.39
7 mytask PROCESSED AAAVBvAAGAAAAFQAAA AAAVBvAAGAAAAFXCcP 05-1月 -14 02.27.39 05-1月 -14 02.27.39
8 mytask PROCESSED AAAVBvAAGAAAAFYAAA AAAVBvAAGAAAAFfCcP 05-1月 -14 02.27.39 05-1月 -14 02.27.39
9 mytask PROCESSED AAAVBvAAGAAAAFgAAA AAAVBvAAGAAAAFnCcP 05-1月 -14 02.27.39 05-1月 -14 02.27.39
10 mytask PROCESSED AAAVBvAAGAAAAFoAAA AAAVBvAAGAAAAFvCcP 05-1月 -14 02.27.39 05-1月 -14 02.27.39
11 mytask PROCESSED AAAVBvAAGAAAAFwAAA AAAVBvAAGAAAAF3CcP 05-1月 -14 02.27.39 05-1月 -14 02.27.39
12 mytask PROCESSED AAAVBvAAGAAAAF4AAA AAAVBvAAGAAAAF/CcP 05-1月 -14 02.27.39 05-1月 -14 02.27.39
13 mytask PROCESSED AAAVBvAAGAAAAGAAAA AAAVBvAAGAAAAGHCcP 05-1月 -14 02.27.39 05-1月 -14 02.27.39
14 mytask PROCESSED AAAVBvAAGAAAAGIAAA AAAVBvAAGAAAAGPCcP 05-1月 -14 02.27.39 05-1月 -14 02.27.39
15 mytask PROCESSED AAAVBvAAGAAAAGQAAA AAAVBvAAGAAAAGXCcP 05-1月 -14 02.27.39 05-1月 -14 02.27.39
16 mytask PROCESSED AAAVBvAAGAAAAGYAAA AAAVBvAAGAAAAGfCcP 05-1月 -14 02.27.39 05-1月 -14 02.27.39
17 mytask PROCESSED AAAVBvAAGAAAAIAAAA AAAVBvAAGAAAAIxCcP 05-1月 -14 02.27.39 05-1月 -14 02.27.39
18 mytask PROCESSED AAAVBvAAGAAAAIyAAA AAAVBvAAGAAAAJjCcP 05-1月 -14 02.27.39 05-1月 -14 02.27.39
19 mytask PROCESSED AAAVBvAAGAAAAJkAAA AAAVBvAAGAAAAJ/CcP 05-1月 -14 02.27.39 05-1月 -14 02.27.39
20 mytask PROCESSED AAAVBvAAGAAAAKAAAA AAAVBvAAGAAAAKxCcP 05-1月 -14 02.27.39 05-1月 -14 02.27.39
21 mytask PROCESSED AAAVBvAAGAAAAKyAAA AAAVBvAAGAAAALjCcP 05-1月 -14 02.27.39 05-1月 -14 02.27.40
22 mytask PROCESSED AAAVBvAAGAAAALkAAA AAAVBvAAGAAAAL/CcP 05-1月 -14 02.27.39 05-1月 -14 02.27.39
23 mytask PROCESSED AAAVBvAAGAAAAMAAAA AAAVBvAAGAAAAMxCcP 05-1月 -14 02.27.39 05-1月 -14 02.27.40
24 mytask PROCESSED AAAVBvAAGAAAAMyAAA AAAVBvAAGAAAANjCcP 05-1月 -14 02.27.40 05-1月 -14 02.27.40
25 mytask PROCESSED AAAVBvAAGAAAANkAAA AAAVBvAAGAAAAN/CcP 05-1月 -14 02.27.40 05-1月 -14 02.27.40
26 mytask PROCESSED AAAVBvAAGAAAAOAAAA AAAVBvAAGAAAAOxCcP 05-1月 -14 02.27.40 05-1月 -14 02.27.40
27 mytask PROCESSED AAAVBvAAGAAAAOyAAA AAAVBvAAGAAAAPjCcP 05-1月 -14 02.27.40 05-1月 -14 02.27.40
28 mytask PROCESSED AAAVBvAAGAAAAPkAAA AAAVBvAAGAAAAP/CcP 05-1月 -14 02.27.40 05-1月 -14 02.27.40
29 mytask PROCESSED AAAVBvAAGAAAAQAAAA AAAVBvAAGAAAAQxCcP 05-1月 -14 02.27.40 05-1月 -14 02.27.40
30 mytask PROCESSED AAAVBvAAGAAAAQyAAA AAAVBvAAGAAAARjCcP 05-1月 -14 02.27.40 05-1月 -14 02.27.40
31 mytask PROCESSED AAAVBvAAGAAAARkAAA AAAVBvAAGAAAAR/CcP 05-1月 -14 02.27.40 05-1月 -14 02.27.40
32 mytask PROCESSED AAAVBvAAGAAAASAAAA AAAVBvAAGAAAASxCcP 05-1月 -14 02.27.40 05-1月 -14 02.27.40
33 mytask PROCESSED AAAVBvAAGAAAASyAAA AAAVBvAAGAAAATjCcP 05-1月 -14 02.27.40 05-1月 -14 02.27.40
34 mytask PROCESSED AAAVBvAAGAAAATkAAA AAAVBvAAGAAAAT/CcP 05-1月 -14 02.27.40 05-1月 -14 02.27.40
35 mytask PROCESSED AAAVBvAAGAAAAUAAAA AAAVBvAAGAAAAUxCcP 05-1月 -14 02.27.40 05-1月 -14 02.27.40
36 mytask PROCESSED AAAVBvAAGAAAAUyAAA AAAVBvAAGAAAAVjCcP 05-1月 -14 02.27.40 05-1月 -14 02.27.40
37 mytask PROCESSED AAAVBvAAGAAAAVkAAA AAAVBvAAGAAAAV/CcP 05-1月 -14 02.27.40 05-1月 -14 02.27.40
38 mytask PROCESSED AAAVBvAAGAAAAWAAAA AAAVBvAAGAAAAWxCcP 05-1月 -14 02.27.40 05-1月 -14 02.27.40
39 mytask PROCESSED AAAVBvAAGAAAAWyAAA AAAVBvAAGAAAAXjCcP 05-1月 -14 02.27.40 05-1月 -14 02.27.40
40 mytask PROCESSED AAAVBvAAGAAAAXkAAA AAAVBvAAGAAAAX/CcP 05-1月 -14 02.27.40 05-1月 -14 02.27.40
40 rows selected
SQL>
SQL> select * from user_parallel_execute_tasks;
TASK_NAME CHUNK_TYPE STATUS TABLE_OWNER TABLE_NAME JOB_PREFIX LANGUAGE_FLAG EDITION FIRE_APPLY_TRIGGER PARALLEL_LEVEL JOB_CLASS
——— ———— ——— ———— ———– ———- ————- ——– —————— ————– ——————————
mytask ROWID_RANGE FINISHED MH EMPLOYEES TASK$_506 1 ORA$BASE TRUE 10 DEFAULT_JOB_CLASS
SET e.object_id = e.object_id + 10
执行结束后：
SQL> select count(*) from mh.employees where object_id=data_object_id;
COUNT(*)
———-
1
SQL> select * from user_parallel_execute_tasks;
TASK_NAME CHUNK_TYPE STATUS TABLE_OWNER TABLE_NAME JOB_PREFIX LANGUAGE_FLAG EDITION FIRE_APPLY_TRIGGER PARALLEL_LEVEL JOB_CLASS
——— ———— ——— ———— ———– ———- ————- ——– —————— ————– ——————————
SQL>
SQL> select * from user_parallel_execute_chunks;
CHUNK_ID TASK_NAME STATUS START_ROWID END_ROWID START_TS END_TS
——– ——— ———- —————— —————— – ——————- ——————-
SQL>
关于Oracle11.2中怎样使用DBMS_PARALLEL_EXECUTE包实现并行就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。
原创文章，作者：奋斗，如若转载，请注明出处：https://blog.ytso.com/199299.html