Temporary tablespace group for Oracle temporary tables (TTG)

Environment:

Sys @ ORCL> select * from v $ version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Prod
PL/SQL Release 10.2.0.1.0-Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0-Production
NLSRTL Version 10.2.0.1.0-Production

In Oracle, temp is like a virtual memory in windows and a swap partition in unix.

TTG is a concept introduced by 10 Gb to reduce IO Competition

Only temporary tablespaces can be set. Common tablespaces cannot be set.

Treating TTG is just like treating a single temporary tablespace.

TTG itself cannot be created. It is created with the addition of temp, and the separation of temp is deleted.

Alter tablespace temp tablespace group '';

We know that a user can only use one temporary tablespace, while only one temporary tablespace exists.

When a session uses a temporary segment, other sessions need to wait until the session with this temporary segment is used.

The emergence of temporary tablespace groups greatly improves the competition for temporary segments for concurrent sessions of the same user.

The emergence of temporary tablespace groups allows users to use multiple temporary tablespaces.

The following is a simple test.

Sys @ ORCL> create temporary tablespace temp1 tempfile size 20 M tablespace group tempg;

Tablespace created.

Sys @ ORCL> create temporary tablespace temp2 tempfile size 20 M tablespace group tempg;

Tablespace created.

Sys @ ORCL> create temporary tablespace temp3 tempfile size 20 M tablespace group tempg;

Tablespace created.

Sys @ ORCL> alter database default temporary tablespace tempg;

Database altered.

Sys @ ORCL> drop tablespace temp;

Tablespace dropped.

Sys @ ORCL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------------------------------------
TEMPG TEMP1
TEMPG TEMP2
TEMPG TEMP3

Sys @ ORCL> alter user hr temporary tablespace tempg;

User altered.

Sys @ ORCL> conn hr/hr
Connected.

Hr @ ORCL> create table t as select * from dba_objects;

Table created.

Hr @ ORCL> begin
2 for I in 1 .. 4
3 loop
4 insert into t select * from t;
5 end loop;
6 commit;
7 end;
8/

PL/SQL procedure successfully completed.

Hr @ ORCL> create table tt as select * from t;

Table created.

Enable two sessions to sort the tables t and tt at the same time as the user hr login, and then monitor the usage of temporary tablespace through the following query.

Sys @ ORCL> select operation_type, SQL _id, tablespace, tempseg_size, number_passes from v $ SQL _workarea_active;

OPERATION _ SQL _ID TABLESPACE TEMPSEG_SIZE NUMBER_PASSES
------------------------------------------------------------------------------
SORT (v2) b7q3tuybvatbt temp1 0
SORT (v2) cn7ucn092pg8s temp3 0

Sys @ ORCL> select SQL _text from v $ SQL where SQL _id in (select SQL _id from v $ SQL _workarea_active );

SQL _TEXT
---------------------------------------------
Select object_id from t order by object_id desc

Select object_id from tt order by object_id desc

It is found that different sessions from the same user hr are sorted simultaneously using different temporary tablespaces in the same temporary tablespace group.

This greatly reduces the waiting time for temporary request segments generated when the same user can only use one temporary tablespace.