Oracle10g 一時表領域グループおさらい の巻 その1

投稿日: 2005年4月13日

<Oracle10g 一時表領域グループおさらい の巻 その1>
ペンネーム:アイスケーキ

今週は単発検証ですが「一時表領域グループ」についておさらいをしていきま
す。

長編にしてしまいますと、もう直ぐGWのため頭の中が途中でalter system
flush 状態になりそうです。
既に気持ちはGW状態の人にも優しい気配りでしょうか。

★☆★

10gでは複数の一時表領域をグループ化して、DBユーザのTEMPORARY TABLESPACE
属性に設定することができます。

おいしい話としては、特定の表領域を指定せず、一時表領域グループを設定す
ることによって同一DBユーザに同時複数セッションで接続した状態でのソート
処理などで一時表領域へのI/Oが発生した場合に、各セッションが同じ一時表
領域を使用せず分散させることが可能になります。

確認用ディクショナリはDBA_TABLESPACE_GROUPS ビューです。
ここには、表領域グループとそのメンバーの表領域が設定されます。

SQL> desc DBA_TABLESPACE_GROUPS
 Name                  Null?    Type
 --------------------- -------- ------------
 GROUP_NAME            NOT NULL VARCHAR2(30)
 TABLESPACE_NAME       NOT NULL VARCHAR2(30)

早速確認をしてみましょう。

■環境
Microsoft Windows XP Pro
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 – Production
With the Partitioning, OLAP and Data Mining options

■いざ!!

○作り方はCREATE文に「tablespace group 句」を追加するだけです。

SQL> select * from dba_tablespace_groups;
no rows selected

SQL> create temporary tablespace temp1
  2  tempfile 'C:ORACLEPRODUCT10.1.0ORADATAORCLDATAFILETEMP_1.TMP' size 10M
  3  tablespace group tmpgpA;

Tablespace created.

SQL> create temporary tablespace temp2
  2  tempfile 'C:ORACLEPRODUCT10.1.0ORADATAORCLDATAFILETEMP_2.TMP' size 10M
  3  tablespace group tmpgpA;

Tablespace created.

SQL> create temporary tablespace temp3
  2  tempfile 'C:ORACLEPRODUCT10.1.0ORADATAORCLDATAFILETEMP_3.TMP' size 10M
  3  tablespace group tmpgpA;

Tablespace created.

SQL> select * from dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME
------------------------------ ---------------
TMPGPA                         TEMP1
TMPGPA                         TEMP2
TMPGPA                         TEMP3

○次にDBユーザのTEMPORARY TABLESPACE属性に表領域グループを割当てます。

SQL> alter user USER1 temporary tablespace TMPGPA;

User altered.

SQL> select username,TEMPORARY_TABLESPACE from dba_users
  2  where username = 'USER1';

USERNAME                       TEMPORARY_TABLESPACE
------------------------------ --------------------------
USER1                          TMPGPA

○使ってみましょう。
同一ユーザに接続した各セッションは異なる一時表領域を使用します。
(ソート処理を行わせます)

ディスクソート処理が発生し易くするように最初からpga_aggregate_targetを
小さくしておいた方が分かり易いです。

ついでにバッファキャッシュも整理してから。

SQL> alter system flush buffer_cache;

System altered.

SQLPLUSを複数立ち上げて、それぞれで検索処理が発生するようにしてください。

SQL> connect USER1/USER1
SQL> select * from wk_table_1 order by 1,2,3;

○実行中に一時セグメントの使用状況を確認します。

SQL> SELECT x.sid, x.username, y.tablespace, y.blocks
  2  FROM v$session x, v$tempseg_usage y
  3  WHERE x.saddr=y.session_addr;

SID USERNAME TABLESPACE     BLOCKS
----- -------- ---------- ----------
  54 USER1    TEMP3            32
  56 USER1    TEMP2            27
  58 USER1    TEMP1            29
               ↑
            異なる一時表領域を使用

○おさらい
DBユーザのTEMPORARY TABLESPACE属性に一時表領域グループを設定する事で、
異なる一時表領域を使用しIO分散が図れます。

但し難点はあるようです。
SQL実行プロセスのプロセスIDでハッシュして内部的には一時表領域を決定し
ているようです。
ということは未使用の一時表領域があっても機械的に算出した領域を使う場合
が発生しますちょっとおバカですね。

今週はここまで。

早く来い来いGWの茅ヶ崎より