With Oracle 19c, Oracle has released a new script, annotated for parallel execution, to create the CATALOG and CATPROC in parallel at instance creation.<\/p>\n
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.<\/p>\n
I have run the tests on my laptop, on a VirtualBox VM with 4 vCPUs.<\/p>\n
Test 1: catalog.sql + catproc.sql<\/strong><\/p>\n In this test, I use the classic way (this is also the case when DBCA creates the scripts):<\/p>\n The catalog<\/strong> is created first on CDB$ROOT and PDB$SEED. Then the catproc<\/strong> is created.<\/p>\n 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:<\/p>\n <\/p>\n Test 2: catpcat.sql<\/strong><\/p>\n In this test, I use the new catpcat.sql using catctl.pl, with a parallelism of 4 processes:<\/p>\n This creates catalog<\/strong> and catproc<\/strong> first on CDB$ROOT, than creates them on PDB$SEED. So, same steps but in different orders.<\/p>\n 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.<\/p>\n At the end, the process took ~ 45 minutes to complete.<\/p>\n 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.<\/p>\n HTH<\/p>\n —<\/p>\n Ludo<\/p>\n","protected":false},"excerpt":{"rendered":" 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 … Continue reading ${ORACLE_HOME}\/perl\/bin\/perl ${ORACLE_HOME}\/rdbms\/admin\/catcon.pl -n 1 -l \/u01\/app\/oracle\/admin\/CDB1\/create -v \\\r\n -b catalog -U \"SYS\"\/\"manager\" ${ORACLE_HOME}\/rdbms\/admin\/catalog.sql\r\n${ORACLE_HOME}\/perl\/bin\/perl ${ORACLE_HOME}\/rdbms\/admin\/catcon.pl -n 1 -l \/u01\/app\/oracle\/admin\/CDB1\/create -v \\\r\n -b catproc -U \"SYS\"\/\"manager\" ${ORACLE_HOME}\/rdbms\/admin\/catproc.sql\r\n<\/pre>\nBEGIN_RUNNING\r\n--------------------------------------------------------------------------------\r\n==== @\/u01\/app\/oracle\/product\/db_19_6_0\/rdbms\/admin\/catalog.sql\r\nContainer:CDB$ROOT Id:1 20-05-04 11:30:54 Proc:0 ====\r\n\r\nEND_RUNNING\r\n--------------------------------------------------------------------------------\r\n==== @\/u01\/app\/oracle\/product\/db_19_6_0\/rdbms\/admin\/catproc.sql\r\nContainer:PDB$SEED Id:2 20-05-04 12:15:00 Proc:0 ====\r\n<\/pre>\n
${ORACLE_HOME}\/perl\/bin\/perl ${ORACLE_HOME}\/rdbms\/admin\/catctl.pl -n 4 -c 'CDB$ROOT PDB$SEED' \\\r\n -l \/u01\/app\/oracle\/admin\/CDB2\/create -d ${ORACLE_HOME}\/rdbms\/admin catpcat.sql<\/pre>\nBEGIN_RUNNING\r\n--------------------------------------------------------------------------------\r\n==== @\/u01\/app\/oracle\/product\/db_19_6_0\/rdbms\/admin\/catpcatstr.sql\r\nContainer:CDB$ROOT Id:1 20-05-04 01:53:22 Proc:0 ====\r\n...\r\nEND_RUNNING\r\n--------------------------------------------------------------------------------\r\n==== @\/u01\/app\/oracle\/admin\/CDB2\/create\/catpcatpdb_seed20.0362629753546919.sql\r\nContainer:PDB$SEED Id:2 20-05-04 02:38:37 Proc:0 ====<\/pre>\n