Posted on 2011-02-15
Medium Priority
Last Modified: 2012-05-11
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.
Question by:hussainkhan22
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34898648
We've answered this before.  It's a parameter.

See your previous question:

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.

Author Comment

ID: 34898765
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
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34898818
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.
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 34901968
>> 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.


Author Comment

ID: 34902468
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
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34902751
>>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.
LVL 15

Accepted Solution

Aaron Shilo earned 2000 total points
ID: 34933450

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.

LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34934425
>>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.

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I. Introduction There's an interesting discussion going on now in an Experts Exchange Group — Attachments with no extension (http://www.experts-exchange.com/discussions/210281/Attachments-with-no-extension.html). This reminded me of questions tha…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Suggested Courses
Course of the Month9 days, 11 hours left to enroll

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question