Help help how to calculate allocation for Storage Areas

[dopena]

Hi,

Any body can help me how to calculate how much space I should allocate for Data Area and Index Area.
Here is the situation: I have a db with 40Gig size and it got only one area right now called Schema Area. Both Data and Index goes on Schema Area. My goal is to Dump and load the db to
a new empty db that has two storage area called Data Area and Index Area so that all data goes to Data Area and Index goes to Index Area. I've done this before but can't remember how I did it.

Thanks.

 

[warba]

You should be able to use:
proutil dbname -C dbanalys

and look at Record block summary and Index block summary to get an idea of the space used for records and indices.

 

[toby.Harman]

Warren's suggestion is correct.

I would like to offer another suggestion which I hope you will consider if the purpose of this re-arrangement is to achieve better performance.

I would like to suggest that you consider using multiple Index areas. An index with a depth of 3 is read 3 times as often as the data it refers to. While the anchor block and a great many of the 1st level of the B-Tree will be cached in memory, busy indices will account for at least 50% of the database reads.

Add to that the index insert operations where multiple index blocks get updated and this becomes even more significant.

I would investigate the use of the indices and try to split the two busiest indices away from each other. This will give benefits in performance which should be noticable.

 

[dopena]

Thanks for all you help. I did the dump and load over the weekend
as a result the performance of the database is way better now.

 

[Peter de Jong]

A regulair index rebuild takes account for 80% of the performance improvement of a full dump and load and is much easier to perform.

So an index rebuild a month will keep your database fast.

Peter

Originally posted by dopena
Thanks for all you help. I did the dump and load over the weekend
as a result the performance of the database is way better now.