From oracle-l-bounce@freelists.org Wed Oct 13 02:55:43 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i9D7thW32722 for ; Wed, 13 Oct 2004 02:55:43 -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 i9D7thI32717 for ; Wed, 13 Oct 2004 02:55:43 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7952672C68A; Wed, 13 Oct 2004 03:01:50 -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 25151-38; Wed, 13 Oct 2004 03:01:50 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E15EA72C667; Wed, 13 Oct 2004 03:01:49 -0500 (EST) Message-ID: <7765c897041013010058e279f1@mail.gmail.com> Date: Wed, 13 Oct 2004 09:00:13 +0100 From: Niall Litchfield To: eyen@sonypictures.com Subject: Re: Number of extents . . . does it matter? Cc: oracle-l@freelists.org In-Reply-To: <32EFAEA4102B7041842430B3B3938573054F1A41@sl-mail2a.spde.net> Mime-Version: 1.0 Content-type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 8bit References: <32EFAEA4102B7041842430B3B3938573054F1A41@sl-mail2a.spde.net> X-archive-position: 11005 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: niall.litchfield@gmail.com Precedence: normal Reply-To: niall.litchfield@gmail.com X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Comments in-line On Tue, 12 Oct 2004 20:32:17 -0700, Yen, Eric wrote: > I am working with an Oracle8i database. > Yes I should upgrade and we have plans to in the near future we are > vendor locked right now. > We are planning a ReOrg and I am wondering if the number of extents > really makes a difference? My stock reaction to this would be no - Mark has outlined why. Certainly I'd be looking for some sort of proof that a) it made a difference. and b) if the reorg is simply to resize objects that the benefit was greater than the cost of the reorg. I think I have done 1 reorg since 1999 - and that was because of vendor upgrade script that moved objects from their existing tablespaces to a default tablespace (grrr). > For example a 500MB table with Initial Extent of 25MB and Next Extent of > 25MB would have 20 extents. > Would changing the Initial and Next Extent to 50MB and having 10 extents > increase performance? Now this statement worries me as it implies, but does not say, that you are considering having different sized extents in the same tablespace. This is a very good way to ensure employment as it will guarantee that you have to monitor free space fragmentation. It isn't the smartest strategy in the world though. Apologies if this is just a misplaced rant. -- Niall Litchfield Oracle DBA http://www.niall.litchfield.dial.pipex.com P.S. There is one addendum to mark's list of when a higher rate of extent aquisition can be harmful to performance and that is when you use quotas. I don't *believe* the effect to be significant though. -- http://www.freelists.org/webpage/oracle-l