Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win



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.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Suggested Courses

604 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