buffer gets vrs buffer cache
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: buffer gets vrs buffer cache

  1. #1
    Join Date
    Jan 2001
    Posts
    72

    Angry

    Hi,
    Can any body answer these questons like they appear and if possible brief explanaton?

    1. Are there any relationships between high buffer gets and low hit ratio in the data buffer cache?

    2. Does high buffer gets indicates that most sql's are processed in memory instead of disk

    3. Is it advisable to increase the shared_pool size if the hit ratio in the buffer cache is very low (10%) but have very high buffer gets?

  2. #2
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    I dont know what you mean by 'Buffer gets' but there are two types of 'gets' in buffer cache, 'consistent gets' and 'db block gets'.

    You calculate Buffer cache hit ratio as follows:
    Code:
    Select name, value from V$SYSSTAT where name in ('db block gets', 'consistent gets', 'physical reads');
    Now Calculate hit ratio in % by:

    (db block gets+ consistent gets - physical reads/db block gets + consistent gets)*100

    If gets are higher, hit ratio cannot be low as explained above.
    Does high buffer gets indicates that most sql's are processed in memory instead of disk
    It means that the data was found in the memory and physical reads were avoided to fetch the data.
    Is it advisable to increase the shared_pool size if the hit ratio in the buffer cache is very low (10%) but have very high buffer gets?
    What has shared pool got to do with it? I think you are a little mixed up between shared pool and Data Buffer cache. Buffer cache is a place in memory where data is read into from the disk. Shared pool is entirely different area of SGA which comprises the library cache (SQL parsed plans etc), dictionary cache and other things are located.


    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  3. #3
    Join Date
    Aug 2001
    Posts
    111

    Smile

    It sounds as though you have SQL doing large number of "buffer gets" which could include consistent_gets, db block gets and disk reads(get blocks/buffers from disk)

    I would look at the SQL first before muckin around with the memory. It is amazing how suddenly the hit ratios improve when SQL is optimised.

    To get what SQL is doing "potentially" bad stuff try searching v$sqlarea for buffer_gets > x and then again where disk_reads > x where x is a integer eg 10000.

    The other alternative is to trace active sessions and TKPROF to get SQL executed by that session and associated statistical goodies (consistent gets, disk reads elapsed time etc)

    Also it pays to find out what the database is waiting on as well. Once you have solved contention and waiting then have a look at ratios.

    SQL tuning, waits and then ratios is a better order to tune a sluggish system.

    Have Fun
    Performance... Push the envelope!

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