Oracle SQL 排序機制與記憶體管理：SORT_AREA_SIZE、磁碟排序避免與參數調校

排序是隱性效能殺手。CREATE INDEX、ORDER BY、GROUP BY、MERGE JOIN 皆觸發排序。記憶體排序 vs 磁碟排序效能差 **14,000 倍**。

一、排序記憶體架構

• 專用連接：PGA 中分配，大小由 SORT_AREA_SIZE (舊版) 或 PGA_AGGREGATE_TARGET (9i+ 自動) 決定。
• 共享伺服器 (MTS)：Large Pool 中分配。
• 保留記憶體：SORT_AREA_RETAINED_SIZE — 排序結束後保留供後續 fetch 使用。

二、磁碟排序觸發與代價

• 排序需求 > 記憶體上限 → 溢寫 TEMP 表空間 (磁碟排序)。
• 代價：極慢、佔用 TEMP 空間、佔用 Buffer Cache 緩衝 TEMP 塊、推高 free buffer waits、排擠其他任務資料塊。

三、參數調校策略

四、監控與診斷

-- 系統級排序統計
SELECT name, value FROM V$SYSSTAT 
WHERE name IN ('sorts (memory)','sorts (disk)','sorts (rows)');

-- Session 級排序記憶體使用
SELECT s.sid, s.username, sw.value "Sort Memory (bytes)"
FROM V$SESSION s, V$SESSTAT sw, V$STATNAME sn
WHERE sw.statistic# = sn.statistic#
  AND sn.name = 'sort area size'
  AND s.sid = sw.sid;

目標：sorts (disk) / sorts (memory) < 1%。超標 → 增加 PGA 總量或單次上限。

五、實務建議

1. 9i+ 全面啟用 PGA_AGGREGATE_TARGET + WORKAREA_SIZE_POLICY=AUTO。
2. 大型數倉報表 Session 可用 ALTER SESSION SET SORT_AREA_SIZE=... 臨時加大 (MANUAL 模式下)。
3. 避免不必要的 ORDER BY、DISTINCT、UNION (改 UNION ALL)。
4. 索引建立指定 NOSORT (資料已排序) 或 PARALLEL 分散排序負載。

1. 上一篇文章： Oracle Freelist 內部機制深度剖析：段頭、鏈表操作與高併發調優
2. 下一篇文章： Oracle SQL 調優四大黃金法則：消除全掃、快取小表、最優索引、最優連接

Oracle 資料庫基礎

Oracle 10g 段空間管理新功能：SHRINK SPACE 線上回收碎片、重置 HWM 完整實戰

一、傳統痛點：HWM 卡住空間、全表掃描讀空塊 刪除資料後，

Windows Oracle ORA-1691 故障處理：LOB 表空間擴展實戰

環境 Oracle Database 11g EE 11.

資料結構教學：字串操作於文本編輯器的實際應用

教學目標與重點 本課程旨在掌握文本編輯的基本原理與方法，重點

IIS 7.5 整合 Oracle Access Manager (OAM) 實現單點登入 (SSO) 完整部署指南

架構原理 瀏覽器請求受保護資源 WebGate Agent