Re: keep/recycle in memory pool contents

From: Finn Jorgensen <finn.oracledba_at_gmail.com>
Date: Wed, 5 Nov 2008 15:42:55 -0500
Message-ID: <74f79c6b0811051242v475f7744g2cd4e8fc9b1966d3@mail.gmail.com>

I usually use this script to display contents of the buffer pool :

accept owner char prompt "Enter owner, please : " accept limit number prompt "Enter Lower buffer limit, please : " show parameter db_cache
select o.owner,o.object_type,nvl(o.subobject_name,o.object_name) object_name, s.buffer_pool, s.blocks, count(*) buffers from dba_objects o, dba_segments s, v$bh bh where o.owner like upper('%&&owner%')
and o.data_object_id is not null
and bh.objd = o.data_object_id

and s.segment_type = o.object_type
and s.owner = o.owner
and s.segment_name = o.object_name

and (o.subobject_name = s.partition_name or (o.subobject_name is null and s.partition_name is null))
group by o.owner,o.object_type,nvl(o.subobject_name,o.object_name) , s.buffer_pool, s.blocks
having count(*) > decode(nvl(&&limit,0),0,1000,nvl(&&limit,0)) /
HTH
Finn

On Wed, Nov 5, 2008 at 10:21 AM, bao jiejie <baojiejie_at_gmail.com> wrote:

> 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;
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Nov 05 2008 - 14:42:55 CST