high CPU and IO when using temporary tablespace group

High LIO (Logical I/O) And CPU Consumption On TS$ When Using A "tablespace group" As TEMP TS [ID 1469347.1]

Applies to:

Oracle Server - Enterprise Edition - Version 11.1.0.6 and later
Information in this document applies to any platform.

Symptoms

The following SQL running 2,190,042 times/ day and becoming one of the largest LIO (Logical I/O) consumers on this Database

"select ts# from ts$ where dflmaxext =:1 and bitand(flags, 1024) =1024 and online$=1"

To check this behavior, you can get a 10046 trace file on the process ID

OR

From AWR reports, you could have this query in the top of "SQL ordered by Gets", "SQL ordered by Executions" or SQL ordered by Parse Calls entries:

SQL ordered by Parse Calls

Parse Calls	Executions	% Total Parses	SQL Id	SQL Module	SQL Text
263,831	263,831	19.74	au7f6hpnhxpkx		select ts# from ts$ where dflm...

SQL ordered by Executions

Executions	Rows Processed	Rows per Exec	CPU per Exec (s)	Elap per Exec (s)	SQL Id	SQL Module	SQL Text
263,831	263,911	1.00	0.00	0.00	au7f6hpnhxpkx		select ts# from ts$ where dflm...

SQL ordered by Gets

Buffer Gets	Executions	Gets per Exec	%Total	CPU Time (s)	Elapsed Time (s)	SQL Id	SQL Module	SQL Text
76,255,959	263,831	289.03	22.46	115.57	119.99	au7f6hpnhxpkx		select ts# from ts$ where dflm...

Changes

Temporary Tablespace group being used

Cause

Usage of Temporary Tablespace group

Before 11.1.0.6 version, The issue is reported in the following bug with existing patch :

Bug 5455880 - Excessive recursive query on TS$ when using a "tablespace group" as temporary tablespace

For versions 11.1.0.6 and above, there is already another reported bug for this behavior which was closed as expected behavior and closed as NOT a BUG.

Bug 6052597 - RECURSIVE QUERY ON TS$ WHEN USING A "TABLESPACE GROUP" AS TEMP TABLESPACE.

An Enhancement request in unpublished
Bug 6627871 - ER: BUG 6052597 RECURSIVE QUERY ON TS$ WHEN USING A "TABLESPACE GROUP" AS TEMP
is already in progress to avoid this behavior when inserting data into a global temporary tablespace

Every time a temporary segment is created, the code must look at the temporary tablespace group definition to look for space in ONLINE tablespaces for the relevant tablespace group. By definition, tablespaces can go online and offline during normal operation of a tablespace group, it is only a problem if none are online.

This is not an issue if you do not have groups because a temporary tablepsace it either online or offline.
With tablespace groups , the group can be partially online and partially offline. Hence on every temporary segment creation in a tablespace group, we have to consider only online tablespaces for space allocation. 

select ts# from ts$ where dflmaxext =:1
and bitand(flags,1024) =1024     /* It's part of a group */
and online$=1                    /* It's online          */

If the online list has to be determined on every temp segment creation, then this is how it has to be ( at runtime )

You have to check online tablespaces in a group on every temp segment allocation, otherwise the information could be stale.

Solution

Do not use tablespace groups for the TEMPORARY tablespace

A temporary tablespace can be removed from a group with the following statement

SQL> alter tablespace <tablespace_name> tablespace group '';