The SGA is a group of shared memory structures that contain data and control structures for an Oracle instance. When multiple users concurrently access the instance memory, then the data can be shared among the users. This is why the SGA is sometimes referred to as the shared memory area. There are three ways to manage SGA memory:
•\ Automatic memory management (AMM): Setting the MEMORY_TARGET parameter. By setting the MEMORY_TARGET parameter, you are allowing the instance to size and resize the SGA and PGA memory areas.
•\ Automatic shared (or SGA) memory management (ASMM): Setting the SGA_TARGET parameter. By setting the SGA_TARGET parameter, you are allowing the instance to size and resize various SGA components.
•\ Manual shared memory management: Setting all of the necessary pool and cache parameters.
Before we get too far along in this section, let me state that tuning the SGA memory can be confusing. It’s confusing because Oracle uses acronyms that are very similar but mean very different things, such as AMM and ASMM. It’s also confusing because you’ll find conflicting and mutually exclusive recommendations in Oracle’s documentation on tuning the SGA. Case in point, take Oracle’s AMM recommendations:
•\ The Oracle Performance Tuning Guide states that using AMM is strongly recommended by Oracle.
•\ The Oracle Database Administration Guide also recommends that you enable AMM.
•\ The Oracle Database Administration Guide also states that using AMM is only for databases where the size of the SGA and PGA memory is less than four gigabytes.
•\ Oracle Support’s note 749851.1 states that Linux environments using HugePages are incompatible with AMM. This is interesting because a large portion of Oracle’s customers run databases on Linux boxes with HugePages enabled.
So which is it? In an ideal environment, everybody would be using AMM, right? Just set one parameter (MEMORY_TARGET) and you’re done. However, in the real world, it’s not so black and white. Sometimes, there are aspects of your environment (e.g., using the Linux OS) that will steer you in one direction or another. Having said all of that, managing memory can be boiled down to these simple recommendations:
•\ For large databases and/or databases in Linux environments, then use ASMM for managing your SGA, and use automatic PGA memory management for setting your PGA. In this mode, you set MEMORY_ TARGET to zero, and then you set SGA_TARGET and PGA_AGGREGATE_ TARGET to nonzero values.
•\ For small databases and/or databases in non-Linux environments, then use AMM. For this method, you set MEMORY_TARGET to a nonzero value and set SGA_TARGET and PGA_AGGREGATE_TARGET to zero. Oracle will then adjust the memory pool sizes as it sees fit. In this mode, you can optionally set SGA_TARGET and PGA_AGGREGATE_TARGET to nonzero values, and Oracle will use these settings as a minimum amount of memory to allocate to those two values.
•\ Do not use the antiquated manual memory management approaches for your SGA and PGA (unless you have a very good reason, like you’re running an old version of Oracle and don’t have one of the other memory management choices available).
If you’re in a Linux environment, then in most scenarios (Linux environment) you should be using ASMM. I wanted to point this out at the beginning of this section so that it’s clear as to what you should be using for memory management. Having said that, I’ll next discuss each of the SGA management techniques.