Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Estimating Table-Size
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 BortelReceived on Sun Jan 25 2004 - 08:24:17 CST