Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Estimating Table-Size

Re: Estimating Table-Size

From: Frank van Bortel <fvanbortel_at_netscape.net>
Date: Sun, 25 Jan 2004 15:24:17 +0100
Message-ID: <bv0ivh$kk8$1@news1.tilbu1.nb.home.nl>


Oliver Braun wrote:

> Hi,
>
> I try to estimate table-size in MB with PL/SQL, but I have a great
> difference between my results and the size-calculation of the programm
> TOAD. If anyone is interested I can send the whole procedure.
>
> The treatment is like this:
>
> The procedure has as input-parameters table_name, owner and the number of
> table_rows.
>
> 1) Get PCT-FREE from SYS.DBA_TABLES (i.e. 10%)
> 2) Get Block-Size from SYS.v$parameter (i.e. 8192bytes)
> 3) Analyse Table-rows (SYS.DBA_TAB_COLUMNS):
> Count table-rows "<250bytes" and ">=250bytes"
> (column-type "NUMBER": column-bytes = data_precision + data_scale
> else : column-bytes = data_length)
>
> 4) Average row size =
> SELECT AVG(VSIZE(Column1)+1 + VSIZE(Column2)+1 ...)
>
> 5) AVG-R-SIZE = AVG-R-SIZE +3 + "<250bytes" + (3* ">=250bytes")
>
> 6) x = Block_Size / AVG-R-SIZE
>
> 7) Round x down to the next integer:
> y = Block_Size - ((Block_Size - (52+(4*x))) * PCT_FREE
>
> 8) Table_Size = (number_table_rows / x) * Block_Size
>
> 9) Calculate Table_Size in MB or KB
>
> What is wrong?
>
> O. Braun
>
>

Check the differences between pct_used and pct_free. Guess the difference between your and the other program is a bit over 2 times (yours being the smaller)?

Oh - remember: you're doing an estimate. My estimates usually end with "This is only an estimate - no guarantees" ;-)

-- 

Regards,
Frank van Bortel
Received on Sun Jan 25 2004 - 08:24:17 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US