2. ROWID partitioning parallel processing case (DBMS_PARALLEL_EXECUTE)

Let's take a look at the case of ROWID partitioning method parallel processing using Oracle DBMS_PARALLEL_EXECUTE.

This is a continuation of the previous article.

1. Overview of parallel processing of DML jobs (DBMS_PARALLEL_EXECUTE)

2. Parallel processing case of ROWID division method

2.1. Create ROWID Partitioned Parallel Processing Jobs

CREATE_TASK( ) to create the task.

-- 1단계: 작업생성
BEGIN
  ​​DBMS_PARALLEL_EXECUTE.CREATE_TASK(TASK_NAME => 'DPE_TEST(BY ROWID)');
END;
/

The result of the job creation can be checked as follows.

-- 작업 생성 확인
SELECT * FROM USER_PARALLEL_EXECUTE_TASKS;

From the above result, it can be confirmed that the Task has been created and the STATUS is CREATED.

For reference, the STATUS of a task has values of CHUNKED, CHUNKING, CHUNKING_FAILED, CRASHED, CREATED, FINISHED, FINISHED_WITH_ERROR, PROCESSING, and each meaning is as follows.

• CHUNKED: chunk created, not yet assigned to a task
• CHUNKING: creating chunks
• CHUNKING_FAILED: Failed while creating chunk
• CRASHED: A database crash or job process crash occurred during task execution, and the error was not recorded and terminated.
• REATED: Task created (no chunk created yet)
• FINISHED: all chunks completed without errors
• FINISHED_WITH_ERROR: All chunks completed, but some errors occurred
• PROCESSING: Tosk has started executing, some of the chunks are in progress or complete

* Reference: http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_parallel_ex.htm#ARPLS67333

2.2. Split work unit

