12c SGA memory distribution by PDBs

With 12c multi-tenant architecture, you could have  several Pluggable database in one Container Database(CBD).

The consolidated architecture is more efficient in using the server's CPU and memory among PDBs.

One question that arises is " can you tell what percent of total  buffers is used up by each PDBs ? "

Below script can tell you just that. You will have to run this at CDB level in your database.

 col PCT_BARCHART format a30

 set linesize 200

 col name format a10

 col con_id format 99

 col PCT format 999

    SELECT NVL(pdb.name,'CDB') name,b.con_id,

  ROUND(b.subtotal *

  (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size'

  )                        /(1024 *1024)) bufferCache_MB,

  ROUND(b.subtotal         /b.total * 100) PCT,

  rpad('*',ROUND(b.subtotal/b.total * 100),'*') PCT_BARCHART

FROM

  ( SELECT DISTINCT a.*

  FROM

    (SELECT con_id,

      COUNT(*) over (partition BY con_id ) subtotal ,

      COUNT(*) over () total

    FROM sys.gv$bh

    ) a

  )b, v$pdbs pdb

  where b.con_id=pdb.con_id(+)

ORDER BY con_id;

output.

NAME      CON_ID BUFFERCACHE_MB  PCT PCT_BARCHART

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

CDB 1              131   28 ****************************

PDB$SEED 2               14    3 ***

PDB1 3               18    4 ****

PDB2 4               89   19 *******************

PDB_2 5               41    9 *********

PDB_1 6               41    9 *********

PDB_4 7               39    8 ********

PDB_3 8               43    9 *********

PDB_5 9               44    9 *********

9 rows selected.

 SQL> 

12c clone PDBs in parallel

In one of the 12c Features presentation, one of the questions asked was if a Clone of PDBs can run in parallel ?

I.E, are there any locks held that will serialize creation of cloned PDBs. 

To clone a PDB in 12c, the procedure is to make the source PDB in read only mode, or use SEED database which is always in read only mode, i was fairly certain that no locks will be held. Still, to rule out any doubt, i ran a simple test. I created 5 PDBs in 5 different sessions at the same time. I noticed that all the PDBs were created at the same time proving that they can be created in parallel. 

The top wait event was "Pluggable Database file copy" of  "User I/O"  wait class while PDBs were being created.

below is the test script and the output.

[oracle@localhost pdbtest]$ cat a.sh
echo " starting PDBS at  $(date)"
for i in {1..5}
do
   echo "Welcome $i times"
  rm -r /home/oracle/pdbtest/$i
   mkdir /home/oracle/pdbtest/$i
   /home/oracle/pdbtest/b.sh $i &
done
wait
echo " Ending  PDBS at  $(date)"
[oracle@localhost pdbtest]$ 

[oracle@localhost pdbtest]$ cat b.sh

sqlplus -s sys/oracle as sysdba << __EOF__
set timing on
set echo on
-- SELECT $1 from dual;
CREATE PLUGGABLE DATABASE pdb_$1
  ADMIN USER  pdb_$1 IDENTIFIED BY oracle
  ROLES = (dba)
  FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/cdb1/pdbseed/',
                       '/home/oracle/pdbtest/$1/');
   drop pluggable database pdb_$1 including datafiles;
exit;
__EOF__
[oracle@localhost pdbtest]$ 

The output below.

[oracle@localhost pdbtest]$ ./a.sh
starting PDBS at  Thu Sep 12 12:42:06 UTC 2013
Welcome 1 times
Welcome 2 times
Welcome 3 times
Welcome 4 times
Welcome 5 times

Pluggable database created.

Elapsed: 00:00:29.82

Pluggable database created.

Elapsed: 00:00:30.04

Pluggable database dropped.

Elapsed: 00:00:00.45

Pluggable database created.

Elapsed: 00:00:30.29

Pluggable database dropped.

Elapsed: 00:00:00.37

Pluggable database dropped.

Elapsed: 00:00:00.30

Pluggable database created.

Elapsed: 00:00:30.59

Pluggable database created.

Elapsed: 00:00:30.67

Pluggable database dropped.

Elapsed: 00:00:00.18

Pluggable database dropped.

Elapsed: 00:00:00.16
Ending  PDBS at  Thu Sep 12 12:42:38 UTC 2013

