| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Table & Index sizing utility
Nice website, but the basic flaw with such formulas is what to input into the field marked "average row size"...
If you know the "average row size", then you must already have a sample of data present in a table somewhere. After all, without actual data already in an Oracle table, you could not possibly guess at "average row size" because you would have to have ANALYZEd the table to get AVG_ROW_LEN from DBA_TABLES.
Almost all datatypes in Oracle are variable-length; there is little correlation between the declared scale of a column (i.e. VARCHAR2(50), NUMBER(10,2), etc) and the actual space consumed. Therefore, the data values themselves strongly influence the amount of space consumed. For example, a NUMBER value of 999,999.99 consumes 5 bytes of storage while a value of 1,000,000.00 consumes only 2 bytes; the SQL function DUMP() will illustrate this. A difference of only 0.01 in data value but 250% difference in storage consumed. This is not something that can be simply guessed at, if accuracy is part of the objective...
The point is that table sizes cannot be predicted without a sample of actual data. And if you have that sample of actual data, you can just load it into an Oracle table and extrapolate the sizing far more accurately than any formula. For example, if you have 10,000 rows of sample data and it consumes 300 database blocks of space, then 10,000,000 rows of similar data will almost certainly consume 300,000 blocks of space.
At best, formulas may illustrate an understanding of the components of a database block. But it's that "average row size" question that'll kill ya...
Here is a re-post from a reply I made on the same topic back on 10-July...
Regarding this question, I like to use the following story... One day, the king ordered that a census be taken. He wanted to know how much money he could expect to collect in taxes.
He called together all the dukes and duchesses in the kingdom and ordered them each to count the subjects in their lands.
Each of the dukes and duchesses went home and called together all of their barons and baronesses, ordering them to count each subject in their lands.
The barons went home and called together all of the counts and countesses, ordering them to count each subject in their lands.
...and so on, and so on, and so on, until the orders arrived at the local village level...
In each village, the local constable, bearing orders to count the heads of all subjects in the village, went into the pub. Ordering a beer, each constable wrote down the number "50" on his census form, and handed the form back to his supervisor. Who tabulated the results and returned the results to his superior. And so on...
The numbers were tabulated accurately and the king had his census. Was the census accurate? After all, the process was accurate and reliable and well thought out and logical...
Why is this story relevant? The earlier, very detailed formulas from the Oracle7 note in MetaLink was utterly accurate in how it described how space was allocated in database blocks, but completely irrelevant in actual practice. The reason is that those formulas had at their core a guess: "avg(length(column-name))". In essence, a guess. Therefore, as accurate as the king's census...
--- In short, the method in the latter is more accurate. It is extrapolation based on a sample of data, not guesswork. Almost every datatype in Oracle is variable-length (i.e. NUMBER, VARCHAR2, NVARCHAR2, RAW, LONG, LOB), while the DATE, CHAR, and NCHAR datatypes are fixed-length. With variable-length datatypes, formulas just don't work well... Hope this helps... ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, July 09, 2002 11:03 PMReceived on Thu Oct 24 2002 - 17:54:18 CDT
> New,
>
> No guru here, but that is the way that I do it -
> average row size vs estimated number of rows * fudge
> factor of 30% plus a little room for underestimation.
> Also, don't forget room for indexes.
>
> I have found this method to be quite successful, since
> the major hurdle is figuring out how many rows the
> customers will have. Often, even they don't know for
> sure, so you have to help them not hurt themselves by
> being liberal in your space estimations.
>
> Remember, if you overestimate, no one will know except
> you and other dbas, since no one really cares after as
> long as the system runs well in production. I have had
> systems that were 50 megs sitting on 50 gig Veritas
> clusters and everone was happy.
>
> Underestimate space, though, and say hello to long
> periods of data shuffling and constant firefighting.
>
> As the old saying goes, "Goofups are forever."
>
> hth,
>
> Jack
-------------------------------------------------------------------------------- ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, October 24, 2002 3:18 PM
>
> Rick,
>
> For the first question, if you are looking to estimate the size of the
> table, check out http://www.suhas.com/tsize.html
>
> Raj
>
>
>
>
>
> Rick_Cale_at_team
> health.com To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Sent by: cc:
> root_at_fatcity.c Subject: Table & Index sizing utility
> om
>
>
> October 24,
> 2002 04:35 PM
> Please respond
> to ORACLE-L
>
>
>
>
>
>
> Hi DBAs,
>
> Does anyone have a table & index sizing utility program/script they can
> share?
> Also where can I find out exactly how much storage Oracle allocates for
> every datatype? I checked the concepts manual but could not
> find it.
>
> Thanks
> Rick
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: Rajesh.Rao_at_jpmchase.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Gorman INET: Tim_at_SageLogix.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
 |
 |