How to Tune DB_BLOCK_BUFFERS in 9i
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: How to Tune DB_BLOCK_BUFFERS in 9i

  1. #1
    Join Date
    Jun 2002
    Posts
    88
    Dear Gurus,

    Just now i run the following query:

    SQL> select round ((1-(sum(decode(name,'physical reads',value,0)) /
    2 (sum(decode(name, 'db block gets',value,0)) +
    3 sum(decode(name, 'consistent gets',value,0))))) * 100, 4)
    4 hitratio
    5 from v$sysstat;

    HITRATIO
    ----------
    76.6596



    Right now My database hitratio is 76.65. But oracle recommonded hit ratio above 90%.

    My Database Buffres(db_cache_size) is 67108864 (Oracle9i Solaris Platform)

    How to tune Database Buffers?

    Please advise me, Thanks in advance,

    Thanks,
    Iyyappan.M

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Increase the buffers.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    go to http://www.hotsos.com and read

    Why 99% Database Buffer Cache Hit Ratio is NOT Ok by Cary Millsap

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by pando
    go to http://www.hotsos.com and read

    Why 99% Database Buffer Cache Hit Ratio is NOT Ok by Cary Millsap
    Was this about the company in Copenhagen, bla-bla-bla :-)) Come on David, you don't really trust that bull****, do you :-)


    [Edited by julian on 08-24-2002 at 10:57 AM]
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  5. #5
    Join Date
    Jun 2002
    Posts
    88

    Tuning DB_BLOCK_BUFFERS IN 9i

    Hi,

    My DB_BLOCK_BUFFERS value is

    select state,count(*)
    from x$bh
    group by state;

    state Count(*)
    ------ --------

    0 274
    1 7642
    3 40


    Total DB_BLOCK_BUFFERS = 7956(274+7642+40)


    My question is how much value i have to put or increase?

    Can u please explain,

    Awaiting ur reply,

    Thanks in advance,
    Iyyappan.M

  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    Re: Tuning DB_BLOCK_BUFFERS IN 9i

    For a start, increase the value by 10%.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  7. #7
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Start Oracle Enterprise Manager and use the buffer cache advisory to check the effect of adding/removing memory to/from the buffer cache. If adding memory has no significant effect on the ratio why waste it!

    Remember, hit ratios are a very basic way of tuning a system. You should consider looking at the YAPP method. Basically this involves eliminating/reducing wait states rather than focusing on hit ratios:

    http://www.oracle-base.com/Articles/8i/Statspack8i.asp
    http://www.oracle-base.com/Links/yapp_anjo_kolk.pdf
    http://www.oracle-base.com/Links/oraperf_bw.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