From oracle-l-bounce@freelists.org Wed Jun 16 15:36:53 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i5GKaX219674 for ; Wed, 16 Jun 2004 15:36: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 i5GKaI619635 for ; Wed, 16 Jun 2004 15:36:29 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9C1C772C38D; Wed, 16 Jun 2004 15:20:20 -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 28421-94; Wed, 16 Jun 2004 15:20:20 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 53D7972C3E3; Wed, 16 Jun 2004 15:20:19 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 16 Jun 2004 15:18:57 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id AD4AE72C231 for ; Wed, 16 Jun 2004 15:18:56 -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 28434-74 for ; Wed, 16 Jun 2004 15:18:56 -0500 (EST) Received: from mail19b.dulles19-verio.com (mail19b.dulles19-verio.com [198.170.241.3]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with SMTP id 0745B72C26D for ; Wed, 16 Jun 2004 15:18:56 -0500 (EST) Received: from www.visionchain.com (128.121.182.210) by mail19b.dulles19-verio.com (RS ver 1.0.94vs) with SMTP id 4-0554186289; Wed, 16 Jun 2004 16:04:49 -0400 (EDT) From: "Leslie Tierstein" To: Cc: Subject: RE: Lost appendix: Space Estimations for Schema Objects Date: Wed, 16 Jun 2004 16:06:54 -0400 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.6604 (9.0.2911.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 In-reply-to: <40D098AA.5030405@sun.com> X-Loop-Detect: 1 X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 2841 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Leslie.Tierstein@visionchain.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org The issue is one of scale and history: The estimated size of my extra-large objects can run very large: The largest fact table at a customer site that has been operational for two years is 48 gigs. And that customer is collecting weekly data. (It is data for a retail supplier; weekly data is summarized by week, by store, by item; Daily data still summarizes transactions, but to the day-level.) Our new customer wants to collect daily data. We're only doing this level of detail, BTW, for the main fact and summary tables. The remaining tables are relatively small and a less detailed estimate works just fine. Historically, the previous guesstimate at Oracle sizing was adapted (badly) from a SQLServer model, and it vastly overestimated the storage required. The client got really peeved when they overbought on hardware, and I'm trying to avoid a repetition of that. One cause of the bad estimate could have been that the client overestimated the weekly download volume: It's based on some percent of items being sold in some percentage of stores in some particular week. However, this being a national retailer, the distribution of items varies widely, based on both local (community) preferences and seasons. (One of the clients manufactures lawn care products, so you can imagine ...) If that's the case, I may get blamed for a bad estimate, but I can point to other sources. However, if my computations are way off, and the client spends more $$ on hardware than required, leaving them fewer $$ to spend on software and consulting/services (i.e., me), than that's not good. And if the estimates are way under, not only might they run out of storage space, but their system might be underpowered, since we base the # of processors to put in the server on the amount of data that needs to be processed and reported on. (Can't do it on # of transactions, since, this being a DW/DSS, there are very few actual users.) Leslie -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of Daniel Fink Sent: Wednesday, June 16, 2004 3:00 PM To: oracle-l@freelists.org Subject: Re: Lost appendix: Space Estimations for Schema Objects Leslie, I have one question, Why is such a level of detail needed? In the past, for each schema or database, we defined 4 sizes of objects (along the lines of autoallocate boundaries). Small - 128k (or the value of db_block_size * db_file_multiblock_read_count). This is also our baseline multiple. Medium - 1m Large - 64m Extra-Large - 256m We then reviewed the tables/indexes and "estimated" their size and put them in the appropriate tablespace (lmt uniform size). Me - So, how big do you *think* this table will be? Them - Well... Me - More than 256 megs? Them - Oh, not that large. Me - Less than 50 megs? Them - Hmm..that sounds about right. Me - Okay, that's a Large. Them - But, that means we are not using 14 megs. Me - That's okay. This gives us pre-allocated space if our estimate is wrong. And 14 megs is not a big deal. After all, how much memory do you have in your PDA? Kinds of puts it in perspective. I've used this approach very successfully in the past 5 years and find that my space wastage is less than 10% (even less on larger databases). The first time I used this approach, it took a lot of work with the development staff to set aside the fine-grained sizing process they were used to. Using this approach, we created 2 data warehouses of about 750g each (back in 8.0.4 time). The only space-related issue in the 9 months I was there was a load process that tried..and tried...and tried to process a bad input file and filled up the normally small error_log table. The next release tracked the number of errors on a particular file and stopped trying to load it after a threshold had been met. Almost all of the other databases that were supported by members in my group had an average of 1 space related failure per week. Of course, I also worked myself out of the contract...(Mogens, are you listening?) Regards, Daniel Fink Leslie Tierstein wrote: > Applying the formulas as documented in Oracle 8, and comparing the results > to an actual Oracle 8i database (a data warehouse; I'm interested only in 8 > fact tables ranging in size from 10 million to 500 million rows; the 9i > database is for a new client, and is the reason for this exercise) yields > the following: > > - Table estimates for non-partitioned tables are within 10% (uniformly lower > for the estimate than the actual) of the actual > - But the table estimates don't take into account partitioned tables, which > are farther off > - B-Tree index estimates were modified to reflect the changed size of the > ROWID; most are still within 10% deviation from the actual > - We're still looking at the bitmap indexes (determining cardinality) and > the local partitioned indexes > > More problematic is actually determining how many rows are going to be in > each fact table -- both at startup and at 6 month intervals. > > Unfortunately, we're not going to 10g any time soon, so I can't use the EM > facility Lex mentioned. > > Leslie ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------