Buffer Cache Hit Question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Buffer Cache Hit Question

  1. #1
    Join Date
    May 2000
    Posts
    50
    Hello all,

    I have been experiencing this strange problem. I am closely monitoring one of my B2B DB used by WebMethods. It is a Oracle 8.1.6.0.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?

  2. #2
    Join Date
    Nov 2000
    Posts
    344
    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.

    -John


  3. #3
    Join Date
    May 2000
    Posts
    50
    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.

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