Multiple vs 1 Tablespace

[Former Member]

We are in the process of building our prod environment and our databases are going to be very Big, around 5-6TB. So far we have been using the default SAP implementation and we have been putting all the objects in either PSAPSR3 or PSAPSR3700 tablespace. Now we are almost ready to build the Prod and I was wondering if it would be beneficial to go with the traditional Tablespace layout by SAP where you have multiple tablespaces or should we just stick to one large tablespace..Please suggest!

[Former Member]

Hi Anup,

I recommend you to go for a third possibility. Start with the new tablespace layout and extract the big tables, may be groups of tables, out of the PSAPSR3 tablespace. Extract the tables as described in note #666061.

The reason to split the big tablespace into smaller ones is that it allows you to restore only parts of the database in case of a logical problem. You're then be able to restore only the necessary tablespaces to solve your problem which usually saves time and resources.

From a performance perspective there is no reason to split the tablespaces apart if you run your database on an up to date I/O subsystem. This only makes sense if you have disks with different performance and you know that a limited amount of tables need this extra performance. But normally I would use the fastest disks for online logs.

If you would go for the old layout you wont win something because you'll end up with four or six big tablespaces and 20 small ones.

You should keep in mind, that the number and size of files could make a difference (the recommendation is still 8GB afaik). On some operating systems it will improve your write performance when you have more files (if you use multiple database writers or async I/O), on others it could make no difference. You should check your environment carefully.

Best regards

Ralph Ganszky

[lbreddemann]

Dear Anup,

as note 355771 already explains, there is no performance benefit to be expected by using multiple tablespaces nowadays where SANs or big EMC-boxes are used.

Tablespaces in Oracle are there to make the administration easier.

The SAP applications and tools DON'T rely on a certain tablespace-layout.

All SAP DB-Objects do have a mapping to a "abstract" dataclass which in turn points to a specific tablespace (see table TSORA).

So as long as all the tablespaces listed in TSORA do exist on the database you won't run into problems.

For details you may want to have a look into note #666061.

So, the answer to your question is not a technical one, but a organisational one.

If one big single tablespace suits best your needs and you don't see any advantages in handling in multiple tablespaces, well then this is OK.

For Oracle and SAP it really does not matter in how many tablespaces the objects of the SAP-Schema are stored - there are also databases used with NetWeaver that don't even have the concept of tablespaces like MaxDB...

Hope that answers your question.

KR Lars

[markus_doehr2]

Forgot to add note 355771, this will be more in-depth.

--

Markus

[markus_doehr2]

Check note 957917 which will explain the new tablespace layout.

Almost all tools (BR*, R3up/SAPup) expect a certain tablespace layout; you can modify that, however, you may have trouble during upgrades because a specific layout is expected.

--

Markus