-
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
-
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
-
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
-
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
-
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
-
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!
-
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
-
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 $
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|