Re: keep/recycle in memory pool contents
Date: Wed, 5 Nov 2008 23:21:18 +0800
sorry do not have a db in hand for check , you can check this link:
--shows touch count for tables/indexes. Use to determine tables/indexes to keep
select decode(s.buffer_pool_id,0,'DEFAULT',1,'KEEP',2,'RECYCLE') buffer_pool,
s.owner, s.segment_name, s.segment_type,count(bh.obj) blocks,
round(avg(bh.tch),2) avg_use, max(bh.tch) max_use
from sys_dba_segs s, X$BH
<https://netfiles.uiuc.edu/jstrode/www/oraview/X$BH.html> bh where s.segment_objd = bh.obj
group by decode(s.buffer_pool_id,0,'DEFAULT',1,'KEEP',2,'RECYCLE'), s.segment_name, s.segment_type, s.owner order by decode(s.buffer_pool_id,0,'DEFAULT',1,'KEEP',2,'RECYCLE'),count(bh.obj) desc,
round(avg(bh.tch),2) desc, max(bh.tch) desc;
On Sat, Nov 1, 2008 at 7:54 PM, fairlie rego <fairlie_r_at_yahoo.com> wrote:
> Don't have access to a system at the moment but I think you need to do a
> join with v$buffer_pool.
> I believe that in x$bh there is a column called buf# which should lie
> between the low and high buffer numbers in v$buffer_pool for both pools
> (KEEP and RECYCLE).
> Hope that helps
> *Christo Kutrovsky <kutrovsky.oracle_at_gmail.com>* wrote:
> Does anyone know how to differentiate between keep and recycle pools
> in v$bh? Seems like that they are included there but I am not 100%
> sure as the numbers don't add up properly.
> Anyone has experience with this?
> Christo Kutrovsky
> DBA Team Lead
> The Pythian Group - www.pythian.com
> I blog at http://www.pythian.com/blogs/
> *Fairlie Rego
> *Senior Oracle Consultant
> M: +61 402 792 405
> Search 1000's of available singles in your area at the new Yahoo!7 Dating. Get
-- ("'-''-/").___..--''"'-._ '7_ 7 ) '_. ( ).'-.__.') (_Y_.) ._ ) '._ '. ''-..-' _.'--'_..-_/ /--'_.' ,' (il),-'' (li),' ((!.-' Best regards， Yours sincerely House baojiejie_at_gmail.com -- http://www.freelists.org/webpage/oracle-lReceived on Wed Nov 05 2008 - 09:21:18 CST