ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
firstname.lastname@example.org. Request a Workshop for
your organization today.
You should expect that any Sun/Solaris server onto which Oracle is being deployed has been configured by the System Admins for NUMA (Non-Uniform Memory Access).
You can also make this default assumption with H/P blade appliances such as the Cisco UCS. But no matter the architecture you should check every server on which Oracle is installed.
Because the Oracle database does not recognized NUMA on installation or any other time unless you tell it the serrver has NUMA architecture and it is enabled in the O/S.
And there is almost an equals sign one can write between ORA-04030 and your database is not configured for NUMA. This page demonstrates how you can tell and what you should do about it if you find NUMA.
Also be sure you read the following MyOraclesupport doc because you may want to push back on your System Admins and have NUMA disabled. Enable Oracle NUMA support with Oracle Server Version 184.108.40.206 (Doc ID 864633.1)
And also read this important work on the Oracle Database and NUMA by Kevin Closson. You Buy a NUMA System, Oracle Says Disable NUMA! What Gives Also of value be sure you read Oracle Support Doc ID 759565.1 as it contains critically important information.
Is NUMA a good thing or a bad thing? Like with everything else in Oracle ... it depends.
In a NUMA configured server specific memory is allocated to specific cpu cores so, as you will see below, it is possible for one core to exhaust all of its local memory while there is still the appearance to almost all tools that there is plenty of memory available.
Remember the name ... "non-uniform" ... that is what it is referring to.
Since posting this page to the Library I have been involved in several support issues with very large, very sophisticated, organizations that initially had no idea what NUMA was.
Oh the UNIX SysAdmins were ... but the DBAs true to the way most IT shops work, were kept ignorant of critical information such as how their servers were configured, what storage they were running on,
what switches were used for the fusion interconnect, and whether the interconnect was bare metal or virtualized.
Having never heard of NUMA before the DBAs had no idea what its impact on their databases might be, and were reluctant to touch something they didn't understand even when the documentation at MyOracleSupport clearly identifies the issue and the resolution.
Thus the following information is critically important for DBAs to read and understand.
If you know top and sar -B you need to know this too.
We experimented with NUMA in a Solaris implementation several years ago and experienced some of the dynamics you have described.
I did truss threads and run an assembler tracking several background processes that handshake/interface with bitmaps and discovered that the segment offset algorithm(s) were different under a NUMA deployment vs. otherwise.
Under a non-NUMA deployment all segment/offset algorithms are binary based (2,4,8,16,etc.) vs. with NUMA they were linear (1,2,3,4,5,6).
Another observation was when cycling through a latch pattern of EASPIN/EASLEEP cycles the ratio was 1:1 under NUMA and binary with the otherwise (ex. EASPIN=16 EASLEEP=4).
I am not sure what that tells us but in the case of latch dynamics it appeared that Oracle had not fully worked out all that was necessary to be efficient when comparing NUMA to non-NUMA.
set linesize 141
col PNAME format a30
col PVAL format a18
col PDESC format a70
SELECT a.ksppinm PNAME, c.ksppstvl PVAL, a.ksppdesc PDESC
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx
AND a.indx = c.indx
AND LOWER(a.ksppinm) LIKE '%numa%'
ORDER BY 1;
default Numa bind mode
FALSE float process
specified Set of nodes that this instance should run on
Not specified aggregate size in bytes of NUMA pool
Number of NUMA nodes
Enable NUMA interleave mode
Enable NUMA specific optimizations _enable_NUMA_support
FALSE NUMA support and
if TRUE, numa aware lock element distribution
Configure NUMA buffer cache stats
TRUE Enable NUMA
user defined value for numa nodes shift
numa trace event
if TRUE, enable pq slave NUMA affinity
proc-group map string
enable/disable PQ granule stealing across NUMA nodes _px_numa_support_enabled
FALSE enable/disable PQ NUMA support
Is Resource Manager (RM) related NUMA scheduled policy
number of cpus for each pg for numa simulation in resource
number of PGs for numa simulation in resource manager
Configure the database for NUMA
conn / as sysdba
ALTER SYSTEM SET "_enable_NUMA_support" = TRUE
COMMENT= 'NUMA Support Enabled 15-Mar-2020'
ALTER SYSTEM SET "_px_NUMA_support_enabled" = TRUE
COMMENT= 'NUMA PX Support Enabled 15-Mar-2020'