Configuring Temporary Tablespaces for RAC Databases for Optimal Performance
Modified 04-AUG-2010 Type BULLETIN Status ARCHIVED
In this Document
Purpose
Scope and Application
Configuring Temporary Tablespaces for RAC Databases for Optimal Performance
References

Applies to:

Oracle Server – Enterprise Edition – Version: 9.2.0.1 to 11.1.0.6 – Release: 9.2 to 11.1
Information in this document applies to any platform.
***Checked for relevance on 04-Aug-2010***
Purpose

This document discusses issues involved in configuring temporary tablespaces for RAC databases. Also provides best practice recommendations in configuring them for optimal performance.
Scope and Application

Oracle RAC DBAs.
Configuring Temporary Tablespaces for RAC Databases for Optimal Performance

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.

The following is the recommended best practices for managing temporary tablespace in a RAC environment:
Make sure enough temp space is configured. Due to the way temp space is managed by instance in RAC, it might be useful to allocate a bit extra space compared to similar single instance database.

Isolate heavy or variable temp space users to separate temporary tablespaces. Separating reporting users from OLTP users might be one option.

Monitor the temp space allocation to make sure each instance has enough temp space available and that the temp space is allocated evenly among the instances. The following SQL is used:

select inst_id, tablespace_name, segment_file, total_blocks,
used_blocks, free_blocks, max_used_blocks, max_sort_blocks
from gv$sort_segment;

select inst_id, tablespace_name, blocks_cached, blocks_used
from gv$temp_extent_pool;

select inst_id,tablespace_name, blocks_used, blocks_free
from gv$temp_space_header;

select inst_id,free_requests,freed_extents
from gv$sort_segment;

If temp space allocation between instances has become imbalanced, it might be necessary to manually drop temporary segments from an instance. The following command is used for this:

alter session set events ‘immediate trace name drop_segments level ‘;

See Bug 4882834 for details.

For each temporary tablespace, allocate at least as many temp files as there are instances in the cluster.
References

BUG:4882834 – EXCESSIVE SS AND TS CONTENTION ON NEW 10G CLUSTERS
BUG:6331283 – LONG WAITS ON ‘DFS LOCK HANDLE’