SGA & PGA

How do we increase SGA & PGA size for oracle 11g database on sunsolaris using spfile. I really appreciate your help. Thanks in advance for your time and help.
hussainkhan22Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Aaron ShiloConnect With a Mentor Chief Database ArchitectCommented:
hi

if your end goal is to release RAM then you must use the _MEMORY_TARGET parameter.
changing all the SGA PGA parameters wount do much for you.

alter system set memory_target = 1025mb scope=spfile.

enjoy
0
 
slightwv (䄆 Netminder) Commented:
We've answered this before.  It's a parameter.

See your previous question:
http://www.experts-exchange.com/Database/Oracle/Q_26752569.html

Pick the size for the parameters in question and make them larger.

Are you using Automatic Memory Management?  If so, it's just a couple of parameters.
0
 
hussainkhan22Author Commented:
Customer asking to increase SGA to 4g and PGA to 1g. So just issuing
SQL>Alter system set sga_max_size=4g scope=spfile;
SQL> Alter system set pga_aggregate_target=1G scope=both;

Is this fine or else do I need to do anything else also. please let me know.

SQL> show paramater sga
lock_sga        boolean     false
pre_page_sga     boolean     false
sga_max_size    big integer      1808M
sga_target         big integer     1808M

SQL> show parameter pga
pga_aggregate_target      big integer      50m
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
slightwv (䄆 Netminder) Commented:
You need to make sure the serer has enough free memory to support these changes.  You don't want Oracle swapping to disk.

>>Is this fine or else do I need to do anything else also.

If that is what you were asked to do, then that is correct.

You will need to bounce the database for the sga changes to take effect.

You might look at changing the sga_target as well.
0
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
>> Customer asking to increase SGA to 4g and PGA to 1g. So just issuing
I would suggest you to do littlebit more research on this figure.
It may or may not give you benefit.
You can just run Awr report or any advisor (read license terms as well) to see what should be the SGA size.
Do not make SGA very huge or low. It should be necessary and sufficient.

0
 
hussainkhan22Author Commented:
How can I check whether server has enough memory or not. Do I need to change sga target also and how much should I specify for sga target as customer only mentioned I need to increase sga to 4 gb, he didn't mentioned anything about sga target or sga max size
0
 
slightwv (䄆 Netminder) Commented:
>>How can I check whether server has enough memory or not.

I believe it is the prstat command on Solaris.  You need to see how much free memory is available.

>>customer only mentioned I need to increase sga to 4 gb,

If you aren't aware of Automatic Memory Management (AMM) and how it works/what it does, do what the customer asks.  If you want to learn about AMM, Google.  There are tons of links on it for the various versions of Oracle.
0
 
slightwv (䄆 Netminder) Commented:
>>goal is to release RAM

That is not the question.  The question is how to INCREASE the SGA and PGA values.

>>you must use the _MEMORY_TARGET parameter.

That depends on how the memory parameters are currently set up.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.