From oracle-l-bounce@freelists.org Tue Apr 26 14:56:23 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j3QJuN3B031813 for ; Tue, 26 Apr 2005 14:56:23 -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 j3QJuN4Z031809 for ; Tue, 26 Apr 2005 14:56:23 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5DC96187016; Tue, 26 Apr 2005 13:53:57 -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 12385-01; Tue, 26 Apr 2005 13:53:57 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C3AE51869A7; Tue, 26 Apr 2005 13:53:56 -0500 (EST) User-Agent: Microsoft-Entourage/10.1.4.030702.0 Date: Tue, 26 Apr 2005 12:51:36 -0600 Subject: Re: LOCALLY MANAGED EXTENT PERFORMANCE From: Tim Gorman To: Oracle-L Message-ID: In-Reply-To: <10644b9e05042611192a971abc@mail.gmail.com> Mime-version: 1.0 Content-type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 8bit X-archive-position: 19044 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 >>> Assuming autoextention on the datafiles, why are you worrying about >>> how many extents until you run out of disk space? It seems to me that >>> it now makes more sense to keep on eye on disk space itself. Unless >>> you are trying to run your mountpoints to 99% full or you have small >>> mountpoints, ask the sysadmins to set a warning at X% and an alarm at >>> Y%. >> >> Autoextension can *postpone* the need for this kind of monitoring, but does >> not eliminate it. Think it through. > > My thinking goes something like this: > Autoextention allows the database to happily add extents, and hence > grow files, at need. At some point this file growth will eat up my > disk space. Therefore, I should monitor my disk space. Not saying you shouldn't monitor file-system disk space too; we're in agreement on that... > > It appears to me that counting the number of extents able to be > allocated and watching the mount point free space tries to achieve the > same thing through different means. I also think that given the > current technology mix of large mount points and Oracle's LMT and > ATTM, monitoring mountpoints is the way to go. I think that because > it is much easier to find a mountpoint's free space than it is to > reverse-engineer ATTM's allocation algorithm (especially when Oracle > can change it at will). Just because features exist doesn't make them good ideas for everything... For illustration, let's step away from the discussion of managing space within the database, and think about backups and restores... If you use autoextension, what will you end up with in terms of datafile sizes? Obviously, a mish-mash of odd-sized files. With some really really big files that are really really busy. Anyway, bearing in mind that backup/restore utilities typically assign one thread of execution to copy each file to or from backup media, what can happen to the times of backup/restore? With only one thread of execution operating on each file, the total time to backup (and more importantly, restore) will be controlled by the amount of time to copy the largest file. Which do you think is probably faster: sixteen threads of execution restoring sixteen 8G files, or one thread of execution restoring one 144G file? Also, just because file-systems can *accommodate* huge files, does it make sense to allow huge files to happen? To understand the question better, what are the advantages of partitioning really large tables within the database? Why should datafiles be treated differently? For illustration, what if your company changes storage vendors? Now, you have to grow onto new storage or -- even worse -- migrate to new storage? How wonderful will a 10-Pbyte file be in that situation? > > Further, let's take autoextention out of the picture. Even in a > fixed-size tablespace with growing objects, I believe it is better to > measure free space as a percentage instead of trying to count extents. > Counting extents will give you an exact number but a percent > measurement, though approximate, is good enough for trend analysis. > That is, if you need the granularity provided by extents versus a > percentage, you are already in a world of hurt. Oh, we measure free space percentage as well -- that's just a lot easier to do and not the main topic of the thread. We monitor both percentage of free space within tablespaces as well as ability to perform "N" extensions. After all, either one can bite you very very quickly, and usually at night or on weekends... > > Actually, percent free is fine but sometimes absolute numbers are just > as good or preferred. "Let's see, 5GB growth/day and 20GB disk free. > I knew I forgot something for last quarter's budget." > >> It's not a good idea to assume autoextension (or even extensibility) in all >> scenarios, whether at the datafile level or at the file-system level. > > I'll agree with that. Lo many years ago I worked at a shop where disk > space was always tight and mount points were very small and numerous. > We counted extents and had to respond to automated pages quickly in > order to keep from running out of space. Ug. That's what we've automated. That's what this thread is about, because "autoallocate" makes the automation rather impossible. "Ug" is right, if you can't automate it. > > However, I think in a strong majority of shops today disk free space > monitoring is perfectly fine and much easier to do. > > Finally, you said: >> Think it through. > I'd love to hear how you come to a different conclusion. File management within file-systems is no different than segment management within tablespaces. Uniform-sized objects are easier to manage over time than odd-sized objects. Plain and simple. That's what I meant by "think it through". The discussion about backup/restore (above) is one such consideration... I use the analogy of tiling my bathroom. I do tiling because I have to, not because I want to. If I don't do it, then things get wet and rot, which is a bad thing, even for someone with my lax hygiene standards. So, I use regular square tile. Nothing fancy or artistic. Any color is fine, as long as it is white. Dealing with odd corners, fitting in around the throne and sink, are the only complications. Square, uniform-sized tiles. Perhaps some odd-sizes to fit in odd places, but the basic units are uniform. This analogy can be stretched a surprising distance. The rules that work within the database work for the same reason outside the database. -Tim -- http://www.freelists.org/webpage/oracle-l