From oracle-l-bounce@freelists.org Tue Apr 26 18:10:59 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j3QNAxjM022304 for ; Tue, 26 Apr 2005 18:10:59 -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 j3QNAx4Z022299 for ; Tue, 26 Apr 2005 18:10:59 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 532B8186DB1; Tue, 26 Apr 2005 17:08:33 -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 14179-07; Tue, 26 Apr 2005 17:08:33 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C3C72186CF7; Tue, 26 Apr 2005 17:08:32 -0500 (EST) User-Agent: Microsoft-Entourage/10.1.4.030702.0 Date: Tue, 26 Apr 2005 16:06:34 -0600 Subject: Re: LOCALLY MANAGED EXTENT PERFORMANCE From: Tim Gorman To: Oracle-L Message-ID: In-Reply-To: <61C900F558E4184DBD8E177CC9D51F67035D7859@msexdb06.lowes.com> Mime-version: 1.0 Content-type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 8bit X-archive-position: 19061 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: tim@evdbt.com Precedence: normal Reply-To: tim@evdbt.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.1 required=5.0 tests=AWL autolearn=ham version=2.63 > > Ever heard "3 Party App" where you have no access to the code? > Gee, no. What's that? :-) C'mon, of course I have. Few (if any) 3rd-party apps bother to address extent allocation mechanisms for tablepaces; even fewer insist on having things one way or the other. In most cases, 3rd-party app knows the concept of a tablespace and that's it. Some might provide sample code for CREATE TABLESPACE statements, but few are willing to give working code because it is so dependent on local standards and platform. And you always have access to that "code" because it is DDL; it's the application code within the 3rd-party app that you rarely have access to... > > I was also temped by the theory that number of extents don't matter much > but I changed my mind after testing LMT with uniform extents for a 3. > party app in which there are several tables that jump to 3-4G from 0 > bytes during day..all users started screaming and we had to go to LMT > with auto allocate to calm down the users... You're saying that transactional insert performance was affected by extent allocation? Gee, I don't think so... Let's run the numbers. If that 3-4G table grows to consist of 1000 extents, then you're talking about an extension once every 60-70 seconds during the day. Which is once every couple thousand transactions, no? So, to summarize, an operation (i.e. LMT extent allocation) that takes milli-seconds per execution, which occurs once every 60-70 seconds or so, is thus the root-cause and primary-cause of a performance problem? Highly doubtful... Less doubtful and more likely, is that in rebuilding the table in question to another tablespace, several other important modifications to storage parameters were also made, such as increasing INITRANS, increasing FREELISTS or using ASSM, or any number of possible changes to indexes on the table, perhaps? Each of those factors is far more likely to contribute to a performance problem on transactional inserts than extent allocation, unless you ended up with something like 86,400 extents (i.e. adding one extent per second during the day), which implies an extent size less than 64Kb which is too silly to contemplate, no? > > You keep repeating yourself, telling well-known facts making it like I > oppose them. I'm not saying uniform LMT is a bad thing. All I'm saying > is it should be used if you know estimated size of tables. Forgive me, repetition seems to be an old man's disease an old man's disease an old man's disease... Ah, but then you keep mentioning this 27G table and how autoallocate helped it. If there is ever situation that screams for uniform-sized extents, it would be that one. Certainly, you know quite well how much space that thing is consuming day-by-day as it grows. So why would you need to cede control over extent sizing to autoallocate? > And even though LMTs reduces the extent allocation cost dramatically, > there are some cases where thousands of extents may cause some headache... Cases such as...? > > Even popular Oracle paper, "How to Stop Defragmenting and Start Living: > The Definitive Word on Fragmentation", warns DBAs about excessive > extents. Below is a clip from the paper: > ... > 2.1.4 Monitor and Potentially Relocate Segments Having More Than > 1024 Extents > Oracle supports an unlimited number of extents in a segment. The > performance for DML operations is largely independent of the number of > extents in the segment. However, certain DDL operations such as dropping > and truncating of segments are sensitive to the number of extents. > Performance measures for these operations have shown that a few thousand > extents can be supported by Oracle without a significant impact on > performance. A reasonable maximum has been determined to be 4096. > .. > > So if number of extents is such a benign thing, then why would Oracle > warn DBAs about it and recommend monitoring tables/indexes with more > than 4096 extents ? Please note in the first section of the paper: "This paper is targeted at an audience of experienced database administrators. It is based on Oracle7 release 7.3 and also covers new features introduced in Oracle8 version 8.0." 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. Does that "tempt" you to rethink the "theory" that number of extents don't matter again? -- http://www.freelists.org/webpage/oracle-l