Temporary Tablespace Group(10g)

Temporary Tablespace Group(10g)
 
Oracle Database 10g

Temporary Tablespace Group(10g)
===============================

 
1. 개념
 
Temporary Tablespace 들을 한 곳으로 모아 놓고 관리한다는 개념이다.
 
2. 특징
 
1) 적어도 한 개의 Temporary tablespace를 포함한다. 얼마나 많은 tablespace를 포함할 수 있는가에 대한 제한은 없다.
 
2) 일반적인 Tablespace와 동일한 성질을 갖는다. 다른 Tablespace 이름과 혼용할 수 없다.
 
3) Temporary Tablespace Group을 직접 생성할 수 있는 방법은 없다. 단지, 최초의 Temporary tablespace가 그 Group에 할당됨을 표현할 때 자동적으로 생성되고, 마지막 Temporary tablespace가 제거될 때 삭제된다.
 
3. 장점
 
1) 사용자가 동시에 다른 세션을 통해서 여러 개의 Temporary tablespace를 사용할 수 있다.
 
2) Parallel Operation의 slave process들이 다중 Temporary tablespace를 사용할 수 있다.
 
3) 데이터베이스 수준에서 다중 Default temporary tablespace를 지정할 수 있다.
그리하여, DBA는 하나 이상의 Default temporary tablespace를 지정할 수 있고, 하나의 SQL operation은 sorting 작업을 할 때 하나 이상의 temporary tablespace를 사용할 수 있다. 이러한 기능은 특정 operation이 temporary tablespace의 공간을 모두 소진하는 상황을 미연에 방지할 수 있다.
 
4. 사용 예
 
1) 생성 예
 
SQL> SELECT group_name, tablespace_name
  2  FROM DBA_TABLESPACE_GROUPS;
no rows selected
 
SQL> select file_name, bytes from dba_temp_files;
FILE_NAME         BYTES
------------------------------------------------   ---------
C:ORACLEPRODUCT10.2.0ORADATASTBYTEMP01.DBF   20971520
 
SQL> CREATE TEMPORARY TABLESPACE temp1
  2  TEMPFILE 'C:ORACLEPRODUCT10.2.0ORADATASTBYTEMP101.DBF' SIZE 10M
  3  TABLESPACE GROUP group1;
Tablespace created.
 
SQL> select file_name, bytes from dba_temp_files;
FILE_NAME         BYTES
------------------------------------------------   ---------
C:ORACLEPRODUCT10.2.0ORADATASTBYTEMP01.DBF   20971520
C:ORACLEPRODUCT10.2.0ORADATASTBYTEMP101.DBF  10485760
 
SQL> SELECT group_name, tablespace_name
  2  FROM DBA_TABLESPACE_GROUPS;
GROUP_NAME                     TABLESPACE_NAME
------------------------------ ---------------
GROUP1                         TEMP1
 
SQL> CREATE TEMPORARY TABLESPACE temp2
  2  TEMPFILE 'C:ORACLEPRODUCT10.2.0ORADATASTBYTEMP201.DBF' SIZE 10M
  3  TABLESPACE GROUP group2;
Tablespace created.
 
SQL> select file_name, bytes from dba_temp_files;
FILE_NAME         BYTES
------------------------------------------------   ---------
C:ORACLEPRODUCT10.2.0ORADATASTBYTEMP01.DBF   20971520
C:ORACLEPRODUCT10.2.0ORADATASTBYTEMP101.DBF  10485760
C:ORACLEPRODUCT10.2.0ORADATASTBYTEMP201.DBF  10485760
 
SQL> SELECT group_name, tablespace_name      
  2  FROM DBA_TABLESPACE_GROUPS;
GROUP_NAME                     TABLESPACE_NAME
------------------------------ ---------------
GROUP1                         TEMP1
GROUP2                         TEMP2
 
SQL> CREATE TEMPORARY TABLESPACE temp3
  2  TEMPFILE 'C:ORACLEPRODUCT10.2.0ORADATASTBYTEMP301.DBF' SIZE 10M
  3  TABLESPACE GROUP '';
