Re: Estimating Table-Size

On Sun, 25 Jan 2004 13:02:17 +0100, "Oliver Braun" <oliverb_at_online.de> 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
>

You don't take into account transaction slots, row overhead and column overhead.

--
Sybrand Bakker, Senior Oracle DBA