Oracle 臨時表空間與 PGA 協同優化：從排序機制到參數調優的完整指南

臨時數據產生與流轉機制

1. 用戶執行 ORDER BY、GROUP BY、DISTINCT、UNION、INDEX CREATE/REBUILD、HASH JOIN 等操作。
2. Oracle 先在 **PGA（程序全局區）的排序區** 內存中排序。
3. 若排序區不足（受 SORT_AREA_SIZE / PGA_AGGREGATE_TARGET 限制），溢寫到 **臨時表空間** 磁盤（建立臨時段）。
4. 操作完成，臨時段釋放（但排序區內存不一定立即歸還 OS）。

關鍵參數演進

最佳實踐：全面啟用自動模式，設定 PGA_AGGREGATE_TARGET 為物理內存 20%~30%（扣除 SGA 後），12c+ 另設 PGA_AGGREGATE_LIMIT 防止單會話佔用過大導致 ORA-04036。

臨時表空間設計黃金法則

1. 專用表空間：創建專屬 TEMP 表空間，切勿共用 SYSTEM/SYSAUX/用戶表空間。
2. 本地管理 + 統一區塊：

   CREATE TEMPORARY TABLESPACE temp
       TEMPFILE '/u01/oradata/temp01.dbf' SIZE 10G
       AUTOEXTEND ON NEXT 1G MAXSIZE 50G
       EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
       

   UNIFORM SIZE 1M 減少臨時段碎片，提升分配效率。
3. 多文件分佈不同磁盤/ASM 磁盤組：並行 I/O 擴展頻寬。
4. 臨時表空間組（11g+）：ALTER TABLESPACE temp1 TABLESPACE GROUP temp_grp; 多臨時表空間負載均衡，防單文件瓶頸。
5. 預留 OS 空間：Linux 下臨時文件創建時才分配磁盤，預先在獨立分區預留空間，避免「無空間寫入」導致 ORA-01652/ORA-27041。

監控視圖與告警 SQL

-- 1. 臨時段使用情況
SELECT tablespace_name, SUM(bytes_used)/1024/1024 AS used_mb,
       SUM(bytes_free)/1024/1024 AS free_mb
FROM v$temp_space_header GROUP BY tablespace_name;

-- 2. 當前會話臨時段佔用 Top 10
SELECT s.sid, s.serial#, s.username, u.tablespace, u.segtype,
       u.blocks * ts.block_size / 1024/1024 AS mb
FROM v$sort_usage u
JOIN v$session s ON u.session_addr = s.saddr
JOIN dba_tablespaces ts ON u.tablespace = ts.tablespace_name
ORDER BY mb DESC FETCH FIRST 10 ROWS ONLY;

-- 3. 歷史高水位
SELECT * FROM dba_hist_temp_stat ORDER BY snap_id DESC FETCH FIRST 20 ROWS ONLY;

PGA 與臨時表空間協同調優策略

典型誤區澄清

• 誤區：SORT_AREA_SIZE 還在用。事實：11g+ 已棄用，自動模式下被忽略。
• 誤區：臨時表空間越大越好。事實：過大掩蓋惡劣 SQL，應優先優化 SQL 減少溢寫。
• 誤區：TEMP 表空間可手動縮小。事實：僅能 ALTER DATABASE TEMPFILE ... RESIZE，且不能小於高水位。
• 誤區：PGA_AGGREGATE_TARGET 是硬上限。事實：它是「目標值」，實際可超過（12c+ 用 LIMIT 硬限制）。

總結

臨時表空間不是「臨時工」，而是排序、雜湊、索引建立等核心操作的**磁盤後備緩衝區**。PGA 內存排序為主，臨時表空間磁盤溢寫為輔。運維核心：適度配置 PGA 自動管理、專用本地管理臨時表空間、多文件分佈、監控溢寫 Top SQL、優化 SQL 減少不必要排序。