|
What are the advantages and disadvantages having one single tablespace versus multiple tablespace? Ans: Well, in the majority of cases, even when using multiple tablespaces they will be on the same storage array or same file system. So the "all eggs in one basket" goes a lot a deeper down the storage layer than just the top part where you deal with logical tablespace storage containers. The major problem with multiple tablespace for a single application: a) deciding the space allocation of each tablespace
This comes down do an issue of micro-managing space versus simply chucking everything into a single container and let god ASM/Oracle/LUN/driver/whatever sort them out. Issues like transportable tablespaces... separating your logical data into different containers... and the like? That is not really sufficient justification to me for having to micro-manage space for a 101 tablespaces. This simply creates a huge workload on the space management part of the database - that is complex and difficult to deal with. Free space from an underutilised (incorrectly sized) tabelspace cannot simply be moved and reallocated to a tablespace stressed for more space. Everything but... Which is why I prefer having fewer tablespaces as this
means not having to micro-manage space and continually having to wave a
threatening lead pipe around to make sure that applications and developers
play rigidly within the large set of strict rules of how these many tablespaces
are to be used.
I've noticed better performance of RMAN with multiple tablespaces of similar size, due to parallelization. As someone else noted, there may be recovery time implications too. Division by app can make a lot of sense. In the past, some people have advocated separating tablespaces by type of access - for example, very volatile tables into their own tablespace, for the rare examples of app-specific fragmentation - or by size of objects. In some cases, it makes sense to put older data into read only tablespaces. In some cases, it makes sense to put some data into tablespaces with different blocksizes than the default, although this limits the ability to use different buffer caches, and has been the source of some mythmaking and bugs, and the better advice would be not to use this except what it was originally designed for, which is transporting tablespaces to or from other databases with different blocksizes. There may be disk, controller or replication hardware reasons to limit the size of LUNs. In some cases, you may want to configure disks differently for redo, flash, OLTP tablespaces, and not-so-OLTP tablespaces. In general, you can only figure out what's best for your system through some good experimentation, you don't want to over-administrate since the requirements and technology will drift over time anyways. The I/O issues aren't that hard to track, since you can
associate them with sessions and tracing can show what Oracle is asking
for. They may not even be the problem.
I guess there is no one answer for this kind of question like many other Oracle choices, because one has to find the balance regarding how much tablespaces are needed. Take a cue from the newer Oracle Apps versions, where the number of tablespaces are reduced to just above dozen. Look at your schemas, their load, whether you would require transportable tablespaces, etc and then try to find out the balance.
Have a Oracle Question
Oracle Books
Oracle Application
Oracle Home
All the site contents are Copyright © www.erpgreat.com
and the content authors. All rights reserved.
|