2

What is the recommended memory_target and 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?

  • It's very difficult to establish those limits without knowing a bit about the Db's workload. Nevertheless, i would reserve as most memory as i can (just leaving enough for the OS), and then let AMM (Automatic Memory Manager) do the work. – Renato Afonso Feb 12 '18 at 8:13
3

Forget memory_target and 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:

enter image description here

When I have no information about earlier memory usage, I just go with a 1:4 ratio for PGA:SGA target.

sga_target=20G
pga_aggregate_target=5G

Which implicitly sets:

sga_max_size=20G
pga_aggregate_limit=10G

Then later you can tune if needed, after observing the workload and memory usage.

  • 1
    Fully agree. Just to complete the answer - you also should properly configure Huge Pages on Linux and set USE_LARGE_PAGES = ONLY. – Mindaugas Riauba Feb 12 '18 at 8:17
  • So on a 32 GB dedicated server you recommend SGA+PGA=25GB? which is about 80% of the total memory? – Basil A Feb 12 '18 at 8:54
0

I would recommend using a PGA to SGA ratio of 2:5 based on the following facts and findings.

PGA_AGGREGATE_TARGET

The parameter 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

The parameter PGA_AGGREGATE_LIMIT when not defined will default to the greater of 2 GB, 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)

Worst Case

In a worst case scenario PGA could be 40% the size of SGA.

In Your Case

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.