oracle 11gR2 memory_max_target and memory_target

Understanding memory_max_target and memory_target

First of all if these parameters have got values like 

memory_max_target=0

memory_target=0

This means that you are not using Automatic Memory Management.  

There is a difference in both of these parameter. If they are set that means you are using Automatic Memory Management (AMM). I am giving an example for better understanding, if have have got 64G memory (RAM) on your database server and your systems guy is happy for database to use 20G memory. That means you have 20G RAM to play with.

In this scenario you can set memory_max_target = 20G and the setup can be done with the following commands:

alter system set memory_max_target=20G scope=spfile;
shutdown immediate
startup

By setting memory_max_target=20G you allow your instance to get hold of 20G RAM. At this stage if you set second parameter memory_target=15G that means you are instructing oracle instance to only use 15G out of 20G RAM. If you do not set memory_target parameter then oracle will use 20G or RAM.

IMPORTANT: It is important to note that when memory_max_target=20G and memory_target=15G, the remaining 5G are still occupied by oracle and are not in use, even OS can not use this until you shutdown the instance.

Why we need memory_max_target when we have memory_target?

memory_max_target is the initialization parameter. Where memory_target is a dynamic parameter. Which means that when memory_max_target is set to 20G and memory_target is set to 15G you can adjust memory_target to some different value while instance is up and running by issuing the following command.

alter system set memory_target=17G scope=both;
or 
alter system set memory_target=17G;

Having this option to adjust memory_target dynamically you can find what is the suitable memory target for the particluar database, suppose it is 12G for our example then at next outage or restart set memory_max_target=12G and leave memory_target=12G