My boss required me to make an estimate about an oracle database size.
This database still doesn' t exist...
He (my boss) will supply me with the number of tables and the approach number of records in each table this database will have, and then I must be able to calculate an estimated size for this database in bytes.
It is just an estimate, this size doesn't need to be precise.
Is this possible? How can I give to my boss an estimated size for this database? How do I calculate this?
You will have to estimate the average row size for each table
(Assumptions here ... I usually guess 75% fill for varchar columns...
Add all table sizes together and multiply by 2 or 3 times ... (allow for
growth) ... you will have to understand how the data will grow in order
to make a "rough guess" at the factor to multiply by ...
If you know what you will need for indexes ... size each index
select number_of_rows/(((100-pctfree)*(blocksize of database-161))
+ num_of_ind_cols_over_127_bytes))) = Blocks_Required
Again ... allow for growth by multipling by a factor...
Rollback segments ... you should always assume large rollbacks ...
you need to know how many users, type of database activity etc ...
I would assume 4 users / rollback seg ... with each segment 5-100mg
depending on the type of db activity ...
Temporary tablespace - I always start with 500mg tablespace here
unless I know the application will require a "LOT" of sorting and grouping.
Recognize that this estimate is extremely rough. One reason is that any table that has an index upon it is probably not going to have storage-pages that are anywhere close to "full." In fact they're more likely to be "about half to one-third empty."
Database management systems must constantly face the trade-off of "speed vs. space," and generally speaking they tilt in favor of speed.
Also, if the database is anticipated to be large enough that pre-estimation of space requirements is likely to be a factor, many other issues can pop into reasonable consideration. You probably need to have a RAID array. You probably should distribute parts of the database to different drives and volumes. Once again you are paying, with space and drives, for ... speed, redundancy, and so on. Thus, the more "important" an advance space-estimate is perceived to be, the less likely it is to be accurate.
Disk-drive salesman driving a Mercedes? Uh huh. This is why. ;-)