Thanks for the question, Keh Wen.
Asked: June 19, 2019 - 1:02 pm UTC
Last updated: August 05, 2019 - 9:18 am UTC
Version: 12.1.0.2
Viewed 1000+ times
You Asked
Hi Tom,
I'm kind of confuse about use or not to use Temporary Tablespaces Group for RAC Databases.
Starting from Oracle 10g, Temporary Tablespaces group cames to improve temporary tablespace management which encourage me to use it.
However, I saw recently from the Note "Configuring Temporary Tablespaces for RAC Databases for Optimal Performance (Doc ID 465840.1)" and it says:
"It is easier to share space within a single temporary tablespace, rather than within a temporary tablespace group. If a session starts allocating temp space from a temporary tablespace within a temporary tablespace group, additional space cannot be allocated from another temporary tablespace within the group. With a single temporary tablespace, a session can allocate space across tempfiles."
Does that mean no matter I have many temporary tablespaces in a temporary tablespace group, a given session will be able to access only one tamporary tablespace? That would be an issue if more sort segment that session requires and no more additional temporary space can be allocated from that temporary tablespace ...
On the other hand, from Oracle Database 12c DBA Handbook, we have the following statement:
"One of the big advantages of using temporary tablespace groups is to provide a single user
with multiple sessions with the ability to use a different actual temporary tablespace for each
session. In the diagram shown in Figure 3-3, the user OE has two active sessions that need
temporary space for performing sort operations.
Instead of a single temporary tablespace being assigned to a user, the temporary tablespace
group is assigned; in this example, the temporary tablespace group TEMPGRP has been assigned
to OE. However, because there are three actual temporary tablespaces within the TEMPGRP
temporary tablespace group, the first OE session may use temporary tablespace TEMP1, and the
SELECT statement executed by the second OE session may use the other two temporary tablespaces,
TEMP2 and TEMP3, in parallel. Before Oracle 10g, both sessions would use the same temporary
tablespace, potentially causing a performance issue."
As we can see the statement "the second OE session may use the other two temporary tablespaces,TEMP2 and TEMP3, in parallel".
The temporary tablespace allocation described above is different from Doc ID 465840.1("additional space cannot be allocated from another temporary tablespace within the group tablespace").
Could you possibly clarify whather a session can allocate more than one temporary tablespaces when user's temporary tablespace is a temporary tablespace group?
Regards,
Lavie
and Connor said...
My understanding of temporary tablespace groups was predominantly to allow parallel slaves to get faster access to temporary segment allocation because a single query which lots of slaves might have some contention because they all might want to allocate temp segments concurrently.
Having said that, I can count on one hand the number of times I've seen temp tablespace groups in use in production environments across all my years working with Oracle databases. For that reason alone, I'd recommend you don't need to use them unless you encounter a specific need to do so.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment