Oracle排序作業優化完整指南：記憶體、臨時表空間與監控實戰

一、排序記憶體架構

• 專用伺服器模式：排序區在PGA (Program Global Area)，獨立於SGA
• 共用伺服器模式：排序區在UGA (User Global Area)，位於Shared Pool或Large Pool
• 無Large Pool時UGA在Shared Pool，有Large Pool時UGA在Large Pool

二、自動排序記憶體管理 (9i+)

啟用後，SORT_AREA_SIZE、HASH_AREA_SIZE等手動參數自動失效。

三、三大優化策略

1. 避免排序：善用索引順序、避免不必要的ORDER BY/GROUP BY
2. 記憶體內排序：調大PGA，降低磁碟排序比例
3. 優化臨時表空間：減少空間分配呼叫

四、觸發排序的操作類型

• 建立索引 (CREATE INDEX)
• 涉及索引維護的平行插入
• ORDER BY / GROUP BY (優先對索引欄位排序)
• DISTINCT
• UNION / INTERSECT / MINUS
• SORT-MERGE JOIN
• ANALYZE (ESTIMATE模式)

五、排序效能監控與診斷

-- 關鍵指標
SELECT * FROM v$sysstat WHERE name LIKE '%sort%';
-- sorts (memory)    : 純記憶體完成排序數
-- sorts (disk)      : 需磁碟臨時段排序數
-- sorts (rows)      : 總排序行數

-- 磁碟排序比率 < 5% 為健康
SELECT disk.value disk, mem.value mem, (disk.value/mem.value)*100 ratio
FROM v$sysstat disk, v$sysstat mem
WHERE disk.name='sorts (disk)' AND mem.name='sorts (memory)';

比率超標 → 增加PGA_AGGREGATE_TARGET或SORT_AREA_SIZE (手動模式)。

六、臨時表空間監控與配置最佳實踐

-- 即時監控
SELECT tablespace_name, current_users, total_extents, used_extents,
       extent_hits, max_used_blocks, max_sort_blocks
FROM v$sort_segment;

臨時表空間建立規格

• INITIAL/NEXT = SORT_AREA_SIZE整數倍 + 1區塊(段標頭)
• PCTINCREASE = 0 (固定Extent大小)
• 依排序類型建立多個臨時表空間，指派不同用戶群
• 臨時檔案分散多顆實體磁碟，平衡I/O

七、NOSORT索引建立技巧

資料已預排序時，建立索引可跳過排序階段：

CREATE INDEX idx_name ON table_name(col_name) NOSORT;

需確認資料確實依該欄位有序，否則報錯。