In 9i, SGA has been made dynamic i.e. sizes of SGA components can be changed without shutting down the database. (not possible in 8i)
Many times DBA’s faced problem in calculating correct memory sizes which lead to performance problems in instance level and DBA’s are more involved in handling instance tuning issues. To avoid this, oracle 10g introduced ASMM.
The following memory components are automatically sized when using ASMM
database buffer cache
Note: LOG_BUFFER will not be automatically sized in any version. It’s a static parameter.
Using ASMM, we can define total memory to SGA and oracle will decide how much to distribute to all caches. This is possible by setting SGA_TARGET parameter. (new in 10g)
Note: to enabled ASMM, we should define STATISTICS_LEVEL = TYPICAL (default) or ALL. To know more about ASMM.
Maximum size for SGA is defined by SGA_MAX_SIZE. Depends on transactions load, SGA size will vary from SGA_TARGET to SGA_MAX_SIZE.
It’s been observed that individual parameters are also defined in some 10g databases which means, those values will act as min values and SGA_TARGET value will act as medium and SGA_MAX_SIZE as max value.
Oracle 10g introduced new background process MMAN in order to manage the memory for SGA components.
Note: Memory allocation to both SGA and PGA has been made automated in 11g using MEMORY_TARGET and MEMORY_MAX_TARGET parameters.
Note: SGA size is not at all dependent on database size and will be calculated based on the transactions hitting the database.