PART 06 - Temporary Space

ソートやハッシュ結合が想定より遅く、原因を追うとTemporary Spaceの逼迫だった、という場面に何度か遭遇しました。目立たない領域ですが、性能に直結する部分なので整理しておきます。

Temporary Space（一時表領域）とは

図1: Temporary Spaceが使われる処理と条件

Temporary Space = 一時的な作業用ディスク領域。

主にソートやハッシュ結合の作業域として使われる。
PGA（セッション専用メモリ）で処理しきれない場合に、あふれた分がここへ書き出される（Spill to Disk）。
トランザクションのコミット/ロールバックの対象外で、REDOが原則生成されない（製品差はある）。
「使い終わったらすぐ捨てる前提」の領域。

PGA（ソート作業）でメモリで足りる ⇒ 高速 ✔　／　足りない ⇒ 一時表領域（Disk）にあふれ、ディスクI/Oで急に遅くなる。

何に使われるのか

ORDER BY: インデックスが効かない並び替えで巨大ソート発生
GROUP BY / DISTINCT: 重複排除や集計のための作業領域
UNION（≠ UNION ALL）: 重複排除のためのソート
HASH JOIN: 片側を一時域でハッシュ化し結合
ウィンドウ関数（OVER）: PARTITION/ORDER単位でソート
大きな中間結果セット: サブクエリやCTEの結果が大きい時
グローバル一時テーブル: 明示的に CREATE GLOBAL TEMPORARY TABLE
インデックス作成（CREATE INDEX）: B-Treeの構築過程でソート

通常表領域との違い

一時はI/Oを多く出す可能性が高い ⇒ 業務データと違う物理ディスクに配置するのがセオリー。

設定とサイジング

サイジングの考え方

「同時に走る最大の大型SQL × その作業量」を試算。
夜間バッチ等の最ピーク時を基準にする。
自動拡張（オートエクステンド）は有効推奨だが、上限なしにすると暴走したSQLでディスク枯渇。

配置の考え方

USERS / INDEX / TEMPは別物理ディスクが理想。
SSDのような高速I/Oが一時で特に効く。
複数の一時ファイルでI/Oを分散。

-- 一時表領域の作成例 (汎用)
CREATE TEMPORARY TABLESPACE temp1
  TEMPFILE '/data/temp1_01.dbf'
  SIZE 4G AUTOEXTEND ON
  NEXT 256M MAXSIZE 32G;

-- ユーザに割り当て
ALTER USER app_user
  TEMPORARY TABLESPACE temp1;

-- 現状の使用率を確認
SELECT tablespace_name,
       used_blocks, total_blocks
  FROM v$sort_segment;

開発者として気をつけるSQL

不要なORDER BY: アプリ側でソート可能なら付けない。ページネーション目的は別途検討。
DISTINCTの濫用: 本当に重複しているのか？を疑う。JOINの設計ミスで重複している例が多い。
UNIONの代わりにUNION ALL: 重複が起きないことが分かっているならALLを使う（ソート不要）。
大きなIN句 / EXISTS: 巨大なリストはハッシュ結合になりやすく一時を消費。
巨大なGROUP BY: 集計キーの粒度を上げ、件数を抑える。サブクエリで先に絞る。
LIMITのないトップN: サーバ側ですべてソートしてしまう。LIMIT/ROWNUMを付ける。

実行計画に「SORT (Disk)」や「SPILL」が出てきたら、ほぼ確実に一時表領域を使っている。

一時表領域の問題を診断する

一時表領域に関するトラブルには主に「容量枯渇」と「使用量の多さによる性能劣化」の2種類がある。それぞれ症状と診断方法が異なる。

症状	原因の候補	確認方法	対処
ORA-01652: temp segment cannot be extended	一時表領域の空き不足	`v$sort_segment` で使用量を確認	一時ファイルを追加またはMAXSIZEを拡張
特定SQLだけ極端に遅い	Spill to Diskが発生している	実行計画で SORT(Disk) を確認、AWRのI/O統計	SQL最適化（UNION ALL化・絞り込み強化）、PGAサイズ拡張
バッチ時間帯にストレージI/Oが急増	バッチSQLが大量に一時表領域へSpill	AWRのI/O統計・Segment統計で一時ファイルのI/Oを確認	バッチSQLのソート削減、並列度の調整

一時表領域でよくある誤り

一時表領域の枯渇は突然発生するが、事前の監視と設計で防ぎやすい。

誤り	何が起きるか	正しい対処
一時表領域をMAXSIZE無制限で設定する	野放図なSpill to Diskがストレージを枯渇させ、他のDBやOSファイルシステムに影響が及ぶ	業務ピーク時のSort Segment使用量をv$sort_segmentで計測し、適切なMAXSIZEを設定する
PGA_AGGREGATE_TARGETを小さく設定したままSpill対策にTEMPを拡張する	PGAが少ないためSort/Hashは常にSpillし、一時表領域を拡張しても根本解決にならない	PGA Advisory（AWRのAdvisory Statistics）を確認し、PGAサイズ拡張でSpillを減らす方が根本的な解決になる
TEMPの解放を待たずに「一時ファイルが残っている」と判断してDBを再起動する	長時間クエリの実行中にはSort Segmentが保持されており、それ自体は正常動作	v$sort_usageでセッション別の一時使用量を確認し、長時間実行のSQLがないかを先に判断する

PART 06 — Temporary Space