Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Tuesday, 22 January 2013

Automatic SGA Memory Management

Oracle 10 introduce Automatic SGA Memory Management earlier version having separate parameter for SGA components such as:
DB_Cache, Shared_pool_size, Large_pool_size, Java_pool_size.
The other parameter such as Log buffer, Other Caches, stream pool (new in oracle 10g) must be set manually (not auto-tuned).
Some times, undersized the memory parameter may results poor performance (excess IO, parse) Out of Memory errors (ORA-4031) oversized Wastes these memory and considered to be worst case configuring.
Where as Oracle 9i introduced SGA memory advisories through which one can predict performance of different size and allow better sizing for SGA parameters.
Buffer Cache Advice – v$db_cache_advice
Shared Pool Advice – v$shared_Pool_advice
As the SGA_TARGET parameter is dynamic, you can change the target memory size at any time without restarting the database. SGA serves the upper limit (extra security) of memory so you can not accidentally set the target memory too high. As similar certain SGA component must remain at a minimum size (can not shrink) so instance also prevents to set the target memory size too low.
Note: If you create your database through DBCA and choose the basic installation option, automatic memory management is enabled and if you select advance installation then DBCA enables you to select automatic memory management.
Features: Automatic SGA Memory Management
  1. Single parameter for Total SGA Size.
  2. Automatically sizes SGA component.
  3. Memory is transferred to where most needed.
  4. Use work load information.
  5. Uses internal advisory predictions.
  6. No need to configure parameter for worst-case.
To enable automatic shared memory management set SGA_TARGET to the total SGA size (or less than total SGA size) and STATISTICS_LEVEL must be set to TYPICAL.
Init Parameter Value looks like as:
If you set any value to other parameters (non auto tuned) then total auto tuned parameters value is automatically subtracted from the SGA_TARGET value.
Auto_Tuned_Components = 4G
When SGA_TARGET is not set any value or it is zero then AUTO-SGA management considered being disabled, it behave like as in oracle 9i (auto parameter set to current component size).
When SGA_TARGET is set or not zero then –
·        Total size of manual parameters is subtracted.
·        Balance is given to the auto-tuned component.
·        SGA_TARGET is dynamic can be increased till SGA_MAX_SIZE
·        Can be reduced till some component reaches to the minimum size.
·        Change is SGA_TARGET value affects only automatically sized components.
Benefit for Automatic Memory Management
  1. It maximizes memory utilization.
  2. Single parameter makes it easier to use.
  3. It helps to eliminate out of memory errors.
  4. It can help to improve performance.
Monitoring Automatic Memory Management
SQL> select * from v$memory_target_advice order by memory_size;

----------- ------------------ ------------ ------------------- ----------
        420                 .5          458               1.344          0
        510                .75          367              1.0761          0
        600                  1          341                   1          0
        690               1.25          335               .9817          0
        780                1.5          335               .9817          0
        870               1.75          335               .9817          0
        960                  2          335               .9817          0
Consider in the above example the row with the memory size of 1 show the current size of memory. You can notice in the example there is nothing to be gained by increasing total memory size beyond 690.
For more details about SGA Parameters and their Manual setting read my other post: Manual SGA Sizing


  1. TeethNightGuard is providing personalized fitting and high quality customized dental protectors.