12c Consolidated AWR report with PDBs

 For a DBA, Automatic Workload Repository (AWR) is THE tool that helps in diagnosis and tuning.  However, with 12c multi-tenant architecture, where one Container Database (CDB) can have multiple Pluggable Database (PDB).  what will happen to the AWR ? 

The Manual  explains 12c AWR as follows.

"If a dictionary table stores information that pertains to the CDB as a whole, instead of for each PDB, then both the metadata and the data displayed in a data dictionary view are stored in the root. For example, Automatic Workload Repository (AWR) data is stored in the root and displayed in some data dictionary views, such as the DBA_HIST_ACTIVE_SESS_HISTORY view. An internal mechanism called an object link enables a PDB to access both the metadata and the data for these types of views in the root."

 It implies that, all the AWR snapshots is taken at CDB level and PDBs have appropriate views  for its corresponding con_id of individual PDBs.

It make total sense in a consolidated environment. If you want to see I/O, cpu utilization on a server, you can get a full picture at a CDB level. Until now in 11g, you needed to login to every DB to check what was happening at a particular time period during say heavy i/o. Now in 12c, you have a global view of all your database(PDBs)  activities on a server at CDB AWR. The individual PDBs still will have views to monitor sqls pertaining to it.

 A simple test is to run  exec dbms_workload_repository.create_snapshot();  in any PDB and CDB and check the max snap_id for each PDB and CDB. (select  con_id,max(snap_id) from dba_hist_sqlstat group by con_id;)
The snap_id is  the same for all PDBs and CDB. This implies, no matter where you run create_snapshot(), Repository is always at CDB level. and every PDB in the DB will get updated VIEW related to its environment.

Below is a small test...

SQL> @a
SQL> -- conn as container DB
SQL> conn sys/oracle as sysdba
Connected.
SQL> show pdbs

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED              READ ONLY  NO
     3 PDB1               READ WRITE NO
     4 PDB2               MOUNTED
     5 DEV2               READ WRITE NO
SQL> exec dbms_workload_repository.create_snapshot();

PL/SQL procedure successfully completed.

SQL> select con_id,max(snap_id) from dba_hist_sqlstat group by con_id order by 1;

    CON_ID MAX(SNAP_ID)
---------- ------------
     1       1204
     3       1204
     5       1204

SQL> ---
SQL> -- conn to pdb called dev2
SQL> --
SQL> conn system/manager@dev2
Connected.
SQL> show pdbs

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     5 DEV2               READ WRITE NO
SQL> -- check the max snap_id
SQL> select con_id,max(snap_id) from dba_hist_sqlstat group by con_id;

    CON_ID MAX(SNAP_ID)
---------- ------------
     5       1204

SQL> exec dbms_workload_repository.create_snapshot();

PL/SQL procedure successfully completed.

SQL> select con_id,max(snap_id) from dba_hist_sqlstat group by con_id order by 1;

    CON_ID MAX(SNAP_ID)
---------- ------------
     5       1205

SQL> --
SQL> -- connect back to cdb and recheck  max snap_id
SQL> conn sys/oracle as sysdba
Connected.
SQL> select con_id,max(snap_id) from dba_hist_sqlstat group by con_id order by 1;

    CON_ID MAX(SNAP_ID)
---------- ------------
     1       1205
     3       1205
     5       1205

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

The next question you might ask is

" can i run AWR reports from PDBs as well as CDBs for consolidated report ? "

Answer: Yes. awrrpt.sql will run at CDB level and PDB level. 
When a snapshot is taken either in PDB or CDB, the data is first populated in the CDB repository. each PDB has all the tables that is required for running awr reports. These tables are populated through object link views from the CDB with information only pertaining to that PDB.

So awrrpt.sql will run at both PDB and CDB levels without errors. However, at PDB level, it will only have info pertaining to it.

Update: If you want to see all the objects with OBJECT link to CDB, you can run the following query.

select distinct object_name from dba_objects where sharing ='OBJECT LINK';

parallel_enable partition by hash cluster

parallel_enable partition by hash

Below i have tried to illustrate a simple example of using GROUP BY function with PARALLEL_ENABLE TABLE functions in ORACLE.

-- Assume you create a sample dataset from DBA_OBJECTs and you want to run a simple sum(obect_id) and count(object_id) group by owner ..

/* now a simple group by result will look like this.

*/

 however, if the data in the table has to be validated through a complex validation process, then this could take a lot of time when the table is large.

 This is because the validation process done through procedure or function is a serial process. In order to speed this process, we can run the validation process in parallel through use of parallel_enabled table function, but still maintain the ability to group by column for the end output. This way, you will be able run data validation process in parallel and at the same time create table functions capable of doing GROUP BY  aggregations.

