Oracle SGA/Memory settings

Posted on 2004-11-04
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
    LVL 9

    Assisted Solution

    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

    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 47

    Accepted Solution

    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.
    LVL 9

    Expert Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    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.

    779 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

    17 Experts available now in Live!

    Get 1:1 Help Now