How to size database ( Excel File Included)

[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.

[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.

Sam

[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.

[padmam]

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

Padmam

[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.

[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.

[bnath001]

Hi Fabien,

I am also interested in your magic excell spreadsheet.
Can you please forward me a copy to :
basam_oracle@yahoo.com

Thanks
nath

[Rekha]

Hi,
Could you please send me a "magic" excell sheet that could help me in calculating the approximative size of tables (and indexes).
My e-mail address: rekha20@usa.net

Thanks

[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.

[Mahi]

Hi Fabien,
Could u plz send me that excel sheet . My email address is maheshkumarv@yahoo.com.
Thanks
Mahesh