DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: buffer cache hit ratios

  1. #1
    Join Date
    Oct 2000
    Posts
    449
    Hello:


    I was calculating ratios and buffer hit cache ratio was at 65%. We were running queries on the system, more than normal. Increased the db blocks to 20000 from 10000 and increased shared pool to 256MB from 128MB. Bounced the database. Started our queries again. Now the buffer hit cache ratio went down as far as 55% and is hovering around 60%. Why am I not seeing better ratios than before though I have doubled.

    Are there any other parameters, I have to work on when I change these two..

    Thanks, ST2000

  2. #2
    Join Date
    Oct 2000
    Posts
    449
    Almost for 3 hrs it is behaving in the same way. Sometimes it has gone down to high 40's and has come back up.. Some queries are also stopped to relax the system.. What's wrong?

    Thanks, ST2000

  3. #3
    Join Date
    May 2002
    Posts
    2,645
    buffer hit cache ratio - maybe because your users aren't finding the blocks they need in memory, except for about 60% of the time? Increasing size is but one way to improve the ratio.

    db_block_what? You can change db_block_size all you want after the database is created, but it won't do anything. That value is set in concrete once you've created the database. If you mean db_block_buffers, then size of the cache is db_block_size times db_block_buffers.

    Increases to the database buffer cache are not a direct relationship. Doubling the cache does not mean your ratio doubles, so you have something else going on other than the size of the cache given that your ratio is still the same. You increase the cache size until there isn't an increase in ratio performance, and then you start on other factors.

    What are your users doing? Flushing buffers out of memory? You can use multiple buffer pools, cache tables in memory, and use indexes.

  4. #4
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Hi.

    1) Increasing the size of the shared pool parameter will not increase the buffer cache hit ratio, but may decrease the shared pool performance depending on the style of application. I read an article by Thomas Kyte that claimed you shouldn't have a shared pool size greater than 75M if bind variables are not used by your application. I hate rules of thumb like this but TK is the man!

    2) Bouncing your instance clears the buffer cache. Depending on the usage of your instance it may take a long time to repopulate the cache, hence the low hit ratio.

    3) Alot of the top guys frown on the use of hit ratios during tuning, suggesting it is quite a dated method. A better method to consider is the YAPP-Method which relies on monitoring wait states. These links will get you started:

    http://www.oraperf.com/links/get_url.php?id=13
    http://www.oraperf.com/index.html
    http://www.oracle-base.com/Articles/8i/Statspack8i.asp

    The logic is that bad performance is caused when the kernel is waiting for resources. The best way to identify these resource bottlenecks is to look at wait states in the kernel. Eliminating/reducing these wait states is the most accurate way to tune the instance. Once you've eliminated/reduced as many wait states as you can you've tuned the instance as best you can with the resources available.

    Cheers
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

  5. #5
    Join Date
    May 2002
    Posts
    193
    Originally posted by TimHall
    Hi.

    1) Increasing the size of the shared pool parameter will not increase the buffer cache hit ratio, but may decrease the shared pool performance depending on the style of application. I read an article by Thomas Kyte that claimed you shouldn't have a shared pool size greater than 75M if bind variables are not used by your application. I hate rules of thumb like this but TK is the man!

    2) Bouncing your instance clears the buffer cache. Depending on the usage of your instance it may take a long time to repopulate the cache, hence the low hit ratio.

    3) Alot of the top guys frown on the use of hit ratios during tuning, suggesting it is quite a dated method. A better method to consider is the YAPP-Method which relies on monitoring wait states. These links will get you started:

    http://www.oraperf.com/links/get_url.php?id=13
    http://www.oraperf.com/index.html
    http://www.oracle-base.com/Articles/8i/Statspack8i.asp

    The logic is that bad performance is caused when the kernel is waiting for resources. The best way to identify these resource bottlenecks is to look at wait states in the kernel. Eliminating/reducing these wait states is the most accurate way to tune the instance. Once you've eliminated/reduced as many wait states as you can you've tuned the instance as best you can with the resources available.

    Cheers




    Sir,

    I have found your suggestions rightly pointing out to the solutions for the same problem that I have.. Sir, I shall be happy if you could come again on the term 'Bouncing your instance clears the buffer Cache' ... Can u come again on this term and more clearly...


    Regards,,...

    K.Diwakar

  6. #6
    Join Date
    May 2002
    Location
    California, USA
    Posts
    175

    Cool

    It means that all data blocks in the data buffer cache will be flushed since the RAM is violative.

    After the DB mounting and opening the data buffer cache has to "warm up" - read blocks from the disks and fill the MRU and LRU lists, etc.. So that will take time and in the begining your buffer cache hit ratio will be low as you are going to make more reads and have less hits.

    Hope that helps,

    clio_usa
    OCP DBA 8/8i/9i
    visit our Web Site

  7. #7
    Join Date
    May 2002
    Posts
    193
    Originally posted by clio_usa
    It means that all data blocks in the data buffer cache will be flushed since the RAM is violative.

    After the DB mounting and opening the data buffer cache has to "warm up" - read blocks from the disks and fill the MRU and LRU lists, etc.. So that will take time and in the begining your buffer cache hit ratio will be low as you are going to make more reads and have less hits.

    Hope that helps,

    clio_usa
    OCP DBA 8/8i/9i
    visit our Web Site

    From what you say, should I infer that 'Bouncing off the instance' literally means restarting the database??

    Regards,,..


    K.Diwakar

  8. #8
    Join Date
    May 2002
    Location
    California, USA
    Posts
    175

    Cool

    Yes, that is what does mean - restart Oracle database instance.

    Hope that helps,

    clio_usa
    OCP DBA 8/8i/9i
    visit our Web Site

  9. #9
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Yep. Bouncing the instance means doing a shutdown then startup of the instance. The instance goes down then up, like a ball when you bounce it, hence the term.

    You should try and reduce the number of times you bounce the instance. Thomas Kyte reckons the only time the instance should be bounced is if the server needs rebooting, or you need to change a parameter that can't be altered using the ALTER SYSTEM command. This means get rid of cold backups.

    Cheers
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

  10. #10
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by TimHall
    Thomas Kyte reckons the only time the instance should be bounced is if the server needs rebooting, or you need to change a parameter that can't be altered using the ALTER SYSTEM command. This means get rid of cold backups.
    I totally agree with that. Another situation when you need to bounce is when having a severe error in the database.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

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