oracle集群临时表空间,RAC 两个节点使用的临时表空间是共享的吗

Any DW, OLTP or mixed workload application that uses a lot of temp space for temporary tables, sort segments etc, when running low on temp space, lots of sessions would start waiting on ‘SS enqueue’ and ‘DFS lock handle’ waits. This would cause some severe performance issues. This best practice note for temporary tablespace, explains how this works in RAC environment and offer recommendations.

Space allocated to one instance is managed in the SGA of that instance, and it is not visible to other instances.

Instances do not normally return temp space to the ‘common pool’.

If all the temp space is allocated to instances, and there is no more temp space within an instance, user requests for temp space will cause a request for temp space to be sent to the other instances. The session requesting the space will get the ‘SS enqueue’ for the temporary tablespace and issue a cross instance call (using a CI enqueue) to the other instances (waiting for ‘DFS lock handle’). All inter instance temp space requests will serialize on this ‘CI enqueue, and this can be very expensive.

A heavy query executing in one instance and using lots of temp space might cause all or most of the temp space to be allocated to this instance. This kind of imbalance will lead to increased contention for temp space.

As users on each instance request temp space, space will be allocated to the various instances. During this phase it is possible to get contention on the file space header blocks, and it is recommended to have at least as many temp files as there are instances in the RAC cluster. This normally shows up as ‘buffer busy’ waits and it is different from the ‘SS enqueue’/’DFS lock handle’ wait issue.

Temporary tablespace groups are designed to accommodate very large temp space requirements, beyond the current limits for a single temporary tablespace: 8TB (2k block size) to 128TB (32k block size).

One possible advantage of temporary tablespace groups is that it provides multiple SS enqueues (one per tablespace), but this only shifts the contention to the CI enqueue (only one system wide)

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.

这个是metlink的解释。临时表空间的使用会不一样。

还有调整建议：Configuring Temporary Tablespaces for RAC Databases for Optimal Performance [ID 465840.1]