Shulou(Shulou.com)06/01 Report--

This article will explain in detail how to use the DBMS_PARALLEL_EXECUTE package to achieve parallelism in Oracle11.2. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have some understanding of the relevant knowledge after reading this article.

Parallelism using DBMS_PARALLEL_EXECUTE packages

The package supports insert, update, delete, merge and anonymous packages to automatically execute in parallel in scheduler job mode.

Supported chunk methods include:

CREATE_CHUNKS_BY_NUMBER_COL Procedure

CREATE_CHUNKS_BY_ROWID Procedure

CREATE_CHUNKS_BY_SQL Procedure

Test:

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-January-14 02.27.39 05-January-14 02.27.39

2 mytask PROCESSED AAAVBvAAGAAAAEoAAA AAAVBvAAGAAAAEvCcP 05-January-14 02.27.39 05-January-14 02.27.39

3 mytask PROCESSED AAAVBvAAGAAAAEwAAA AAAVBvAAGAAAAE3CcP 05-January-14 02.27.39 05-January-14 02.27.39

4 mytask PROCESSED AAAVBvAAGAAAAE4AAA AAAVBvAAGAAAAE/CcP 05-January-14 02.27.39 05-January-14 02.27.39

5 mytask PROCESSED AAAVBvAAGAAAAFAAAA AAAVBvAAGAAAAFHCcP 05-January-14 02.27.39 05-January-14 02.27.39

6 mytask PROCESSED AAAVBvAAGAAAAFIAAA AAAVBvAAGAAAAFPCcP 05-January-14 02.27.39 05-January-14 02.27.39

7 mytask PROCESSED AAAVBvAAGAAAAFQAAA AAAVBvAAGAAAAFXCcP 05-January-14 02.27.39 05-January-14 02.27.39

8 mytask PROCESSED AAAVBvAAGAAAAFYAAA AAAVBvAAGAAAAFfCcP 05-January-14 02.27.39 05-January-14 02.27.39

9 mytask PROCESSED AAAVBvAAGAAAAFgAAA AAAVBvAAGAAAAFnCcP 05-January-14 02.27.39 05-January-14 02.27.39

10 mytask PROCESSED AAAVBvAAGAAAAFoAAA AAAVBvAAGAAAAFvCcP 05-January-14 02.27.39 05-January-14 02.27.39

11 mytask PROCESSED AAAVBvAAGAAAAFwAAA AAAVBvAAGAAAAF3CcP 05-January-14 02.27.39 05-January-14 02.27.39

12 mytask PROCESSED AAAVBvAAGAAAAF4AAA AAAVBvAAGAAAAF/CcP 05-January-14 02.27.39 05-January-14 02.27.39

13 mytask PROCESSED AAAVBvAAGAAAAGAAAA AAAVBvAAGAAAAGHCcP 05-January-14 02.27.39 05-January-14 02.27.39

14 mytask PROCESSED AAAVBvAAGAAAAGIAAA AAAVBvAAGAAAAGPCcP 05-January-14 02.27.39 05-January-14 02.27.39

15 mytask PROCESSED AAAVBvAAGAAAAGQAAA AAAVBvAAGAAAAGXCcP 05-January-14 02.27.39 05-January-14 02.27.39

16 mytask PROCESSED AAAVBvAAGAAAAGYAAA AAAVBvAAGAAAAGfCcP 05-January-14 02.27.39 05-January-14 02.27.39

17 mytask PROCESSED AAAVBvAAGAAAAIAAAA AAAVBvAAGAAAAIxCcP 05-January-14 02.27.39 05-January-14 02.27.39

18 mytask PROCESSED AAAVBvAAGAAAAIyAAA AAAVBvAAGAAAAJjCcP 05-January-14 02.27.39 05-January-14 02.27.39

19 mytask PROCESSED AAAVBvAAGAAAAJkAAA AAAVBvAAGAAAAJ/CcP 05-January-14 02.27.39 05-January-14 02.27.39

