Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2002
    Posts
    1,245

    Unanswered: SGA Recommendation

    I'm cringing as I write this because I fear that I have not done sufficient research. However, I have looked in my Oracle 9i DBA Handbook and my Oracle 9i for Windows Handbook and I have done a quick search on this site using "SGA recommendation". I have also browsed through the orafaq.com site.

    I am looking for a "rule of thumb" recommendation for SGA settings for my Oracle 8.1.6 instance running on a windows 2K server. The server has 2 GB of RAM installed.

    The current settings (willed to me by my predecessor) are:

    Shared Pool: 50 MB
    Buffer Cache: 150 MB
    Large Pool: 600 KB
    Java Pool: 20 MB

    Total SGA: 221 MB

    Given that this is the only instance running on this server and that there are no other applications on the server, I was thinking that these settings seemed awfully low. In addition, the sort area size is currently configured to 64 KB.

    Would anyone care to give me some "rules of thumb" regarding the proper allocation of memory to the various pools?

    I suspect that I may have a problem because I see a high degree of disk activity on this server where the Oracle home is resident, but the overall CPU utilization is low and there does not seem to be an inordinate amount of activity currently on the server (~ 15 users).

    Any guidance you may be willing to provide will be gratefully received.

    Regards,

    hmscott
    Have you hugged your backup today?

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    Show me a statspack report. There are too many unknowns here.
    For all we know, you don't even bind any variables
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    I was afraid of that. There's no simple rule of thumb here?

    Okay. I've seen StatsPack, I've read (a tiny) amount about it. Back to the books some more.

    Regards,

    hmscott
    Have you hugged your backup today?

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    I said POST your statspack results here if you want help.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It depends.
    It depends upon how the application is written.
    If you can identify the packages within the application,
    you could try pinning all of them into the Shared Pool & see how much space they consume.
    It depends upon the transaction rate.
    Typically the higher the transaction rate the large the DB_BLOCK_BUFFER should be.

    HTH & YMMV

    HAND!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482
    The rule on thumb could be:

    - allocate 50% of the available RAM to DB BUFFER CACHE
    - allocate 15% of the available RAM to SHARED POOL
    - for 15 users - SORT_AREA_SIZE would be 512 KB
    - allocate 640 KB for REDO LOG POOL

    Then run the STATSPACK to see how the hit ratios are. Someone created Script to estimate Oracle SGA assuming you have a developement database before hand.


    Hope that helps,

    clio_usa - OCP 8/8i/9i DBA

  7. #7
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    This link will give you some more ideas...

    http://www.ss64.com/orasyntax/initora.html

    The thing to remember is that there is no right solution; as anacedent says, there are too many factors to consider that all impinge on one another in some way.

    With regard to the Pool Size, try the following - if the answers are close to 1 then the pool size is OK:

    Select (sum(pins - reloads)) / sum(pins)
    from V$LIBRARYCACHE;

    Select (sum(gets - getmisses - usage - fixed)) / sum(gets)
    from V$ROWCACHE;
    Last edited by cis_groupie; 07-14-04 at 11:15.
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  8. #8
    Join Date
    Dec 2002
    Posts
    1,245
    Ay!!!! Many thanks to all for your guidance. This will help a lot.

    Regards,

    hmscott

    Quote Originally Posted by cis_groupie
    This link will give you some more ideas...

    http://www.ss64.com/orasyntax/initora.html

    The thing to remember is that there is no right solution; as anacedent says, there are too many factors to consider that all impinge on one another in some way.

    With regard to the Pool Size, try the following - if the answers are close to 1 then the pool size is OK:

    Select (sum(pins - reloads)) / sum(pins)
    from V$LIBRARYCACHE;

    Select (sum(gets - getmisses - usage - fixed)) / sum(gets)
    from V$ROWCACHE;
    Have you hugged your backup today?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •