Oracle SGA/Memory settings

Posted on 2004-11-04
Medium Priority
Last Modified: 2008-01-16
I have Oracle running on an intel box as:

dual xeon 2.4,
2gb of ram.
c: 4gb - 150MEG free
d: 46gb - 23.7gb free
(all scsi raid 5)

The SGA/Memory settings are as follows but I was wondering if these are too low and could I safely increase them and if so to what?

Shared Pool: 136 MB
Buffer Cache: 80 MB
Large Pool: 1024 KB
Java Pool: 32 MB

TOTAL SGA: 169.76974

Max Concurrent Users: 2950  --- this is what it's set to but we only have about 150 users in reality, though connections are made via mts so perhaps more than 150 connections opened?

Sort Area Size: 512 KB

Question by:richkeegan
  • 2

Assisted Solution

konektor earned 240 total points
ID: 12491221
never let all applications running on server exceed 2 gb. system may probable take 500mb, so if there are no running application on the server u have abt 1GB to memory for all your instances (next 500mb is reserve for applications, which are not permanently running, but time to time may be...).
check also size of PGA (u can do it in enterprise manager)
if u exceed the RAM and system begin to swap, it's the end of database performance.

Author Comment

ID: 12491731
I can't see a pga box for this server ? I can see it on my 9.2 server. If I select the sql plus:

NAME                                            VALUE
---------------------------------------- ------------
aggregate PGA auto target                           0
global memory bound                                 0
total expected memory                               0
total PGA inuse                            11,892,736
total PGA allocated                        17,606,656
maximum PGA allocated                      17,606,656
total PGA used for auto workareas                   0
maximum PGA used for auto workareas                 0
total PGA used for manual workareas                 0
maximum PGA used for manual workareas               0
estimated PGA memory for optimal                    0

NAME                                            VALUE
---------------------------------------- ------------
maximum PGA memory for optimal                      0
estimated PGA memory for one-pass                   0
maximum PGA memory for one-pass                     0
LVL 48

Accepted Solution

schwertner earned 260 total points
ID: 12492429
this SGA  can not be 167.76 MB. Something is wrong. I calculate it to 250 MB.
if it really is 167 MB then it is too small for the capacity of your system.
In this case increase the Buffer Cache (to 150 MB) and the Shared Pool (150 MB).
The advisors in the OEM will give you after some period the usage figures for these settings.
Try to keep about 500 MB for SGA. Every connection needs 2-5 MB, so the RAM will be enough.

Expert Comment

ID: 12492553
SELECT value FROM v$parameter WHERE name = 'pga_aggregate_target'; -- size of PGA

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

840 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