Memory Structures - Expert Oracle Database Architecture - page 169

Automatic Memory Management
Starting in Oracle 11 g Release 1 and above, the database also offers automatic memory management—sort of a one
stop shop for all of your memory settings. With Oracle 10 g and automatic SGA memory management—the DBA could
get away with just two major memory settings—the PGA_AGGREGATE_TARGET and the SGA_TARGET . The database would
automatically allocate and reallocate memory chunks within each as described earlier. In Oracle 11 g , 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.

■ Before implementing automatic memory management, consider checking for any MOS related operating
system specific notes such as 749851.1 (for Linux) and 1399274.1 (for Solaris).

Note

Just as with automatic SGA memory management, 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:

memory_target = 756m •

sga_target = 256m •

pga_aggregate_target = 256m •

The stored parameter file for that database currently has:

SYS@ORA12CR1> create pfile='/tmp/pfile' from spfile;
File created.

SYS@ORA12CR1> !cat /tmp/pfile;
ORA12CR1.__data_transfer_cache_size=0
ORA12CR1.__db_cache_size=67108864
ORA12CR1.__java_pool_size=4194304
ORA12CR1.__large_pool_size=4194304
ORA12CR1.__oracle_base='/orahome/app/oracle'#ORACLE_BASE set from environment
ORA12CR1.__pga_aggregate_target=520093696
ORA12CR1.__sga_target=272629760
ORA12CR1.__shared_io_pool_size=0
ORA12CR1.__shared_pool_size=184549376
ORA12CR1.__streams_pool_size=0
*.compatible='12.1.0.1'
*.control_files='/u01/dbfile/ORA12CR1/control01.ctl','/u02/dbfile/ORA12CR1/control02.ctl'
*.db_block_size=8192
*.db_name='ORA12CR1'
*.memory_target=792723456
*.open_cursors=300