SGA_MAX_SIZE and Memory_Target Parameter in Oracle 10g & 11g

Hi guys, i will explain you the Difference between 10g SGA_MAX_SIZE, SGA_TARGET & 11g feature MEMORY_TARGET, MEMORY_MAX_TARGET


10g Feature :-


SGA_MAX_SIZE : Not Dynamic

SGA_TARGET     : Dynamic


SGA_MAX_SIZE & SGA_TARGET = Oracle introduces this feature in 10g with Automatic Shared Memory Management (ASMM) where oracle manages only sga and living PGA to parameter pga_aggregate_target.


We can say like this SGA_MAX_SIZE parameter value is the maximum size you can increase for your SGA.


Ex:- If SGA_MAX_SIZE=10gb and SGA_TARGET=6GB in future if you want to increase the size of SGA you can increase only upto 10gb.


> SGA_TARGET : When you set SGA_TARGET to any value the Automatic Shared Memory Management (ASMM) is enabled The main advantage of ASMM is no need to worry for a DBA to size the SGA components.

> But still PGA is not managed here in 10g.


Note:-

  If you are using a server parameter file (SPFILE), the database remembers the sizes of the                    automatically tuned SGA components across instance shutdowns.

  As a result, the database instance does not need to learn the characteristics of the workload again     each  time the instance is started.

  The instance can begin with information from the previous instance and continue evaluating                   workload where it left off at the last shutdown.


                                                   ============ xxxxx ===========

11g New Feature :-


   MEMORY_TARGET             : Dynamic

   MEMORY_MAX_TARGET  : Not Dynamic



> Prior to 11g, Oracle manages PGA seperately by the pga_aggregate_target parameter.

> Oracle introduces AMM feature from 11g which will manage SGA as well as PGA.

> When we set MEMORY_TARGET, Oracle will dynamically assign memory to SGA & PGA            

   Note :-  MEMORY_TARGET= SGA_TARGET + PGA_AGGREGATE_TARGET.



> You can see in this demo my requirement is to enable 11g feature Memory_Target and give 10gb size to SGA.

> I have created database with DBCA and selected SGA size by enabling the feature."Use Automatic Memory Management"

> Oracle created this database with 38gb SGA size, this size oracle calculated 40% from existing RAM which is available in my server.



SQL> select sum(value)/1024/1024/1024 "TOTAL SGA (GB)" from v$sga;

TOTAL SGA (GB)
--------------
    38.3281136

> You can see here memory_target and sga_max_size has enabled.


SQL> show parameter memory

NAME                                                   TYPE                      VALUE
------------------------------------ ----------- -----------------------------------
hi_shared_memory_address                integer                        0
memory_max_target                         big integer                 39424M
memory_target                                  big integer                 39424M
shared_memory_address                     integer                         0


SQL> show parameter sga

NAME                                 TYPE                        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                              boolean                    FALSE
pre_page_sga                     boolean                    FALSE
sga_max_size                     big integer               39424M
sga_target                           big integer                    0



> Now i need to allocate some restricted size for the SGA as per my requirement.

> Like we decided to allocate 10gb to SGA and Max_Target should be 2 gb extra.

> So we need to set these 11g parameters SGA_MAX_SIZE,MEMORY_MAX_SIZE AND MEMORY_TARGET.


Steps:-


SQL> alter system set sga_max_size=10g scope=spfile;

System altered.

SQL> alter system set memory_max_target=12g scope=spfile;

System altered.

SQL> alter system set memory_target=12g scope=spfile;

System altered.


>> To effect this SGA  size you need to bounce the database.


SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area     1.0689E+10 bytes
Fixed Size                             2264616 bytes
Variable Size                         4328522200 bytes
Database Buffers                  6341787648 bytes
Redo Buffers                        16900096 bytes
Database mounted.
Database opened.

SQL> show parameter sga

NAME                                         TYPE                    VALUE
------------------------------------ ----------- ------------------------------
lock_sga                                  boolean                  FALSE
pre_page_sga                         boolean                  FALSE
sga_max_size                         big integer               10G
sga_target                               big integer                 0


SQL> show parameter memory

NAME                                                     TYPE                 VALUE
------------------------------------ ----------- ---------------------------------
hi_shared_memory_address                integer                  0
memory_max_target                        big integer               12G
memory_target                                 big integer               12G
shared_memory_address                     integer                 0



>> You can observe here the SGA_max_size is 10gb and Memory_max_target and memory_target is 12g.

>> We can exceed the SGA size upto 12gb.




Happy Learning...