This discussion is archived
9 Replies Latest reply: Sep 13, 2012 11:42 AM by Aman.... RSS

Oracle Memory Parameter calculation

961966 Newbie
Currently Being Moderated
Hi experts,

I have installed SAP on Oracle 10G 10.2.0.4 patch level on RHEL5.3
i want to know How can i set the oracle memory parameter? any calculation behind it or other techninque.

server Memory around 32 GB
oracle installed on 16 GB + 16 GB for SAP instance.

because currnety

SAG_MAX_SIZE=3GB
DB_CACHE_SIZE=1 GB
Shared_POOL=500 MB

Due to which Buffer Quality hit ration not more then 74%.

Please guide & thanks in advance.

Regards
Vivek
  • 1. Re: Oracle Memory Parameter calculation
    sb92075 Guru
    Currently Being Moderated
    958963 wrote:
    Hi experts,

    I have installed SAP on Oracle 10G 10.2.0.4 patch level on RHEL5.3
    i want to know How can i set the oracle memory parameter? any calculation behind it or other techninque.

    server Memory around 32 GB
    oracle installed on 16 GB + 16 GB for SAP instance.

    because currnety

    SAG_MAX_SIZE=3GB
    DB_CACHE_SIZE=1 GB
    Shared_POOL=500 MB

    Due to which Buffer Quality hit ration not more then 74%.
    what value would be acceptable to you & WHY that value?



    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 2. Re: Oracle Memory Parameter calculation
    961966 Newbie
    Currently Being Moderated
    i want to increase the hit ratio & increse the server perofrmance.
    Pleae suggest.
  • 3. Re: Oracle Memory Parameter calculation
    sb92075 Guru
    Currently Being Moderated
    958963 wrote:
    i want to increase the hit ratio &
    what value would be acceptable to you & WHY that value?
    increse the server perofrmance.
    post SQL & results that show increase in ratio improves performance.
  • 4. Re: Oracle Memory Parameter calculation
    961966 Newbie
    Currently Being Moderated
    so what should i do ?

    if buffer quality is less; may be accure no issue of performance.?

    OR suggest how can i check my DB is running good performance?

    Edited by: 958963 on Sep 13, 2012 7:48 AM
  • 5. Re: Oracle Memory Parameter calculation
    sb92075 Guru
    Currently Being Moderated
    958963 wrote:
    so what should i do ?
    What should you do about what?

    there is NO relationship between Buffer Cache Hit Ratio & performance.
  • 6. Re: Oracle Memory Parameter calculation
    JohnWatson Guru
    Currently Being Moderated
    You seem to have plenty of RAM, so a reasonable starting point would be to follow the advisors, as displayed in your statspack/AWR reports and in the views:
    v$pga_target_adivce
    v$sga_target_advice
    v$db_cache_advice
    v$java_pool_advice
    v$streams_pool_advice

    I have always wondered why there is no v$large_pool_advice. Clearly there is an advisor or ASMM couldn't work, but as far as I know it has never been made visible.
  • 7. Re: Oracle Memory Parameter calculation
    961966 Newbie
    Currently Being Moderated
    so that what is the use of buffer??
  • 8. Re: Oracle Memory Parameter calculation
    sb92075 Guru
    Currently Being Moderated
    958963 wrote:
    so that what is the use of buffer??
    when all else fails Read The Fine Manual

    http://docs.oracle.com/cd/E11882_01/server.112/e25789/memory.htm#sthref1708
  • 9. Re: Oracle Memory Parameter calculation
    Aman.... Oracle ACE
    Currently Being Moderated
    958963 wrote:
    so what should i do ?

    if buffer quality is less; may be accure no issue of performance.?

    OR suggest how can i check my DB is running good performance?
    The use of buffer cache is to store and provide the data from the memory. This would save eventually the disk reads to happen again and again after the necessary first time. But what you are doing wrong is to merge or correlate both buffer cache with its hit ratio. Ratio is what is it is, a ratio and any given time, it can be anything and is tweaked based on the operations that you perform in the db. This was a good factor when the memory was very costly and having everything in the memory was a good idea-obviously that the db's also were very small. Now, this is not a constraint anymore. So if you have a high ratio, that's not a proof that the entire db is performing very good. It may be a good factor to tell specifically about the buffer cache, which also won't be a correct statement to make as the ratio can be influenced . So use a more better approach like taking a Statspack report of both good and bad times and compare to find where are the deltas and the best, ask your users and check your service request queue. If there isn't anything popping in it up , you and your db are doing a good job.

    Aman....

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points