Parallel Oracle Catalog/Catproc creation with catpcat.sql

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

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:

 

Test 2: catpcat.sql

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

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 catctl.pl.

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

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.

HTH

Ludo

The following two tabs change content below.

Ludovico

Principal Product Manager at Oracle
Ludovico ia a member of the Oracle Database High Availability (HA), Scalability & Maximum Availability Architecture (MAA) Product Management team in Oracle. He focuses on Fleet Patching and Provisioning (FPP), MAA-optimized software lifecycle management, Cloud MAA and Online Operations.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.