排序是 SQL 执行中最常见的操作：ORDER BY、GROUP BY、HASH JOIN、CREATE INDEX 都要排序。Oracle 优先在 PGA 的 Sort Area 里做内存排序，如果 Sort Area 不够，就溢写到临时表空间（磁盘排序）。磁盘排序不仅慢，还会产生大量临时段 I/O，撑满临时表空间时报 ORA-01652。这篇文章把 PGA 排序区、磁盘排序机制、临时表空间管理，以及一个我遇到的"凌晨排序爆 TEMP"案例拆清楚。

1 内存排序 vs 磁盘排序：分水岭在 PGA

PGA_AGGREGATE_TARGET 控制 PGA 总量，但单个操作的排序区上限是 _SORT_AREA_SIZE（隐含参数，通常自动管理）。一个操作如果需要排序 100MB 数据，而可用排序区只有 10MB，Oracle 会用两阶段排序：第一阶段，把数据分成 10MB 的"运行段"（run），在内存里排好每个 run，写到临时表空间；第二阶段，合并这些 run。这就是磁盘排序。V$SQL_WORKAREA_ACTIVE 可以查看当前正在执行的排序操作，WORK_AREA_SIZE 是分配的排序区，EXPECTED_SIZE 是预计需要的，ACTUAL_MEM_USED 是实际使用的。

如果 ACTUAL_MEM_USED < EXPECTED_SIZE，说明发生了磁盘排序（MULTIPASSES 或 ONEPASS）。理想状态是 OPTIMAL（全部在内存里，一次排完）。ONEPASS 是勉强能接受（一次溢写，一次合并），MULTIPASSES 是灾难（多次溢写合并）。

2 临时表空间不是普通的表空间，是排序的"下水道"

临时表空间只存临时段（排序段、哈希段、位图段），不存永 久数据。它的特点是：会话结束时自动释放空间，不需要 DBA 手动回收。但多个会话共享同一个临时表空间，如果某个大查询占用了 100GB 临时空间，其他会话的排序就没空间了，报 ORA-01652。

临时表空间组（Temporary Tablespace Group）可以把多个临时表空间绑在一起，会话自动轮询使用，分散 I/O 压力。临时文件（Tempfile）和普通数据文件的区别是：tempfile 不记录 redo（因为临时数据不需要恢复），且可以设为 AUTOEXTEND，但 maxsize 要有限制，防止无限膨胀。

3 实验：模拟大排序、观察磁盘排序与 TEMP 满

实验环境：Oracle 19c，PGA_AGGREGATE_TARGET=2G，临时表空间 TEMP 5GB。

-- 查看当前 PGA 和临时表空间设置
SHOW PARAMETER PGA_AGGREGATE_TARGET;
SELECT tablespace_name, file_name, bytes/1024/1024 mb, maxbytes/1024/1024 max_mb
FROM dba_temp_files;

-- 造一张大表，1000 万行
CREATE TABLE big_sort_table AS
SELECT rownum id, DBMS_RANDOM.STRING('A',100) payload, DBMS_RANDOM.VALUE(1,1000000) sort_key
FROM dual CONNECT BY ROWNUM <= 10000000;

-- 测试 1：小排序（内存排序）
SELECT * FROM big_sort_table WHERE id < 100000 ORDER BY sort_key;
-- 观察 V$SQL_WORKAREA_ACTIVE
SELECT sql_id, operation_type, policy, estimated_optimal_size/1024/1024 opt_mb,
       actual_mem_used/1024/1024 used_mb, tempseg_size/1024/1024 temp_mb, passes
FROM v$sql_workarea_active WHERE sql_id = '...';
-- 预期：OPTIMAL，passes=0，temp_mb=0

-- 测试 2：大排序（磁盘排序）
SELECT * FROM big_sort_table ORDER BY sort_key;
-- 预期：ONEPASS 或 MULTIPASSES，temp_mb > 0

-- 测试 3：撑满 TEMP
-- 同时开 5 个会话，每个都做大排序
-- 会话 1-5：
SELECT * FROM big_sort_table ORDER BY sort_key;
-- 预期：前几个会话成功，后面的报 ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

实验结果：小排序（10 万行）时，排序区 8MB，全部内存完成，passes=0，耗时 0.5 秒。大排序（1000 万行）时，Oracle 分配了 1GB 排序区（PGA 上限），但仍不够，溢写 2.3GB 到 TEMP，ONEPASS，耗时 45 秒。5 个会话同时大排序时，TEMP 5GB 很快被占满，第 4 个会话开始等待，第 5 个会话报 ORA-01652。

应急处理：

-- 紧急加临时文件
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/temp02.dbf' SIZE 10G AUTOEXTEND ON MAXSIZE 20G;

-- 杀掉占用大量 TEMP 的会话
SELECT s.sid, s.serial#, s.username, t.blocks*8192/1024/1024 temp_mb, sql.sql_text
FROM v$sort_usage t
JOIN v$session s ON t.session_addr = s.saddr
LEFT JOIN v$sql sql ON t.sql_id = sql.sql_id
ORDER BY t.blocks DESC;
-- ALTER SYSTEM KILL SESSION 'sid,serial#';

4 那个凌晨排序爆 TEMP 的案例

一个数据仓库，凌晨跑 ETL，有个步骤是对 5 亿行数据做 ORDER BY。平时 PGA_AGGREGATE_TARGET=10G，能撑住。某天新上了个并行度 16 的 hint，16 个并行进程每个都要排序 5 亿行的一部分，PGA 总量虽然 10G，但每个进程分到不到 1GB，大量磁盘排序，TEMP 100GB 半小时内爆满。DBA 当时加了 TEMP 文件到 200GB，但 ETL 还是慢——因为磁盘排序是 I/O 瓶颈，不是空间瓶颈。最后解决办法：把并行度降到 4，每个进程 PGA 分到 2.5GB，内存排序比例提升，TEMP 只用到 30GB，ETL 从 4 小时降到 1.5 小时。这个案例说明：加 TEMP 空间只能解决 ORA-01652，解决不了磁盘排序慢的问题。

【踩坑笔记】监控 TEMP 使用，不能只看表空间使用率，要看 V$SORT_USAGE 里的活跃排序。如果某个会话的 TEMP 占用超过 10GB 且持续半小时，要告警——它可能是失控的笛卡尔积或缺失连接的排序。另外，PGA_AGGREGATE_TARGET 在 Exadata 上可以设大（因为内存多），但在普通服务器上，别超过物理内存的 25%，否则 OS swap 会拖死整个系统。

5 总结：排序优化的三层漏斗

第一层，SQL 层：减少不必要的排序。比如用 UNION ALL 代替 UNION（UNION 要排序去重），用 EXISTS 代替 DISTINCT，在索引列上 ORDER BY（利用索引有序性，避免额外排序）。第二层，PGA 层：确保大排序操作有足够的 PGA，必要时加工作区大小（_SORT_AREA_SIZE 或 PGA 整体调大）。第三层，TEMP 层：临时表空间设多个文件，分散 I/O；用临时表空间组分散会话压力；监控并杀掉异常排序会话。最后送一句话：排序是数据库的"重体力活"，能不在磁盘上做就别做。内存排序是电梯，磁盘排序是爬楼梯，差十倍。