RE: how to calculate table size
I've been using this query for both normal and empty tables and works so far.
For those tables with no data in them (or that have suffered no deletion) you
can ommit the second query since the table should not have any emptied block. Emptied
blocks are those who have been occupied by data from the table but that have been
deleted; for these, Oracle marks them as deleted but are still asigned to a table. I
do not know if you want to consider this free space as part of the table or not.
There is a way to deallocate unused space to a table that has been previously
used. You must use 'alter table ... deallocate' for that. You have explanations on
this in the manual, check:
http://download-west.oracle.com/docs/cd/A91202_01/901_doc/server.901/a90125/statements_32.htm#2080417
I am sending this mail to the list as well, I am a learner and do not consider
myself an expert, maybe someone else can join and comment something.
-Mensaje original-
De: Basavaraja, Ravindra [mailto:[EMAIL PROTECTED]
Enviado el: martes, 01 de abril de 2003 20:41
CC: '[EMAIL PROTECTED]'
Asunto: RE: how to calculate table size
Hi Fermin,
Thanks for your reply.
I am estimating the growth of database tables for a new database and many tables don't
have any data.
Can I still use the same queries to estimate the size of the tables or do you have
anything different?
Thanks
-Original Message-
Sent: Tuesday, April 01, 2003 4:24 AM
To: Multiple recipients of list ORACLE-L
Hi Ravindra,
Use the following, supposing your db_block_size is 2048 (change as appropiate).
SELECT segment_type, segment_name,BLOCKS*2048/1024 "Kb"
FROM DBA_SEGMENTS
WHERE OWNER=UPPER('') AND SEGMENT_NAME = UPPER('');
You should substract emptied blocks from this table, using:
ANALYZE TABLE . ESTIMATE STATISTICS;
SELECT TABLE_NAME, EMPTY_BLOCKS*2048/1024 "Kb"
FROM DBA_TABLES
WHERE OWNER=UPPER('') AND TABLE_NAME = UPPER('');
This will give you how many kb are occupied by empty blocks, so substract this
amount from the prior result.
Hope this helps.
-Mensaje original-
De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Basavaraja,
Ravindra
Enviado el: martes, 01 de abril de 2003 1:24
Para: Multiple recipients of list ORACLE-L
Asunto: how to calculate table size
Hi,
Anyone having any formula to calculate table size?Basically to estimate the growth of
table over a peroid of time.
I have the row_size,db_block_size.How do i get the table size.
Thanks
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Basavaraja, Ravindra
INET: [EMAIL PROTECTED]
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: [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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Fermin Bernaus Berraondo
INET: [EMAIL PROTECTED]
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: [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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Fermin Bernaus Berraondo
INET: [EMAIL PROTECTED]
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: [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
Hi Ravindra,
Use the following, supposing your db_block_size is 2048 (change as appropiate).
SELECT segment_type, segment_name,BLOCKS*2048/1024 "Kb"
FROM DBA_SEGMENTS
WHERE OWNER=UPPER('') AND SEGMENT_NAME = UPPER('');
You should substract emptied blocks from this table, using:
ANALYZE TABLE . ESTIMATE STATISTICS;
SELECT TABLE_NAME, EMPTY_BLOCKS*2048/1024 "Kb"
FROM DBA_TABLES
WHERE OWNER=UPPER('') AND TABLE_NAME = UPPER('');
This will give you how many kb are occupied by empty blocks, so substract this
amount from the prior result.
Hope this helps.
-Mensaje original-
De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Basavaraja,
Ravindra
Enviado el: martes, 01 de abril de 2003 1:24
Para: Multiple recipients of list ORACLE-L
Asunto: how to calculate table size
Hi,
Anyone having any formula to calculate table size?Basically to estimate the growth of
table over a peroid of time.
I have the row_size,db_block_size.How do i get the table size.
Thanks
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Basavaraja, Ravindra
INET: [EMAIL PROTECTED]
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: [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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Fermin Bernaus Berraondo
INET: [EMAIL PROTECTED]
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: [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;> > >
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
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).
