John Cantu, April 03, 2016 - 3:30 pm UTC
Wow, Connor. i am really shocked to hear about that Oracle didn't have the logic in there to provide more of a load balance between files. I have often wondered if there would be a negative change in performance or other negative issue between a tablespace that had one data file or one that had multiple within the same set of disks when I thought about adding an additional data file to our production system to avoid running out of space. I resisted because if something went haywire, I wouldn't know what to tell them regarding the "innocent" change that Oracle training tells us we can't make. I suppose I can run that test. What are your thoughts? do you think that there will be quirks between using one data file sized at 20 gB or two data files each sized at 10 gB on a regular tablespace?
I wish you taught Oracle training and would bring up all of these quirks. I feel like a doctor from the 15th century performing operations just like I was taught. then, having half of my patients die because of operation induced infections caused by lack of hygiene, which at the time wasn't known to be necessary since bacteria hadn't been discovered yet. However, Oracle knows bacteria exists so they should mention it to us in training.
April 04, 2016 - 1:08 am UTC
"Oracle didn't have the logic in there to provide more of a load balance between files"
Well we do now :-) That's just how things are with any software... you build something, find a gap, fill it, make it better, etc. In you're on 11.2 onwards, which hopefully most people are, then you'd never know it was *not* there in the past. And dont forget, we are talking tempfiles here. Permanent tablespaces have (for as long as I can remember) allocated extents across *all* the datafiles in the tablespace.
And in reality, besides the potential issue of file level locking (which is an OS layer dependency), the concept of "which" file to use gets less all the time, as the number of abstractions between database and disk increases. Nowdays, its almost impossible to know *which* disk(s) a file resides on (if on a disk at all) with all the smarts inside storage controllers/arrays etc.
(Thanks for the feedback)
Any reason to use temporary tablespace groups on Oracle 19/21c+?
blaisem, August 17, 2023 - 12:06 am UTC
Well we do now
Does this mean there is practically no reason to use temporary tablespace groups in Oracle 19/21c?
I know in your OP you mentioned OS issues with concurrent access to single files. Given this comment was in 2016, do modern OS's, or in particular any recent Oracle Linux version, have diminished performance with concurrent access to single files, that would warrant considering a temporary tablespace group under large enough data volumes / DML operations?
Just trying to get a feel if this feature should be on my radar for performance improvements, or if it's effectively become obsolete over time.
August 25, 2023 - 4:52 am UTC
I've almost never seen a temp tspace group "in the wild" so I dont think there is a massive need for them.
There's possibly an argument for massively parallel queries, namely, allowing each parallel query thread to get access to their "own" temp tspaces within a group.
But definitely (imo) this is a niche feature
Others welcomed to add their experiences.