CREATE_CHUNKS_BY_ROWID( ,

is a BOOLEAN type, if TRUE means row count, and FALSE means block count.

Execute as follows to create chunks that divide the row count of the Z_DPE_TEST_TAB table into 10,000 rows.

-- 2단계: 작업 단위 분할
BEGIN
  DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID(TASK_NAME   => 'DPE_TEST(BY ROWID)',
                                               TABLE_OWNER => USER,
                                               TABLE_NAME  => 'Z_DPE_TEST_TAB',
                                               BY_ROW      => TRUE,
                                               CHUNK_SIZE  => 10000);
END;
/

Let's check the division state of the unit of work.

-- 작업 분할 상태 확인
SELECT *
  ​​FROM USER_PARALLEL_EXECUTE_CHUNKS
 ​WHERE TASK_NAME = 'DPE_TEST(BY ROWID)';

It can be confirmed that the STATUS of each chunk at the time of division is UNASSIGNED. For reference, the STATUS of the chunk has values of UNASSIGNED, ASSIGNED, PROCESSED, and PROCESSED_WITH_ERROR, and each meaning is as follows.

• UNASSIGNED: chunk created, not yet assigned to a task
• ASSIGNED: chunk is assigned to task and running
• PROCESSED: The operation completed without errors
• PROCESSED_WITH_ERROR: The operation completed, but an error occurred during execution

* Reference: http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_parallel_ex.htm#ARPLS67333

2.3. job run

RUN_TASK( , , , ) to execute the task.

Here, LANGUAGE_FLAG means the standard version for Oracle to process SQL_STMT, and DBMS_SQL.NATIVE can be used as default.

For reference, the contents of the Oracle Document are as follows:

• 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

PARALLEL_LEVEL means the number of jobs to be executed simultaneously, that is, the degree of parallelism (DOP), and may be equal to or smaller than the number of chunks, which are work units. In the same case, one job processes one chunk, and in a small case, one job processes several chunks.

-- 동시 실행현황 확인: 작업 실행전
SELECT AUDSID, COUNT(*)
  FROM Z_DPE_TEST_TAB
 GROUP BY AUDSID
 ORDER BY AUDSID;

If you check the COUNT for each AUDSID before executing the task, you can see that all data is set to null and has not been executed yet.

Now let's run the job.

-- 3단계: 작업 실행
DECLARE
  L_SQL_STMT VARCHAR2(32767);
BEGIN
  L_SQL_STMT := 'UPDATE  Z_DPE_TEST_TAB
                    SET  VAL = ROUND(DBMS_RANDOM.VALUE(1,10000))
                        ,AUDSID = SYS_CONTEXT(''USERENV'',''SESSIONID'')
                  WHERE  ROWID BETWEEN :START_ID AND :END_ID';

  DBMS_PARALLEL_EXECUTE.RUN_TASK(TASK_NAME      => 'DPE_TEST(BY ROWID)',
                                 SQL_STMT       => L_SQL_STMT,
                                 LANGUAGE_FLAG  => DBMS_SQL.NATIVE,
                                 PARALLEL_LEVEL => 10);
END;
/

Executed SQL specifies the execution scope with two bind variables :START_ID and :END_ID. If this variable name is used differently, an error will occur, so be sure to use this name.

The SQL executed here is written to check which session updated the current row by updating a random value in the VAL column and updating the current SESSIONID value in the AUDSID column for testing.

For reference, the AUDSID column has the same meaning as V$SESSION.AUDSID. If each chunk unit operation takes a long time, session monitoring can be performed by querying V$SESSION with AUDSID.

If you execute the following SQL during execution, you can check how many rows are being updated in how many sessions.

-- 동시 실행현황 확인: 작업 실행중
SELECT AUDSID, COUNT(*)
  FROM Z_DPE_TEST_TAB
 GROUP BY AUDSID
 ORDER BY AUDSID;

Let's check the progress of each chunk in which the work is divided, not the session unit.

-- 작업의 Chunk별 진행상황/완료 확인
SELECT *
  FROM USER_PARALLEL_EXECUTE_CHUNKS
WHERE TASK_NAME = 'DPE_TEST(BY ROWID)';

From the above result, you can see that STATUS is changed to PROCESSED, and START_TS (start time) and END_TS (end time) are managed.

To check the progress by chunk status, execute the following SQL.

-- Chunk의 상태별 진행상황
SELECT STATUS, COUNT(*)
  FROM USER_PARALLEL_EXECUTE_CHUNKS
 WHERE TASK_NAME = 'DPE_TEST(BY ROWID)'
GROUP BY STATUS;

From the above result, it can be confirmed that 172 chunks have been completed in PROCESSED status. Since the image was captured after the job was completed, only one state was recorded. If the operation takes a long time, you can check the number of chunks for each status.

2.4. Confirm task completion and delete

DROP_TASK( ) to delete the job.

For reference, if you do not delete it, an ORA-29497: duplicate task name (DUPLICATE_TASK_NAME) error will occur when creating a task with the same TASK_NAME later.

-- 작업 완료 확인
SELECT *
  FROM USER_PARALLEL_EXECUTE_TASKS;

From the above result, it can be confirmed that the task's STATUS has been completed with FINISHED.

-- 4단계: 작업 완료 확인 및 작업 삭제
BEGIN
  DBMS_PARALLEL_EXECUTE.DROP_TASK(TASK_NAME => 'DPE_TEST(BY ROWID)');
END;
/

If you check the USER_PARALLEL_EXECUTE_TASKS view again after executing DROP_TASK, you can see that the task has been deleted.

So far, we have looked at a simple case of ROWID partitioning method parallel processing.

Next, we look at how uniform the work units divided by ROWID are, whether there are no omissions, and how the correlation between the number of chunks and the number of jobs (parallelism) is.

Related articles:

Oracle Character Set Conversion (9): 6. How to Convert User Implemented Character Sets (2) Oracle Character Set Conversion(8): 6. How to Convert User Implemented Character Set (1) Oracle Character Set Conversion(1): 1. Necessity, Correct Oracle Character Set Setup Guide DBMS_PARALLEL_EXECUTE Description Table of Contents 4. User-defined SQL partitioning method parallel processing case (DBMS_PARALLEL_EXECUTE) 3. NUMBER Column Split Method Parallel Processing Case (DBMS_PARALLEL_EXECUTE) 2.5. Detailed check of unit of work partitioning (DBMS_PARALLEL_EXECUTE) 1. Overview of parallel processing of DML jobs (DBMS_PARALLEL_EXECUTE)

Tags: ORACLEDBMS_PARALLEL_EXECUTEparallel processingDML Parallel ProcessingDML parallel processing

English

한국어 简体中文 日本語 Deutsch Español English