ソートに関する検証 その2

~ソートに関する検証 その2 ~  ペンネーム ちゃむ

前回は、ソートを伴なう処理とそれを確認する方法を説明した。
今回は、主に、メモリ上でソートを行なう際、どこで行われるかを中心に
説明する。

初期化パラメータのSORT_AREA_SIZEは、メモリ上でソート処理する際のMAXサイズ
を決めるものである。そのサイズに収まらない場合はディスク上で、つまり、一時
表領域上でソート処理を行なうことになる。
メモリ上でソートを行なう場合、MTS(マルチスレッドサーバ)とDEDICATE
サーバ(専用サーバ)ではソートを行うエリアが異なる。

MTS接続 → SGA内のUGA
専用サーバ接続 → PGA内のUGA

まず、用語の説明も含めてOracleで使用するメモリ構造の説明をする必要が
あるだろう。

SGA(SYSTEM GLOBAL AREA)→ 1つのOracleインスタンスのデータと制御情報が
入る共有メモリ。データベースバッファ、共有プール、REDOログバッファなど
が含まれる。

PGA(PROGRAM GLOBAL AREA)→ セッションを確立すると、サーバプロ
セスごとに確保される領域。データや制御情報が含まれる非共有メモリ。

UGA(USER GLOBAL AREA)→ セッション単位に1つのUGAが存在する。MTS接
続の場合SGA内、専用サーバ接続の場合PGA内に確保される領域。

CGA (CALL GLOBAL AREA) → MTS接続、専用サーバ接続に関わらず、PGA内に
保持される。セッションの変数、配列などを含むスタック領域が確保される。

これらをソートエリアと関連させて図にしたものが以下である。

MTS接続で大量ソートを行なってしまうと、SGA内を圧迫してしまう恐れがある
ので、そのような処理を行う場合は専用サーバで接続するべきである。もし、
MTSを使用している環境(初期化パラメータのmts_で始まるパラメータを設定し
ている環境)でも、以下のようにtnsnames.oraで「(SERVER = DEDICATED)」を
指定すれば、ネット経由でも専用接続で接続できる。但し、DEDICATE接続の場
合は、SORT_AREA_SIZEは、セッションごとにPGA内に確保されるため、メモリ
の使用量は、最大で SORT_AREA_SIZE × ソートエリアを使用しているセッション
の数だけ必要になる。そのため、メモリ使用量としてはMTS接続よりも多くなっ
てしまう。あまり多くのソート処理が同時に発生すると、PGAの領域が確保できず、
ORA-4030(プロセスメモリ不足)などがでる原因の一つにもなる。

------------------tnsnames.ora始め--------------------

#MTS接続
O803.WORLD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.12)(PORT = 1521))
    )
    (CONNECT_DATA =
      (sid = ora803)
    )
  )

#DEDICATE接続
O803D.WORLD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.155)(PORT = 1521))
    )
    (CONNECT_DATA =
      (sid = ora803)
      (SERVER = DEDICATED)
    )
  )
------------------tnsnames.ora終わり--------------------

それぞれの接続文字列で、接続して実際にどちらのMTS接続かDEDICATE接続か
調べてみよう。

1.MTS接続の場合

SQL> CONNECT SCOTT/TIGER@O803
接続されました。
SQL> SELECT SID,SERVER FROM V$SESSION WHERE AUDSID =
(SELECT USERENV('SESSIONID') FROM DUAL);

      SID  SERVER
-----------------
        7  SHARED

V$SESSIONのSERVER列は 「SHARED」または、「NONE」で検索される。

2.DEDICATE接続の場合

SQL> CONNECT SCOTT/TIGER@O803D
接続されました。
SQL> SELECT SID,SERVER FROM V$SESSION WHERE AUDSID =
(SELECT USERENV('SESSIONID') FROM DUAL);

      SID  SERVER
--------------------
        7  DEDICATED

V$SESSIONのSERVER列は 「DEDICATED」で検索される。

ソートエリアサイズに収まらない場合は、一時表領域上でソート処理を行なう
ことになると説明した。
どこのソート領域で使用されるかは、Oracleユーザ毎に決まる。
具体的には、CREATE USERあるいはALTER USER時に指定するTEMPORARY TABLESPACE
である。

CREATE USER CHAMU IDENTIFIED BY OCHAMU
TEMPORARY TABLESPACE TEMP
DEFAULT TABLESPACE USERS;

DEFAULT TABLESPACEはCREATE TABLEなどを行なうときに表領域を指定しないと
このDEFAULT TABLESPACEで指定された表領域が使用される。

CREATE USER CHAMU IDENTIFIED BY OCHAMU;

上記のように作成するとTEMPORARY TABLESPACEとDEFAULT TABLESPACEはSYSTEM表領域に
なってしまうので注意が必要である。とくに、ソートをSYSTEM表領域で行なわせるのは、
絶対にやめた方がいい。

CREATE USER CHAMU IDENTIFIED BY OCHAMU;

SELECT USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE FROM DBA_USERS
WHERE USERNAME = 'CHAMU';

USERNAME  DEFAULT_TABLESPACE  TEMPORARY_TABLESPACE
--------------------------------------------------
CHAMU     SYSTEM              SYSTEM

以上 忘年会予約係より 茅ヶ崎にて