How to size database ( Excel File Included)

• 06-05-2001, 09:17 PM
pandyar
How to find out the size of the datafile for a database consist of three tables with following specs:
1 year growth 5 Year growth
Table 1 400,000 record/year, 1 million record/5 year
Table 2 100, 000 record/year, 250, 000 records/5 year
Table 3 45/year, 60/5 year

Table1: Average Record length: 80
Table 2: Average Record length 100
Table3: Average Record length 35

I really need to understand this concept. Also I would like to understand the magic about min. extent, maxextents....

in short storage clause for the tables.

Thanks!!!

I really need help.

Thanks again!!!!
• 06-06-2001, 12:23 AM
anandharaj
hi pandyar

It is difficult to measure the exact size of the datafile for the period of time.

We can calculate approx. datafile size by taking average record length multiply by average no.of records plus add 30% additional size for the particular table. Like wise we can proceed for other tables in the tablespace for the datafile.

regards

Anandharaj
• 06-06-2001, 03:09 AM
Hi,

I've got a "magic" excell sheet that could help you in calculating the approximative size of tables (and indexes).
I could send it tou you if you indicate me your e-mail.

Bye

Fabien
• 06-06-2001, 10:58 AM
Hi, friends

I'll send the excell sheet and an explanation on how to use it by tomorrow, as I waited for a max. number of people to answer, but I am now in a hurry, (sorry for this!)
Padman, thanks for your useful help in any time, and keep going!

Fabien
• 06-06-2001, 10:59 AM
dragon99
Could I also get a copy of Execl Sheet? my email is youngz99@hotmail.com
Thanks.

Dragon
• 06-06-2001, 11:00 AM
farrokhp
• 06-06-2001, 11:08 AM
jm

Can you pls send me the excell sheet on my email id rainingday32@hotmail.com

thanks again

jm

• 06-07-2001, 04:11 PM
Unna
Can you please forward me a copy to wujee@yaoo.com.

Thanks,
Unna
• 06-08-2001, 07:28 AM
anandharaj
Hello Fabien,

I am interested in your magic Excell sheet also. Could you send a copy to

ashwin32@123india.com

anand
• 06-08-2001, 07:47 AM
hi fabien

i am interested in ur magic sheet do please send it to my mail id:sravu_m@hotmail.com

• 06-08-2001, 11:53 AM
mayse
Seems like you have been sitting on a gold mine.

Could the moderator please put this in the dbasupport.com's technical docs/tools utilities part of the site. That way we all can download it when we want and we could be sparred from being spammed with all the exposed e-mail addresses.
• 06-08-2001, 12:00 PM
sambavan
Fabin,

If you are very much intersted in helping the folks, could you please send the script to uday the super moderator.

Thank you for your entusiastic participation. Keep going. http://www.dbasupport.com/forums/

Sam
• 06-08-2001, 12:24 PM
tamilselvan
There was no magic sheet on capacity planning as you believed. It is an estimation.
Capacity planning will only work for approximately 60%. Each table in the database requires different type of storage clause. Last week I was testing a table's storage space by changing different value for INITRAN parameter. I was surprised by the outcome. The rest of storage clause parameters are not changed. Number of rows inserted is 10000. Data block size 8K.
Case 1.
INITRAN 1
Number of blocks used for 10000 rows - 155
Case 2
INITRAN 5
Number of blocks used for 10000 rows - 165
Case 3
INITRAN 10
Number of blocks used for 10000 rows - 175
In every case the same data was inserted in the table. But I got different results just by changing only one parameter.
Then you can imagine by changing PCTFREE, PCTUSED etc values, how the storage space is going to be.

Today, hardware price is cheap. I can buy 60 GB disk for the price of 6 GB Disk what I paid 3 years ago.
DO NOT WASTE MUCH TIME ON CAPACITY PLANNING.
• 06-08-2001, 12:33 PM
Good Explanation Tamilselvan
Dear Tamilselvan, 8th June 2001 22:01 hrs chennai

Good explanation.If you would have told this before like sambavan said

---------
Waaw,

The wand is buring hot

would not have happened(sambavan you are really a jovial person making us to refresh at times we all are deep into logical world. )
---------

But more than that i am hapy to see the active participation of many competeting to share and Gain Knowledge thats invaluable.

Cheers

• 06-08-2001, 01:55 PM
mayse
Tamilselvan,

Shouldn't your findings be of concern to those who deal in 10**6 records ?
I think this directly affects segment growth rates which in turn determines the backup and recovery and segment management (like index rebuilds etc...) techniques implemented by the DBA.
So any space management techniques that can be exploited in the physical d/b design should be thoroughly researched, and your findings are a perfect example of that.

It would be interesting for someone with the tools and detemination (may be it already exists) to generate excel graphs that would display the relation ship of the # of rows,block size, pctfree, pctused, initran, etc.. (other segment size parameters) on segment growth.

Am I thinking correct here ? I am new to d/b management so this kind of information goes a long way.
• 06-08-2001, 02:15 PM
tamilselvan
Always use Average Row Size obtained from DBA_TABLES or used blocks to figure out the table size.
In a transaction environment, each row size in a transaction table differs from other. As I said earlier there is no way you can calculate the exact space requirement for a table. It is only approximation.

