What is the recommended
memory_max_target for ORACLE DB running on a dedicated linux server with 32GB RAM?
The server is dedicated to the Oracle 12 database.
Is there any recommendations in such case?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
memory_max_target. AMM has always been a feature that worked on paper, but failed in practice. Oracle finally admitted it semi-officially as of the release of 12.2:
When I have no information about earlier memory usage, I just go with a 1:4 ratio for PGA:SGA target.
Which implicitly sets:
Then later you can tune if needed, after observing the workload and memory usage.
I would recommend using a PGA to SGA ratio of 2:5 based on the following facts and findings.
PGA_AGGREGATE_TARGET when not defined will default to the greater of
10 MB or
20% of SGA as mentioned in the official docs:
Default value : 10 MB or 20% of the size of the SGA, whichever is greater
Reference: 1.213 PGA_AGGREGATE_TARGET (Oracle Docs)
PGA_AGGREGATE_LIMIT when not defined will default to the greater of
200% PGA_AGGREGATE_TARGET or
3MB * Proceses as mentioned in the official docs:
Default value : By default, PGA_AGGREGATE_LIMIT is set to the greater of 2 GB, 200% of PGA_AGGREGATE_TARGET, and 3 MB times the PROCESSES parameter. It will be set below 200% of PGA_AGGREGATE_TARGET if it is larger than 90% of the physical memory size minus the total SGA size, but not below 100% of PGA_AGGREGATE_TARGET.
Reference: 1.212 PGA_AGGREGATE_LIMIT (Oracle Docs)
In a worst case scenario PGA could be 40% the size of SGA.
Splitting up the 32 GB I would round down to 28 GB (leave 4 GB for the OS) and then split in a ratio of 2 to 5:
PGA_AGGREGATE_LIMIT = 8G SGA_MAX_MEMORY = 20G
Why this additional answer?
This answer is a slight variation of Balazs Papp's explanation (who I hold to the highest esteem) based on my current experience and the documentation provided by Oracle. It is here solely for the purpose of explaining why I come to a slightly different conclusion.