Re: keep/recycle in memory pool contents

From: bao jiejie <baojiejie_at_gmail.com>
Date: Wed, 5 Nov 2008 23:21:18 +0800
Message-ID: <ef9b14410811050721r48eb88b2s178bc8468d84e743@mail.gmail.com>

sorry do not have a db in hand for check , you can check this link:
https://netfiles.uiuc.edu/jstrode/www/oraview/V$BH.html

--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
>
> -Fairlie
>
>
>
> *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/
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
>
> *Fairlie Rego
> *Senior Oracle Consultant
> http://el-caro.blogspot.com/
> M: +61 402 792 405
>
>
> ------------------------------
> Search 1000's of available singles in your area at the new Yahoo!7 Dating. Get
> Started<http://au.rd.yahoo.com/dating/mail/tagline1/*http://au.dating.yahoo.com/?cid=53151&pid=1011>
> .
>

-- 

("'-''-/").___..--''"'-._
 '7_ 7  )   '_.  (     ).'-.__.')
 (_Y_.)   ._   )  '._ '. ''-..-'
_.'--'_..-_/  /--'_.' ,'
(il),-''  (li),'  ((!.-'

Best regards，
Yours sincerely House
baojiejie_at_gmail.com
--
http://www.freelists.org/webpage/oracle-l

Received on Wed Nov 05 2008 - 09:21:18 CST