First query does exactly this, but can be very slow, I mean very.
Second query runs much faster, but care needs to be taken about CLUSTERs, all objects in cluster share the same data_object_id.
Alex.
-- buffers distribution by segments
select
count(*) buffers
,e.tablespace_name
,e.owner||'.'||e.segment_name||' ('||e.segment_type||')' segment
from
sys.x_$bh b
,sys.dba_extents e
where b.file# = e.file_id
and b.dbablk between e.block_id and e.block_id+e.blocks-1
group by
e.owner||'.'||e.segment_name||' ('||e.segment_type||')'
,e.tablespace_name
order by 1 desc, 2, 3
;
--
-- buffers distribution by objects
select
count(*) buffers
,b.obj d_obj_id
,o.owner||'.'||o.object_name||' ('||object_type||')' object
from
sys.x_$bh b
,sys.dba_objects o
where o.data_object_id = b.obj
group by b.obj, o.owner||'.'||o.object_name||' ('||object_type||')'
order by 1 desc, 2, 3
;
--
-----Original Message-----
From: BigP [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, June 11, 2002 6:08 PM
To: Multiple recipients of list ORACLE-L
Subject: buffer cache
Hi Guys ,
IS there any way I can find what is occupying how much of buffer cache . Like .. what table is taking most of space etc .
Thanks ,
Bp
