SHMALL Sizing with Oracle Automatic Memory Management (AMM)
user10437903 Aug 26, 2013 5:31 PMI have listed my environment, some pertinent information from Oracle and some non-sourced findings.
My questions are at the bottom. Some questions may not be relevent based on the answers provided.
ENVIRONMENT
Three Node RAC Cluster
Single Database
Oracle Linux 5 Update 2
Oracle Grid Infrastructure 11gR2 (11.2.0.2.0)
- Oracle Automatic Storage Management 11gR2 (11.2.0.2.0)
Oracle Database 11gR2 (11.2.0.2.0) Enterprise Edition with RAC option
RAM Currently: 16 Gb Soon to be Upgraded to: 32 Gb
DEFINITION
SHMALL is the total amount of shared memory, in pages, that the system can use at one time.
ORACLE SIZING RECOMMENDATIONS
Oracle Doc ID 301830
Set shmall equal to the sum of all the SGAs on the system, divided by the page size.
Oracle Doc ID 1441282.1
kernel.shmall = physical RAM size / pagesize For most systems, this will be the value 2097152.
See Note 301830.1 for more information.
Oracle® Database Installation Guide 11g Release 2 (11.2) for Linux E24321-07
shmall 2097152 /proc/sys/kernel/shmall
NON-SOURCED FINDINGS
- The total size cannot exceed available Physical RAM.
- The value set should be short of using all physical memory accommodating other memory needs (i.e. OS, shadow processes).
- Leave around 1 Gb of RAM for Linux OS
QUESTIONS
1) I understand that SGA is shared memory and PGA is private memory in dedicated server processes.
I read Doc ID 301830.
I am not confusing MEMORY_TARGET which I know encompasses SGA and PGA.
I've had two different responses from Oracle to the following question which is why I am here asking the question.
If we're using AMM, do we need to size shmall accounting for SGA and PGA?
a) If no, then according Doc ID 301830 we should size shmall to sum of all SGAs, correct?
b) If yes, then why is shared memory being allocated for private memory (PGA)?
2) When using AMM, the sizes for SGA and PGA can grow/shrink based on workload.
a) If my system is up and running what is the best way to size the shmall given the SGA and PGA are targets (meaning they can grow)?
b) Is there some buffer (percentage) that we should leave when sizing?
3) ASM instance has an SGA of around 256 Mb. Does this need to be included?
4) Does dbcsonole, emagent or other oracle tools use shared memory in shmall?
5) Other than the OS and shadow processes, what "other memory needs" would need to be accommodated OUTSIDE shmall?
Thank you.