From oracle-l-bounce@freelists.org Mon Apr 25 00:54:07 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j3P5s7Bk010478 for ; Mon, 25 Apr 2005 00:54:07 -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 j3P5s24Z010468 for ; Mon, 25 Apr 2005 00:54:03 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E9B0A186245; Sun, 24 Apr 2005 23:51:37 -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 30704-02; Sun, 24 Apr 2005 23:51:37 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6201B186174; Sun, 24 Apr 2005 23:51:37 -0500 (EST) Message-ID: <002501c54963$3097eca0$6401a8c0@dilbert> From: "Don Granaman" To: , References: Subject: Re: LOCALLY MANAGED EXTENT PERFORMANCE Date: Sun, 24 Apr 2005 23:51:21 -0700 MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1478 X-archive-position: 18919 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: granaman@cox.net Precedence: normal Reply-To: granaman@cox.net 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.0 required=5.0 tests=AWL autolearn=ham version=2.63 I agree. Uniform extent sizes are not inherently bad. If you don't know how large something will be, put it in autoLMT - that is the main reason its there. Small to moderately large segments are also good candidates. It works well in tablespaces with many mixed-sized segments. For segments that will be huge though, why not supersize from the start? I need to perform a major data conversion soon that will result in several 50+ GB tables. They don't really need *any* 64k extents and will each be dedicated tablespaces. This particular system is RAC, so I am going to choke down the obligatory side order of automatic to get the ASSM entree. However, I would prefer uniform extents on the side in this case. One table > 300 GB on autoLMT/ASSM in this system has a largest extent of 64M, so why not just start there and make them uniform? The extent count difference (auto will be higher) doesn't bother me - it will converge fast anyway on this scale (details left as an exercise for the reader). The potential for unnecessary free space fragmentation during a segment's awkward adolescence (where it doesn't know how big it wants to be when it grows up) bothers me a bit more. Sure, you can tweak it some - by making the smallest sizes by setting initial large enough for one thing. For example, (doing this from memory - your mileage may vary) for 8k blocks: initial>=2M starts with 1 MB extents and initial>1024M starts with 8 MB extents. Initial>X(?) may start with 64M - or something larger than 8 MB, but I couldn't get there with finite space (a few hundred GB) to test on. But... Who really wants to micromanage autoLMTs anyway? Isn't that sort of missing the point? OraSaurus - and contrarian by nature... ----- Original Message ----- From: "Tim Gorman" To: Sent: Sunday, April 24, 2005 12:44 PM Subject: Re: LOCALLY MANAGED EXTENT PERFORMANCE > Exactly why might a large number of extents be a bad thing? In other words, > are you sure you are attaching the proper level of importance to the issue? > > To help figure out if this is true, can you describe exactly what operations > might be affected by the number of extents, and how? Queries? > Inserts/updates/deletes? Truncates? Drops? Monitoring queries? > > And, are you certain that autoLMT resolves the problem of "too many > extents"? Isn't there an upper limit on extent size even with autoLMT? If > so, then how is this different from intelligently sized uniform LMTs? > > My apologies for the Socratic questioning, but this thread contained too > many assertions that need a little more examination... > > -Tim > > > on 4/22/05 11:07 PM, Dogan, Ibrahim - Ibrahim at Ibrahim.Dogan@Lowes.com > wrote: > > > > > Even with LMTs, you still wory about number of extents whenever you run > > any command that performs extent allocation/deallocation (create > > table/rebuild index/truncate table etc..) > > > > My point was that I saw many people going back to DMT because of very > > same issue you're experiencing with LMTs.. When LMT with uniform extent > > size is used, you need to babysit the segments to make sure they don't > > go beyong couple thousand extents.. But you don't have this problem if > > you use LMT with AUTO extent allocation. My biggest table is 27G in a > > AUTO LMT and it has around 600 extents.. > > > > I generally prefer AUTO LMT and reorg the tables after bulk deletes... > > > > > > Thanks, > > > > Ibrahim DOGAN > > Sr. Sybase/Oracle DBA > > www.lowes.com -- http://www.freelists.org/webpage/oracle-l