My point is simple. Hard disk's price is cheaper. Only you need to ensure that your box has more room for adding more disks.
• 06-08-2001, 04:09 PM
bnath001
magic excel sheet
Hi Fabien,

Can you please forward me a copy to :
basam_oracle@yahoo.com

Thanks
nath

• 06-08-2001, 05:01 PM
Rekha
Hi,
Could you please send me a "magic" excell sheet that could help me in calculating the approximative size of tables (and indexes).

Thanks
• 06-09-2001, 03:53 AM
sudip
Hi!all
Do we have the "magic Sheet" here in the dba forum. Instead of poor fabien mailing it to everybody it would be better to have it somewhere in oracle Scripts.
Hope Fabien wont mind to share.
• 06-09-2001, 07:41 AM
Mahi
database size
Hi Fabien,
Could u plz send me that excel sheet . My email address is maheshkumarv@yahoo.com.
Thanks
Mahesh
• 06-09-2001, 09:12 AM
Hi, everybody

Don't worry Sudip, I don't mind to share: real experience comes from knowledge sharing, and this site is a really magnific example!

I'll send the sheet and sql script to uday on next week (since I'm now finally resting for the week-end... :) ). Hope it will be one day present on the site with the explanation on how to use it...
For this, I'd like too to answer to tamilsevan: the purpose of this excell worsheet is to HELP in sizing databases in a general way:
For "junior" dbas, -anyone of us started one day, isn't it?- it is not very simple to size a table, index, and tablespace...
For experienced dbas: try to estimate how much time you will gain on sizing a hundred-tables with associated indexes database whith a 'cut & paste'...

I work for a huge tyres manufacturer located in France (did you guess?), and 'own' about 200 7/8/8i oracle databases on NT and Unix, all around the world, from the smallest one (150Mo, one table...) , to the most complex ones (huge 1.5To datawarehouse linked with other databases, databases with hundreds of tables & indexes, 24/24 7/7 availability, etc...).I'm in charge of those databases, from the architecture to the tuning and availability, and support for developpers...
The worksheet gives a good estimation for sizing "generic" databases, it helped me in sizing the huge majority of 'my' databases and appears to be very, very helful and precise when filled the good way.
I don't by disks buy myself, but when I am asked to give to the server's administrators an estimation on the disk space (and processor/memory use, but that's another subject) my database will use, it is always a NECESSITY to give a good one and to don't overestimate it, as I am not alone on these servers!
As you said, "don't waste time in sizing": that's EXACTLY the purpose of the excell sheet. (I'd like to add too, that disk space is still awfully expensive on unix machines... :))
The few people who already received my mail will have noticed that I wrote about maxtrans and initrans (which are present in the worksheet)...

For every people who asked me to send them the excell sheet, I'll continue on next week too. I already started to to so, but had no much time to complete to you all! I also mispelled a few adresses (nobody's perfect...), and I seem to have some difficulties to reach "123india" (sorry for you, guys...).

Cheers, and keep smiling!
(no offense)

Fabien

• 06-11-2001, 04:17 AM
Cjbk
Excel Sheet
Hello Fabien,

I am also very interested in your Excel Sheet.

If anyoone who has got Fabien 's Excel sheet, please kindly
share it with me.. Maybe too many requests will bother Fabien
a lot..

Thank you very much !!!!

chizeta@sinaman.com

Cjbk
• 06-11-2001, 05:03 AM
shawish_sababa

my email is shawish_sababa@hotmail.coml
• 06-11-2001, 08:41 AM
r_sonakiya
I wana look why every one needs your magical excel sheet.Could you please send me at r_sonakiya@hotmail.com

• 06-11-2001, 09:33 AM
jm

raining32@hotmail.com

thanks
• 06-12-2001, 07:32 AM
anandharaj
Hello Fabien,

I am interested in your magic Excell sheet also. Could you send a copy to

ashwin32@123india.com

• 06-12-2001, 08:43 AM
PSoni
Hi,
Could I also request for for that magic sheet
my email-id is
oracletst@yahoo.com

PSoni
• 06-12-2001, 10:49 AM
MSaeed
Fabien:

ora_dba2002@yahoo.com

Thanks
Keep up the good work.

• 06-12-2001, 11:00 AM
tomate
can i get the excel sheet for estimating table size too.
my email t.schmidt@traian.de

Regards
Thomas
• 06-12-2001, 11:27 AM
m_d_marian
Is there a way to publicate this "magic spreadsheet" on this forum? This tread will never be closed otherways, and this will become a full time job for you. BTW why should I miss doing magic? Please send it to me also djcarving@hotmail.com
• 06-29-2001, 12:26 PM
pando
right guys, this EXCEL sheets is available for download ok, but unfortunately they are in Spanish (there are two versions, one for Oracle 7 and one for Oracle 8x)

http://www.oraes.com/Trucos/Inicio.html

lucky me I can read Spanish :D
• 07-03-2001, 12:55 PM
pando
F = number of column < 250 bytes
V = number of column > 250 bytes
D = sum (in bytes) of all columns

U ===> if 1 then nonunique index, if 0 then unique index

FILAS = number of rows
EspLibre = Total Freespace
EspDisp = Available space

• 07-23-2001, 09:57 PM
uday
Finally the Excel Sheet is here...
Geez... I cant delete these threads anymore...