Buffer Cache Tuning

[psychofun]

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?

[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.

Detailed procedure could be found Here

Hope that helps,

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

[Orca777]

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

[psychofun]

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.

[marist89]

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

[WROOS]

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!

[psychofun]

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

[WROOS]

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 $

[anandkl]

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