Posted on 2011-02-15
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.
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

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 500 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

Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
It’s 2016. Password authentication should be dead — or at least close to dying. But, unfortunately, it has not traversed Quagga stage yet. Using password authentication is like laundering hotel guest linens with a washboard — it’s Passé.
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.
Connecting to an Amazon Linux EC2 Instance from Windows Using PuTTY.

717 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