Re: Lost appendix: Space Estimations for Schema Objects

  • From: Daniel Fink <Daniel.Fink@xxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Wed, 16 Jun 2004 12:59:54 -0600

<jumping into the reorg debate quagmire>

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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 06-2004