SAP Knowledge Base Article
1873631 - Best practices to set SGA and PGA
Component: BC-DB-ORA (Basis Components > Database Interface, Database Platforms > Oracle), Version: 14,
Released On: 21.10.2024
Symptom
This document was designed to server as a basic Oracle memory configuration guide.
You may have reached it either by facing one of the errors below, or due performance issues.
After the system is running for a while, you starting receiving ORA-04031 errors.
ORA-04031: unable to allocate <bytes> bytes of shared memory
You have already checked the following SAP Notes but they didn't help:
1472386 - ORA-04031 out of memory error in BW systems
1478228 - Oracle ORA-4031 WHEN GATHER STATS IN PARALLEL
869006 - Composite SAP note: ORA-04031
690241 - The Shared SQL Area in the Shared Pool and large SQL Stmnts
If you did not check this notes yet, please make sure you go through them before applying the solution proposed here.
Environment
SAP systems based on NetWeaver;
Oracle database
Automatic Shared Memory Management (ASMM) or Automatic Memory Management (AMM) enabled.
Reproducing the Issue
Start the system, after running for a while the error start to appear for some sessions.
Cause
In some cases ASMM does not handle the memory areas distribution properly and this might lead to ORA-04031 errors due
to memory overflow. The same may happen to AMM.
Resolution
Disable ASMM and AMM. To do that, make sure the parameters below are deleted or set to zero:
sga_target (enables ASMM)
memory_target (as of 11g, enables AMM)
Then set the more appropriate value for each of the memory areas in the following order:
PGA_AGGREGATE_TARGET - out of total amount of available memory available - 20%for OLTP or 40% for OLAP
SHARED_POOL_SIZE - use the formula from note 690241 for the memory after asigning the PGA amount
DB_CACHE_SIZE - all remaining memory after adjusting shared pool
Very specific scenarios may require setting other parameters, but for 99.9% of the environment those three parameters will be
more than enough.
You can refer to the parameter note for your database version and all notes mentioned there for each of these parameters:
830576 - Parameter recommendations for Oracle 10g
1431798 - Oracle 11.2.0: Database Parameter Settings
1888485 - Database Parameter for 12.1.0.2
2470718 - Oracle Database Parameter 12.2 / 18c / 19c
See Also
SAP Note 789011 - FAQ: Oracle memory areas
SAP Note 1472386 - ORA-04031 out of memory error in BW systems
SAP Note 1478228 - Oracle ORA-4031 WHEN GATHER STATS IN PARALLEL
SAP Note 869006 - Composite SAP note: ORA-04031
SAP Note 690241 - The Shared SQL Area in the Shared Pool and large SQL Stmnts
SAP Note 830576 - Parameter recommendations for Oracle 10g
SAP Note 1431798 - Oracle 11.2.0: Database Parameter Settings
SAP Note 1888485 - Database Parameter for 12.1.0.2
SAP Note 2470718 - Oracle Database Parameter 12.2 / 18c / 19c
Keywords
overflow buffer user session fails crash ORA-04030 shared pool buffer cache stop abort hang sga system global area memory
pga program work process fail sga_max_size ORA-04031 tuning performance recommendation
Products
Products
SAP NetWeaver all versions
This document refers to
SAP Note/KBA Component Title
1888485 BC-DB-ORA
1478228 BC-DB-ORA Oracle ORA-4031 WHEN GATHER STATS IN PARALLEL
1472386 BC-DB-ORA ORA-04031 out of memory error in BW systems
1431798 BC-DB-ORA Oracle 11.2.0: Database Parameter Settings
869006 BC-DB-ORA Composite SAP note: ORA-04031
830576 BC-DB-ORA Parameter recommendations for Oracle 10g
690241 BC-DB-ORA The Shared SQL Area in the Shared Pool and large SQL Stmnts
