• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 508
  • Last Modified:

Increase the SGA size of the database

Hi ,

  I want to know the steps to increase the memory of my database to 8GB.?

  My current database SGA configuration is :

Total System Global Area 4294967296 bytes
Fixed Size                  2158768 bytes
Variable Size            2730364752 bytes
Database Buffers         1560281088 bytes
Redo Buffers                2162688 bytes
  • 4
  • 3
  • 2
1 Solution
slightwv (䄆 Netminder) Commented:
What part of 'memory'?

Oracle memory is made up of many different pools and pieces.

Are you using Automatic Memory Management?  If so, what database version (they changed it betwen 10g and 11g).

Here is the 11g docs:

2-Day DBA link:
slightwv (䄆 Netminder) Commented:
"JUST" the SGA:

alter system set sga_max_size=8G scope=spfile;

then bounce the database.
Mark GeerlingsDatabase AdministratorCommented:
Can you give us some information including:
1. How much memory does the server have?  
2. Is this the only Oracle database running on the server?
3. What do you expect the larger SGA to do for you?
4. What kind of application do you have (transaction-processing, data warehouse, hybrid, etc.)?
5. How many user sessions are active at a time in the database?
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.

joe_echavarriaDatabase AdministratorAuthor Commented:
Here is the information  :

  - 24 GB, Windows 2003 R2 Enterprise Edition Enterprise 64x Edition. x5550 2.67 GHZ, 16 cpu.
  -  Yes this is the only database running on the server.
  - I expect that having  a larger SGA will help with my performance problems.  Is a 1 TB database and the current SGA size is 4 GB.   The application support team recommend me to increase the memory to 8 GB.
  - I will say is Hybrid.  Is a web based application that manage documents.
  - Is a web based application, I will say more than 100 people can be using the application at a time.

 Do you think that increasing the size of the memory will help me  with the performance ?

 What you think ?
slightwv (䄆 Netminder) Commented:
>>I expect that having  a larger SGA will help with my performance problems
>>Do you think that increasing the size of the memory will help me  with the performance ?

Where is your proof of this?

How do you know disk i/o isn't causing the performance issues?

There is no silver bullet for performance tuning.  People make entire careers out of nothing but this.

You must have some idea where the performance issues are or at least what the symptoms are before you start tuning.

That said:  If you have the memory free, I'm sure Oracle will appreciate it but at some point in the tuning process 'more memory' will no longer increase performance.
Mark GeerlingsDatabase AdministratorCommented:
OK, with a 64-bit O/S, 24 GB of RAM, and just one Orracle database, making the SGA 8GB instead of 4GB may help performance.  But, this depends on the application (including: is it well-designed and well-indexed; do the users often ask for information that was recently queried or processed by another user or program, etc.) and it may depend on the disk system also.

Where is the Windows swapfile?  It should *NOT* be on a RAID5 disk.  Does the Windows swapfile get used much now?  If you do make the SGA larger make sure that you monitor the Windows swapfile to make sure that it does not become frequently-used.

Also, where is the Oracle temp tablespace?  I hope that is not on a RAID5 disk!  If it is, that *WILL* hurt performance!
joe_echavarriaDatabase AdministratorAuthor Commented:
All the oracel datafiles are located in the same disk and the disk is on a SAN using RAID 50

And the windows swapfile is on drive c:\ a local disk. -

What u think ?
slightwv (䄆 Netminder) Commented:
>>What u think ?

What do we think about what?  This question was how to increase you 'memory' to 8G.

The followup questions asked by the Experts, including myself, were just to point out that Performance tuning is MUCH MORE than just memory.

We cannot tune your system for you.  Tuning takes an ongoing effort with the necessary tools.  These 'tool's consist of gathering the correct information then the knowledge to process that information to make sound decisions.

This is a little to much to ask in a Q&A site like Experts-Exchange.  This is why someone can make a career out of tuning.

>>I hope that is not on a RAID5 disk!

There are times when I have run ALL oracle files on RAID 5 and sacrificed performance.  The main reason: I didn't trust my Sys Admin team to be able to properly recover a mirror in the event of a failed disk.  I trusted them to hot-swap a drive...
Mark GeerlingsDatabase AdministratorCommented:
I would not want the Oracle temp tablespace on a RAID5 (or RAID50) device unless the SAN has enough write cache to hold the entire temp tablespace, plus more.  I prefer to put the temp tablespace in a RAMdisk.  On Windows you need third-party software for this.  (We used a product from www.superspeed.com that worked really well and had the best customer support of any software product I've purchased - and we rarely needed support for it.)  Linux supports RAMdisks natively, so we don't need third-party software now to have our temp tablespace in a RAMdisk.  (We do have to use some non-default boot options in Linux to get RAMdisks of 500MB )

What does the Windows Performance Monitor show you now about: memory and swap file usage; CPU usage and disk I/O?  That tool (the Windows Performance Monitor) is the tool I missed the most when we moved our database from Windows to Linux some years ago.  I appreciate the stability of Linux, but it doesn't have a tool that comes close to Windows Performance Monitor for being able to display: memory and swap file usage; CPU usage and disk I/O all in real time in a single GUI screen.
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

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now