Tablespace created.
 
SQL> SELECT group_name, tablespace_name      
  2  FROM DBA_TABLESPACE_GROUPS;
GROUP_NAME                     TABLESPACE_NAME
------------------------------ ---------------
GROUP1                         TEMP1
GROUP2                         TEMP2
주) Group 이름을 ''로 지정하는 것은 group에 포함시키지 않겠다는 의미이다.
즉, CREATE TEMPORARY TABLESPACE temp3 TEMPFILE 'C:ORACLEPRODUCT10.2.0ORADATASTBYTEMP301.DBF' SIZE 10M ;
명령과 동일하다.
 
2) Group을 변경, 사용자에게 할당하는 예
 
SQL> ALTER TABLESPACE temp3 TABLESPACE GROUP group2;
Tablespace altered.
 
SQL> SELECT group_name, tablespace_name
  2  FROM DBA_TABLESPACE_GROUPS;
GROUP_NAME                     TABLESPACE_NAME
------------------------------ ---------------
GROUP1                         TEMP1
GROUP2                         TEMP2
GROUP2                         TEMP3
 
SQL> ALTER TABLESPACE temp2 TABLESPACE GROUP '';
Tablespace altered.
 
SQL> SELECT group_name, tablespace_name
  2  FROM DBA_TABLESPACE_GROUPS;
GROUP_NAME                     TABLESPACE_NAME
------------------------------ ---------------
GROUP1                         TEMP1
GROUP2                         TEMP3
 
SQL> ALTER TABLESPACE temp1 TABLESPACE GROUP group2;
Tablespace altered.
 
SQL> SELECT group_name, tablespace_name
  2  FROM DBA_TABLESPACE_GROUPS;
GROUP_NAME                     TABLESPACE_NAME
------------------------------ ---------------------
GROUP2                         TEMP1
GROUP2                         TEMP3
 
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE group2;
Database altered.
 
SQL> CREATE USER sun IDENTIFIED BY sun
  2  DEFAULT TABLESPACE users        
  3  TEMPORARY TABLESPACE group2;
User created.
 
SQL> select username, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE
  2  from dba_users         
  3  where username = 'SUN';
USERNAME     DEFAULT_TABLESPACE      TEMPORARY_TABLESPACE
-----------  -------------------     ---------------------
SUN          USERS                   GROUP2

주) 일반적인 Temporary tablespace가 아니라, Temporary tablespace Group이 지정된 것을 확인할 수 있다.
 
<DBA Session>
SQL> grant connect, resource, dba to SUN;
Grant succeeded.
 
SQL> select username, session_num, tablespace from v$sort_usage;
no rows selected
 
<SUN Session 1>
SQL> select a.table_name, b.table_name from dba_tables a, dba_tables b;
 
<SUN Session 2> 동시에
SQL> select a.table_name, b.table_name from dba_tables a, dba_tables b;

<DBA Session>
SQL> select username, session_num, tablespace from v$sort_usage;
USERNAME                       SESSION_NUM TABLESPACE
------------------------------ ----------- ----------
SUN                                     19 TEMP1
SUN                                     78 TEMP3
주) SUN 유저가 두개의 서로 다른 temporary tablespaces에서 Sort를 진행중이다.

[출처] Temporary Tablespace Group(10g)|작성자 예민

 

추가 테스트 결과 (11gR2)

	hole temp tablespace	temp tablespace group
temp 파일 개수 / TBS 개수	10GB * 12개 / 1개	10GB * 1개 / 12개
71GB 인덱스 생성 시간( parallel 12)	00:19:51.66	00:18:11.42

사이즈가 많이 크지는 않아서 갭은 별로 없지만 성능 향상이 있습니다.

 구글 검색 결과 temp tablespace group 사용시에

Reads 66%, Writes 29%의 성능 향상을 보인다는 테스트 결과도 있습니다.