20 mytask PROCESSED AAAVBvAAGAAAAKAAAA AAAVBvAAGAAAAKxCcP 05-January-14 02.27.39 05-January-14 02.27.39

21 mytask PROCESSED AAAVBvAAGAAAAKyAAA AAAVBvAAGAAAALjCcP 05-January-14 02.27.39 05-January-14 02.27.40

22 mytask PROCESSED AAAVBvAAGAAAALkAAA AAAVBvAAGAAAAL/CcP 05-January-14 02.27.39 05-January-14 02.27.39

23 mytask PROCESSED AAAVBvAAGAAAAMAAAA AAAVBvAAGAAAAMxCcP 05-January-14 02.27.39 05-January-14 02.27.40

24 mytask PROCESSED AAAVBvAAGAAAAMyAAA AAAVBvAAGAAAANjCcP 05-January-14 02.27.40 05-January-14 02.27.40

25 mytask PROCESSED AAAVBvAAGAAAANkAAA AAAVBvAAGAAAAN/CcP 05-January-14 02.27.40 05-January-14 02.27.40

26 mytask PROCESSED AAAVBvAAGAAAAOAAAA AAAVBvAAGAAAAOxCcP 05-January-14 02.27.40 05-January-14 02.27.40

27 mytask PROCESSED AAAVBvAAGAAAAOyAAA AAAVBvAAGAAAAPjCcP 05-January-14 02.27.40 05-January-14 02.27.40

28 mytask PROCESSED AAAVBvAAGAAAAPkAAA AAAVBvAAGAAAAP/CcP 05-January-14 02.27.40 05-January-14 02.27.40

29 mytask PROCESSED AAAVBvAAGAAAAQAAAA AAAVBvAAGAAAAQxCcP 05-January-14 02.27.40 05-January-14 02.27.40

30 mytask PROCESSED AAAVBvAAGAAAAQyAAA AAAVBvAAGAAAARjCcP 05-January-14 02.27.40 05-January-14 02.27.40

31 mytask PROCESSED AAAVBvAAGAAAARkAAA AAAVBvAAGAAAAR/CcP 05-January-14 02.27.40 05-January-14 02.27.40

32 mytask PROCESSED AAAVBvAAGAAAASAAAA AAAVBvAAGAAAASxCcP 05-January-14 02.27.40 05-January-14 02.27.40

33 mytask PROCESSED AAAVBvAAGAAAASyAAA AAAVBvAAGAAAATjCcP 05-January-14 02.27.40 05-January-14 02.27.40

34 mytask PROCESSED AAAVBvAAGAAAATkAAA AAAVBvAAGAAAAT/CcP 05-January-14 02.27.40 05-January-14 02.27.40

35 mytask PROCESSED AAAVBvAAGAAAAUAAAA AAAVBvAAGAAAAUxCcP 05-January-14 02.27.40 05-January-14 02.27.40

36 mytask PROCESSED AAAVBvAAGAAAAUyAAA AAAVBvAAGAAAAVjCcP 05-January-14 02.27.40 05-January-14 02.27.40

37 mytask PROCESSED AAAVBvAAGAAAAVkAAA AAAVBvAAGAAAAV/CcP 05-January-14 02.27.40 05-January-14 02.27.40

38 mytask PROCESSED AAAVBvAAGAAAAWAAAA AAAVBvAAGAAAAWxCcP 05-January-14 02.27.40 05-January-14 02.27.40

39 mytask PROCESSED AAAVBvAAGAAAAWyAAA AAAVBvAAGAAAAXjCcP 05-January-14 02.27.40 05-January-14 02.27.40

40 mytask PROCESSED AAAVBvAAGAAAAXkAAA AAAVBvAAGAAAAX/CcP 05-January-14 02.27.40 05-January-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

At the end of the execution:

SQL > select count (*) from mh.employees where object_id=data_object_id

COUNT (*)

-

one

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 >

On how to use the DBMS_PARALLEL_EXECUTE package in Oracle11.2 to achieve parallel sharing here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.