Friday, 27 September 2013

How To Enable Automatic Memory Management in Oracle 11G Database

How To Enable Automatic Memory Management in Oracle 11G Database


This is the new feature introduce in oracle 11g database for automatically managing the SGA and PGA
which is known as automatic memory management(AMM) .prior to oracle 11g database there is Automatich shared memory management (ASMM) in oracle 10g database which only manage SGA automatically .

For enabling and using there are two parameters in oracle 11g database which are:-

MEMORY_MAX_SIZE
MEMORY_TARGET


in which you can set MEMORY_TARGET upto the value of MEMORY_MAX_SIZE.

For enabling AMM

1. set memory_max_size to some value say 2gb

SQL>alter system set memory_max_size=2g scope=spfile;

2. set memory_target upto the value of memory_max_size say 1600m

SQL> alter system set memory_target=1600m scope=spfile;
3. set sga and pga equals to 0

SQL> alter system set pga_aggregate_target=0 scope=spfile;
SQL> alter system sga_max_size=0 scope=spfile;
4. now shutdown database
shutdown immediate
startup

now you don't need to set sga or pga manually now its oracle responsibility to set it according to the requirement

For Disabling AMM

1. set memory_target=0
SQL> alter system set memory_target=0 scope=spfile;
2. set sga and pga to some value say sga_aggregate_target to 1700m and pga_target =300m SQL> alter system sga_max_size=1700m scope=spfile;
SQL> alter system set pga_aggregate_target=300m scope=spfile;


3. now restart the database
shutdown immediate;
startup

now you have to manage and set PGA manually SGA is automatically set and all the caches are automatically set by oracle

1 comment:

  1. I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in Oracle 11g, kindly contact us http://www.maxmunus.com/contact
    MaxMunus Offer World Class Virtual Instructor led training on Oracle 11g. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
    For Demo Contact us:
    Name : Arunkumar U
    Email : arun@maxmunus.com
    Skype id: training_maxmunus
    Contact No.-+91-9738507310
    Company Website –http://www.maxmunus.com


    ReplyDelete