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;
Step 1: Confirm task creation
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
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)';
Step 2: Check task split status
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
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;
Step 3: Check Concurrent Execution Status (Before Task Execution)
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;
Step 3: Check Concurrent Execution Status (Task Execution)
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)';
Step 3: Check the progress/completion of each chunk of the task
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;
Step 3: Check Chunk Progress 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;
Confirm task completion
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;
/
Step 4: Check Job Completion and Delete Job
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.
I executed it using DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID, but when I look at the status of the task, I see NO_CHUNKS status. Do you know the reason?
Blocks are divided by ROWID, but when all blocks within the chunk have no data, it seems to be in the NO_CHUNKS state.
Testing is difficult because I haven't used Oracle recently.
Please note.
I executed it using DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID, but when I look at the status of the task, I see NO_CHUNKS status. Do you know the reason?
The environment I tested was Oracle 11g R2 (11.2), and there was no NO_CHUNKS status at that time.
After doing a Google search, I found the following:
———————————–
NO_CHUNKS: Table associated with the task has no chunks created
———————————–
* Source: Oracle 19c document
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_PARALLEL_EXECUTE.html#GUID-5581516D-AB22-4748-8476-C5BB38BE7E2F
Blocks are divided by ROWID, but when all blocks within the chunk have no data, it seems to be in the NO_CHUNKS state.
Testing is difficult because I haven't used Oracle recently.
Please note.