Setting Minimum Values for Memory

Problem
You’re using automatic memory management, but you think that the database sometimes doesn’t
allocate enough memory for the PGA_AGGREGATE_TARGET component.

Solution
Although automatic memory management is supposed to do what it says—automate memory
allocation—there are times when you realize that Oracle isn’t allocating certain memory components
optimally. You can set a minimum value for any of the main Oracle memory components—buffer cache, shared pool, large pool, Java pool, and the PGA memory. For example, even after specifying automatic memory management, you can specify a target for the instance PGA with the following command, without having to restart the database:

SQL> alter system set pga_aggregate_target=1000m;

Oracle will, from this point forward, never decrease the PGA memory allocation to less than the
value you’ve set—this value implicitly sets a minimum value for the memory parameter. The database
will continue to automatically allocate memory to the various components of the SGA, but first it
subtracts the memory you’ve allocated explicitly to the PGA—

in this case, 1,000 MB, from the MEMORY_TARGET parameter’s value. What remains is what the database will allocate to the instance’s SGA.

How It Works
Ever since Oracle introduced the SGA_TARGET (to automate shared memory management) in Oracle
Database 10g and the MEMORY_TARGET parameter (to automate shared memory and PGA memory
management) in Oracle Database 11g, some DBAs have complained that these parameters sometimes
weren’t appropriately sizing some of the components of Oracle memory, such as the buffer cache.
There’s some evidence that under automatic memory management, the database could lag behind
an event that requires a sudden increase in the allocation to either one of the individual components of the SGA or to the PGA. For example, you may have a spurt of activity in the database that requires a quick adjustment to the shared pool component of memory—the database may get to the optimal
shared pool size allocation level only after it notices the events that require the higher memory. As a
result, the database may undergo a temporary performance hit. Several DBAs have, as a result, found
that automatic memory management will work fine, as long as you set a minimum value for, say, the
buffer cache or the PGA or both, by specifying explicit values for the SGA_TARGET and the
PGA_AGGREGATE_TARGET initialization parameters, instead of leaving them at their default value of zero.
The database will still use automatic memory management, but will now use the specific values you set for any of the memory components as minimum values. Having said this, in our experience, automatic memory management works as advertised most of the time; however, your mileage may vary, depending on any special time-based workload changes in a specific database. At times like this, it’s perfectly all right to set minimum values that represent your own understanding of your processing requirements, instead of blindly depending on Oracle’s automatic memory algorithms.