Increase the Memory target parameter in Oracle

Steps for setting the Memory target parameter in Oracle

Memory_target = Sga_target + PGA_target.

Oracle introduces the Memory target, it automatically manages the SGA + PGA. We only need to set the MEMORY_TARGET parameter. We do not need to set SGA_TARGET and PGA_TARGET.

Note: If we set it with SGA_TARGET or PGA_TARGET then it should be less value than MEMORY_TARGET and its values should be considered as the minimum value, its values automatically do not go less than that value.

In practice, we should make the SGA_TARGET and PGA_TARGET to 0 values, when we set the MEMORY_TARGET parameter.

For Setting MEMORY_TARGET, we also set the MEMORY_MAX_TARGET parameter:

show parameter memory_target;

alter system set memory_max_target=5G;
alter system set memory_target = 5G;

--Restart the Oracle Database for take parameter effects:
Shutdown immediate;
Startup;

It is a general practice, to keep the Memory_MAX_TARGET larger than MEMORY_TARGET. In future, if we need to increase the MEMORY_TARGET parameter then we can able to change it dynamically without restarting the Oracle Database.

alter system set memory_max_target=7G;
alter system set memory_target = 5G;
--Restart the Oracle Database without restart.

--In future, we can increase the memory_target to 7G without restart the database.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.