RE: recycle and keep buffer in other tablespace block sizes

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Wed, 21 Jul 2004 16:11:21 -0400
Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CF09660C14@bosmail00.bos.il.pqe>

Comments in-line, below.

Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
"Post Hoc Ergo Propter Hoc"

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Juan Carlos Reyes Pacheco
Sent: Wednesday, July 21, 2004 3:56 PM
To: oracle-l_at_freelists.org
Subject: RE: recycle and keep buffer in other tablespace block sizes

I read Mark
What I was asking why if the view has the same name from one release to other=20
the number of fields change
"but your query in 9i don't work, can't find ds.end_buf#" means "why things like things happens."
Sorry If I didn't express correctly=20

<MB>  Ah, ok, I see.  Well, obviously, the way the buffer cache is
<MB>  is managed has changed in 9i.  I would guess it has to do with
<MB>  9i's ability to grow and shrink the buffer cache dynamically,
<MB>  and the added layer(s?) of code to support that, but I haven't
<MB>  totally worked out how it fits together yet.

-------Original Message-------
=20

From: oracle-l_at_freelists.org
Date: 07/21/04 15:51:15
To: oracle-l_at_freelists.org
Subject: RE: recycle and keep buffer in other tablespace block sizes
=20

Juan,
=20

Did you read my previous posting?
=20

In particular, the part where I said "I've figured out how to do this in 8i, but in 9i, I have not succeeded in writing such a query. I spent some time running in circles a few weeks ago, and never did get anywhere."
=20

And then I detailed how my query works for 8i ONLY, and asked if anyone had worked it out for 9i........
=20
=20
=20

-Mark
=20
=20

Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
"Post Hoc Ergo Propter Hoc"
=20
=20

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Juan Carlos Reyes Pacheco
Sent: Wednesday, July 21, 2004 3:03 PM
To: oracle-l_at_freelists.org
Subject: RE: recycle and keep buffer in other tablespace block sizes
=20
=20

Thanks Mark
but your query in 9i don't work, can't find ds.end_buf#
=20

I found this few days ago
http://searchoracle.techtarget.com/tip/1,289483,sid41_gci992782,00.html
=20

I tested this query in 9i , but I got nothing,=3D20
=3D20

select
t1.owner c0,
object_name c1,
case when object_type =3D3D 'TABLE PARTITION' then 'TAB PART' when object_type =3D3D 'INDEX PARTITION' then 'IDX PART' else object_type end c2,
sum(num_blocks) c3,
(sum(num_blocks)/greatest(sum(blocks), .001))*100 c4, buffer_pool c5,
sum(bytes)/sum(blocks) c6
from
(select

o.owner owner,
o.object_name object_name,
o.subobject_name subobject_name,
o.object_type object_type,

count(distinct file# || block#) num_blocks from
dba_objects o,
v$bh bh
where
o.data_object_id =3D3D bh.objd
and
o.owner not in ('SYS','SYSTEM')
and
bh.status !=3D3D 'free'
group by

o.owner,
o.object_name,
o.subobject_name,
o.object_type

order by
count(distinct file# || block#) desc)
t1,
dba_segments s
where
s.segment_name =3D3D t1.object_name
and
s.owner =3D3D t1.owner
and
s.segment_type =3D3D t1.object_type
and
nvl(s.partition_name,'-') =3D3D nvl(t1.subobject_name,'-') group by
t1.owner,

object_name,
object_type,
buffer_pool

having
sum(num_blocks) > 10
order by
sum(num_blocks) desc
;
=3D20
=3D20

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Jul 21 2004 - 15:16:43 CDT