一、排序区在哪：PGA，不在 SGA

这个区分很关键，因为它决定了「加内存加到哪」。 排序慢，加 SGA 没用，得加 PGA。 反过来，缓存命中率低，加 PGA 也没用。 PGA 的总量是 PGA_AGGREGATE_TARGET，但它不是平均分。 Oracle 是按「当前有多少个会话在做排序」动态分的—— 十个会话在排序，每人一份；只有一个在排序，它能用得多一些。 这种模式叫「自动 PGA 管理」，对应参数 WORKAREA_SIZE_POLICY=AUTO。

二、什么操作会吃排序区

下面这些都会排序，而且很多是「偷偷排的」： ORDER BY：最明显的一种。 GROUP BY 和 DISTINCT：要分组或去重，就得先排好（或者用哈希，看优化器选哪个）。 UNION／INTERSECT／MINUS：集合运算要去重，去重就要排。 建索引：索引本身就是按顺序排好的结构。 排序合并连接：两张表各自排好再对撞。 「偷偷排」最典型的是 DISTINCT。 很多人写 SELECT DISTINCT 只是为了「去重」，没意识到它在排序。 如果只是想去重、不在乎顺序，GROUP BY 可能更划算——优化器有机会改用哈希聚合，不落盘。 还有一个容易忽略的：ORDER BY 后面跟了索引列，也可能不排序。 如果索引的顺序正好和 ORDER BY 一致，优化器会直接顺着索引读，执行计划里连 SORT 都没有。 所以「排序慢」的一个解法是「建个索引让它不用排」。

三、内存排序 vs 磁盘排序

排序区够大，全在内存里排完，这叫【最优执行】（optimal）。 不够大，就先读一部分、排好、写进临时表空间，这叫【一次通过】（onepass）。 再不够，同样的数据要读写好几遍，叫【多次通过】（multipass）。

V$SQL_WORKAREA 里能直接看到每条 SQL 的 EXECUTIONS、OPTIMAL_EXECUTIONS、 ONEPASS_EXECUTIONS、MULTIPASS_EXECUTIONS。 如果 MULTIPASS 不是 0，就该加 PGA 了。 这个视图比「看总体命中率」精确得多，因为它能看到是【哪条 SQL】在落盘。

四、专用模式下的调整

专用服务器模式下，每个会话一个进程、一份 PGA，排序区是私有的。 调法很简单： PGA_AGGREGATE_TARGET 定总量，WORKAREA_SIZE_POLICY 设成 AUTO，完事。 千万别手动设 SORT_AREA_SIZE。 这个参数是「每个会话最多用多少」，它是按会话上限算的，不是按总量。 设成 100M，一百个会话同时排序就是 10G——机器直接 OOM，而且是在最忙的时候。 手动模式只有一个场景值得用： 某个批处理任务的单个排序特别大，自动管理给不够，又不想把整个 PGA_AGGREGATE_TARGET 抬高。 做法是在那个会话里 ALTER SESSION SET WORKAREA_SIZE_POLICY=MANUAL 再设 SORT_AREA_SIZE， 影响范围只在这一个会话。

五、共享模式下的调整

共享服务器模式下，会话和进程是分离的，排序区不能放在 PGA 里—— 因为进程是共用的，下一个人接着用的时候，上一个会话的数据还在里面。 所以这时候排序区从【大池】里分配，参数是 SORT_AREA_SIZE 和 SORT_AREA_RETAINED_SIZE。 SORT_AREA_RETAINED_SIZE 管的是「排完之后保留多少」。 排序做完，结果集还要一行行返回给客户端，这部分内存就是它管的。 设成 0 就是「排完立刻释放」，每次取下一行都要重新读临时段—— 这个坑很隐蔽，现象是「排序本身不慢，但取结果特别慢」。 不过说实话：现在几乎没有新系统用共享服务器。 它的好处是「连接数很多时省内存」，坏处是「一个会话的慢 SQL 会拖累同进程的其他人」。 连接数多的问题，现在一般用连接池解决，不用共享服务器。

六、怎么判断排序区够不够

