From oracle-l-bounce@freelists.org Wed Jul 21 15:16:43 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i6LKGS509177 for ; Wed, 21 Jul 2004 15:16:38 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i6LKGG609148 for ; Wed, 21 Jul 2004 15:16:26 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 42E3072E7EC; Wed, 21 Jul 2004 14:54:43 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 21374-09; Wed, 21 Jul 2004 14:54:43 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9CCEC72E382; Wed, 21 Jul 2004 14:52:12 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 21 Jul 2004 14:50:13 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6EC6472E744 for ; Wed, 21 Jul 2004 14:50:09 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 18068-74 for ; Wed, 21 Jul 2004 14:50:08 -0500 (EST) Received: from tera.umi.com (tera.umi.com [192.195.245.144]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0351D72D186 for ; Wed, 21 Jul 2004 14:47:23 -0500 (EST) Received: from aabo-exchange04.bos.il.pqe (aabo-exchange04.bos.il.pqe [172.24.3.67]) by tera.umi.com (8.12.10/8.12.10) with ESMTP id i6LKAkMS031459 for ; Wed, 21 Jul 2004 16:13:18 -0400 Received: from bosmail00.bos.il.pqe ([172.24.3.64]) by aabo-exchange04.bos.il.pqe with Microsoft SMTPSVC(6.0.3790.0); Wed, 21 Jul 2004 16:11:22 -0400 X-MIMEOLE: Produced By Microsoft Exchange V6.0.6556.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 8bit Subject: RE: recycle and keep buffer in other tablespace block sizes Date: Wed, 21 Jul 2004 16:11:21 -0400 Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CF09660C14@bosmail00.bos.il.pqe> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: recycle and keep buffer in other tablespace block sizes Thread-Index: AcRvXcO8j3EiJXPOQNyjGRSDdRrKKQAAJuTQ From: "Bobak, Mark" To: X-OriginalArrivalTime: 21 Jul 2004 20:11:22.0069 (UTC) FILETIME=[E4471850:01C46F5E] X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 5686 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Mark.Bobak@il.proquest.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Juan Carlos Reyes Pacheco Sent: Wednesday, July 21, 2004 3:56 PM To: oracle-l@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 Ah, ok, I see. Well, obviously, the way the buffer cache is is managed has changed in 9i. I would guess it has to do with 9i's ability to grow and shrink the buffer cache dynamically, and the added layer(s?) of code to support that, but I haven't totally worked out how it fits together yet. -------Original Message------- =20 From: oracle-l@freelists.org Date: 07/21/04 15:51:15 To: oracle-l@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@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Juan Carlos Reyes Pacheco Sent: Wednesday, July 21, 2004 3:03 PM To: oracle-l@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@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@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@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@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 -----------------------------------------------------------------