This discussion is archived
8 Replies Latest reply: May 25, 2009 6:34 AM by 26741 RSS

Buffer Cache hit ratio and performance problem

600574 Newbie
Currently Being Moderated
Hi,

I have some performance problem in my application, I think it's due to swapping and oversized buffer cache.

My buffer cache hit ratio is going below 30% when I run certain large queries, where as it should be 90% and above, . I have arround 3.7 GB SGA and buffer cache 1.84 GB, shared pool is 1.17 GB, library cache is 111 MB. parse to execute ratio is 38%.

DB_BLOCK_BUFFERS value is 0.

What should be the correct size of the buffer cache for a SGA size 3.7 GB. This DB is running on UNIX and on oracle 10G.

Whats the relationship between oversized buffer cache swap file. How can oversize buffer cache results in swapping. please provide a link so I could read and understand more about this.

Thank you
  • 1. Re: Buffer Cache hit ratio and performance problem
    orausern Explorer
    Currently Being Moderated
    I feel that , if there is performance problem in app, you should analyze it in isolation? - I mean which sqls are problematic and whether something can be done to make them more efficient- that may be the direct way to approach the issue instead of trying to resolve through re-configuring the sga etc.

    Thanks

    Edited by: orausern on May 24, 2009 7:50 AM
  • 2. Re: Buffer Cache hit ratio and performance problem
    600574 Newbie
    Currently Being Moderated
    Hi,

    There are more DB's in the same server. That's why I think due to swapping there is performance degrade. I have tested the application on a different server as standalone and everything was fine, so I don't think there is a problem with SQL. I have got enough memory on my servers .

    Thank you
  • 3. Re: Buffer Cache hit ratio and performance problem
    26741 Oracle ACE
    Currently Being Moderated
    My buffer cache hit ratio is going below 30% when I run certain large queries, where as it should be 90% and above, ...
    That isn't necessarily true. Actually, when run large queries the BCHR may well go down. Particularly if you have FullTableScans that are not direct reads.
    Imagine doing a Hash Join with FullTableScans of 2 tables of 1GB each. Then imagine 2 or 3 concurrent queries running.
    What should be the correct size of the buffer cache for a SGA size 3.7 GB.
    There's no "correct size". A large buffer cache can be helpful. A small buffer cache would be detrimental. But "large" and "small" are relative -- relative to the actual sizes of your queries and the nature of your I/Os.
    Oracle does build in a Buffer Cache Advisory which you might want to review (it is available in the 10g Database Control and can also be queried directly in v$DB_CACHE_ADVICE.

    Swapping isn't directly related to the size of the Buffer Cache. The Buffer Cache is part of the SGA. The OS only sees the Shared Memory area-- which may be comprised of multiple memory segments obtained by oracle. If the physical RAM is insufficent for the Shared Memory + Code for all running programs + memory for each server process / PGA then some processes might be swapped out.

    We don't know what your total RAM is. But in most current implementations -- say a server with 8GB or more, a 3.7GB SGA should not necessarily result in swapping (unless you have some very large processes or very very many processes).
  • 4. Re: Buffer Cache hit ratio and performance problem
    orausern Explorer
    Currently Being Moderated
    Do you have an AWR for this? there are OS related stats in it which can give a clue...also what are the wait events in AWR for this activity?
  • 5. Re: Buffer Cache hit ratio and performance problem
    701909 Newbie
    Currently Being Moderated
    My buffer cache hit ratio is going below 30% when I run certain large queries, where as it should be 90% and above
    Says who?

    The data buiffer hit ratio has limited value see why: http://www.dba-oracle.com/t_buffer_cache_hit_ratio_value.htm
    What should be the correct size of the buffer cache for a SGA size 3.7 GB.
    42
    Whats the relationship between oversized buffer cache swap file.
    Inversely porportional
  • 6. Re: Buffer Cache hit ratio and performance problem
    iloveoracle Explorer
    Currently Being Moderated
    *0 is the default value for DB_BLOCK_BUFFERS* . It dosents seems like a problem at all.
    I think that you must tune your application ...please cheack if your application is using bind
    variable or not if the application is not using bind variables ..rewrite your sql query to use bind
    variables.

    If bind variables are not used it will be causing hard parsing which in turn is flushing off
    your shared pool and database buffer cache...

    and alternately if you not want to rewrite the sql query you can use the
    cursor_sharing parameter of the Database. Which i will not recommend to do
    it might affect the intelligence of the optoimizser to determine the optimal
    execution path of the SQL Query

    hope this may help...
  • 7. Re: Buffer Cache hit ratio and performance problem
    600574 Newbie
    Currently Being Moderated
    Hi,

    I was monitoring my database i found memory utilization is 47%. average value is 46% . Swap utilization is 22%. Are these values good on production system? is it that the memory utilization % should be much higher than the average 47% on my DB.

    I am running the DB on automatic memory management

    Thank you
  • 8. Re: Buffer Cache hit ratio and performance problem
    26741 Oracle ACE
    Currently Being Moderated
    47% of what ? What is the total physical RAM ?
    How much of it goes to FileSystemBuffers ?

    How is swap implemented ? Are processes being swapped ? What are the rates of page-in and page-out ?


    Talk to your Unix administrator .

    Generally it shouldn't matter unless you have processes actually being swapped. Or have a very "high" rate of paging ("high" is relative).

    Concentrate on your database performance. Don't look at memory ratios or buffer hit ratios. Look at throughput and response times.

Legend

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