-- now in my first iteration, I try to do the  paralle_enable table function using  PARTITION BY ANY,  This is the what is most common way to partition data.

the function created to do this is below.

FUNCTION test_group_any( p_cursor IN SYS_REFCURSOR)
  RETURN t_owner_summary_tab PIPELINED PARALLEL_ENABLE(
    PARTITION p_cursor BY ANY)
IS
  in_row data_tab%ROWTYPE;
  out_row t_owner_summary_row ;
  v_total NUMBER := NULL;
  v_owner data_tab.owner%TYPE;
  v_count NUMBER := 0;
BEGIN
  -- for every transaction
  LOOP
    FETCH p_cursor
    INTO in_row;
    EXIT
  WHEN p_cursor%NOTFOUND;
    -- if we pass the extensive validation check
    --   IF super_complex_validation(in_row.object_id,in_row.owner) THEN

    -- set initial total or add to current  total
    -- or return total as required
    IF v_total        IS NULL THEN
      v_total         := in_row.object_id;
      v_owner         := in_row.owner;
      v_count         := 1;
    ELSIF in_row.owner = v_owner THEN
      v_total         := v_total + in_row.object_id;
      v_count         := v_count + 1;
    ELSE
      out_row.owner          := v_owner;
      out_row.sum_total_id   := v_total;
      out_row.count_total_id := v_count ;
      PIPE ROW(out_row);
      v_total := in_row.object_id;
      v_owner := in_row.owner;
      v_count := 1;
    END IF;
    --  END IF;  -- end super complex validation 

  END LOOP; -- every transaction
  out_row.owner          := v_owner;
  out_row.sum_total_id   := v_total;
  out_row.count_total_id :=v_count;
  PIPE ROW(out_row);
  RETURN ;
END test_group_any;

-- the data processed by each parallel process is random and you will get a lot more rows than expected.. The result is not what we are looking for.

 So what if you partition by hash on owner ?

 Note:  in order to PARTITION BY HASH,  the database needs to know the table/object you are passing. so a sys_refcursor will not work.  I changed sys_refcursor to table of %ROWTYPE. from the above function and changed to PARTITION BY HASH instead of PARTITION by ANY.

FUNCTION test_group_hash(  p_cursor IN t_data_tab_ref_cursor)
  RETURN t_owner_summary_tab PIPELINED PARALLEL_ENABLE(  PARTITION p_cursor BY HASH(  owner))
IS

-- still not what we want.. the output is almost similar to partition by any ..

-- so now, i added one additional line  CLUSTER BY OWNER.  This will tell the database to send all rows with the same value for owners to a single parallel slave function. 

FUNCTION test_group_hashcluster(  p_cursor IN t_data_tab_ref_cursor)
  RETURN t_owner_summary_tab PIPELINED PARALLEL_ENABLE(   PARTITION p_cursor BY HASH(  owner))

 CLUSTER p_cursor BY(  owner)

 Hurray. success

 now the result is exactly similar to our sql group by function.

however, it took about 27 seconds even with parallel degree 5 in my Virtual machine ..

So i added code to BULK COLLECT in parallel and it runs in less than 2 seconds.

Acknowledgments.

  Darryl Hurley who wrote chapter Chapter 3 

  Oracle-developer.net 

dbms_comparison quicker than SQL MINUS

Overview

The DBMS_COMPARISON package is an Oracle-supplied package that you can use to compare database objects at two databases. This package also enables you converge the database objects so that they are consistent at different databases. Typically, this package is used in environments that share a database object at multiple databases. When copies of the same database object exist at multiple databases, the database object is a shared database object.

Using DBMS_COMPARISON is considered to be faster than traditional comparison  and used internally by database when using streams replication. When the data to be compared is huge, this package could speed up comparison.

Limitation

The index columns in a comparison must uniquely identify every row involved in a comparison. The following constraints satisfy this requirement:

• A primary key constraint
• A unique constraint on one or more non-NULL columns
  
  

Performance

   Internally the package does  a FAST INDEX SCAN on the primary key or unique key INDEX to find the MIN and MAX value of the table.

     Then, it does a FULL TABLE SCAN to populate the buckets. If there is  miss match in the checksum values of the data in these buckets, additional scans are done to identify the rows..

   The performance of the dbms_compare is equal to the time it takes to do 2 FAST INDEX SCANS and 2 FULL TABLE SCANS. 

    To speed this further, you could 

