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
Solved

How to Increase the size of the SGA ?

Posted on 2011-02-23
20
1,881 Views
Last Modified: 2012-05-11
Hey All,

Kindly please help me on this. According to ADDM in Oracle Enterprise Manager, I can
Increase SGA to 1024. My question is if I implement this DB Configuration will the changes take effect on the fly or I still need to restart the oracle services?

Kindly please help.
0
Comment
Question by:titanium0203
  • 8
  • 6
  • 6
20 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34966619
Resizing the SGA requires a bounce of the instance.

Using Automatic Memory Management (AMM) adjusting the individual pools does not.

I'm curious, resize to 1024 what?  G, M, ?
0
 
LVL 7

Expert Comment

by:MrNed
ID: 34966663
In 10g, you can increase the SGA_TARGET parameter dynamically without a restart, but only up to the value of SGA_MAX_SIZE. SGA_MAX_SIZE requires a restart for changes to take effect.

I'm not sure what parameter OEM is referring to though.
0
 

Author Comment

by:titanium0203
ID: 34966804
hi Slightwv & MrNed,

Kindly please check the attachment. This is a print screen from Enterprise Manager 10G.If I click the "Implement" button are there any impacts on the DB?
Do I need to restart any services, fyi this is a production server.

Regards,
titanium0203
Pic.bmp
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34966822
As MrNed posted, sga_target is OK as long as it doesn't exceed sga_max_size.

I would refer to the online docs for the exact references for these parameters.
0
 
LVL 7

Expert Comment

by:MrNed
ID: 34966840
Yes it looks OK and should be safe to do. Although I wouldn't assume increasing it will remove any performance problem you may be experiencing (if any?). Finding and removing the source of User I/Os would be a better solution :)
0
 

Author Comment

by:titanium0203
ID: 34966864
Hi Slightwv & MrNed,

Upon checking, our sga_max_size is the same as sga_target, What will happen is
I just increase the sga_target. Kindly please recommend me any best solution to this problems.

Regards
titanium0203
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34966876
Only if you think it will increase performance, remember these are only advisers suggesting the changes.  The suggested changes to not ensure performance.

If the memory is available on the server and when you can take the down time, increase sga_max_size and bounce the database.
0
 

Author Comment

by:titanium0203
ID: 34966886
Hey All,

All sga infos are as below :

Fixed SGA Size                                        1,250,428.00      No
Redo Buffers                                        7,135,232.00      No
Buffer Cache Size                            473,956,352.00      Yes
Shared Pool Size                            117,440,512.00      Yes
Large Pool Size                                4,194,304.00      Yes
Java Pool Size                                        4,194,304.00      Yes
Streams Pool Size                                4,194,304.00      Yes
Granule Size                                        4,194,304.00      No
Maximum SGA Size                            612,368,384.00      No
Startup overhead in Shared Pool      46,137,344.00      No
Free SGA Memory Available                              0.00      

kindly please advice on this.

Regards,
titanium0203
0
 
LVL 7

Expert Comment

by:MrNed
ID: 34966899
I would not increase the sga_max_size without a good reason. It sounds like you are just going by what the advisor has recommended, and it will pretty much ALWAYS have a recommendation about increasing SGA. After all, if you have a 1TB database why not allocate 1TB of SGA to cache it all!

You need to consider what else on that hardware uses the memory if any is even available.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34966905
What produced that output? Is that in bytes?

Looks like sga_max_size is about 600M. A jump to 1G seems appropriate is the server has the memory free.

As for the individual pool sizes, I suggest you read the online documentation about Automatic Memory Management (AMM). If it works for your database, these need to be 0 (zero) in the spfile and you let Oracle manage them.
0
 

Author Comment

by:titanium0203
ID: 34966963
Hey All,

Why Free SGA Memory Available  = 0, should I increase it? and how can I increase the value?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34966976
0 is not a bad thing.  It is only a bad thing if performance suffers.

How to increase it has already been posted: sga_max_size but only if the server has the free memory to support it.

You NEVER want memory swapping to disk.
0
 

Author Comment

by:titanium0203
ID: 34967065
Hey All,

Kindly please recommend me how to do the setting on this oracle? I'm not a dba.
0
 
LVL 7

Expert Comment

by:MrNed
ID: 34967087
Then ask your DBA to do it :)

Seriously though - you need to confirm that you have enough free memory before doing anything. I would not feel comfortable giving you a step by step instruction that could potentially bring a production system to its knees.
0
 

Author Comment

by:titanium0203
ID: 34967132
Hi MrNed,

Ok, I'm the DBA. could you please advice and guide me on this process?
How can I check if my server got enough memory or not?
If possible give me the list and I'll do the checking and post the result.
0
 
LVL 7

Expert Comment

by:MrNed
ID: 34967158
Well first give me some details:
- What is your Oracle Server version (EM could be a diff version)
- What OS are you using

You will need an outage to complete this, but you can make the required changes beforehand and let the next available restart put it into effect.
0
 

Author Comment

by:titanium0203
ID: 34967187
Hi MrNed,

Check Below :

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
"CORE      10.2.0.1.0      Production"
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
0
 
LVL 7

Expert Comment

by:MrNed
ID: 34967249
I assume that means you are running Windows Server? Might be easiest if you post a screenshot of Task Manger's Performance tab.

Are there any other major apps running on this server that might be intermittently using lots of memory? Even if it's free now, it might use 100% of memory overnight.
0
 

Author Comment

by:titanium0203
ID: 34967665
Hi MrNed,

Kindly please check attached file. Nope, this machine is dedicated for Oracle DB.

Thanks
WinTaskManager.bmp
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 125 total points
ID: 34969771
You want the good news or the not-so-good news?

The good news is:  You have plenty of free memory and I would actually increase sga_max_size to more than 1G.

The not-so-good news is:  After having our talented sys admin look at your task manger, he guesses you are running Windows 2003 32Bit.

With the 32Bit OS I would not increase the SGA to more than 1.5G.  Every time I approaches 2 Gig on Windwos 32Bit, Oracle misbehaved.

With that much memory on that server, I would STRONGLY encourage you to upgrade to a 64Bit OS and move Oracle to 64Bit.  Then you can take full advantage of that memory.

As it stands, from a sql prompt:
alter system set sga_max_size=1.5G scope=spfile;
alter system set sga_target=1.5G scope=spfile;

then bounce the database.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

860 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