Hi,

I'm moving to Oracle 9.2 and I'm switching to locally managed extent management but have a few questions.

I read in an earlier thread that the recommended approach is to use different tablespaces for tables with different extent sizes.

I have about 200 tables that will require a non-default extent size. Will all the lines in my ora_storage not give me a serieus performance penalty? I will have 400 lines in there since I have my data and index separated in to different tablespaces to spread them out over separate controllers.

I would create 3 or 4 tablespaces for these 200 tables and 1 more for the rest of the tables that are all small. Should I use Uniform Allocation with a small extent size for this last one as well and what is the recommended size (1MB?) or should I configure this tablespace with Automatic Allocation?

What is an 'ok' number of tablespaces, is there a performance penalty, what is too many? With my separate data and index I would end up with at least 10 tablespaces.

Would I use the basic 'out of the box' ora_storage without any additional storage parameters?

Thanks a lot for your help. Any hints and tips are much appreciated.

Ronald.

Hi Ronald

You can create a file called "ora_storage2". Baan will read from
that for the access method (0214) for your tables.

It would just have 2 lines

*:*:T:group:0214::
*:*:I:group:0214::

Then you can have as many lines as you'd like in your ora_storage file, as baan will only use that when creating a table.

Dave

Hi Ronald,

you are using Oracle 9.2. OK.
You need only 1 TBS for Data and 1 TBS for Indices. Use the Option 'automatic extend management' = yes AND 'automatic segment management' = Yes and it works well. No Reason to use seperat TBS with different extent allocation, Oracle calculates the extend size automatic.

Martin

but for online-backups (one tbs per comp)
it makes sence to have more than one tbs

@rochus

that was not the question.

No Reason to use seperat TBS with different extent allocation

martin

sorry

One other thing to consider....
If you're trying to distribute IO across multiple disks / i/o channles it won't be as easy with 1 tablespace for data and 1 for indexes.

sure you can add datafiles that span these disks and channles, but how do you make sure that table X stayes within that datafile?

So there is something to be said for multiple tablespaces. simply for the control you gain.

Dave

Thanks for all your comments.
One more question though, if I leave the extent size up to Oracle I understand that it will increase the extent size based on the size of the table. However the maximum extent size that oracle uses is only 10MB?
I have some tables where the index is over 10GB and that would create quite a few extents, impacting performance.
Would this not support the use of tablespaces with uniform extent size?
Thanks again.
Ronald.