Memory target

MEMORY_TARGET is a database initialization parameter (introduced in Oracle 11g) that can be used for automatic PGA and SGA memory sizing.

Parameter description:

MEMORY_TARGET
Property 	Description
Parameter type 	Big integer
Syntax 	        MEMORY_TARGET = integer [K | M | G]
Default value 	0
Modifiable 	ALTER SYSTEM
Range of value  152 MB to MEMORY_MAX_TARGET
Basic  	        No

MEMORY_TARGET provides the following:

• A single parameter for total SGA and PGA sizes
  
• Automatically sizes SGA components and PGA
  
• Memory is transferred to where most needed
  
• Uses workload information
  
• Uses internal advisory predictions
  
• Can be enable by DBCA at the time of Database creation.

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

DB_CACHE_SIZE
SHARED_POOL_SIZE
LARGE_POOL_SIZE
JAVA_POOL_SIZE
PGA_AGGREGATE_TARGET

Four most commonly configured components are automatically sized:

• Shared Pool
• Large Pool
• Java Pool
• Buffer Cache (DEFAULT buffer pool)
• Pga_Aggregate_Target
• STATISTICS_LEVEL must be set to TYPICAL

The New view V$MEMORY_DYNAMIC_COMPONENTS shows the current sizes of all dynamically tuned memory components, including the total sizes of the SGA and instance PGA.

SQL> select * from v$memory_target_advice order by memory_size;

MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR    VERSION
----------- ------------------ ------------ ------------------- ----------
       250                .25          173                   1          1
       500                 .5          173                   1          1
       750                .75          173                   1          1
      1000                  1          173                   1          1
      1250               1.25          173                   1          1
      1500                1.5          173                   1          1
      1750               1.75          173                   1          1
      2000                  2          173                   1          1
 8 rows selected.

Enable MEMORY_TARGET

SQL> show parameter memory_target
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------
memory_target                        big integer 0
SQL> show parameter memory_max_target
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------
memory_max_target                    big integer 1500M
SQL> alter system set memory_target=1000m;
System altered.
SQL> show parameter memory_target
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------
memory_target                        big integer 1000M

Now our automatic tuning for both SGA and PGA started by using the above parameter. We can increase the value of MEMORY_TARGET parameter to MEMORY_MAX_TARGET, if we will try to increase it more from the size of max parameter it will throw error.

Resize MEMORY_TARGET

• MEMORY_TARGET is dynamic
• Can be increased till MEMORY_MAX_TARGET
• Can be reduced till some component reaches minimum size
• Change in value of MEMORY_TARGET affects only automatically sized components

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

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

Now we can increase the size of MEMORY_TARGET to the upper bound of 1500 because our max limit is 1500.

SQL> alter system set memory_target=1400m;
System altered.

If we try to increase it more than 1500 it will throw error ,look here

SQL> alter system set memory_target=1600m;
alter system set memory_target=1600m
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET

So to prevent from this error we should increase our MEMORY_MAX_TARGET parameter to avoid the above error. we can check which parameter is static and which is dynamic by querying v$parameter view.

SQL>select name,isses_modifiable,issys_modifiable,isinstance_modifiable
  from v$parameter where name like 'memory_max_target'
NAME                  ISSES_MO ISSYS_MO ISINSTAN
---------------      -------- -------- --------
memory_max_target       FALSE    FALSE    FALSE

SQL> alter system set memory_max_target=2000m scope=spfile;
System altered.

SQL> startup force
ORACLE instance started.
Total System Global Area 1000189952 bytes
Fixed Size                  1337492 bytes
Variable Size             708839276 bytes
Database Buffers          285212672 bytes
Redo Buffers                4800512 bytes
Database mounted.
Database opened.

Now we can change the value of parameter Memory_target.

SQL> alter system set memory_target=1600m;
System altered.

Disable MEMORY_TARGET

We can disable automatic SGA and PGA tuning by setting MEMORY_TARGET parameter value to 0.

SQL> alter system set memory_target=0;
System altered.

SQL> show parameter target
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------
archive_lag_target                   integer     0
db_flashback_retention_target        integer     1440
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
memory_max_target                    big integer 2000M
memory_target                        big integer 0
pga_aggregate_target                 big integer 944M
sga_target                           big integer 656M

Now, Oracle will only tune SGA automatically and we have to tune PGA manually.