Buffer Cache Tuning
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Buffer Cache Tuning

  1. #1
    Join Date
    Jan 2002
    Posts
    113
    I have a 8.1.7 database and I am suspecting the buffer cache has been over sized. How can I determine how many buffers are going unused and can be removed from the buffer cache?
    Arm yourself with knowledge

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

    Cool

    You can use on NON-PRODUCTION servers, the V$RECENT_BUCKET view. To enable it, setup DB_BLOCK_LRU_EXTENDED_STATISTICS to nonzero value in init.ora file and enable collection of V$RECENT_BUCKET statistics from V$SYSSTAT.

    Detailed procedure could be found Here

    Hope that helps,

    clio_usa
    OCP DBA 8/8i/9i
    visit our web site: Here

  3. #3
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Hello;
    Try to reduce the buffer_cache in pieces of 5%;
    Then monitor the BufferCachce-Hit-Ratio over a longer period
    if it's in summary still over 95% then you can reduce again.

    This you can do in production-environment.

    Orca

  4. #4
    Join Date
    Jan 2002
    Posts
    113
    clio_usa,
    Are you sure that can be done in a 8.1.7 database? There is no db_block_lru_extended_statistics parameter or v$recent_bucket view that I can find.
    Arm yourself with knowledge

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by clio_usa
    You can use on NON-PRODUCTION servers, the V$RECENT_BUCKET view. To enable it, setup DB_BLOCK_LRU_EXTENDED_STATISTICS to nonzero value in init.ora file and enable collection of V$RECENT_BUCKET statistics from V$SYSSTAT.
    [/B]
    Those structures were removed in 8i. You can find the information at:

    http://otn.oracle.com/docs/products/...9_mem.htm#5239
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Feb 2002
    Posts
    13
    Here is a rudimentary script for use in estimating buffer_cache size. Must be logged on as sys to access most x$ views.

    select /*+ ordered use_hash(b) */ n.bp_name buffer_pool,
    count(*) current_buffers, count(*) +
    count(decode(lru_flag, 0, decode(tch, 0, null, 1, null, 1))) -
    count(decode(state, 0, 1, decode(lru_flag, 8, decode(tch, 0, 1, 1, 1))))
    ideal_buffers
    from (select /*+ ordered */ p.bp_name, s.addr
    from x$kcbwbpd p, x$kcbwds s
    where s.inst_id = userenv('Instance')
    and p.inst_id = userenv('Instance')
    and s.set_id >= p.bp_lo_sid
    and s.set_id <= p.bp_hi_sid
    and p.bp_size != 0) n, x_$bh b
    where b.inst_id = userenv('Instance')
    and b.set_ds = n.addr
    group by n.bp_name
    /

    Hope this helps!

  7. #7
    Join Date
    Jan 2002
    Posts
    113
    wroos,

    I logged in as SYS and ran the script but I got this error:


    and p.bp_size != 0) n, x_$bh b
    *
    ERROR at line 12:
    ORA-00942: table or view does not exist

    Arm yourself with knowledge

  8. #8
    Join Date
    Feb 2002
    Posts
    13
    Sorry:

    Line:
    and p.bp_size != 0) n, x_$bh b

    should be:
    and p.bp_size != 0) n, x$bh b

    Removed underscore between x and $



  9. #9
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    hi,
    as told by Orca777,just trying reducing the buffers and check the hit ration,once you find the ration less than 95% the stop at the point...

    regards
    anandkl
    anandkl

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