Solved

SGA & PGA

Posted on 2011-02-15
8
1,793 Views
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.
0
Comment
Question by:hussainkhan22
8 Comments
 
LVL 76

Expert Comment

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

Author Comment

by:hussainkhan22
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
0
 
LVL 76

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.
0
 
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.

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:hussainkhan22
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
0
 
LVL 76

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.
0
 
LVL 15

Accepted Solution

by:
Aaron Shilo earned 500 total points
ID: 34933450
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
 
LVL 76

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.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now