Virtual CPUs effect on Oracle SGA allocations.

Saturday Nov 08, 2008

Several years ago, I wrote about how Oracle views multi-threaded processors. At the time we were just introducing a dual-core processor. This doubling of the number of cores was presented by Solaris as virtual CPUs and Oracle would automatically size the CPU_COUNT accordingly. But what happens when you introduce a 1RU server that has 128 virtual CPUs?

The UltraSPARC T1/T2/T2+ servers have many threads or virtual CPUs. The CPU_COUNT on these systems is sized no different than before. So, the newly introduced T5540 with 4xUltraSPARC T2+ processors would have 256 threads and CPU_COUNT would be set to 256.

So, what does CPU_COUNT have to do with memory?

Thanks to my friends in the Oracle Real World Performance group, I was made aware that Oracle uses CPU_COUNT to size the minimum amount of SGA allowed. In one particular case, the DBA was trying to allocate 70 database instances on a T5140 with 64GB of memory and 128 virtual CPUs. Needless to say, the SGA_TARGET would have to be set fairly low in-order to accomplish this task. A SGA_TARGET was set to 256MB, but the following error was encountered.

    ORA-00821: Specified value of sga_target 256M is too small

After experimentation, they were able to start Oracle with a target of 900MB, but with 70 instances this would not fly. Manually lowering the CPU_COUNT allowed the DBA to use an SGA_TARGET of 256MB. Obviously, this is an extreme case and changing CPU_COUNT was reasonable.

Core and virtual CPU counts have been on the rise for some years now. Combine rising virtual CPU count with the current economic climate and I would suspect that consolidation will be more popular than ever. In general, I would not advocate changing CPU_COUNT manually. If you had one instance on this box, the default be just fine. CPU_COUNT automatically sizes so many other parameters that you should be very careful before making a change.

Oracle's vision of multi-core processors on Solaris

Thursday Feb 16, 2006

There seems to be some confusion about how an Oracle instance uses multi-core processors. From a database point of view, it can use all CPU resource that is offered by Solaris. To find out what CPU resources are available, use the "prtdiag" and "psrinfo" commands. The example below shows a single board USIV domain on a SF25K. There are a total of 4 * USIV Processors running 8 cores @1.2GHz each. The prtdiag output shows the 4 processors with two CPU ID's each. The psrinfo command simply shows all 8 cores.

catscratchb:root> /usr/sbin/prtdiag
System Configuration:  Sun Microsystems  sun4u Sun Fire 15000
System clock frequency: 150 MHz
Memory size: 32768 Megabytes

========================= CPUs =========================

         CPU      Run    E$    CPU     CPU
Slot ID   ID       MHz    MB   Impl.    Mask
--------  -------  ----  ----  -------  ----
/SB01/P0   32, 36  1200  16.0  US-IV    2.2
/SB01/P1   33, 37  1200  16.0  US-IV    2.2
/SB01/P2   34, 38  1200  16.0  US-IV    2.2
/SB01/P3   35, 39  1200  16.0  US-IV    2.2

catscratchb:root> /usr/sbin/psrinfo
32      on-line   since 02/07/2006 18:00:23
33      on-line   since 02/07/2006 18:00:25
34      on-line   since 02/07/2006 18:00:25
35      on-line   since 02/07/2006 18:00:25
36      on-line   since 02/07/2006 18:00:25
37      on-line   since 02/07/2006 18:00:25
38      on-line   since 02/07/2006 18:00:25
39      on-line   since 02/07/2006 18:00:25

Oracle does size internal latching structures based on the number of CPUs available. This sizing does NOT disallow Oracle shadow process from using CPU resource, it simply makes the system better suited to scale. To find out how many CPUs Oracle thinks are available, run the following SQL command as sysdba. We have found that Oracle sizes its data structures based on the number or CPU IDs that are reported by psrinfo.

SQL> connect / as sysdba

SQL> select ksppinm name, ksppstvl value, ksppdesc description
       from x$ksppi x, x$ksppcv y
       where (x.indx = y.indx)
         and ksppinm like '%cpu_count%'
       order by name;

NAME            VALUE  DESCRIPTION
--------------- ------ ----------------------------------
cpu_count       8      number of CPUs for this instance