12.1: The Default Calculation Of Shared Pool Subpools Is Vulnerable Leading To ORA-4031
(Doc ID 2225248.1)
Last updated on FEBRUARY 15, 2019
Applies to:
Oracle Database - Enterprise Edition - Version 12.1.0.1 to 12.1.0.2 [Release 12.1]Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
There are several factors which influence the shared pool size and the number of subpools being allocated during database startup.
Under some circumstances, 12.1 memory algorithm (Automatic Memory Management (AMM) or Automatic Shared Memory Management (ASMM)) calculates an incorrect number of shared pool subpools, based on cpu_count and on the size of memory_target/memory_max_target or sga_target/sga_max_size. There could be too many calculated subpools with small size leading to ORA-4031 errors as the examples below show.
In 12.1, with cpu_count set to 32, the number of subpools that the shared pool is divided into is as follows:
=====
When memory_target=memory_max_target=4G, 12c AMM calculates 4 subpools:
connect / as sysdba
SELECT
'shared pool ('||NVL(DECODE(TO_CHAR(ksmdsidx),'0','0 - Unused',ksmdsidx),'Total')||'):' sgastatx_subpool, SUM(ksmsslen) bytes, ROUND(SUM(ksmsslen)/1048576,2) MB
FROM
x$ksmss
WHERE
ksmsslen > 0
GROUP BY ROLLUP (ksmdsidx )
ORDER BY sgastatx_subpool ASC;
/
SUBPOOL BYTES MB
------------------------------ ---------- ----------
shared pool (1): 134217728 128
shared pool (2): 134217728 128
shared pool (3): 134217728 128
shared pool (4): 117440512 112
shared pool (Total): 520093696 496
Case B
=====
When memory_target=4G and memory_max_target=8G, 12c AMM calculates 7 subpools:
SUBPOOL BYTES MB
------------------------------ ---------- ----------
shared pool (1): 83886080 80
shared pool (2): 83886080 80
shared pool (3): 83886080 80
shared pool (4): 117440512 112
shared pool (5): 83886080 80
shared pool (6): 83886080 80
shared pool (7): 83886080 80
shared pool (Total): 620756992 592
==> A subpool with 80 MB in size is very small.
The cpu_count determines how many subpools the shared pool and large pool are divided into. After many tests, the following algorithm is observed to be used for the default calculation of subpools during startup in 12.1:
Changes
When AMM is disabled (memory_target/memory _max_target not set) and sga_target>0. With sga_target=4G, sga_max_size=8G and cpu_count=32, the default number of subpools is:
SGASTATX_SUBPOOL BYTES MB
------------------------------------------------------- ---------- ----------
shared pool (0 - Unused): 486539264 464
shared pool (1): 285212672 272
shared pool (Total): 771751936 736
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Changes |
| Cause |
| Solution |
| References |