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 - ProdPL/SQL Release 10.2.0.1.0 - ProductionCORE    10.2.0.1.0      ProductionTNS for Linux: Version 10.2.0.1.0 - ProductionNLSRTL 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 20M tablespace group tempg;Tablespace created.sys@ORCL> create temporary tablespace temp2 tempfile size 20M tablespace group tempg;Tablespace created.sys@ORCL> create temporary tablespace temp3 tempfile size 20M 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                          TEMP1TEMPG                          TEMP2TEMPG                          TEMP3sys@ORCL> alter user hr temporary tablespace tempg;User altered.sys@ORCL> conn hr/hrConnected.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                                          0SORT (v2)  cn7ucn092pg8s    temp3                                          0sys@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 descselect 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.