サポート日記Ｑ＆Ａ その１
サポート日記Ｑ＆Ａ その１
ペンネーム：グリーンペペ
＜Ｑ：ユーザー様からのご質問＞
8i以降で作成できるローカル管理の一時表領域のUNIFORMサイズの指針として
１．「SORT_AREA_SIZEの倍数 = UNIFORMサイズ」 ２．「SORT_AREA_SIZEの倍数+DB_BLOCK_SIZE = UNIFORMサイズ」
(一時表領域のセグメントヘッダー（Oracleブロックの1ブロック分）を考慮し
た指針)
とありますが、どちらが正しいのですか？
＜Ａns.：by グリーンペペ＞
まずはうんちくから、、
＠SORT_AREA_SIZEって何？
初期化パラメータSORT_AREA_SIZEは、ソートに使用するメモリの最大量をバイ
トで指定する。ソートを完了させるために、SORT_AREA_SIZE以上の領域が必要
な場合、一時セグメントを使ったディスクソートが行われる。ディスクソート
はパフォーマンス低下の要因なのでSORT_AREA_SIZEを増やし、できるだけメモ
リでソート処理が行われるようにする。
ただしSORT_AREA_SIZEはサーバプロセスごとに確保されるので、不必要に大き
くするとスワッピングやページングの原因となりかねない。
＠一時セグメントって何？
一時セグメントは、ディスクソート発生時に一時表領域内に割り当てられるソ
ートの作業領域のことである。
一時セグメントは、複数のトランザクションから利用されるが、セグメント内
の１つのエクステントを複数のトランザクションで共有することはできない。
よって一時セグメントのエクステントサイズを適切にチューニングし、無駄な
く一時表領域内の領域割り当てが行われるようにすべきである。
＠一時セグメントのエクステントサイズはどうすればいいの？
9iから導入されている自動PGA管理の設定をしていない場合は、ディスクソー
ト発生時の作業領域はSORT_AREA_SIZEで設定した値と等しい値で割り当てられ
る。
よってローカル管理の一時表領域の場合は、エクステントサイズを設定する
UNIFORMサイズを
SORT_AREA_SIZEの倍数 = UNIFORMサイズ
ディクショナリ管理の一時表領域の場合は、エクステントサイズを設定する
INITIAL,NEXT,PCTINCREASEサイズを
SORT_AREA_SIZEの倍数 = INITIALサイズ = NEXTサイズ, PCTINCREASE = 0
と設定する。
— それでは、どっぷり検証生活スタート！ —————————–
ディスクソート発生時のv$sort_segmentを参照し、
LOCAL管理の一時表領域のUNIFORMサイズの指針として
１．「SORT_AREA_SIZEの倍数 = UNIFORMサイズ」 ２．「SORT_AREA_SIZEの倍数+DB_BLOCK_SIZE = UNIFORMサイズ」
(一時表領域のセグメントヘッダー（Oracleブロックの1ブロック分）を考慮した指針)
どちらの指針が正しいのか検証する。
＜検証内容＞
□環境
OS:Red Hat Linux release 6.2
Oracle:8.1.7.0.1EE
□初期パラメタ確認。
SQL> select name,value from v$parameter
where name in ('db_block_size','sort_area_size');
NAME
----------------------------------------------------------------------
VALUE
----------------------------------------------------------------------
db_block_size
2048 ←２Ｋ
sort_area_size
122880 ←１２０Ｋ
□ローカル管理の一時表領域を作成。
SORT_AREA_SIZEと同サイズである120KをUNIFORMサイズに指定
SQL> CREATE TEMPORARY TABLESPACE temp05
TEMPFILE 'temp05.dbf' SIZE 10m REUSE
AUTOEXTEND ON NEXT 10m MAXSIZE 100m
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 120k;
Tablespace created.
SQL> select * from dba_tablespaces
where tablespace_name = 'TEMP05';
TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS
--------------- -------------- ----------- ----------- ----------- ------------ ---------- --------
TEMP05 122880 122880 1 0 122880 ONLINE
CONTENTS LOGGING EXTENT_MAN ALLOCATIO PLU
--------- --------- ---------- --------- ---
TEMPORARY NOLOGGING LOCAL UNIFORM NO
□テストユーザーの一時表領域をtemp05に変更。
SQL> alter user tpc temporary tablespace temp05; User altered.
□テストテーブルにアクセスしsortを発生させる。
SQL> connect tpc Connected. SQL> set autotrace traceonly SQL> select distinct code from code where rownum select distinct code from code where rownum select TABLESPACE_NAME,EXTENT_SIZE,TOTAL_EXTENTS,TOTAL_BLOCKS from v$sort_segment; TABLESPACE_NAME EXTENT_SIZE TOTAL_EXTENTS TOTAL_BLOCKS ------------------------------- ----------- ------------- ------------ TEMP05 60 1 60
＜結論＞
少量のディスクソート発生時に、一時セグメントの情報ビューであるv$sort_segment
を参照すると以下の結果が得られた。
TOTAL_BLOCKS * DB_BLOCK_SIZE = SEGMENT SIZE 60 * (2K)2048 = 120K
この値はSORT_AREA_SIZEの等倍である。
セグメントヘッダー(Oracleブロックの1ブロック分の
領域が一時表領域に割り当てられたならばTOTAL_EXTENTS=2
になるはずである。
よって、
LOCAL管理の一時表領域のUNIFORMサイズの指針は
「SORT_AREA_SIZEの倍数 = UNIFORMサイズ」で充分といえよう。
以上、絶景な富士山が臨める茅ヶ崎より