SGA size not decreasing after reducing memory_target (Oracle 11.2.0.1)

memoryoracleoracle-11g-r2

I have an Oracle 11.2.0.1 database using dynamic memory allocation with parameter MEMORY_TARGET. When I decrease it with SCOPE=BOTH, the size of the SGA doesn't seem to decrease along with it. I have tested this on multiple systems. Here is an example

SQL> show parameter memory_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------------
memory_target                        big integer 820M
SQL> sho sga

Total System Global Area  855982080 bytes
Fixed Size                  2180544 bytes
Variable Size             520096320 bytes
Database Buffers          327155712 bytes
Redo Buffers                6549504 bytes
SQL> alter system set memory_Target=720M scope=both;

System altered.

SQL> show sga

Total System Global Area  855982080 bytes
Fixed Size                  2180544 bytes
Variable Size             520096320 bytes
Database Buffers          327155712 bytes
Redo Buffers                6549504 bytes
SQL> show parameter memory_target;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------------
memory_target                        big integer 720M
SQL>

However, after I bounce the instance, the SGA seems to size itself appropriately. 

SQL> sho sga

Total System Global Area  751595520 bytes
Fixed Size                  2179536 bytes
Variable Size             415239728 bytes
Database Buffers          327155712 bytes
Redo Buffers                7020544 bytes
SQL>

Best Answer

Do not use show sga for querying the current SGA size.

Total System Global Area in the output of show sga is computed as select sum(value) from v$sga, you can easily confirm this by enabling SQL trace.

SQL> select * from v$sga;

NAME                      VALUE
-------------------- ----------
Fixed Size              2258040
Variable Size         490736520
Database Buffers      356515840
Redo Buffers            6471680

SQL> select sum(value) from v$sga;

SUM(VALUE)
----------
 855982080

This includes Variable Size, and that is not the current usage. Use v$memory_dynamic_components. For an even more detailed list, use v$sgastat and v$pgastat, I will not post output of those here.

SQL> show parameter memory_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_target                        big integer 820M

SQL> show sga

Total System Global Area  855982080 bytes
Fixed Size                  2258040 bytes
Variable Size             494930824 bytes
Database Buffers          352321536 bytes
Redo Buffers                6471680 bytes

SQL> select component, current_size, min_size, max_size from v$memory_dynamic_components;

COMPONENT                 CURRENT_SIZE   MIN_SIZE   MAX_SIZE
------------------------- ------------ ---------- ----------
shared pool                  134217728  130023424  134217728
large pool                     8388608    8388608   71303168
java pool                      4194304    4194304    4194304
streams pool                   4194304          0    4194304
SGA Target                   515899392  515899392  515899392
DEFAULT buffer cache         352321536  289406976  352321536
KEEP buffer cache                    0          0          0
RECYCLE buffer cache                 0          0          0
DEFAULT 2K buffer cache              0          0          0
DEFAULT 4K buffer cache              0          0          0
DEFAULT 8K buffer cache              0          0          0
DEFAULT 16K buffer cache             0          0          0
DEFAULT 32K buffer cache             0          0          0
Shared IO Pool                       0          0          0
PGA Target                   343932928  343932928  343932928
ASM Buffer Cache                     0          0          0

Now, decreasing memory_target (and that is not necessarily the SGA).

SQL> alter system set memory_target=720M scope=memory;

System altered.

SQL> show parameter memory_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_target                        big integer 720M

SQL> show sga

Total System Global Area  855982080 bytes
Fixed Size                  2258040 bytes
Variable Size             494930824 bytes
Database Buffers          352321536 bytes
Redo Buffers                6471680 bytes

show sga still shows the old values.

SQL> select component, current_size, min_size, max_size from v$memory_dynamic_components;

COMPONENT                 CURRENT_SIZE   MIN_SIZE   MAX_SIZE
------------------------- ------------ ---------- ----------
shared pool                  134217728  130023424  134217728
large pool                     8388608    8388608   71303168
java pool                      4194304    4194304    4194304
streams pool                   4194304          0    4194304
SGA Target                   515899392  515899392  515899392
DEFAULT buffer cache         352321536  289406976  352321536
KEEP buffer cache                    0          0          0
RECYCLE buffer cache                 0          0          0
DEFAULT 2K buffer cache              0          0          0
DEFAULT 4K buffer cache              0          0          0
DEFAULT 8K buffer cache              0          0          0
DEFAULT 16K buffer cache             0          0          0
DEFAULT 32K buffer cache             0          0          0
Shared IO Pool                       0          0          0
PGA Target                   239075328  239075328  343932928
ASM Buffer Cache                     0          0          0

Notice how SGA Target did not change at all. Instead, PGA Target was decreased. Decreasing it further:

SQL> alter system set memory_target=420M scope=memory;

System altered.

SQL> show sga

Total System Global Area  855982080 bytes
Fixed Size                  2258040 bytes
Variable Size             629148552 bytes
Database Buffers          218103808 bytes
Redo Buffers                6471680 bytes

Notice how Variable Size has changed, but Total System Global Area has not.

SQL> select component, current_size, min_size, max_size from v$memory_dynamic_components;

COMPONENT                 CURRENT_SIZE   MIN_SIZE   MAX_SIZE
------------------------- ------------ ---------- ----------
shared pool                  134217728  130023424  134217728
large pool                     8388608    8388608   71303168
java pool                      4194304    4194304    4194304
streams pool                   4194304          0    4194304
SGA Target                   381681664  381681664  515899392
DEFAULT buffer cache         218103808  218103808  352321536
KEEP buffer cache                    0          0          0
RECYCLE buffer cache                 0          0          0
DEFAULT 2K buffer cache              0          0          0
DEFAULT 4K buffer cache              0          0          0
DEFAULT 8K buffer cache              0          0          0
DEFAULT 16K buffer cache             0          0          0
DEFAULT 32K buffer cache             0          0          0
Shared IO Pool                       0          0          0
PGA Target                    58720256   58720256  343932928
ASM Buffer Cache                     0          0          0

Variable Size is there because, memory_max_size and sga_max_size was computed from memory_target at instance startup time.

SQL> show parameter memory_max

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_max_target                    big integer 820M
SQL> show parameter sga_max

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 820M

This allows us to increase SGA back to that point. But those you can not change dynamically:

SQL> alter system set memory_max_target=420M scope=memory;
alter system set memory_max_target=420M scope=memory
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified


SQL> alter system set sga_max_size=420M scope=memory;
alter system set sga_max_size=420M scope=memory
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified


SQL> alter system set memory_max_target=920M scope=memory;
alter system set memory_max_target=920M scope=memory
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified


SQL> alter system set sga_max_size=920M scope=memory;
alter system set sga_max_size=920M scope=memory
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
Related Question