This content has been marked as final. Show 8 replies
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.
Edited by: orausern on May 24, 2009 7:50 AM
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 .
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).
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?
My buffer cache hit ratio is going below 30% when I run certain large queries, where as it should be 90% and aboveSays 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
*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
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...
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
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.