I have been experiencing this strange problem. I am closely monitoring one of my B2B DB used by WebMethods. It is a Oracle 18.104.22.168.0 version. On Friday a user complained of poor performance and then I started to notice it. This DB is being used since Feb '01 but having this problem only since Friday. I noticed that the Buffer Cache Hit Ratio is fluctuating from 83.33% to -66.66%. I have looked to several reference materials including searching the forums in this site. I increased the db_block_buffers from 5000 to 7500 and bounced the instance. No big difference. Queried the x$bh table just to know that out of the 7500 blocks (8K Block Size) only some 800 blocks are used and others are free. I then decreased DBB to 3000 since more than 6000 are free. But no difference. Also queried x$kcbcbh and x$kcbrbh to see if increasing or decreasing the DBB would make any difference. Can someone tell me what other things I can look into to make this scenario better?
Look in v$SQL to find the queries that are consuming the most
resources. Then do an explain plan on them and see if it looks ok. You want to avoid full table scans on big tables. Its OK to have them on small tables. If you have lots of full table scans on the same small tables you might want do
'alter table x cache ' for them. This will keep them in the
buffer cache longer.
The biggest improvements can be found in tuning your SQL
statements so that they use indexes. You may need to add
an index or 2.
Thank you John. In fact, that's exactly what I am doing right now. I should have done this first even before tuning the DB parameters instead of doing the other way.
Click Here to Expand Forum to Full Width