Jump to Navigation


SGA_TARGET is a database initialization parameter (introduced in Oracle 10g) that can be used for automatic SGA memory sizing.
This feature is known as  Automatic Shared Memory Management (ASMM).

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)

1. Check STATISTICS_LEVEL    --  Get DB Statistics

SQL> Alter system statistics = ALL

[ALL | TYPICAL | BASIC]  Default : TYPICAL - It is needed to be ALL or TYPICAL

SQL> show parameter statistics_level

NAME                                        TYPE        VALUE
------------------------------------ ----------- ------------------------------

statistics_level                            string      TYPICAL


SQL> show parameter sga_max_size
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
sga_max_size                         big integer 1000M

It must have SGA_TARGET =< SGA_MAX_SIZE.



You can Disable the automatic SGA tuning by setting the parameter SGA_TARGET to value digit 0.

4. Check PGA

SQL> select * from v$pgastat

The total sga + pga should be less than the actual physical memory available.

SQL> show parameter pga

NAME                                       TYPE        VALUE
------------------------------------ ----------- ------------------------------

pga_aggregate_target                 big integer 1G

5. AMM and the memory_target parameter

In Oracle 11g and beyond, automatic memory management is enabled by using the memory_target  and memory_max_target initialization parameters. 
The memory_target parameter specifies the amount of shared memory available for Oracle to use when dynamically controlling the SGA and PGA.
The memory_max_target AMM parameter specifies the max size that memory_target may take.  The Oracle documentation notes
that the memory_target parameter specifies a target value for system-wide RAM memory, both PGA and SGA RAM:

6. Disabling Automatic Shared Memory Management (ASMM) & AMM

You need to set following sga parameter to 0, if ASMM, or AMM set to ON.(By default oracle 11g using ASMM, or AMM set to ON)

SQL> alter system set sga_target=0 scope=spfile;
SQL> alter system set pga_aggregate_target=0 scope=spfile;
SQL> show parameters memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_max_target                    big integer 0
memory_target                        big integer 0

7. Other SQLs

SQL> select component, oper_type, oper_mode, initial_size/1024/1024 "Initial", TARGET_SIZE/1024/1024  "Target", FINAL_SIZE/1024/1024   "Final", status from v$sga_resize_ops;

SQL> select component, current_size/1024/1024 "CURRENT_SIZE", min_size/1024/1024 "MIN_SIZE", user_specified_size/1024/1024 "USER_SPECIFIED_SIZE", last_oper_type "TYPE" from v$sga_dynamic_components;


Main menu 2

Story | by Dr. Radut