AMM is sort of a one-stop shop for all of your memory settings. The DBA can now get away with setting a single memory parameter—the MEMORY_TARGET. This MEMORY_TARGET represents the total amount of memory the combined SGA and PGA allocations should strive to stay within (remember, the PGA memory can be somewhat uncontrollable). The database will dynamically determine what the proper SGA size is and what the proper PGA size is, based on workload history. Over time, as the workload performed in the database changes, the allocations to the SGA and PGA will change as well. For example, if you are heavy OLTP (Online Transaction Processing) during the day and heavy batch processing at night, you might discover that the daytime SGA is much larger than the PGA, and the nighttime SGA is much smaller than the PGA. This would reflect the different memory needs of these two application types.
Note If you run your Oracle databases in a Linux environment that uses HugePages, read Oracle Support document ID 749851.1. The note states the use of AMM is incompatible with Linux HugePages. The use of Linux HugePages is a standard practice with Oracle databases as it improves performance.
The DBA can set up lower bounds for the size of each memory area by setting the SGA_TARGET and PGA_AGGREGATE_TARGET or the lower bound of each of the pools in the SGA by setting their values to that lower bound. The database will remember the optimal settings for the pools and the SGA and PGA in the stored parameter file if you are using one. For example, on one of my test systems, I’ve set
SQL> alter system set memory_target = 756m scope=spfile; SQL> alter system set sga_target = 300m scope=spfile;
SQL> alter system set pga_aggregate_target=300 scope=spfile;
Next, I bounce (stop/start) the instance to instantiate the parameters:
SQL> startup force;
Inspecting the spfile, it currently has the following settings:
SQL> create pfile=’/tmp/pfile’ from spfile; File created.
SQL> !cat /tmp/pfile;
CDB.__db_cache_size=71303168
CDB.__inmemory_ext_roarea=0
CDB.__inmemory_ext_rwarea=0
CDB.__java_pool_size=0
CDB.__large_pool_size=4194304
CDB.__oracle_base=’/opt/oracle’#ORACLE_BASE set from environment
CDB.__pga_aggregate_target=478150656
CDB.__sga_target=314572800
CDB.__shared_io_pool_size=8388608
CDB.__shared_pool_size=213909504
CDB.__streams_pool_size=0
CDB.__unified_pga_pool_size=0
*.memory_target=792723456
*.pga_aggregate_target=314572800
*.sga_target=314572800
…
As you can see, the double-underscore parameters include the __sga_target and __pga_aggregate_target settings as well as the various pools. These values are derived based on the memory parameter settings in the spfile, as well as the observed server workload. In this fashion, Oracle will remember your last optimal SGA/PGA settings and use them upon the next restart.
Note This last feature, of storing the recommended values for the pools, only works if you are using an spfile for your initialization file (and not a text-based init.ora file).