| 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: How to calculate table size in 8i
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 Wed Jul 10 2002 - 02:18: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
>
> --- "CHAN Chor Ling Catherine (CSC)"
> <clchan_at_nie.edu.sg> wrote:
> > Hi Gurus,
> >
> > I found an article in metalink 105765.1 "How to
> > Determine Approximate Hard
> > Drive Space Needed for a Specific Table". The
> > formula for disk space is
> > simply multiplying the average row length (by
> > analyzing the table) * the
> > number of rows in the table. It's very different
> > from Metalink 10640.1
> > "Extent and Block Space Calculation and Usage in V7
> > Database" where it takes
> > the block header etc in considerations but of
> > course, article 10640.1 is for
> > Version 7.
> >
> > How do you gurus calculate table space in Version 8
> > ? Please advise.
> > Thanks.
> >
> > Regds,
> > New Bee
> >
> >
> > Doc ID </help/usaeng/Search/search.html> :
> > Note:105765.1 Content
> > Type: TEXT/PLAIN
> > Specific Table Creation Date: 18-APR-2000
> > Type: PROBLEM Last Revision Date: 26-DEC-2000
> > Status: PUBLISHED
> > Problem Description
> > -------------------
> > How can you determine how much disk space is needed
> > for a table?
> >
> >
> > Solution Description
> > --------------------
> > You can use SQL to determine how much space is
> > needed for the table based
> > upon the average row length.
> >
> > 1. compute statistics about this table:
> >
> > analyze CEUSER.CE_STATEMENT_LINES
> > compute statistics;
> >
> > Now determine the average row length in bytes:
> >
> > select avg_row_len
> > from dba_tables
> > where table_name=' CE_STATEMENT_LINES';
> >
> > AVG_ROW_LEN
> > ===============
> > 98
> >
> >
> > 2. Multiple the average row length in bytes by the
> > number of rows you
> > believe
> > you will need:
> >
> > 98 (bytes) x 10000 records = 980000 bytes needed
> >
> > References
> > ----------
> > For more information on the ANALYSE command, you may
> > wish to refer to:
> >
> > Oracle8 SQL Reference Release 8.0, Part No.A58225-01
> >
> > Also:
> >
> > Oracle8i SQL Reference Release 8.1.5, Part Number:
> > A67779-01
> > .
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: CHAN Chor Ling Catherine (CSC)
> > INET: clchan_at_nie.edu.sg
> >
> > Fat City Network Services -- (858) 538-5051 FAX:
> > (858) 538-5051
> > San Diego, California -- Public Internet
> > access / Mailing Lists
> >
> --------------------------------------------------------------------
> > 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).
>
>
> __________________________________________________
> Do You Yahoo!?
> Sign up for SBC Yahoo! Dial - First Month Free
> http://sbc.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Jack Silvey
> INET: jack_silvey_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> 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 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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).
 |
 |