This post was originally published on this site

With Oracle 19c, Oracle has released a new script, annotated for parallel execution, to create the CATALOG and CATPROC in parallel at instance creation.

I have a customer who is in the process of migrating massively to Multitenant using many CDBs, so I decided to give it a try to check how much time they could save for the CDB creations.

I have run the tests on my laptop, on a VirtualBox VM with 4 vCPUs.

Test 1: catalog.sql + catproc.sql

In this test, I use the classic way (this is also the case when DBCA creates the scripts):

${ORACLE_HOME}/perl/bin/perl ${ORACLE_HOME}/rdbms/admin/ -n 1 -l /u01/app/oracle/admin/CDB1/create -v 
  -b catalog  -U "SYS"/"manager" ${ORACLE_HOME}/rdbms/admin/catalog.sql
${ORACLE_HOME}/perl/bin/perl ${ORACLE_HOME}/rdbms/admin/ -n 1 -l /u01/app/oracle/admin/CDB1/create -v 
  -b catproc  -U "SYS"/"manager" ${ORACLE_HOME}/rdbms/admin/catproc.sql

The catalog is created first on CDB$ROOT and PDB$SEED. Then the catproc is created.

Looking at the very first occurrence of BEGIN_RUNNING (start of catalog for CDB$ROOT) and the very last of END_RUNNING in the log (end of catproc in PDB$SEED), I can see that it took ~ 44 minutes to complete:

==== @/u01/app/oracle/product/db_19_6_0/rdbms/admin/catalog.sql
Container:CDB$ROOT Id:1 20-05-04 11:30:54 Proc:0 ====

==== @/u01/app/oracle/product/db_19_6_0/rdbms/admin/catproc.sql
Container:PDB$SEED Id:2 20-05-04 12:15:00 Proc:0 ====


Test 2: catpcat.sql

In this test, I use the new catpcat.sql using, with a parallelism of 4 processes:

${ORACLE_HOME}/perl/bin/perl ${ORACLE_HOME}/rdbms/admin/ -n 4 -c 'CDB$ROOT PDB$SEED' 
  -l /u01/app/oracle/admin/CDB2/create -d ${ORACLE_HOME}/rdbms/admin catpcat.sql

This creates catalog and catproc first on CDB$ROOT, than creates them on PDB$SEED. So, same steps but in different orders.

By running vmstat in the background, I noticed during the run that most of the time the creation was running serially, and when there was some parallelism, it was short and compensated by a lot of process synchronizations (waits, sleeps) done by the

At the end, the process took ~ 45 minutes to complete.

==== @/u01/app/oracle/product/db_19_6_0/rdbms/admin/catpcatstr.sql
Container:CDB$ROOT Id:1 20-05-04 01:53:22 Proc:0 ====
==== @/u01/app/oracle/admin/CDB2/create/catpcatpdb_seed20.0362629753546919.sql
Container:PDB$SEED Id:2 20-05-04 02:38:37 Proc:0 ====

So the answer is no: it is not faster to run catpcat.sql with parallel degree 4 compared to running catalog.sql and catproc.sql serially.