For how many years have you been working
with physical servers that are starving your database of the memory
necessary to deploy important new performance features such as the Result
Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and
Full Database Caching? Too long? Contact me to learn how to improve all
queries ... not just some queries.
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 18.104.22.168 (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 a40
col PVAL format a20
col PDESC format a60
SELECT a.ksppinm PNAME, c.ksppstvl PVAL, a.ksppdesc PDESC, b.ksppstdf PDFLT
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;
Configure the database for NUMA
conn / as sysdba
ALTER SYSTEM SET "_enable_NUMA_support" = TRUE
COMMENT= 'NUMA Support Enabled 15-Mar-2017'
ALTER SYSTEM SET "_px_NUMA_support_enabled" = TRUE
COMMENT= 'NUMA PX Support Enabled 15-Mar-2017'