Buffer Cache Usage
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Buffer Cache Usage

Hybrid View

  1. #1
    Join Date
    Jul 2001
    Posts
    93
    I'm wondering if having a very large - 1.5G - buffer cache can effect cpu usage. One of our production servers has 80000 db_block_buffers with a block size 0f 8k. I used the follwoing query to find out the status of the buffers.

    select decode(state, 0, 'Free', 1,
    decode(lrba_seq,0,'Available','Being Modified'),
    2, 'Not Modified',
    3, 'Being Read', 'Other') "BLOCK STATUS",
    count(*) cntfrom sys.x$bh
    group by decode(state, 0, 'Free',1, decode(lrba_seq,0,'Available','Being Modified'),
    2, 'Not Modified',3, 'Being Read','Other')

    BLOCK STATUS CNT
    -------------- ----------
    Available 79430
    Being Modified 331
    Being Read 239

    If i reduce the db_block_buffers from 80000 to 60000 will this have an impaceon the performance. Based upon the count from the above it looks like the buffer cache is oversized.

    Any help would be greatly appreciated.

    Thanks in Advance.

  2. #2
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    read

    Are you still using cache hit ratios? — Cary Millsap's follow-up to his “99.9% paper” goes even further to explain why the buffer cache hit ratio is an illegitimate measure of SQL efficiency

    at http://www.hotsos.com
    I'm stmontgo and I approve of this message

  3. #3
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Originally posted by stmontgo

    Are you still using cache hit ratios? ...
    Steve
    Why we have not to use HR (for cache or not only).
    Another question: We should not use HR ONLY.
    HR is information about a db too. And with another measures,
    such as wait statistics, os statistics and so on.
    HR can show more data about perfomance and health of db.
    We need use HR in right way.

  4. #4
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    If you read the following articles you'll see that hit ratios are not a very good indicator of system performance. In some cases very high hit ratios can indicate poor performance due to endless spins for latches.

    You can still use hit ratios if it makes you happy, just make sure you follow them up with some real tests or you may find yourself out of a job.

    http://www.oracle-base.com/Links/yapp_anjo_kolk.pdf
    http://www.oracle-base.com/Links/oraperf_bw.pdf
    http://www.hotsos.com/dnloads/1.Mill...CacheRatio.pdf
    http://www.hotsos.com/dnloads/1.Mill....11.14-LIO.pdf

    Cheers

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

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