• Alter the degree of the underlying tables 
• enable PARALLEL DEGREE_POLICY to AUTO and PARALLEL_DEGREE_LIMIT=8 to speed it further. and after it completes, set the parameter back to original value  

Advantage over sql minus

To get all rows without dbms_compare will take 4 FTS with could take double the time as dbms_compare.  delta = (A-B) U (B-A)

  select * from table_A 

  minus

  select * from table_B

  union all

  (select * from table_B

  minus

  select * from table_A) ;
    

• This would take 4 FULL TABLE SCANS compared to 2 in DBMS_COMPARE.
•  it took a lot more temporary tablespace than dbms_compare as it has to sort the entire table with all the columns.

Also, the rowid of the divergent rows are stored for each comparison. So if we need to converge the data, it can done very quickly. 

   

Perform a Compare with DBMS_COMPARISON

To perform a comparison you will follow these steps:

1. Create the comparison 
2. Execute the comparison 
3. Review the results of the comparison 
4. Converge the data if desired
5. Recheck data (the synced data -- optional) 
6. Purge and Drop

Create Comparison

BEGIN
dbms_comparison.create_comparison(
comparison_name=>'VJ_COMPARE',
schema_name=>'SCOTT',
object_name=>'PO_TXN',
dblink_name=>null,
remote_schema_name=>'SCOTT',
remote_object_name=>'VJ_PO_TXN');
END;
/

Execute Comparison

-- If the Primary key or unique index are already defined on the table, then it is picked up automatically for comparision.

Set serveroutput on
declare
     compare_info dbms_comparison.comparison_type;
     compare_return boolean;
begin
     compare_return :=
     dbms_comparison.compare (
     comparison_name=>'VJ_COMPARE',
     scan_info=>compare_info,
     perform_row_dif=>TRUE);
     if compare_return=TRUE
     then
          dbms_output.put_line('the tables are equivalent.');
     else
          dbms_output.put_line('Bad news... there is data divergence.');
          dbms_output.put_line('Check the  and
               dba_comparison_scan_summary views for locate the differences for
               scan_id:'||compare_info.scan_id);
     end if;
end;
/

Data Converge (optional)

  If you want to converge the data, you can use DBMS_COMPARISON.CONVERGE to sync the tables. You need to specify which table is the master whose data is merged.

  

set serveroutput on

  declare

     compare_info dbms_comparison.comparison_type;

     scan_id_num number;

begin

      select scan_id into scan_id_num from DBA_COMPARISON_SCAN_SUMMARY where comparison_name='VJ_COMPARE' and status='ROW DIF';

     dbms_comparison.converge (

          comparison_name=>'VJ_COMPARE',

          scan_id=>scan_id_num,

          scan_info=>compare_info,

          converge_options=>dbms_comparison.CMP_CONVERGE_LOCAL_WINS);

          -- converge_options=>dbms_comparison.CMP_CONVERGE_REMOTE_WINS is the other option.

          dbms_output.put_line('--- Results ---');

          dbms_output.put_line('Local rows Merged by process:

                               '||compare_info.loc_rows_merged);

          dbms_output.put_line('Remote rows Merged by process:

                               '||compare_info.rmt_rows_merged);

          dbms_output.put_line('Local rows Deleted by process:

                               '||compare_info.loc_rows_deleted);

          dbms_output.put_line('Remote rows Deleted by process:

          '||compare_info.rmt_rows_deleted);

end;

/

Drop Comparison

 BEGIN

  DBMS_COMPARISON.PURGE_COMPARISON(

    comparison_name => 'VJ_COMPARE',

    scan_id         => NULL,

    purge_time      => NULL);

END;

/

BEGIN

  DBMS_COMPARISON.DROP_COMPARISON(

    comparison_name => 'VJ_COMPARE');

END;

/

Multiple EXTPROC in 11.2 /EXADATA and 12c PDBs

we know that external procedures or C libraries can interface with oracle database  through extproc. it is defined as follows.

CREATE OR REPLACE  LIBRARY libname as '$APP_V1_HOME/appexe.so' within the database and write a plsql wrapper over it.

There are two ways to set the value for the environment variable APP_V1_HOME defined during library creation. They are

•  extproc.ora
•  SID_LIST_LISTENER.

extproc.ora : The value of APP_VI_HOME used to be stored in listener.ora under SID_LIST_LISTENER until oracle version 11gr2.  However in 11gr2, Oracle introduced the concept of a single file where all the environment variables can be stored. The file is in $ORACLE_HOME/hs/admin/extproc.ora . There are no configuration changes required for either listener.ora or tnsnames.ora.

NOTE: In case this is a RAC or exadata, update the extproc.ora on ORACLE_HOME and not GRID home across all the nodes.

Working with the above example, the information in extproc.ora will be

SET APP_V1_HOME=/home/app/9.3

SET EXTPROC_DLL=ANY

if you had a second app or a newer version of the app in a different directory, all we need to do is add that info in extproc.ora as follows

# Specify the EXTPROC_DLLS environment variable to restrict the DLLs that extproc is allowed to load.

# Without the EXTPROC_DLLS environment variable, extproc loads DLLs from ORACLE_HOME/lib 

SET EXTPROC_DLL=ANY

# APP version V1

SET APP_V1_HOME=/home/app/9.3

#newer app version V2

SET APP_V2_HOME=/home/app/9.4

and define the library in the database as follows

CREATE OR REPLACE  LIBRARY libname as '$APP_V2_HOME/appexe.so' .

Now the above will work within the database and across different databases in the same ORACLE_HOME.

2) SID_LIST_LISTENER

Until 11.2 , the only way to configure extproc was through listener.ora configuration. 

The steps are

1) configure listener.ora

2) add tnsname.ora entry

3) define library with AGENT clause

1) Configure listener.ora.

