DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Shared Pool Size - Correctly Set?

  1. #1
    Join Date
    Aug 2002
    I just run this query to check if the shared pool size is correctly set.

    select to_number(v$parameter.value) value, v$sgastat.bytes,
    (v$sgastat.bytes/v$parameter.value) * 100 "Percent Free"
    from v$sgastat, v$parameter
    where v$sgastat.name = 'free memory'
    and v$parameter.name = 'shared_pool_size'


    VALUE BYTES Percent Free
    ---------- ---------- ------------
    18000000 5509728 30.6096
    18000000 1003520 5.57511111

    There are no performance issues currently. Should I leave well alone or do you think the shared_pool_size should be increased.

  2. #2
    Join Date
    Mar 2001
    I think a more important thing to look at is if your hit ratio is lower than 95% (that's what Oracle recommends as being the threshold) rather than size. Check both buffer cache and library cache. If your 95% or above then you don't need to do a thing (for now that is until the app gets bigger).

    Check this note out which may help with this type of thing:


    Oh one more thing. Look at the compatibility parameter in your init.ora. If it's set to a base version of Oracle and you have a patched version of Oracle running then you may want to change that compatibility parameter to match your running version so that the Oracle kernel will take advantage of the performance things implemented in the patches. Look at this ONLY if you are having a problem. As you said, you have no problems now so best to leave it for now.


    [Edited by ramaral on 08-15-2002 at 11:27 AM]

  3. #3
    Join Date
    Aug 2002
    Hi Ramaral

    Thanks for your prompt reply. Library cache hit ratio is 99.25%, buffer hit ratio 94.88% so like you said, I'll leave well alone.

    The compatability parameter is actually set to a base version and we do have patched versions. Did ask Oracle about this and they advised to leave it as it is.

    Well thanks for your advise.


  4. #4
    Join Date
    Apr 2001

    Are you in Essex UK ?
    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

Posting Permissions

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

Click Here to Expand Forum to Full Width