8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Multitenant : Parallel PDB Creation Clause in Oracle Database 12c Release 2 (12.2)

From Oracle database 12.2 onward pluggable databases (PDBs) are created in parallel. You have some level of control over the number of parallel execution servers used to copy files during the creation of a pluggable database (PDB).

Parallel PDB Creation Clause
Monitoring Parallel Execution Servers

Parallel PDB Creation Clause

By default Oracle decides how many parallel execution servers should be used to copy the datafiles from the source (seed or PDB) to the new PDB. You can influence the decision using the PARALLEL clause in the CREATE PLUGGABLE DATABASE command. This functionality relies on the COMPATIBLE parameter being set to 12.2 or higher.

The databases use Oracle Managed Files (OMF) so we don't need to worry about the FILE_NAME_CONVERT or PDB_FILE_NAME_CONVERT settings.

The following are functionally identical, both letting Oracle decide on the degree of parallelism (DOP).

-- Automatic DOP.
CREATE PLUGGABLE DATABASE pdb2 FROM pdb5;
CREATE PLUGGABLE DATABASE pdb2 FROM pdb5 PARALLEL;

Use an integer to manually specify the DOP. Oracle can choose to ignore this if it doesn't make sense. The DOP is limited by the number of datafiles. If the PDB only has 4 datafiles, a DOP of more than 4 will be limited for 4.

-- Manual DOP.
CREATE PLUGGABLE DATABASE pdb2 FROM pdb5 PARALLEL 8;

To create a PDB serially, use the value "0" or "1".

-- Serial
CREATE PLUGGABLE DATABASE pdb2 FROM pdb5 PARALLEL 0;
CREATE PLUGGABLE DATABASE pdb2 FROM pdb5 PARALLEL 1;

Monitoring Parallel Execution Servers

If you are cloning small PDBs, like the seed, you may struggle to be quick enough to see the parallel execution servers. I used the following query whilst cloning a PDB with 10 datafiles on a system that had no other load.

SELECT qcsid, qcserial#, sid, serial#
FROM   v$px_session
ORDER BY 1,2,3;

The typical output I saw for some tests is shown below.

-- No PARALLEL Clause
SELECT qcsid, qcserial#, sid, serial#
FROM   v$px_session
ORDER BY 1,2,3;

     QCSID  QCSERIAL#        SID    SERIAL#
---------- ---------- ---------- ----------
         4      22070         46      30666
         4      22070         50      21167
         4      22070        283      61472
         4      22070        287      27180
         4                     4      22070

SQL>


-- PARALLEL
SELECT qcsid, qcserial#, sid, serial#
FROM   v$px_session
ORDER BY 1,2,3;

     QCSID  QCSERIAL#        SID    SERIAL#
---------- ---------- ---------- ----------
         4      22070         40      51220
         4      22070         50      38168
         4      22070        275      46743
         4      22070        283      13753
         4                     4      22070

SQL>


-- PARALLEL 1
SELECT qcsid, qcserial#, sid, serial#
FROM   v$px_session
ORDER BY 1,2,3;

no rows selected

SQL>


-- PARALLEL 2
SELECT qcsid, qcserial#, sid, serial#
FROM   v$px_session
ORDER BY 1,2,3;

     QCSID  QCSERIAL#        SID    SERIAL#
---------- ---------- ---------- ----------
         4      22070         50      18977
         4      22070        283      12244
         4                     4      22070

SQL>


-- PARALLEL 4
SELECT qcsid, qcserial#, sid, serial#
FROM   v$px_session
ORDER BY 1,2,3;

     QCSID  QCSERIAL#        SID    SERIAL#
---------- ---------- ---------- ----------
         4      22070         32      37798
         4      22070         34      26140
         4      22070        280      12497
         4      22070        282      15558
         4                     4      22070

SQL>


-- PARALLEL 8
SELECT qcsid, qcserial#, sid, serial#
FROM   v$px_session
ORDER BY 1,2,3;

     QCSID  QCSERIAL#        SID    SERIAL#
---------- ---------- ---------- ----------
         4      22070         32      44668
         4      22070         40      47045
         4      22070         44      53818
         4      22070         46      28793
         4      22070        275      39609
         4      22070        282      14300
         4      22070        283      11396
         4      22070        287      35723
         4                     4      22070

SQL>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.