ASMM (Automatic Performance Tuning)

http://oradbpedia.com/wiki/Automatic_Performance_Tuning

Oracle 10g adds the capability to monitor and automatically tune the buffer cache size, alleviating the DBA from this responsibility. This ability is part of Oracle's self-tuning database. Automatic Shared Memory Management (ASMM) will automatically configure the shared memory areas within certain parameters. ASMM will dynamically configure the size of the default buffer cache, the shared pool, the Java pool, and the large pool.

To set up ASMM, the statistics_level parameter must be set to TYPICAL or ALL. The sga_target parameter needs to be set to a non-zero value. It is the sga_target parameter that provides the biggest guideline to ASMM. The total size of all four shared areas cannot exceed this value. ASMM will dynamically adjust the size of these four shared areas to meet the overall database workload, the total never exceeding the sga_target size. Additionally, if you set the db_cache_size, shared_pool_size, java_pool_size, and large_pool_size parameters, these parameters serve as minimum sizes for their respective shared areas. The mimimum sizes of the shared areas is defined by the DBA and the maximum total size of all areas is defined by the DBA. ASMM determines where to size the shared areas within those boundaries.

The sga_target parameter is a dynamic parameter. Setting this parameter to zero turns off ASMM. The maximum value for sga_target is equal to the setting for the sga_max_size parameter. Note that the non-default buffer caches, the log buffer, the Streams pool, the fixed SGA, and other internal allocations are not affected by ASMM. The v$sga_current_resize_ops view shows current resize operations. The v$sga_resize_ops view shows the last 400 completed resize operations.

SGA_TARGET

http://www.orafaq.com/wiki/Sga_target

SGA_TARGET provides the following:

• Single parameter for total SGA size
• Automatically sizes SGA components
• Memory is transferred to where most needed
• Uses workload information
• Uses internal advisory predictions
• STATISTICS_LEVEL must be set to TYPICAL

By using one parameter we don't need to use all other SGA parameters like.

• DB_CACHE_SIZE (DEFAULT buffer pool)
• SHARED_POOL_SIZE (Shared Pool)
• LARGE_POOL_SIZE (Large Pool)
• JAVA_POOL_SIZE (Java Pool)

http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10755/initparams190.htm

The following pools are manually sized components and are not affected by Automatic Shared Memory Management:

·         Log buffer

·         Other buffer caches, such as KEEP, RECYCLE, and other block sizes

·         Streams pool

·         Fixed SGA and other internal allocations

The memory allocated to these pools is deducted from the total available for SGA_TARGET when Automatic Shared Memory Management computes the values of the automatically tuned memory pools.