第一个指标：V$SYSSTAT 里的 sorts (memory) 和 sorts (disk) 的比例。 落盘比例超过 5% 就该查了。 注意这是个累计值，要从两次采样相减才看得出「现在」的情况。 第二个指标：V$PGASTAT 里的 cache hit percentage。 这个数是「有多少工作区是在内存里完成的」，90% 以上算健康。 同一张视图里的 over allocation count 更直接：它数的是「内存不够、被迫缩小工作区」的次数， 这个数一直在涨就是明显不够。 第三个：V$PGA_TARGET_ADVICE。 和 14.2 讲的 V$DB_CACHE_ADVICE 一个思路：给一张「如果 PGA 是 X，命中率会是多少」的预测表。 这张表的价值在于告诉你曲线在哪变平。

七、动手 Demo：内存排序和落盘排序，实测差多少

这一篇的 Demo 是自己实现一次外排：把数据分块排好、写进临时文件，再归并读回来。 这正是 Oracle 在排序区不够时做的事，只是它写的是临时表空间。 两次结果会用断言比一遍，确认「快的那条路」和「慢的那条路」答案一样—— 否则比的就不是快慢，是对错。

# 内存排序 vs 落盘排序（外排 + 归并）
import heapq, os, random, tempfile, time
d = os.path.join(tempfile.gettempdir(), "sort63")
os.makedirs(d, exist_ok=True)
rnd = random.Random(9)
rows = [rnd.randint(1, 10 ** 9) for _ in range(400000)]

def ext_sort(rows, cap):
    runs = []
    for i in range(0, len(rows), cap):
        chunk = sorted(rows[i:i + cap])
        fp = os.path.join(d, "run%03d.txt" % len(runs))
        with open(fp, "w") as f:
            f.write("\n".join(str(x) for x in chunk))
        runs.append(fp)
    def gen(fp):
        with open(fp) as f:
            for ln in f:
                yield int(ln)
    out = list(heapq.merge(*[gen(fp) for fp in runs]))
    for fp in runs:
        os.remove(fp)
    return out

t0 = time.perf_counter()
ok = sorted(rows)
t1 = time.perf_counter()
got = ext_sort(rows, 40000)          # 每段 4 万，共 10 段
t2 = time.perf_counter()
assert ok == got
print("内存排序 %.3f 秒，落盘归并 %.3f 秒" % (t1 - t0, t2 - t1))

实跑输出：

--------------------------------------------------------------------
第六十三课：排序区——放不下就落盘，慢一个量级
--------------------------------------------------------------------

  **排序要么在内存里做，要么拆成几段、各自排好写盘，再归并读回来。**  **后者叫外排，慢很多。**
  **这一课实测这个差距，并且测出「内存要多大的排序区才够」。**

  一、内存排序 vs 落盘排序
  --------------------------------------------------------------------
  **400000 条整数：**

  方式                 耗时
  -----------------------------
  全在内存里排         0.105 秒
  分成 10 段落盘再归并 0.764 秒
  已选择 2 行
  **落盘这一路慢 7.3 倍。**  **两次结果完全一样（脚本里断言过），差的全是 IO 和归并。**
  **Oracle 里这个差距只会更大。**
  **因为它的临时表空间读写在磁盘上，而这里的文件大概率还在系统缓存里**——
  **换句话说，上面这个倍数是个【乐观】的下限。**

  二、排序区给小了，落盘段数会翻倍
  --------------------------------------------------------------------

  排序区（条） 落盘段数 耗时
  ------------------------------
  200000       2        0.721 秒
  50000        8        0.747 秒
  10000        40       0.870 秒
  已选择 3 行
  **排序区从 20 万降到 1 万，段数从 2 段涨到 40 段，耗时跟着涨。**
  **注意涨得不是线性的**——归并的代价和段数有关，也和每段大小有关。
  **Oracle 里这个「段数」有个名字：`sorts (disk)` 计数器涨一格就是落盘一次。**
  **查 `V$SYSSTAT` 里 `sorts (memory)` 和 `sorts (disk)` 的比例**，**就知道排序区够不够。**
  **落盘比例高，就该加大 `PGA_AGGREGATE_TARGET`。**

  三、真正吃排序区的操作，比你想的多
  --------------------------------------------------------------------
    ORDER BY、GROUP BY、DISTINCT      最常见的三种
    UNION、INTERSECT、MINUS           要去重，就得排
    排序合并连接（SORT MERGE JOIN）  两张表先各自排好再对撞
    建索引、ANALYZE                   索引本身要按顺序建
  **所以「一条查询慢」不一定是它没走索引，也可能是它在偷偷排序。**
  **`EXPLAIN PLAN` 里看到 `SORT ORDER BY` 就要留意，看到 `SORT ORDER BY STOPKEY` 反而还好**——
  **`STOPKEY` 意思是「只要前 N 行」，排到 N 就停。**

  四、专用模式 vs 共享模式：排序区从哪来
  --------------------------------------------------------------------
  **专用服务器（dedicated）：一个会话一个进程，排序区在 PGA 里，私有的。**
  **共享服务器（shared）：会话和进程是分离的，排序区只能用 large pool 里那块公共的。**
  **所以共享服务器模式下，`SORT_AREA_SIZE` 这类参数的意义变了**——
  **它不再按会话分配，而是抢公共池。**
  **现在几乎没人用共享服务器了，知道有这回事就行。**

  五、调排序区的正确姿势
  --------------------------------------------------------------------
  **第一步：设 `PGA_AGGREGATE_TARGET`（比如 2G），把 `WORKAREA_SIZE_POLICY` 设成 `AUTO`。**
  **第二步：什么都不用做。**
  **Oracle 会按「当前有多少个排序在跑」自动分配，单个排序最多能用总目标的 5%。**
  **第三步：看 `V$PGASTAT` 里的 `cache hit percentage`。**
  **这个数在 90% 以上就不用管了；低于 80% 才考虑加内存。**
  **别拿 `SORT_AREA_SIZE` 硬调**——
  **那是手动管理模式，一个会话就能吃掉一大块内存，几十个会话一起来就 OOM。**

