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.

ORACLE: Edition-Based Redefinition or EBR and errors (ORA-42300,ORA-38802,ORA-38807)

Edition-Based Redefinition

Looks like nice introduction of versioning in the database. Still researching, bear with me on details:

Reference Sample Commands:

alter database default edition =ORA$BASE;#default no need  to execute, only needed if a differnet version becomes default.
create user scott   identified by scott; #optional if schema exists
grant create session,create procedure to scott;#optional if schema exists with privs
create edition version1 as child of ora$base;
alter user scott enable editions;
 grant use on edition version1 to scott;
 grant use on edition version1 to <ANYSCHEMA>;

 Very imp : alter session set edition = version1;

Experiment one: Only editioned views

1. Create one versioned view on base(default).

2. Created another versioned view  on version created in step 0.

select USERNAME,EDITIONS_ENABLED from dba_users;
USERNAME                       E
------------------------------ -
MGMT_VIEW                      N
SYS                            N
SYSTEM                         N
SCOTT                          Y   <===Enabled editions for this schema
SPACE                          N

create edition version1 as child of ora$base; #I created as sysdba

SQL> alter session set edition =VERSION1;

Session altered.

SQL> create editioning view v1_emp as select empno,ename from scott.emp;

View created.

SQL> select object_name,object_type,status,edition_name  from user_objects;

OBJECT_NAME                    OBJECT_TYPE         STATUS  EDITION_NAME

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

XEMP                           TABLE               VALID

SALGRADE                       TABLE               VALID

BONUS                          TABLE               VALID

PK_EMP                         INDEX               VALID

EMP                            TABLE               VALID

DEPT                           TABLE               VALID

PK_DEPT                        INDEX               VALID

V1_EMP                         VIEW                VALID   VERSION1  ******

SQL> create edition version2 as child of version1;

Edition created.

SQL> alter session set edition = version2;

Session altered.

SQL> create editioning view v2_dept as select * from scott.dept;

View created.

SQL> select object_name,object_type,status,edition_name  from user_objects;

OBJECT_NAME                                                                                                           OBJECT_TYPE          STATUS  EDITION_NAME

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

XEMP                                                                                                                  TABLE                VALID

SALGRADE                                                                                                              TABLE                VALID

BONUS                                                                                                                 TABLE                VALID

PK_EMP                                                                                                                INDEX                VALID

EMP                                                                                                                   TABLE                VALID

DEPT                                                                                                                  TABLE                VALID

PK_DEPT                                                                                                               INDEX                VALID

V1_EMP                                                                                                                VIEW                 VALID   VERSION1

V2_DEPT                                                                                                               VIEW                 VALID   VERSION2

Errors encountered and explaination:

1. SQL> create edition version1 as child of ora$base;

create edition version1 as child of ora$base

*

ERROR at line 1:

ORA-38807: Implementation restriction: an edition can have only one child

Only one child version allowed. 

eg: ORA$BASE can have child V1, V1 can have V2, V2 can have V3 and so on but V1 cannot have 2 child V2 & V3(parallel versions)

2. SQL> alter session set edition = version1;

ERROR:

ORA-38802: edition does not exist

Version must be create before use. required the following:

create edition version1 as child of ora$base;

alter user scott enable editions;#Once only

3.SQL> alter session set edition = version2;

Session altered.

SQL> create editioning view v2_emp as select empno,ename,job from scott.emp;

create editioning view v2_emp as select empno,ename,job from scott.emp

                                                                   *

ERROR at line 1:

ORA-42300: an Editioning view is already defined on this table

Once editioned view V1_emp is created, another on the same table cannot be created.