Create a  separate listener for extproc and define the individual environments in SID_LIST LISTENER.

In case of RAC /Exadata environment, use the ORACLE_HOME to do this. The GRID_HOME is maintained by OraAgent and does some automatic configuration of LOCAL_LISTENER parameters if the file is not touched. Also, i see no additional advantage starting listener for extproc from GRID_HOME.

Below is the sample listing of listener.ora. Note that the enthronement variable APP_HOME points to different directories for different SID_NAME.

The  SID_NAME NEED NOT correspond to the database names. They are just place holders.

LISTENER_DLIB =
(DESCRIPTION_LIST =
  (DESCRIPTION =
  (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROCDLIB))
  )
)

SID_LIST_LISTENER_DLIB =
  (SID_LIST =
   (SID_DESC=
      (SID_NAME=DLIB93)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
      (PROGRAM=extproc)
      (ENVS="LD_LIBRARY_PATH=/home/app/9.3,EXTPROC_DLLS=ANY",APP_HOME=/home/app/9.3)

    )
   (SID_DESC=
      (SID_NAME=DLIB94)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
      (PROGRAM=extproc)
      (ENVS="LD_LIBRARY_PATH=/home/app/9.4,EXTPROC_DLLS=ANY,APP_HOME=/home/app/9.4")

    )
  )

2) configure tnsnames.ora

exaproc_93 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROCDLIB))
    )
    (CONNECT_DATA =
      (SID = DLIB93)
      (PRESENTATION = RO)
    )
  )

exaproc_94 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROCDLIB))
    )
    (CONNECT_DATA =
      (SID = DLIB94)
      (PRESENTATION = RO)
    )
  )

to test the validity, you could start your listener as

lsnrctl start listener_dlib

and check the connect string.

tnsping exaproc_93 

3) configure the LIBRARY ...AGENT.

CREATE <PUBLIC> DATABASE LINK  DBLIB_AGENT using 'extproc_93';

CREATE OR REPLACE  LIBRARY libname as '$APP_HOME/appexe.so'  agent 'DBLIB_AGENT';

observe the AGENT clause in library definition.

Whenever the library is called, oracle looks at the DBLINK in the AGENT clause. If it exists, then the tnsnames.ora file in the DBLINK is lookedup. The tnsnames.ora will in turn point to the SID information in listener.ora. The environment info for that listener corresponding to the SID is pickup dynamically from listener.

  AGENT --> DB LINK --> tnsnames.ora --> listener.ora --> ENVS for that SID.

So , if you need to point your application to different version of shared library without touching the application code, you can simply change the connect string of the DBLINK or change the ENVS info in listener.ora itself.

eg. DROP   <PUBLIC> DATABASE LINK  DBLIB_AGENT ; -- with 'exaproc_64'

    CREATE <PUBLIC> DATABASE LINK  DBLIB_AGENT using 'exaproc_94';