第一段：40 万条整数，全在内存里排 0.1 秒级，分成 10 段落盘再归并要 0.7 秒级。 差七倍上下。 这个倍数要打个折扣看：这里的「磁盘」大概率还在操作系统的文件缓存里，没真的落到盘上。 Oracle 的临时表空间是实打实的读写，所以真实差距只会更大。 换句话说，七倍是【乐观】的下限。 第二段：排序区从 20 万条降到 1 万条，段数从 2 段涨到 40 段，耗时跟着涨。 注意涨得不夸张——因为归并是 k 路归并，段数多了每一路反而更短。 真正贵的是「每段都要写一次盘、读一次盘」这个固定成本。 这也解释了 Oracle 的一个现象：sorts (disk) 涨了不一定就慢很多，但如果 multipass 出现了， 那就不是几倍的事了。 因为 multipass 是「同一批数据读写好几遍」，成本按遍数翻。 第三段列的那些操作，回到执行计划里都能对应上： SORT ORDER BY、HASH GROUP BY、SORT UNIQUE、MERGE JOIN SORT。 看到 SORT 开头的，就知道它在吃排序区。

30 秒实验

1. 把 cap 从 40000 改成 400000（一段搞定），再改成 4000（100 段），各跑一次。 看耗时怎么变——这就是「排序区大小」对落盘排序的影响曲线。
2. 把数据量从 40 万降到 4 万，重跑。 倍数的差距会缩小——因为固定开销（建文件、开文件）摊到了更少的数据上。
3. 把 ext_sort 里的归并改成「两两归并、多轮」（外部归并排序的经典做法），再测。 段数多的时候它比一次 k 路归并更省内存。

常见误区

1. 「只有 ORDER BY 才排序」。 GROUP BY、DISTINCT、UNION、建索引、 排序合并连接全都要。
2. 「SORT_AREA_SIZE 调大就能加快排序」。 自动管理下这个参数根本不生效。 手动模式下它按会话算，几十个会话一起来就 OOM。
3. 「排序慢就加 SGA」。 排序区在 PGA 里。加错地方等于没加。
4. 「sorts (disk) 有值就是有问题」。 大查询落盘很正常。 要看比例，还要看有没有 multipass。

第 14 章到此结束。参数、SGA、排序区，三块内存调完，接下来该看 SQL 本身了。 下一篇进第 15 章：SQL 调优——从「加内存」转向「改写语句、加索引、看执行计划」。

• ☐ （本篇完，下一篇 15.1 常规 SQL 语句优化）

相关笔记

• 系统全局区（SGA）优化
• 索引对象
• PL/SQL 游标
• 1. 🐬 MySQL必知必会/00-索引
• 🏺 Oracle从入门到精通 · 索引