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
asra_us@oracle.com. Request a Workshop for
your organization today.
It matters
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.
Why?
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 11.2.0.1 (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.
It matters
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;
PNAME
PVAL PDESC
----------------------------- -------------
--------------------------------------
_NUMA_bind_mode
default Numa bind mode
_NUMA_float_spawner
FALSE float process
spawner
_NUMA_instance_mapping Not
specified Set of nodes that this instance should run on
_NUMA_pool_size
Not specified aggregate size in bytes of NUMA pool
_db_block_numa
1
Number of NUMA nodes
_enable_NUMA_interleave TRUE
Enable NUMA interleave mode
_enable_NUMA_optimization FALSE
Enable NUMA specific optimizations _enable_NUMA_support
FALSE NUMA support and
optimizations
_gc_numa_lock_elements FALSE
if TRUE, numa aware lock element distribution
_numa_buffer_cache_stats 0
Configure NUMA buffer cache stats
_numa_shift_enabled
TRUE Enable NUMA
shift
_numa_shift_value
0
user defined value for numa nodes shift
_numa_trace_level
0
numa trace event
_pq_numa_working_set_affinity TRUE
if TRUE, enable pq slave NUMA affinity
_proc_grp_numa_map
proc-group map string
_px_numa_stealing_enabled TRUE
enable/disable PQ granule stealing across NUMA nodes _px_numa_support_enabled
FALSE enable/disable PQ NUMA support
_rm_numa_sched_enable TRUE
Is Resource Manager (RM) related NUMA scheduled policy
enabled
_rm_numa_simulation_cpus 0
number of cpus for each pg for numa simulation in resource
manager
_rm_numa_simulation_pgs 0
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'
CONTAINER=ALL
SCOPE=SPFILE
SID='*';
System altered.
ALTER SYSTEM SET "_px_NUMA_support_enabled" = TRUE
COMMENT= 'NUMA PX Support Enabled 15-Mar-2020'
CONTAINER=ALL
SCOPE=SPFILE
SID='*';
