Solved

How to Increase the size of the SGA ?

Posted on 2011-02-23
20
1,805 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)
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
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
Comment Utility
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)
Comment Utility
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
Comment Utility
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
Comment Utility
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)
Comment Utility
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
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.

 

Author Comment

by:titanium0203
Comment Utility
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)
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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.

Join & Write a Comment

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

762 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

9 Experts available now in Live!

Get 1:1 Help Now