Speed Delete huge amount of rows using dbms_parallel_execute (performance)

The best method to delete  millions of rows  from a large table is to simply rebuild the table.
However, in some environments, that is not possible and you want to really delete the data as quickly as possible. Additional complication could be that you need to delete rows from a master-detail table. The solution to this is to have as many sessions as possible run the delete statements. Each of this sessions can focus on a chunk of data to delete so that they can work in parallel.

This process could be built manually or we could use a new 11g package dbms_parallel_execute.

 Usually, the table you want data to be deleted from, may be the master table with many Detail tables having Foreign Key Constraints (FK) . In this case, you will have to delete the Child table data and then the master table data all in parallel chunks.

below is a sample script where i start 50 concurrent sessions and delete about 40 million rows from the master table and its corresponding detail table ...

drop table po_parallel purge;

-- create table with key columns that needs to be deleted based on the delete logic.

create table po_parallel as (select /*+ parallel(a,16) full(a) hash(a,bc) */ a.PO_TXN_KEY from "APS"."PO_TXN" a,
APS.cal bc where bc.cal_key = a.batch_cal_key and bc.actv_flg = 'N'  ;

/* if you need to delete rows from master-detail tables based on master key column, you need to create a procedure. If it is delete from a single table, then a simple delete statement will be enough.
Note: One common  rookie mistake is to write a delete statement without the where IN clause to pick the pre-selected key-columns. */

create or replace
PROCEDURE parallel_DML_PO (p_start_id IN NUMBER, p_end_id IN NUMBER) AS
BEGIN

    -- Delete from the detail table before the master.
  Delete from APS.INVC_TXN  where PO_TXN_KEY in    ( select po_txn_key from PO_PARALLEL WHERE PO_TXN_KEY BETWEEN p_start_id AND p_end_id);

Delete from APS.PO_TXN_ATTR  where PO_TXN_KEY in ( select po_txn_key from PO_PARALLEL  WHERE PO_TXN_KEY BETWEEN p_start_id AND p_end_id);

Delete  from APS.PO_TXN a  where PO_TXN_KEY in   ( select po_txn_key from PO_PARALLEL  WHERE PO_TXN_KEY BETWEEN p_start_id AND p_end_id);

commit;

end  parallel_DML_PO;

/

-- Next you can run the procedure to execute in parallel.

DECLARE
  l_task     VARCHAR2(30) := 'test_task';
  l_sql_stmt VARCHAR2(32767);
  l_try      NUMBER;
  l_status   NUMBER;
BEGIN

   -- create a task
  DBMS_PARALLEL_EXECUTE.create_task (task_name => l_task);
   -- point to key column and set batch size
  DBMS_PARALLEL_EXECUTE.create_chunks_by_number_col

(task_name    => l_task,
 table_owner  => 'VIJAY',
 table_name   => 'PO_PARALLEL',
 table_column => 'PO_TXN_KEY',
 chunk_size   => 100000);

    -- specify the sql statement
  l_sql_stmt := 'BEGIN parallel_DML_PO(:start_id, :end_id); END;';

   -- run task in parallel
  DBMS_PARALLEL_EXECUTE.run_task(task_name      => l_task,
                                 sql_stmt       => l_sql_stmt,
                                 language_flag  => DBMS_SQL.NATIVE,
                                 parallel_level => 50);

  -- If there is error, RESUME it for at most 2 times.
  l_try := 0;
  l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task);
  WHILE(l_try < 2 and l_status != DBMS_PARALLEL_EXECUTE.FINISHED)
  Loop
    l_try := l_try + 1;
    DBMS_PARALLEL_EXECUTE.resume_task(l_task);
    l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task);
  END LOOP;

-- DBMS_PARALLEL_EXECUTE.drop_task(l_task);
END;
/

-- to monitor the progress see

SELECT chunk_id, status, start_id, end_id
FROM   user_parallel_execute_chunks
WHERE  task_name = 'test_task'
ORDER BY chunk_id;

Once the job is complete you can drop the task, which will drop the associated chunk information also.

BEGIN
  DBMS_PARALLEL_EXECUTE.drop_task('test_task');
END;
/

While this is a example specific to my database, A decade later, I had to do some complex inserts. I did speed it up using FORALL, however, since I needed billions of rows generated, I used this method to insert billions of rows in parallel. You can check that example here.