Re: Database Size Estimation

From: Jan Gelbrich <j_gelbrich_at_westfalen-blatt.de>
Date: Fri, 14 Nov 2003 12:32:31 +0100
Message-ID: <bp2els$1ijl8h$1@ID-152732.news.uni-berlin.de>

"Vijays" <srinivasanvijay_2000_at_yahoo.com> schrieb im Newsbeitrag news:fe3d44d1.0311140052.54dc0530_at_posting.google.com...
> Hi,
>
> I have been asked to do the database size estimation. I have the the
> list of tables, indexes and average number of rows expected at this
> point and also for the next 5 years...With that how can i estimate the
> database size. This the first time i am exposed to do such thing. Any
> help is highly appreciated.
>
> I am hoping to get a tool or some excel sheet where i key in the
> respective information to get the sizing !! is something possible like
> that ??
>
> Rgds

Here are some values to start with:
all values are in bytes required

overhead for each row: 3

colums:

1 varchar2                       = 1 + count(characters), e.g. "MyName" = 6

chars = 1+6 = 7.
1 varchar2 over 255 char = 3 + count(characters)

better take and average and not maximal fill of the columns, that is e.g.: varchar2(100) declared, but only (50) used in reality

1 number                 = 1 + count(digits)/2

1 date                   = 7

1 row:                   = 3 + sum(column values see above)
-------------
1 table:                 sum(row_values)
-------------
1-col-index:           16 + column value see above
n-col-index:           16 + sum(column values see above)


>>

1 row - n indexes      sum(index_values)

all indexes for table: sum(index_values) * table_num_rows note: a multi-indexed table can have more storage for the indexes than for the table itself !

and finally
the database = sum(table_values) + sum(index_values)

+ x% due to storage parameter PCTFREE; the value also depends on

init parameter db_block_size

I think it should be easy for You to set up Your own Excel sheet for Your purposes.
The thing is only that these values are hard to find - no doco. I had to search a while at my time ...

hth, Jan Received on Fri Nov 14 2003 - 05:32:31 CST