HI,
We are trying to move to type II storage Area. Do I need to dedicate a single schema area for a highly used table?
Or can I club two or three adjacent tables along with highly used table? Which method can help maximise performance?
Please help.
I am using OE 10.2B Sp06.
From http://knowledgebase.progress.com/articles/Article/P81853
Also, with Type II areas, several tables could be in the same Type II Storage Area and get much of the same effect as each having a separate area. The "rules" for which tables, to group in the same Storage Area are much the same as in Type I Areas, the factor which contributes most significantly, is the "records per block" (rpb) that is set for that Storage Area. This can be estimated by running a "PROUTIL -C TABANALYS" report then dividing the:
(database-blocksize - 100)/mean_record_size
and then rounding up the result to the rpb values available (1,2,4,8,16,32,64,128,256).
Of course, you could put an ultra-active table in its own area for the purpose of putting this area's files on an independant HD for maximum performance (i.e. sole access to this HD will be for this table).
Also from http://knowledgebase.progress.com/articles/Article/P81745:
- Plac[e] all indexes that belong to a spefic table in a separate Storage Area from the Storage area that will hold the data of this same table.
Again, this area could be on an independant HD for maximum throughput.
In general, make sure that all tables are in table-only areas, all indexes are in index-only areas, and no application storage objects are left in the schema area.
As stated, choose your RPB values based on mean record size. You will also need to choose appropriate blocks-per-cluster settings for each area. For fast-growing tables, the largest (512) will minimize extend operations in your variable extents. For small/static tables, and for indexes, use 8.
Since you will be doing a dump and load to implement the new structure, make sure you read the SP06 release notes as (potential) index rebuild performance has changed significantly in the 10.2B timeframe. Get to know the new and changed idxbuild parameters as they can make a world of difference to your total downtime. And if you can install SP07 before the rebuild, even better.
Thank you David and Rob for the guidance.
You may find this helpful as well: