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

-prelim and direct_access

I recently asked a question about the 'difference' between /prelim and direct_access on oracle-l.
Tanel Poder gave a great answer. Now it's nearly clear, at least for me:

-prelim allows sysdba to start a preliminary connection to db, which
means that a server process is started for you and it attaches to sga
segments, but it does not allocate anything inside sga (no process
state nor session state object and session structures are allocated
for example). This means tha no latch gets are needed in prelim mode
and if you have an instance hang due these latches your session wont
get stuck. You can do very limited things with prelim mode, mostly
just various dumps with oradebug.

Oradebug direct-access allows you to also query few x dollar tables
with sql-like syntax. thats fake sql though its oradebugs hack, not
real sql. The same is doable with oradebug dumptype as well.

Or, to summarise it (just for me):

• -prelim allows me to start a sqlplus binary and connect it to the (correct ≈ right $ORACLE_HOME and $ORACLE_SID) SGA without creating anything within this SGA. So for me it's the cheapest way to get access to the shared memory of the specific instance.
• this prelim state is of no really use in ordinary life. Just for some really rare occasions, it might be of any use. Most of these are oradebug variants. I have not tested any ALTER SYSTEM or ALTER SESSION statement yet. Some of them might be worth testing, but most will fail. Ordinary SQL will fail all the time (I'm sure).
  
• oradebug direct-access is a nice gadget to query some x$ tables without really going through all the sql-parse-pin-heap-whatever engine ;-)
• at last, -prelim is a kind of last resort to access an instance and at least get some informations, until some really hard direct SGA access methods must be considered