From oracle-l-bounce@freelists.org Wed Apr 27 13:35:09 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j3RIZ9YK010698 for ; Wed, 27 Apr 2005 13:35:09 -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 j3RIZ94Z010694 for ; Wed, 27 Apr 2005 13:35:09 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 062D5186F4A; Wed, 27 Apr 2005 12:32:42 -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 08118-02; Wed, 27 Apr 2005 12:32:41 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 71C7A18713B; Wed, 27 Apr 2005 12:32:41 -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: Wed, 27 Apr 2005 13:29:50 -0400 Message-ID: <61C900F558E4184DBD8E177CC9D51F6703715492@msexdb06.lowes.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: LOCALLY MANAGED EXTENT PERFORMANCE Thread-Index: AcVKrHnumBoyLLQOTkeSfVroG0Il2AAna5Lg From: "Dogan, Ibrahim - Ibrahim" To: , "Oracle-L" X-OriginalArrivalTime: 27 Apr 2005 17:29:51.0302 (UTC) FILETIME=[B7CB6660:01C54B4E] X-archive-position: 19091 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=AWL,LINES_OF_YELLING, SUBJ_ALL_CAPS autolearn=no version=2.63 > Less doubtful and more likely, is that in rebuilding the=20 > table in question to another tablespace, several other=20 > important modifications to storage parameters were also made,=20 > such as increasing INITRANS, increasing FREELISTS or using=20 > ASSM, or any number of possible changes to indexes on the=20 > table, perhaps? No way..all storage parameters remained same except the extent size. is INITTRANS or FREELISTS changed when you do export from autoallocate and import it into unfiorm sized? Diagnosing perf bottleneck is not rocket science... You turn on sql trace and look into raw trace file or tkprof file it is right there.. TRUNCATE TABLE is taking 45-60 seconds.. This app had over 700 tables and almost 7-8 of them had over 30,000 extents... I didn't set up the uniformed LMT, I was called in to analyze the long running processes..I'd set the extent size to default, 1M but somebody apparently thought that would be waste of space :( > Cases such as...? You even repeat the questions as if I didn't answer them... I think I sent the test SQL that will give you some idea.. Go to my 2. or 3. email.. > Ah, but then you keep mentioning this 27G table and how=20 > autoallocate helped it. If there is ever situation that=20 > screams for uniform-sized extents, it would be that one. =20 > Certainly, you know quite well how much space that thing is=20 > consuming day-by-day as it grows. So why would you need to=20 > cede control over extent sizing to autoallocate? You're missing the point. It is not the only table I have.. This is 3 party app and there hundreds of tables with different space requirements. And bunch of them are very volatile.. They grow and get truncated in middle of day.. And you make it sound like autoallocate creates millions of extents with different sizes. This is wrong.. Actually it is pretty uniform in itself: It has only 4 or 5 extent sizes. 64K, 1M, 8M, 64M ... Some DBAs who use only uniformed size LMTs generally ignore/don't know this fact. If this many extent sizes confuse some people and they think it will create fragmentation, I have nothing else to say.. And if uniform is a cure to all cases, why would hundreds of Oracle engineers come up with an alternative? A marketing gimmick ? I don't think so.. > LMT was not introduced until version 8.1. So, the advice in the paper is for DMT only, which is all that existed at the time it was > written. Yes but the paper was written with LMTs in mind..here is another clip from the paper: 2.1 Eliminating Extent Fragmentation Using SAFE Oracle provides administrators extreme flexibility in sizing the extents that are allocated to segments. This full specification flexibility is almost never needed and in fact, should be avoided in most cases. By following a simple set of administrative rules, fragmentation at the extent level can be entirely eliminated. We will use the term SAFE to refer to these rules. SAFE stands for Simple Algorithm for Fragmentation Elimination. SAFE consists of a set of rules, many of which have been internalized and implemented as part of the LOCALLY MANAGED TABLESPACE feature being introduced in Oracle8i. ORACLE8i users should refer to the presentation on that subject for an updated version of SAFE.=20 Thanks, =20 Ibrahim DOGAN Sr. Sybase/Oracle DBA www.lowes.com -- http://www.freelists.org/webpage/oracle-l