dynamic paramater change when CPUs (dis)appear

 In my last 2 blogposts I covered how Oracle instance parameters change based on cpu_count at startup, and also what's the minimum requirement for SGA size. 

But there is more to discover! Modern systems can change the number of CPUs while running. This feature is quite popular in cloud environments but in fact was already available more than 20 years ago. 

To see if this has any impact on my (21c) sandbox instance, I used a small "trick" in Linux: 
As root I can set a CPU offline (or back online) by 

echo 0 > /sys/devices/system/cpu/cpu7/online

This CPU becomes invisible to the processes - it also "disappears" in /proc/cpuinfo. 

At every change, the alert.log shows an entry like (The sampling frequency was 70 sec on my system)

2022-11-20T14:09:12.762038+00:00
Detected change in CPU count to 7
* Load Monitor used for high load check
* New Low - High Load Threshold Range = [0 - 0]

In this case my test was quite small (only 1..8 PCUs) but at least in that range only a small set of parameters changed:

PARAMETER
cpu_count
cpu_min_count
job_queue_processes
parallel_max_servers
parallel_servers_target

cpu_count and cpu_min_count makes sense, the remaining parameters are only related to

parallel and job processes. All of them are following the simple formula cpu_count * 20. 

This follows the "normal" derived values for these parameters as shown before. 

Again this is nothing tremendous spectacular, but worth to know for performance engineering or analysis in volatile systems: Some parameters change and might lead to unexpected behavior, if the change isn't taken into consideration. 

minimal SGA requirements - dependent on CPU_COUNT

In my previous post I described how different spfile parameters change dependent on the number of cpus available in the system (or the value of CPU_COUNT in my artificial testcase).
During these tests I saw this error (and so had to re-arrgange my scripts and try again): 

ORA-00821: Specified value of sga_target 16M is too small, needs to be at least 2032M

Even the instance started fine with a smaller cpu_count, it seems the SGA needs to grow when more CPUs are used. So it's worth to re-run the test and see, what's the minimum SGA per CPU. (Again, these tests are made on my sandbox with Version 21.8 - your values might differ). 

My measurement tells me SGA requirements between 260MB for 1 CPU and 11568MB for 255 CPUs (the raw data can be seen here). 

It looks there is a quite linear growth, and this is also confirmed: 

Call:

lm(formula = sga ~ cpu, data = data)

Residuals:

    Min      1Q  Median      3Q     Max 

-91.887 -55.661  -3.829  48.098 116.941 

Coefficients:

             Estimate Std. Error t value Pr(>|t|)    

(Intercept) 112.25259    7.38744    15.2   <2e-16 ***

cpu          44.50121    0.05003   889.5   <2e-16 ***

---

Signif. codes:  0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1

Residual standard error: 58.81 on 253 degrees of freedom

Multiple R-squared:  0.9997,    Adjusted R-squared:  0.9997 

F-statistic: 7.912e+05 on 1 and 253 DF,  p-value: < 2.2e-16

The core message is: SGA requirements starts with 112.3MB and adds about 44.5MB for each CPU. 

Even the graph looks quite straight, there is a little deviation from the numbers. To make them visible, I generated the delta and get this plot. 

It seems the residuals smile at me. We can also see the calculation up to 32 cpus is slightly different than the remaining, it drops to a minimum (negative residual) at 112 and then increases again. Beside this major pattern there are smaller groups also visible, this indicates different (internal) functions have their own increments and together, they provide this picture. 

Please be aware the units: in the first picture we have units of GB, de residuals are shown in MB. Over all they are still small. 

(In case you want to play with the data, my R commands are available)

You might think this is all irrelevant as with huge number of CPUS there also comes huge amount of memory which can be used for SGA. But at least for XE on nodes with many CPUs, there is a real impact (with a simple solution). 

spfile parameters derived from cpu_count 11.1 up to 21

In Oracle databases some spfile parameters are derived based on others, if they are not set explicit.
One of the main drivers I discovered is CPU_COUNT.
So I did some tests based in the past for version, now up to 21.

the script I used this time is basically (of course with proper version name).

In comparison to previous versions sga_target is bigger again, now it's 12g (otherwise instances with higher ocunts wouldn't even start)

#!/usr/bin/ksh  -x

integer i=1
while ((i <= 255));
do
  print " $i ";

  echo "*.audit_file_dest='/_some_dir_/BX1/adump'
*.audit_trail='db'
*.compatible='21.0.0.0.0'
*.control_files='/u02/oradata/CDB1/control01.ctl','/u02/oradata/CDB1/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u02/oradata'
*.db_name='cdb1'
*.db_unique_name='cdb1'
*.diagnostic_dest='/u01/appl/oracle'
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=12g
*.enable_pluggable_database=true
*.undo_tablespace='UNDOTBS1'
*._disable_cpu_check='TRUE'
*.cpu_count=$i" > /_some_dir_/cpus/initBX1_$i.ora

  echo "shutdown abort;
startup pfile='/_some_dir_/cpus/initBX1_$i.ora' ;

insert into init_parameters
select '21.08'     ,
       $i         ,
       a.ksppinm  ,
       b.ksppstvl ,
       c.ksppstvl
from x\$ksppi a, x\$ksppcv b, x\$ksppsv c
where a.indx = b.indx and a.indx = c.indx;

commit;

select distinct(\"CPUs\") from init_parameters;

shutdown;
exit;"> cpus/21doit$i.sql

sqlplus "/ as sysdba" @cpus/21doit$i.sql >cpus/21log$i.log

sleep 5

  (( i = i + 1));
done

To get a good picture of the changes, feel free to select parameters, versions or whatever you want below.
Raw data is available at GistHub.
The js app is available here.
(It's based on an idea PerfSheet.js by Luca Canali.

As some of the values are not numbers, I applied this mapping:

TRUE	1
FALSE	0
OFF	0
ADAPTIVE	-1
INSTANCE	-2

---