Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: November 12, 2019 - 3:07 pm UTC

Last updated: November 25, 2019 - 12:30 pm UTC

Version: 19.3

Viewed 10K+ times! This question is

You Asked

Since Oracle 12.2, Oracle has introduced a new feature of local temporary tablespace. The documentation does not provide any information on the pros and cons of this new feature. Can you please provide some information on why this feature was introduced and how it can benefit end users?

Thanks,
Arun

and Connor said...

Its main benefit for RAC databases. RAC has multiple nodes all of which access shared storage. But for temporary data, it can also make sense for that this data does NOT have to be shared, ie, if I am on a single node running a query that needs to sort, why should I bother coordinating my temporary data usage with all of the other nodes etc.

So a *local* temporary tablespace lets a node have a temporary tablespace where the files are local to that node, to reduce inter-node chatter and offer faster access for that node to temporary tablespace storage.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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

Connor McDonald
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.

Connor McDonald
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?
Connor McDonald
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

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database