Parallel processing in PL/SQL

P

Parallel processing demo:

Prepare data for processing:

--DROP TABLE test_par PURGE;
CREATE TABLE test_par (
    id              NUMBER PRIMARY KEY,
    order#          NUMBER,
    session_id      NUMBER
);
--
TRUNCATE TABLE test_par;
--
INSERT INTO test_par (id, order#)
SELECT LEVEL, LEVEL
FROM DUAL
CONNECT BY LEVEL <= 100000;
--
COMMIT;

Process data in chunks:

DECLARE
    in_threads      CONSTANT NUMBER     := 8;
    in_chunk_size   CONSTANT NUMBER     := 15000;
    --
    l_task_name     VARCHAR2(64)        := 'test_par#' || TO_CHAR(SYSDATE, 'HH24MISS');
    l_exec          VARCHAR2(32000);
    l_try           PLS_INTEGER         := 0;
    l_status        PLS_INTEGER;
BEGIN
    UPDATE test_par SET session_id = NULL;
    --
    DBMS_PARALLEL_EXECUTE.CREATE_TASK(task_name => l_task_name, comment => '');
    DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID (
        task_name       => l_task_name,
        table_owner     => USER,
        table_name      => 'TEST_PAR',
        by_row          => TRUE,
        chunk_size      => in_chunk_size
    );
    --
    l_exec := 'UPDATE test_par t
        SET t.session_id = SYS_CONTEXT(''USERENV'', ''SESSIONID'')
        WHERE ROWID BETWEEN :start_id AND :end_id';
    --
    DBMS_PARALLEL_EXECUTE.RUN_TASK (
        task_name       => l_task_name,
        sql_stmt        => l_exec,
        language_flag   => DBMS_SQL.NATIVE,
        parallel_level  => in_threads
    );
    --
    l_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS(l_task_name);
    --
    WHILE l_try < 2 AND l_status != DBMS_PARALLEL_EXECUTE.FINISHED LOOP
        DBMS_PARALLEL_EXECUTE.RESUME_TASK(l_task_name);
        --
        l_status    := DBMS_PARALLEL_EXECUTE.task_status(l_task_name);
        l_try       := l_try + 1;
    END LOOP;
    --
    DBMS_PARALLEL_EXECUTE.DROP_TASK(l_task_name);
    DBMS_OUTPUT.PUT_LINE('DONE');
    --
    COMMIT;
END;
/

Check progress from another session:

SELECT t.*
FROM user_parallel_execute_tasks t;
--
SELECT p.*
FROM user_parallel_execute_chunks p
ORDER BY 1 DESC;

Check results:

SELECT session_id, COUNT(*)
FROM test_par
GROUP BY session_id
ORDER BY session_id;

Cleanup:

DROP TABLE test_par PURGE;