Re: keep/recycle in memory pool contents
- From: "Finn Jorgensen" <finn.oracledba@xxxxxxxxx>
- To: baojiejie@xxxxxxxxx
- Date: Wed, 5 Nov 2008 15:42:55 -0500
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@xxxxxxxxx> 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;
>
>
Other related posts:
