RE: how to calculate table size

2003-04-01 Thread Fermin Bernaus Berraondo

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

2003-04-01 Thread Fermin Bernaus Berraondo

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

2002-07-09 Thread Tim Gorman



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

2002-07-09 Thread Stephane Faroult

"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

2002-07-09 Thread Jack Silvey

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).