RE: buffer cache

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-----
Sent: Tuesday, June 11, 2002 6:08 PM
To: Multiple recipients of list ORACLE-L

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Alexander.Feinstein_at_mitchell1.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).