With some changes (v_$bh instead of x_$bh, objd instead of obj) the '
buffers distribution by objects ' script was worked BUT MY v_$bh IS EMPTY.
Any idea why?
-----Original Message-----
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: miercuri 12 iunie 2002 05:32
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
