DBA Hub

📋Steps in this guide1/2

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 creation of a pluggable database (PDB).

oracle 12cconfigurationintermediate
by OracleDba
12 views
1

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 clause in the command. This functionality relies on the parameter being set to 12.2 or higher. The databases use Oracle Managed Files (OMF) so we don't need to worry about the or settings. The following are functionally identical, both letting Oracle decide on the degree of parallelism (DOP). 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. To create a PDB serially, use the value "0" or "1".

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
-- Automatic DOP.
CREATE PLUGGABLE DATABASE pdb2 FROM pdb5;
CREATE PLUGGABLE DATABASE pdb2 FROM pdb5 PARALLEL;

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

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

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. The typical output I saw for some tests is shown below. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
SELECT qcsid, qcserial#, sid, serial#
FROM   v$px_session
ORDER BY 1,2,3;

-- 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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!