Re: How to calculate table size in 8i
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)" <[EMAIL PROTECTED]> 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 : > Note:105765.1 Content > Type: TEXT/PLAIN > Specific TableCreation 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 1 records = 98 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: How to calculate table size in 8i
"CHAN Chor Ling Catherine (CSC)" 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 > Catherine, IMHO having a *rough* idea of the size of a table, and more precisely about its rate of growth is more than enough. AFAIK the block structure, for regular, heap organized tables at least, has not changed much since V7 so the V7 recipes still hold. If you have data to analyze, it's probably much easier to have a look at 'blocks', compute how many rows you have per block on average, and then derive the size needed in some distant future. If you have no significant data to talk off, you can roughly consider that the space available in a block is the block size minus 100 to 150 bytes of header, from which you must take PCTFREE off. This gives you a number of bytes ready to store data, which you can divide by an estimate of your row length plus 5 bytes of row overhead to get an approximate number of rows per block. Personally, I don't find this exercise very interesting. You have so many incertainties at all levels (what is the *average* length of this VARCHAR2(500) or number column?), not least the number of lines expected (I have seen estimates wrong by 60%) that I find it safer to label tables 'small', 'medium', 'big', 'huge', have enough disks (do not forget 60% for indexes, rollback segments and comfortable temp space) and then, at least in the beginnings, check whether everything goes as expected. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
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 - From: "Jack Silvey" <[EMAIL PROTECTED]> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, July 09, 2002 11:03 PM Subject: Re: How to calculate table size in 8i > 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)"> <[EMAIL PROTECTED]> 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 : > > 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;> > >
