RE: How to find objects blocks in buffer

From: Rahul <rahul_at_ratelindo.co.id>
Date: Wed, 26 Jul 2000 11:34:52 +0700
Message-Id: <10569.112966@fatcity.com>

here's what i use for 8i....
on v7, the obj column will NOT match to the object_id you have to use the dbafil and dbablk to get the object name from the dba_segments..

------------+--------------+-------------+------------- prompt OBJECTS IN DB_BLOCKS - other than SYS/SYSTEM

select	count(*) db_blocks,
	(count(*)*4096)/1024 kb,
	b.object_name,
	b.object_type,
	c.buffer_pool
from	x$bh A, 
	all_objects B,
	dba_segments C
where	A.obj=B.data_OBJECT_ID
and	b.object_name=c.segment_name
and	b.owner not in ('SYS','SYSTEM')

group by b.object_name,b.object_type,c.buffer_pool order by 1 desc
/
------------+--------------+-------------+-------------

> ----------
> From: Surya Rao[SMTP:surya.rao_at_reuters.com]
> Sent: Wednesday, July 26, 2000 7:11 PM
> To: Vaikunts_at_ABCBS.com; Gautam_Reddy_at_Dell.com; oracledba_at_quickdoc.co.uk
> Cc: ORACLE-L_at_fatcity.com
> Subject: Re: How to find objects blocks in buffer
> Sensitivity: Confidential
>
>
> The below would work only for v8 and above as the query makes references
> to buffer pools. What would be analogous query for v7.3?
>
> surya
> -------
>
>
> Gautam,
>
> This is a script from John Lewis's Website...It is at the following location
> http://www.jlcomp.demon.co.uk/buffer.html
> <http://www.jlcomp.demon.co.uk/buffer.html>
>
>
> select
> /*+ ordered */
> bp.name pool_name,
> ob.name object,
> ob.subname sub_name,
> sum(ct) blocks
> from
> (
> select
> set_ds,
> obj,
> count(*) ct
> from
> x$bh
> group by
> set_ds,
> obj
> having count(*)/5 > (
> select max(set_count)
> from v$buffer_pool
> )
> ) bh,
> obj$ ob,
> x$kcbwds ws,
> v$buffer_pool bp
> where
> ob.dataobj# = bh.obj
> and ob.owner# > 0
> and bh.set_ds = ws.addr
> and ws.set_id between bp.lo_setid and bp.hi_setid
> and bp.buffers != 0 -- Eliminate any pools not in use
> group by
> bp.name,
> ob.name,
> ob.subname
> order by
> bp.name,
> ob.name,
> ob.subname
>
>
>
>
>
> -----Original Message-----
> From: Gautam_Reddy_at_Dell.com [mailto:Gautam_Reddy_at_Dell.com]
> Sent: Tuesday, July 25, 2000 4:27 PM
> To: oracledba_at_quickdoc.co.uk
> Cc: ORACLE-L_at_fatcity.com
> Subject: RE: How to find no of blocks
>
>
> Took me the whole morning to get this query. Here is the script of what I
> asked for. Can anyone validate and let me know.
>
> select
> substr(decode(state,0,'FREE',1,decode(lrba_seq,0,'AVAILABLE' ,
> 'BEING USED'),3,'BEING USED',state),1,11) status,
> substr(o.owner,1,15) owner,
> substr(o.object_name,1,30) object,
> count(*)
> from
> x$bh b,
> dba_objects o
> where
> o.object_id = b.obj and
> o.owner not in ('SYS','SYSTEM')
> group by
> decode(state,0,'FREE',1,decode(lrba_seq,0,'AVAILABLE',
> 'BEING USED'),3,'BEING USED',state),
> o.owner, substr(o.object_name,1,30);
>
>
> Thx
> Gautam Reddy
> 728-3656(512)
> gautam_reddy_at_dell.com <mailto:gautam_reddy_at_dell.com>
> www.dell.com <http://www.dell.com/>
>
>
> -----Original Message-----
> From: Gautam_Reddy_at_dell.com [mailto:Gautam_Reddy_at_dell.com]
> Sent: Tuesday, July 25, 2000 11:04 AM
> To: oracledba_at_quickdoc.co.uk
> Cc: ORACLE-L_at_fatcity.com
> Subject: How to find no of blocks
>
>
> How to find how many blocks of an object are present in the DB Buffer area
> at a given time.
>
> Thx
> Gautam
>
>
>
> --------
> If you're bored, then visit the list's website: http://www.lazydba.com (updated
> daily)
> to unsubscribe, send a blank email to oracledba-unsubscribe_at_quickdoc.co.uk
> to subscribe send a blank email to oracledba-subscribe_at_quickdoc.co.uk
>
> -----------------------------------------------------------------
> Visit our Internet site at http://www.reuters.com
>
> Any views expressed in this message are those of the individual
> sender, except where the sender specifically states them to be
> the views of Reuters Ltd.
>
> --------
> If you're bored, then visit the list's website: http://www.lazydba.com (updated daily)
Received on Tue Jul 25 2000 - 23:34:52 CDT