Oracle 臨時表空間管理全攻略：PGA/臨時段/臨時表空間三層架構、配置優化與監控實戰

排序、雜湊連接、群組聚合、索引建立、分析收集——所有「中間結果集」都會產生臨時資料。Oracle 採用 **三層架構** 處理：PGA 排序區 (記憶體) → 臨時表空間 (磁碟) → 臨時段 (邏輯單位)。本文系統解析三層交互機制、配置優化策略、監控視圖與實戰避坑指南。

三層架構交互流程

[SQL 執行需排序/雜湊/群組]
         │
         ▼
┌────────────────────────┐
│ PGA 排序區             │  ← 記憶體優先，速度最快 (ns/μs)
│ (SORT_AREA_SIZE /      │
│  PGA_AGGREGATE_TARGET) │
└───────────┬────────────┘
            │ 記憶體不足 (超過排序區大小)
            ▼
┌────────────────────────┐
│ 臨時表空間             │  ← 磁碟落盤，速度較慢 (ms)
│ (TEMPORARY TABLESPACE) │
└───────────┬────────────┘
            │ 分配臨時段
            ▼
┌────────────────────────┐
│ 臨時段                 │  ← 邏輯儲存單位，由區間組成
│ (TEMPORARY SEGMENT)    │
└────────────────────────┘

核心配置參數與優化策略

1. PGA 記憶體管理 — 降低落盤概率的第一道防線

優化原則：監控 V$PGASTAT 中 cache hit percentage > 90%，若偏低 → 增加 TARGET 或優化 SQL (減少排序/雜湊)。

2. 臨時表空間配置 — 落盤時的效能保障

• 專用磁碟/ASM 磁碟組：隔離於資料/Redo/Undo，避免 I/O 爭用。建議使用高速 SSD/NVMe 或 ASM Normal/High Redundancy。
• 多暫存檔案：ALTER TABLESPACE temp ADD TEMPFILE ... 多檔案分散 I/O，平行分配臨時段。
• 本地管理 + 統一區間大小：CREATE TEMPORARY TABLESPACE temp TEMPFILE ... EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M; 減少空間碎片與分配爭用。
• 非 OMF 環境預留空間：Linux/Unix 下暫存檔案建立時才分配空間 (稀疏檔案特性)，需預先規劃分區容量，避免運行時「磁碟滿」導致 ORA-01652。

3. 使用者/服務層級隔離

-- 建立專用臨時表空間供特定服務/租戶
CREATE TEMPORARY TABLESPACE temp_dw TEMPFILE '+DATA/temp_dw01.dbf' SIZE 50G
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;

-- 指派給使用者
ALTER USER dw_user TEMPORARY TABLESPACE temp_dw;

-- 或資料庫預設 (12c+ PDB 級)
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_dw;

效益：資源隔離、配額控制、故障域限縮、效能基線清晰。

關鍵監控視圖與診斷查詢

1. 臨時段使用總覽 (V$SORT_SEGMENT / V$TEMPSEG_USAGE)

-- 實例級臨時段使用率
SELECT tablespace_name, total_blocks, used_blocks, free_blocks,
       ROUND(used_blocks * 100 / total_blocks, 2) AS pct_used
FROM v$sort_segment;

-- 會話級臨時段佔用 (誰在用、用多少)
SELECT s.username, s.sid, s.serial#, u.tablespace, u.contents,
       u.blocks * t.block_size / 1024 / 1024 AS mb_used,
       u.sql_id, u.sql_exec_id
FROM v$tempseg_usage u
JOIN v$session s ON u.session_addr = s.saddr
JOIN dba_tablespaces t ON u.tablespace = t.tablespace_name
ORDER BY mb_used DESC;

2. 臨時表空間空間壓力預警

SELECT tablespace_name,
       SUM(bytes) / 1024 / 1024 AS total_mb,
       SUM(bytes - free_bytes) / 1024 / 1024 AS used_mb,
       ROUND(SUM(bytes - free_bytes) * 100 / SUM(bytes), 2) AS pct_used
FROM v$temp_space_header
GROUP BY tablespace_name;

3. 歷史臨時空間使用趨勢 (AWR/Statspack)

SELECT snap_id, begin_interval_time,
       temp_space_allocated / 1024 / 1024 AS alloc_mb,
       temp_space_freed / 1024 / 1024 AS freed_mb
FROM dba_hist_tbspc_space_usage
WHERE tablespace_name = 'TEMP'
ORDER BY snap_id;

常見問題與解決方案

最佳實踐清單

1. 啟用自動 PGA 管理：WORKAREA_SIZE_POLICY=AUTO + 合理 PGA_AGGREGATE_TARGET。
2. 臨時表空間專用高速儲存：SSD/NVMe/ASM 獨立磁碟組，多暫存檔案平行 I/O。
3. 統一區間大小：UNIFORM SIZE 1M-10M 視資料量而定，減少位圖管理開銷。
4. 預設臨時表空間 ≠ SYSTEM：嚴禁使用者預設臨時表空間為 SYSTEM (產生字典碎片、爭用)。
5. 監控儀表板：納入 V$SORT_SEGMENT 使用率、V$TEMPSEG_USAGE Top Sessions、ORA-01652 告警。
6. SQL 優化優於擴容：大排序多源於缺索引、笛卡兒積、不當 Hint，優化 SQL 效益遠大於單純加記憶體/磁碟。

總結

臨時表空間不是「臨時工」，而是高併發 OLTP 與大數據倉庫的**效能核心基礎設施**。掌握 **PGA → 臨時表空間 → 臨時段** 三層流轉機制，以「記憶體優先、磁碟兜底、隔離治理」為原則配置與監控，才能在排序、雜湊、聚合等核心運算中穩住效能底線。