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

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

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

初期化パラメータの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）→ セッション単位に１つの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接続か
調べてみよう。

１．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」で検索される。

２．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

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

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