A reader, November 13, 2019 - 1:46 pm UTC
So, what you are saying is that the contents of local temporary tablespace are not visible across instances? If user1 and user2 both have the same local temporary tablespace defined, say TEMP_LOCAL and user1 runs queries on instance1 and user2 runs query on instance2, how would it work? Sorry, but it is still not clear.
Thanks,
Arun
November 14, 2019 - 10:44 pm UTC
So, what you are saying is that the contents of local temporary tablespace are not visible across instances?
Yes.
From the docs
"Oracle uses local temporary tablespaces to write spill-overs to the local (non-shared) temporary tablespaces which are created on local disks on the reader nodes."
So two users might share the same local temporary tablespace *name*, but on each instance, that name may point to a different file.
To Arun
J. Laurindo Chiappa, November 13, 2019 - 9:39 pm UTC
Hello, Arun : let me try to explain in other words.... See, much unlike UNDO, the content of a temporary tablespace created by a given session is NOT needed by any other sessions (or any other nodes), for any use.... The situation is, a local temporary tablespace is used only for spilling temporary results of SQL statements, such as queries that involve sorts, hash aggregations, and joins - no one besides the session running the query needs to see this 'intermediate' data.....
So, if a session A is using some local temp space in the local temporary tablespace in instance 1 and another session B is using some local temp space in instance 2, NO NEED to one of the sessions 'see' se data created by the other session.... Equally, in the RAC perspective no need to 'sincronize' the temp data in instance 1 to/with instance 2, this kind of temporary data is particular...
The fact of no syncronization needed for local temp tablespace IS the main advantage for performace : the 'sincronization' of data would be done by inter-nodes network, less things to transport/access between nodes is equal to more network band available to data blocks and alike...
Regards,
Chiappa
A reader, November 15, 2019 - 3:11 pm UTC
I would be curious to know if any of these considerations apply to engineered systems like Exadata.
November 18, 2019 - 12:12 pm UTC
Exadata is RAC :-)
But *any* RAC system will typically benefit from maximizing local data access, and minimizing cross-node access.
A reader, November 18, 2019 - 4:33 pm UTC
So, on Exadata, local disk has better performance than storage servers? If that is the case, I just wasted million dollars...
After careful study of this feature, it would benefit only a small group of users who somehow have attached poor performing disks to shared storage and the faster performing disks to local storage AND have configured TEMP tablespace to be way undersized.
To get any real benefits from local temporary tablespace, a system would need really large high performance local disks.
I have not seen any numbers showing performance improvement. Has Oracle published any numbers?
November 25, 2019 - 12:30 pm UTC
Storage servers are fantastic at what they are *designed* for, namely, fast disks have Oracle "smarts" to remove some of that computational load from the database server.
They are also *separated* from the database servers via infiniband, or RoCE in the latest release. "Infiniband" is not the same as "infinitely-fast".
So why would I clog that with temporary data that I'm about to throw away the moment I finish my query.
To "A Reader"
J. Laurindo Chiappa, November 20, 2019 - 8:15 pm UTC
Hi - first things first, it´s out of discussion that high-end storages using disk volumes WILL present a better performance than local single disks, Of Course ... Point is, LOCAL TEMP don´t address it, the enhancement here is that the storage is OUTSIDE the database server, so all blocks must be sent by the storage to the database server, and this occupies some network band : the target of LOCAL TEMP TABLESPACES is to eliminate this network usage...
Will be this gain / network usage reducing significative overall, specially when talking about Exadata and its immense network power AND contrasting this gain with the loss coming from the inferior performance of a local disk ? Maybe yes, maybe no, you will NEED to test in YOUR environment ....
And answering your question NO, afaik Oracle don´t test at this level...
Regards,
Chiappa