Oracle : Experiment on DBMS_PARALLEL_EXECUTE

Nice method to work on large tables requiring bulk operation but want to avoid locking the whole table.

Here is my setup for the test on favourite scott schema(XEMP is table created from EMP):
SQL> set time on timing on
12:06:21 SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE
XEMP                           TABLE

Elapsed: 00:00:00.00
12:06:30 SQL> select count(1) from xemp;

  COUNT(1)
----------
   7340032

Elapsed: 00:00:10.23
---------------------------Experiment-----------------------------------------------------------

Test 1: Simple update:
07:18:13 SQL> show user
USER is "SCOTT"
07:18:19 SQL> update scott.xemp set sal=sal+1000;
7340032 rows updated.
Elapsed: 00:09:51.99

Test 2: Experiment on proc start.
07:28:29 SQL> 07:28:29 SQL> exec DBMS_PARALLEL_EXECUTE.create_task (task_name => 'exp1');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.67

COLUMN task_name FORMAT A30
SELECT task_name, status FROM   user_parallel_execute_tasks;

Break into chunks by rowid for processing:

07:37:36 SQL> exec DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid(task_name   => 'exp1', table_owner => 'SCOTT', table_name  => 'XEMP', by_row      => TRUE, chunk_size  => 100000);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.69

optional check to check success of above procedure:

07:38:05 SQL> COLUMN task_name FORMAT A10

SELECT task_name,

       status

FROM   user_parallel_execute_tasks;

TASK_NAME  STATUS

---------- -------------------

test_task  CHUNKED07:40:07 SQL> 07:40:07 SQL> 07:40:07   2  07:40:07   3

TASK_NAME  STATUS

---------- -------------------

exp1       CHUNKED <=====This is what u get.

Elapsed: 00:00:00.06

If want to see how many chunks got created:

SELECT chunk_id, status, start_rowid, end_rowid FROM   user_parallel_execute_chunks WHERE  task_name = 'exp1' ORDER BY 1;

 In our case got 183 chunks.

Run complete:

07:43:38 SQL> exec DBMS_PARALLEL_EXECUTE.run_task(task_name => 'exp1',sql_stmt       => 'UPDATE scott.xemp set sal=sal+1000  WHERE rowid BETWEEN :start_id AND :end_id',language_flag  => DBMS_SQL.NATIVE,parallel_level => 4);

PL/SQL procedure successfully completed.

Elapsed: 00:04:57.11 ===>Improved run

08:04:24 SQL> SELECT task_name, status FROM   user_parallel_execute_tasks;

TASK_NAME  STATUS

---------- -------------------

exp1       FINISHED

-------------------------------------Experiment Complete-------

During run monitoring session:(see 4 sessions)

select inst_id,s.SID,s.SERIAL#,s.username,s.event,s.WAIT_TIME,s.SECONDS_IN_WAIT/60,s.sql_id,status,sql_id from gv$session s where s.username='SCOTT';

  INST_ID    SID    SERIAL# USERNAME   EVENT                           WAIT_TIME S.SECONDS_IN_WAIT/60 SQL_ID        STATUS   SQL_ID

---------- ------ ---------- ---------- ------------------------------ ---------- -------------------- ------------- -------- -------------

         1     17         27 SCOTT      PL/SQL lock timer                       0           .016666667 bzufv5c7udvb2 ACTIVE   bzufv5c7udvb2

         1     20         15 SCOTT      latch: cache buffers lru chain         -1           .016666667 6zws17szpsmrh ACTIVE   6zws17szpsmrh

         1    138         31 SCOTT      latch: cache buffers chains            -1           .016666667 6zws17szpsmrh ACTIVE   6zws17szpsmrh

         1    146         81 SCOTT      log buffer space                        1           .033333333 6zws17szpsmrh ACTIVE   6zws17szpsmrh

Validating results(if update worked or not):

08:03:01 SQL> l

  1* select sum(sal) as SUMSAL from scott.xemp

08:03:04 SQL> /

                  SUMSAL

------------------------

            176698423480

After Rerun:

08:13:58 SQL> select sum(sal) as SUMSAL from scott.xemp;

                  SUMSAL

------------------------

            184038455480 ====>Verified

All that u need :

exec DBMS_PARALLEL_EXECUTE.create_task (task_name => 'exp2');

COLUMN task_name FORMAT A30

SELECT task_name, status FROM   user_parallel_execute_tasks;

exec DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid(task_name   => 'exp2', table_owner => 'SCOTT', table_name  => 'XEMP', by_row      => TRUE, chunk_size  => 100000);

exec DBMS_PARALLEL_EXECUTE.run_task(task_name => 'exp2',sql_stmt => 'UPDATE scott.xemp set sal=sal+1000  WHERE rowid BETWEEN :start_id AND :end_id',language_flag  => DBMS_SQL.NATIVE,parallel_level => 4);

SELECT task_name, status FROM   user_parallel_execute_tasks;

Note: Once task is marked complete, all steps need to be re done.

Additional step(to cleanup):
exec DBMS_PARALLEL_EXECUTE.drop_task('exp2');

I did the experiement based on chunking on rowid's other method like rows/sql

Good refernence link: https://oracle-base.com/articles/11g/dbms_parallel_execute_11gR2

You could try:

1. Other methods of chunking.

2. Parallel counts.

3. Number of rows per chunk.