Skip to Main Content

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Connor McDonald

Thanks for the question.

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

Answered by: Connor McDonald - Last updated: November 25, 2019 - 12:30 pm UTC

Category: Database Administration - Version: 19.3

Viewed 1000+ times

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 we 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.

and you rated our response

  (5 ratings)

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

Reviews

November 13, 2019 - 1:46 pm UTC

Reviewer: A reader

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

Followup  

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

November 13, 2019 - 9:39 pm UTC

Reviewer: J. Laurindo Chiappa from Sao Paulo, SP, Brazil

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

November 15, 2019 - 3:11 pm UTC

Reviewer: A reader

I would be curious to know if any of these considerations apply to engineered systems like Exadata.

Connor McDonald

Followup  

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.

November 18, 2019 - 4:33 pm UTC

Reviewer: A reader

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

Followup  

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"

November 20, 2019 - 8:15 pm UTC

Reviewer: J. Laurindo Chiappa from Sao Paulo, SP, Brazil

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