Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Lost appendix: Space Estimations for Schema Objects

RE: Lost appendix: Space Estimations for Schema Objects

From: Leslie Tierstein <>
Date: Wed, 16 Jun 2004 16:06:54 -0400
Message-ID: <>

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.)


-----Original Message-----
[]On Behalf Of Daniel Fink Sent: Wednesday, June 16, 2004 3:00 PM
Subject: Re: Lost appendix: Space Estimations for Schema Objects


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?)

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
> 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
> for the estimate than the actual) of the actual
> - But the table estimates don't take into account partitioned tables,
> 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:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at

Please see the official ORACLE-L FAQ:
To unsubscribe send email to:
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Wed Jun 16 2004 - 15:36:53 CDT

Original text of this message