Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3012
  • Last Modified:

How to Increase the size of the SGA ?

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
titanium0203
Asked:
titanium0203
  • 8
  • 6
  • 6
1 Solution
 
slightwv (䄆 Netminder) Commented:
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
 
MrNedCommented:
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
 
titanium0203Author Commented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 
slightwv (䄆 Netminder) Commented:
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
 
MrNedCommented:
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
 
titanium0203Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
titanium0203Author Commented:
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
 
MrNedCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
titanium0203Author Commented:
Hey All,

Why Free SGA Memory Available  = 0, should I increase it? and how can I increase the value?
0
 
slightwv (䄆 Netminder) Commented:
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
 
titanium0203Author Commented:
Hey All,

Kindly please recommend me how to do the setting on this oracle? I'm not a dba.
0
 
MrNedCommented:
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
 
titanium0203Author Commented:
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
 
MrNedCommented:
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
 
titanium0203Author Commented:
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
 
MrNedCommented:
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
 
titanium0203Author Commented:
Hi MrNed,

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

Thanks
WinTaskManager.bmp
0
 
slightwv (䄆 Netminder) Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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.

  • 8
  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now