When you upgrade your entire CDB with AutoUpgrade, there is a parameter which allows you some tuning or tweaking. And I often see some misunderstanding here. Therefore, for CDB upgrades – explaining catctl_options is my focus today.
Photo by Arthur Mazi on Unsplash
catctl_options – what it is meant for
This AutoUpgrade config file parameter allows you to set specific settings, mostly for tuning, but also to override some defaults. In the documentation you will find the whole list of options. You can set it in your AutoUpgrade config file, e.g.:
upg1.catctl_options=-T
catctl_options in CDB upgrades
Here, I see the option used quite often. And more than I expected. There seems to be an urge to make entire CDB upgrades run faster. But let me cover at first how every “entire CDB” upgrade works.
At first, always the CDB$ROOT gets upgraded when you do this “entire CDB” upgrade. By default, with a CPU_COUNT>7 we will use 8 parallel workers unless you define something else. Typically, 3-4 workers are enough, the gain in speed to completion is really minimal with more workers.
Then we upgrade the PDBs in parallel, and the PDB$SEED needs to be taken into account. Often, it is the one taking the longest since it triggers a recompilation which does not happen for the other PDBs. I am not 100% sure if this is still the case when you move to 26ai but it was the case in previous releases.
Now, our most important parameter is CPU_COUNT. Daniel has blogged about this 5 years ago already – and I think it is time for a quick refresh.
Why?
Because I see config files with settings such as:
catctl_options=-n 16 -N 4
used in an environment where CPU_COUNT=16 and the customer has 5 PDBs.
The documentation defines -n and -N as:
- -n
Number of processes to use for parallel operations. - -N
Number of processors to use when upgrading PDBs. For Classic upgrades, the default is 2
catctl_options set incorrectly
What happens now when you have CPU_COUNT=16, and you want to upgrade 5 PDBs with a setting of:
catctl_options=-n 16 -N 4
This will lead to 3 upgrade cycles.
Let us do some simple math:
- CDB$ROOT gets upgraded always at first – since we have CPU_COUNT=16 it will use 8 parallel workers
- PDB$SEED and 5 user-created PDBs make a total of 6 PDBs
- Each of these 6 PDBs will allocate 4 parallel workers since -N 4 has been defined
- We have a maximum parallel degree (or number of workers) defined as -n 16.
As a result, 4 PDBs can be upgraded in parallel since each requires 4 workers, and we have a maximum of 16 workers set.
This leads to 3 upgrade cycles:
- Cycle 1: CDB$ROOT with 8 workers
- Cycle 2: PDB$SEED, PDB1, PDB2, PDB3, each with 4 workers
- Cycle 3: PDB4 and PDB5, each with 4 workers
This is not only inefficient since it is unbalanced with the last cycle only employing 8 workers while we wanted a faster upgrade having 16 available, it also leads to 3 upgrade cycles. Let’s assume each cycle has taken about 30 mins plus/minus a bit, we end up at about 90 minutes in total.
To be frank, I see this way too often these days. Which tells me that people are not happy with the parallel activity being performed out of the box.
catctl_options set correctly
At first, you don’t have to touch them. The default should be fine in 95% of all cases.
Only start tweaking them when you are unhappy, and you think and surveil that your system can take way more load. But please, check before.
Back to my above example, by default, AutoUpgrade would do this:
- CDB$ROOT gets upgraded always at first – since we have CPU_COUNT=16 it will use 8 parallel workers
- PDB$SEED and all 5 user-created PDBs get upgraded in parallel, each of them with 2 workers
We end up with 2 upgrade cycles:
- Cycle 1: CDB$ROOT with 8 workers
- Cycle 2: PDB$SEED, PDB1 – PDB5, each with 2 workers
In total, we may take again 30 mins for the PDB$SEED, and then now 40 minutes due to the higher concurrency for each PDB. But since this runs in parallel for all PDBs at the same time, we may complete the upgrade in total in 70 minutes – 20 minutes faster than in the previous example.
Leave the default – unless you think your system can take more load.
catctl_options tuned wisely
Again, let me use the above example. We have a CPU_COUNT of 16 and 5+1 PDB since the PDB$SEED gets upgraded together with the PDBs, always in the first PDB upgrade cycle. Since we don’t have a lot of PDBs but only a few, my focus will be to include them all into one cycle but max out the system.
Every additional cycle will have a higher impact with a small number of PDBs.
The CDB$ROOT cycle is nothing I can speed up by adding more workers. We figured out that more than 8 parallel workers will give no benefit but instead may lead to slower upgrades overall. Therefore, I won’t spend any attention on this.
But for my 5+1 PDBs, I could employ 3 workers per PDB. This would require: 6 x 3 workers = 18 workers in total. In order to achieve this, I need now tweak the default parameters and set:
catctl_options=-n 18 -N 3
Overall, I will employ 18 workers, 3 for each of the PDBs
- CDB$ROOT gets upgraded with 8 parallel workers
- PDB$SEED and all 5 user-created PDBs get upgraded in parallel, each of them with 2 workers
We are slightly overloading the system but the result would be 3 workers in each of the PDBs instead of 2. This would be worth a try – and we may speed up the upgrade a little bit but still keep only two upgrade cycles.
If we’d go even higher to 4 workers per PDB we need to adjust the -n parameter at first to allow more parallel workers in sum:
catctl_options=-n 24 -N 4
This will now request 24 parallel workers with a CPU_COUNT=16 – so be aware and monitor your system. I don’t expect much of an improvement since more parallel workers in multiple PDBs at the same time increase concurrency as well, especially in the dictionary of CDB$ROOT but also potentially in library cache.
Summary
Only adjust the default settings when you are not satisfied with the overall results. By default, 2 workers work on each PDB’s upgrade, the PDB$SEED counts as a PDB – and overall, the CPU_COUNT dictates how many parallel PDB upgrades you’ll get.
You may be able to tweak this and overload the system. But be careful, especially when you have other databases running in parallel.
Further Links and Information
- Daniel’s Blog Post on how to control upgrade parallelism from 2021
- catctl_options in the Oracle documentation
–Mike
