Mohd., October 15, 2016 - 3:26 pm UTC
Dear Connor,
Thanks for answering my question.
But your answer is more inclined to typical backup/DBA activity, which certainly make sense. Thanks for that input.
But I am playing a Application DBA role and I want to add some value to existing design and that was the CORE purpose asking this question.
Debating DBA for maintaining multiple tablespaces (pupose) VS single tablespace(current) may not be easy job for me as DBA will make use of the MACHINE's power ( and why he should agree to multiple tablespaces when he has to take all the pain of migrating existing objects to 6 different tablespaces? )
Backup time is his job and he might turn down my suggestion due to his own sake of his expertise showing I am lacking understanding of current power of machines.
to make it short for you......
If I/O is not issue in today's world and backup time is not a factor to consider, then breaking the single tablespace to 6 tablespaces will have any impact on SQL performance ?? or it is going to be a minor improvement OVER the efforts of migrating objects to different tablespaces? (considering the data growth in DW environment )
October 17, 2016 - 12:42 am UTC
"then breaking the single tablespace to 6 tablespaces will have any impact on SQL performance ??"
For the vast majority of installations, I would anticipate no difference in performance.
And ?
A reader, October 17, 2016 - 11:30 am UTC
"For the vast majority of installations, I would anticipate no difference in performance. "
So means for the little rest it would.
So 1 or 2 examples are welcomed.
October 18, 2016 - 1:41 am UTC
For example, there might be a system where no storage infrastructure or management is in place, so its the old style JBOD arrangement. Hence the spreading of I/O across physical disks is a task for the DBA.
(That's *very* rare nowadays but possible).
Mohd., October 17, 2016 - 4:46 pm UTC
I am a bit surprise to hear that no performance gain at all
1.Lets say parallelism is used to query data ( single TBS Vs Multiple TBS) which model will benefit more in response time??
2. This DB will be moving to EXADATA machine later on, which model is advisable for EXADATA ( single TBS Vs Multiple TBS) ??
October 18, 2016 - 1:43 am UTC
Parallelism is not bound or defined by number of tablespaces. There *might* be some platforms where file level locking might impinge upon concurrent operations, and hence *might* be some need to have multiple datafiles to allow better performance...but that would be boundary case.
Exadata will be ASM and the number of tablespaces is your choice for *administrative* benefit as previously discussed.
Tablespaces
A reader, October 17, 2016 - 8:24 pm UTC
Tablespaces are an administrative tool, not a performance tool.
examples:
You can backup by tablespace, i.e. different backup intervals for different types of data.
You can unplug tablespaces from one database and into another to move specific subsets of data.
Partition swaps/drops.
OATM
A reader, October 17, 2016 - 8:29 pm UTC
<Just look at Oracle Apps OATM, single tablespace and everything is dumped into it.
Even OATM still uses something like 16 tablespaces.
Tablespace performance
A reader, October 18, 2016 - 12:28 am UTC
It seems the OP thinks that scalability is increased the more tablespaces you have, as if only one session can select the data from one tablespace at a time. Of course, this isn't the case. IOs are not limited in this fashion
ASM and file level locking
A reader, October 19, 2016 - 8:55 pm UTC
Dear Connor,
as you said -> "There *might* be some platforms where file level locking might impinge upon concurrent operations,"
We are on Linux and ASM, does file level locking happen with ASM ?
October 20, 2016 - 1:12 am UTC
No, because ASM is not a file system (as such).
Prakash, January 24, 2017 - 9:17 am UTC
In oracle 11g database,
1) Is single tablespace have multiple datafiles?.
2) Is multiple tablespace have multiple datafiles?.
3) Is multiple tablespace have single datafiles?.
I am new to oracle 11g database world since last 1 month.
which one is wrong? or datafile location is also important?
January 24, 2017 - 9:35 am UTC
1) Yes it *can* have multiple datafiles (but does not have to)
2) Yes, see (1)
3) Yes (but under special set of circumstances which is not worth mentioning).
But if you're new, here's a definition to assist, taken from the Concepts guide
http://docs.oracle.com/database/122/CNCPT/logical-storage-structures.htm#CNCPT402 "A tablespace is a logical storage container for segments. Segments are database objects, such as tables and indexes, that consume storage space. At the physical level, a tablespace stores data in one or more data files or temp files."