ソート系操作の一覧
| Operation | 発生するSQL構文 | メモリ消費 | TEMP SPILLリスク |
|---|---|---|---|
| SORT ORDER BY | ORDER BY 句 | 入力行全体 | 高 |
| SORT GROUP BY | GROUP BY（ハッシュ集計できない場合） | グループキー + 集計中間値 | 中 |
| HASH GROUP BY | GROUP BY（12c以降のデフォルト） | グループキーのハッシュテーブル | 低〜中 |
| SORT UNIQUE | DISTINCT（ソート方式） | 入力行全体 | 高 |
| HASH UNIQUE | DISTINCT（ハッシュ方式） | ハッシュテーブル | 中 |
| WINDOW SORT | OVER() 分析関数（パーティション+ORDER BY） | ウィンドウ内の全行 | 高 |
| WINDOW BUFFER | OVER() 分析関数（パーティションのみ） | パーティション内全行 | 中 |
| SORT JOIN | SORT MERGE JOIN の内部ソート | 結合キー順ソート | 中 |
SORT ORDER BY
何を表すか: ORDER BY 句によるソート処理。入力行全体をワークエリア（PGA の Sort Area）に格納してソートする。
- 読み方: 直前のステップの Rows（入力行数）× Bytes（行サイズ）= ソート対象データ量を確認する
- 正常パターン: ページング（ROWNUM や OFFSET/FETCH）と組み合わせて上位 N 行のみ取得。
SORT ORDER BY STOPKEYと表示されて早期終了する - 問題パターン: 大量行（数百万以上）のソートで TEMP SPILL が発生し
direct path write temp/direct path read temp待機が増加 - 閾値・注意点:
SORT ORDER BY STOPKEYが出ていれば早期終了済み。出ていなければ全件ソート - 関連: INDEX 順が ORDER BY と一致する場合は SORT ORDER BY を排除できる（INDEX FULL SCAN を活用）
SORT GROUP BY / HASH GROUP BY
SORT GROUP BY: GROUP BY 列でソートしてからグループを集約する方式。ソートのコストが高い。
HASH GROUP BY: グループキーのハッシュテーブルを使って集約する方式。12c 以降のデフォルト。ソートなしで集約できるため通常こちらが高速。
- 読み方: HASH GROUP BY の Rows はグループ数（DISTINCT キー数）。入力 Rows が多くてもグループ数が少なければメモリ消費は小さい
- 問題パターン: グループ数が非常に多い（カーディナリティが高いキーでの GROUP BY）場合、ハッシュテーブルが肥大化して TEMP SPILL
- 閾値・注意点:
SORT GROUP BY ROLLUPは ROLLUP 句の集計。小計行を追加するため行数が増加する
SORT UNIQUE / HASH UNIQUE
何を表すか: DISTINCT 処理や UNION（重複排除あり）の内部実装。SORT UNIQUE はソート後に重複を除去、HASH UNIQUE はハッシュテーブルで重複を除去。
- 正常パターン: DISTINCT が本当に必要な場合
- 問題パターン: 不必要な DISTINCT（JOIN の重複を DISTINCT でマスクしている設計）。JOIN の重複を根本解決し DISTINCT を除去できれば大幅な性能改善
- 閾値・注意点: UNION ALL（重複排除なし）で十分な場合に UNION を使っていると SORT UNIQUE が発生する
WINDOW SORT / WINDOW BUFFER
何を表すか: 分析関数（ROW_NUMBER() OVER(...)、RANK() OVER(...)、SUM() OVER(...) 等）の内部処理。
- WINDOW SORT: PARTITION BY + ORDER BY を持つ分析関数。パーティション内をソートしてウィンドウ枠を処理する
- WINDOW BUFFER: PARTITION BY のみ（ORDER BY なし）の分析関数。パーティション内データをバッファしてから集計
- 正常パターン: パーティション内の行数が適切（数千〜数万程度）
- 問題パターン: PARTITION BY のカーディナリティが低い（全行が 1 パーティション）場合に全データをメモリに乗せる必要があり TEMP SPILL が発生
- 閾値・注意点:
WINDOW SORT PUSHED RANKが出れば RANK/ROW_NUMBER の STOPKEY 最適化が効いている
集合演算（UNION / INTERSECT / MINUS）
| Operation | SQL 構文 | 内部処理 | 注意点 |
|---|---|---|---|
| UNION-ALL | UNION ALL | 単純に行を連結（重複排除なし） | 最も軽量。重複が不要なら UNION の代わりに必ず使う |
| SORT UNIQUE | UNION（重複排除あり） | UNION-ALL した後に SORT UNIQUE で重複除去 | 全行ソートが発生。大量データでは重い |
| INTERSECTION | INTERSECT | 両クエリの共通行のみを返す。内部的にソートして比較 | INNER JOIN に書き換えた方が高速な場合が多い |
| MINUS | MINUS（EXCEPT） | 左クエリから右クエリの行を差し引く | NOT EXISTS / ANTI JOIN に書き換えることでインデックスを活用できる場合あり |
TEMP SPILL の判断と対処
ソート・ハッシュ結合・グループ集計がメモリ（PGA の Work Area）に収まらないと一時表領域（TEMP）へスピルが発生し、パフォーマンスが大幅に低下します。
TEMP SPILL の検出方法
- AWR の Top 10 Foreground Events に
direct path write temp/direct path read tempが出現 - V$SQL_WORKAREA_ACTIVE でアクティブな Work Area が DISK モードになっている
- ALLSTATS の Reads 列が TEMP への物理読込を示す
TEMP SPILL の対処
- 根本対処: ソート前のフィルタを強化して処理行数を削減
- メモリ増量: セッションレベルで
ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL; ALTER SESSION SET SORT_AREA_SIZE = ...（一時的な対処） - PGA 拡張:
PGA_AGGREGATE_TARGETまたはPGA_AGGREGATE_LIMITを見直す - バッチ分割: 一度に処理する行数を減らす WHERE 条件の追加