Re: keep/recycle in memory pool contents

From: Finn Jorgensen <>
Date: Wed, 5 Nov 2008 15:42:55 -0500
Message-ID: <>

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)) /

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

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

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

Original text of this message