Temporary Tablespace for RAC Databases for optimal performance

TEMPORARY TABLESPACES FOR RAC DATABASES FOR OPTIMAL PERFORMANCE:
-- Troubleshoot "SS Enqueue" and "DFS Lock Handle"
-- For Oracle 10g / 11g RAC

What are Temporary Tablespaces?

Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. For example, if you join two large tables, and Oracle cannot do the sort in memory (see SORT_AREA_SIZE initialisation parameter), space will be allocated in a temporary tablespace for doing the sort operation. Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc.

The DBA should assign a temporary tablespace to each user in the database to prevent them from allocating sort space in the SYSTEM tablespace. This can be done with one of the following commands:


SQL> CREATE USER crm identified by password DEFAULT TABLESPACE data TEMPORARY TABLESPACE temp;

SQL> ALTER USER scott TEMPORARY TABLESPACE temp;

Performance analysis using Temporary tablespace:

Properly configuring the temporary tablespace helps optimize disk sort performance. Temporary tablespaces can be dictionary-managed or locally managed. Oracle recommends the use of locally managed temporary tablespaces with a UNIFORM extent size of 1 MB.

You should monitor temporary tablespace activity to check how many extents the database allocates for the temporary segment. If an application extensively uses temporary tables, as in a situation when many users are concurrently using temporary tables, then the extent size could be set smaller, such as 256K, because every usage requires at least one extent. The EXTENT MANAGEMENT LOCAL clause is optional for temporary tablespaces because all temporary tablespaces are created with locally managed extents of a uniform size. The default for SIZE is 1M.

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 <TS number + 1>';

See Oracle support site for Bug 4882834

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


-- Temporary tablespace / datafile management 
-- Applies to any Oracle version in any platform

-- Find temp file and temp tablespace with size
select file#,status,bytes/1024/1024 "Size_MB",name from v$tempfile;

select file_name,tablespace_name,bytes/1024/1024/1024,status from dba_temp_files

Few Temp tablespace and Tempfile commands for both RAC & Non-RAC:

-- Add tempfile to existing TEMP tablespace

ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/datafile/temp01.dbf' SIZE 2048M autoextend on;

ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/PROD/tempfile/temp01.dbf' SIZE 100M autoextend on;

-- Add new temp tablespace and make as default
create temporary tablespace TEMP2 tempfile '/u02/oradata/datafile/temp01.dbf' size 2G autoextend on;

alter database default temporary tablespace TEMP2;

-- Making off line old temp tablespace
alter database tempfile '/u02/oradata/datafile/temp02.dbf' offline;

-- drop temp tablespace ( Don't drop immediatly, If no user use TEMP tablespace, then you can drop)

drop tablespace TEMP2 including contents and datafiles;

-- To drop tempfile


alter tablespace TEMP2 drop tempfile '+DATA/PROD/tempfile/temp02.365.877895953';

-- drop tempfile from TEMP tablespace

ALTER TABLESPACE TEMP DROP TEMPFILE '/u02/oradata/datafile/temp02.dbf';

ALTER TABLESPACE TEMP DROP TEMPFILE '+DATA/PROD/tempfile/temp02.dbf';
-- shrink temp tablespace

alter tablespace TEMP shrink tempfile '/u02/oradata/datafile/temp01.dbf' keep 10G;

-- Auto extend a tempfile

alter database tempfile '+DATA/PROD/tempfile/temp01.dbf' autoextend on;


--- Who is using temp tablespace / what temp tablespace is being used ?

SELECT a.username, a.osuser, a.sid||','||a.serial# SID_SERIAL, c.spid Process,b.tablespace tablespace, 
a.status, sum(b.extents)* 1024*1024 space
FROM  v$session a,v$sort_usage b, v$process c, dba_tablespaces d
WHERE    a.saddr = b.session_addr
AND      a.paddr = c.addr
AND      b.tablespace=d.tablespace_name
group by a.username, a.osuser, a.sid||','||a.serial#,c.spid,
b.tablespace, a.status;

I hope, It will help to optimize the performance.