From oracle-l-bounce@freelists.org Mon Apr 25 16:26:56 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j3PLQuZ7018804 for ; Mon, 25 Apr 2005 16:26:56 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j3PLQt4Z018800 for ; Mon, 25 Apr 2005 16:26:55 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 95004186781; Mon, 25 Apr 2005 15:24:30 -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 15208-09; Mon, 25 Apr 2005 15:24:30 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 128521868BF; Mon, 25 Apr 2005 15:24:30 -0500 (EST) X-MimeOLE: Produced By Microsoft Exchange V6.0.6487.1 content-class: urn:content-classes:message MIME-Version: 1.0 Content-type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 8bit Subject: RE: LOCALLY MANAGED EXTENT PERFORMANCE Date: Mon, 25 Apr 2005 16:22:41 -0400 Message-ID: <61C900F558E4184DBD8E177CC9D51F67035D76B9@msexdb06.lowes.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: LOCALLY MANAGED EXTENT PERFORMANCE Thread-Index: AcVJBlcb9kcIq23nQHuqVRLO1UkaHAAzT5VQ From: "Dogan, Ibrahim - Ibrahim" To: , X-OriginalArrivalTime: 25 Apr 2005 20:22:41.0634 (UTC) FILETIME=[882AFC20:01C549D4] X-archive-position: 18972 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Ibrahim.Dogan@Lowes.com Precedence: normal Reply-To: Ibrahim.Dogan@Lowes.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=0.6 required=5.0 tests=LINES_OF_YELLING,SUBJ_ALL_CAPS autolearn=no version=2.63 As I told in my email, I have a 27G tables with around 600 extents.. So too-many-extents problem is pretty much solved by autoallocate LMT. Having too many extents makes uet$ and fet$ system tables (please note that they're clustered) big and this may slow down=20 operations that allocates/deallocates extents. Normally this should not be big trouble in LMTs if your're not truncating volatile tables in middle of day millions of times. To see the different please test the following code in both uniform sized (8K) and autoallocate LMT and let us know the results: CREATE TABLE proof (segment_name char(2000), segment_type char(2000), segment_name2 char(2000), segment_type2 char(2000) ) TABLESPACE LMTTBS drop table proof INSERT INTO proof (segment_name, segment_type,segment_name2, segment_type2) SELECT segment_name, segment_type, segment_name, segment_type FROM dba_segments; commit; INSERT INTO proof (segment_name, segment_type,segment_name2, segment_type2) SELECT segment_name, segment_type, segment_name, segment_type FROM proof; commit; INSERT INTO proof (segment_name, segment_type,segment_name2, segment_type2) SELECT segment_name, segment_type, segment_name, segment_type FROM proof; commit; INSERT INTO proof (segment_name, segment_type,segment_name2, segment_type2) SELECT segment_name, segment_type, segment_name, segment_type FROM proof; commit; INSERT INTO proof (segment_name, segment_type,segment_name2, segment_type2) SELECT segment_name, segment_type, segment_name, segment_type FROM proof; commit; select sysdate from dual / truncate table proof / select sysdate from dual / Thanks, =20 Ibrahim DOGAN Sr. Sybase/Oracle DBA www.lowes.com > -----Original Message----- > From: oracle-l-bounce@freelists.org=20 > [mailto:oracle-l-bounce@freelists.org] On Behalf Of Tim Gorman > Sent: Sunday, April 24, 2005 3:45 PM > To: oracle-l@freelists.org > Subject: Re: LOCALLY MANAGED EXTENT PERFORMANCE >=20 >=20 > Exactly why might a large number of extents be a bad thing? =20 > In other words, are you sure you are attaching the proper=20 > level of importance to the issue? >=20 > To help figure out if this is true, can you describe exactly=20 > what operations might be affected by the number of extents,=20 > and how? Queries? Inserts/updates/deletes? Truncates? =20 > Drops? Monitoring queries? >=20 > And, are you certain that autoLMT resolves the problem of=20 > "too many extents"? Isn't there an upper limit on extent=20 > size even with autoLMT? If so, then how is this different=20 > from intelligently sized uniform LMTs? >=20 > My apologies for the Socratic questioning, but this thread=20 > contained too many assertions that need a little more examination... >=20 > -Tim >=20 >=20 > on 4/22/05 11:07 PM, Dogan, Ibrahim - Ibrahim at=20 > Ibrahim.Dogan@Lowes.com > wrote: >=20 > >=20 > > Even with LMTs, you still wory about number of extents whenever you=20 > > run any command that performs extent=20 > allocation/deallocation (create=20 > > table/rebuild index/truncate table etc..) > >=20 > > My point was that I saw many people going back to DMT=20 > because of very=20 > > same issue you're experiencing with LMTs.. When LMT with uniform=20 > > extent size is used, you need to babysit the segments to make sure=20 > > they don't go beyong couple thousand extents.. But you=20 > don't have this=20 > > problem if you use LMT with AUTO extent allocation. My=20 > biggest table=20 > > is 27G in a AUTO LMT and it has around 600 extents.. > >=20 > > I generally prefer AUTO LMT and reorg the tables after bulk=20 > deletes... > >=20 > >=20 > > Thanks, > >=20 > > Ibrahim DOGAN > > Sr. Sybase/Oracle DBA > > www.lowes.com > >=20 > >=20 > >> -----Original Message----- > >> From: Wolfson Larry - lwolfs [mailto:lawrence.wolfson@acxiom.com] > >> Sent: Saturday, April 23, 2005 12:55 AM > >> To: Dogan, Ibrahim - Ibrahim; oracle-l@freelists.org > >> Subject: RE: LOCALLY MANAGED EXTENT PERFORMANCE > >>=20 > >> And I thought Oracle said you didn't have to worry about=20 > how many any=20 > >> more. > >>=20 > >> I didn't exactly blame LMTs I just said there were a > >> lot of extents. > >> Not my app. > >>=20 > >> Thanks for info > >>=20 > >> -----Original Message----- > >> From: Dogan, Ibrahim - Ibrahim [mailto:Ibrahim.Dogan@Lowes.com] > >> Sent: Friday, April 22, 2005 11:46 PM > >> To: Wolfson Larry - lwolfs; oracle-l@freelists.org > >> Subject: RE: LOCALLY MANAGED EXTENT PERFORMANCE > >>=20 > >> Do not use uniform sized LMTs for volatile tables since=20 > you may end=20 > >> up having a table/index with thousand of extents.. > >>=20 > >> More important, don't blame LMTs for it.. > >>=20 > >> If you use AUTO extent allocation instead of uniform size,=20 > you won't=20 > >> have problem... > >>=20 > >> Thanks, > >>=20 > >> Ibrahim DOGAN > >> Sr. Sybase/Oracle DBA > >> www.lowes.com >=20 > -- > http://www.freelists.org/webpage/oracle-l >=20 -- http://www.freelists.org/webpage/oracle-l