年前接了个活要把一套基于 Oracle 的业务系统往崖山数据库YashanDB上迁。迁移方案评审时开发提了个很实在的问题两边的排序性能到底差多少毕竟报表模块一大堆ORDER BY还有不少存储过程里做了大批量SELECT ... ORDER BY再逐行处理的逻辑排序慢直接卡用户页面。与其拍脑袋不如先做个专项测试。这篇文章就记录我这次“Oracle vs 崖山排序性能”对比测试的完整过程包括环境准备、用例设计、实测数据、差异分析和几个坑。如果你也在评估同类兼容数据库的排序能力可以直接抄作业。1. 测试背景与目标设计排序是数据库最基础、也最能暴露问题的操作之一。它不只是ORDER BY那一下DISTINCT、GROUP BY、UNION、MINUS甚至JOIN里的 merge sort、CREATE INDEX的 sort 阶段底层都离不开排序。所以单测一条ORDER BY并不能代表真实负载我把这轮测试定位成“排序场景专项摸底”重点不是跑分而是找差异。1.1 为什么要单独测排序很多做兼容性评估的团队习惯拿 TPC-H 或者 TPC-DS 整套跑一遍。这个思路没错但覆盖面太广一旦某个查询出现数量级差异反而很难定位是优化器问题、统计信息问题、还是排序实现本身的问题。排序专项测试的优势在于场景内聚、变量可控、结果好解释。我这次对接的业务系统里排序负载大致分成三类大量小结果集排序分页查询、中等数据量全排序报表明细以及大结果集ORDER BY后写临时表的批量加工。对应到数据库内核正好覆盖内存排序、内存溢出到临时表空间排序、以及排序算子与其它算子尤其 hash join、hash group by的组合场景。把这些摸一遍迁移风险评估也就有了底。另一个关键点排序和内存参数、统计信息、临时表空间都强相关。同一个 SQL、同一个数据量参数不同排序路径可能完全不一样Oracle 里常见的SORT ORDER BY / STOPKEY、SORT GROUP BY在兼容数据库上是否也有对应计划这直接影响开发对“SQL 不用改”的预期。所以我这次测的不只是“谁快谁慢”还包括执行计划形态、算子行为、参数映射关系。1.2 测试目标与指标体系这次的核心问题三个单条排序 SQL 在两端执行时间差多少是否有超过 1.5 倍的差距数据量超过内存排序上限时两边的临时空间排序代价差异兼容模式下排序相关参数能否平滑映射是否出现“配置无法等效”的情况。指标不贪多抓三个响应时间首行返回最快的那种场景额外记录、执行计划形态、临时表空间/内存排序占比。响应时间用 JDBC 程序去跑避免 SQL 客户端自带的开销和网络交互噪声。执行计划在 Oracle 用dbms_xplan.display_cursor在崖山用它的会话级计划缓存视图加 EXPLAIN 命令双确认。临时表空间使用量、内存排序次数Oracle 靠v$sql_workarea和v$tempseg_usage崖山则在测试期间直接盯系统视图和TOP。指标定好接下来就是搭环境。这里有个经验测试数据库要关掉一切自动调整和自适应特性否则同一条 SQL 跑三次计划可能变三次结果没法横向比。Oracle 侧我固定了 optimizer_adaptive_featuresfalse崖山侧也找到对应的优化开关确保对比基线一致。2. 测试环境与数据准备排序测试对环境很敏感尤其是内存大小和临时表空间布局。如果两端的 PGA/workarea 参数不一致测出来的差距可能根本不是数据库内核的差距而是你配置出的差距。所以环境准备这一步不能图省事。2.1 软硬件配置与参数对齐我选了两台相同规格的物理机做隔离测试避免同机竞争。配置如下主机同一批次服务器CPU 32 核内存 128GBSSD 盘本地存储操作系统同版本 Linux关闭 swap透明大页设为 madviseOracle19c 单实例SGA 32GBPGA_AGGREGATE_TARGET16GBWORKAREA_SIZE_POLICYAUTO崖山当前官网长期支持版单实例安装时数据文件路径独立内存参数上把排序相关 workarea 上限调成与 Oracle 侧可比较的数值。参数对齐是这次测试里比较费神的一环。Oracle 的PGA_AGGREGATE_TARGET管着所有 workarea 的总预算单个排序最多可以吃到_pga_max_size的 5%默认就是 PGA 的 5%。崖山这边对 workarea 的管理方式不完全一样我按“单算子最大排序内存”对齐而不是按总量对齐。比如 Oracle 单排序最多吃 800MB崖山就同样给单排序 800MB。这样才具备可比性。注意如果你在自己的环境里复现一定要把两边优化器相关的自适应特性关掉。Oracle 是optimizer_adaptive_features、optimizer_adaptive_reporting_only崖山侧咨询官方文档确认没有默认开启的 auto plan 校正若有就关闭。否则同一个 WHERE 条件因为绑定变量 peeking 不同计划都能翻车。2.2 测试数据规模和分布设计数据我用存储过程生成而不是手工 INSERT。因为手工 INSERT 几百万行既慢又容易产生大量归档日志测试还没开始先等半天写盘没必要。生成逻辑很简单一张宽表 20 个字段前 2 个字段参与测试第一字段的数值分布做成偏态分布约有 10% 的值占据 90% 的行第二字段生成随机小数。数据规模设四档10 万行、100 万行、1000 万行、5000 万行。前两档用来测内存排序1000 万行去触发内存溢出视参数而定5000 万行看大规模临时段排序。为了排除 index 干扰所有测试表都不建索引纯粹考察 base table scan sort 算子。生成数据的时长也记录一下顺手可以观察两端的批量写性能虽然这不是本测试重点但迁移时导入数据总得心里有数。Oracle 用DBMS_RANDOM生成崖山用内置随机函数两边生成 SQL 写法基本一致说明语法兼容这块整体顺畅。2.3 测试脚本与热数据预热测试脚本用 Python JDBC连接串走 1521/监听端口。Python 里我封装了一个简单函数接收一条 SQL 和一个 number 参数执行并返回耗时。每次查询前先跑一条“SELECT COUNT(1) FROM test_table”做预热避免 OS page cache 冷热不均影响结果。每类用例跑 5 遍去掉最大最小值取中位数这个比平均值抗干扰。有一点很多人忽略显式 commit 与事务边界不要忘记处理。如果测试中你开了事务又没提交后面的会话可能读到旧快照尤其在默认读已提交隔离级别下测出来的数据是错的。我的脚本里跑查询前强制set autocommit(true)查询后立即 commit确保每一条 SQL 都是在一致快照下独立执行的。3. 排序场景用例设计与实测数据这一节是全文最核心的部分。我按照“常见业务排序类型”来设计用例而非教科书式的 sort 算子微基准。毕竟业务里没有人会天天跑ORDER BY裸表大家都带 WHERE、LIMIT、GROUP BY。3.1 六个排序场景与对应 SQL以下是实际执行的用例用例编号场景SQL 形态设计目的C1单键全量排序SELECT col1 FROM t ORDER BY col1最基础的 sort 算子直接对比C2多键排序SELECT col1, col2 FROM t ORDER BY col1, col2考察多列排序的比较函数开销C3Top-NSELECT ... ORDER BY col1 LIMIT 10验证 STOPKEY/limit 优化是否生效C4排序分页SELECT ... ORDER BY col2 OFFSET 100000 LIMIT 100业务最常见的深分页场景C5分组排序SELECT col1, COUNT(*) ... GROUP BY col1 ORDER BY col1复合算子hash group by sortC6排序写临时表CREATE TABLE tmp AS SELECT ... ORDER BY col1模拟存储过程里加工大结果集的场景这里面 C6 对迁移评估最有价值。原业务里存储过程经常干这个事把一坨“明细 JOIN 汇总 ORDER BY”的结果先落临时表再逐行处理。如果排序本身慢整个晚上的批处理都要往后推。C6 我设置了一个特殊情况临时表最终不保留测完直接 DROP所以它的耗时大量花在“排序 写出”上。3.2 100 万行数据实测结果先在 100 万行这个档位上看差异此时绝大多数排序发生在内存里单排序区 100MB 够用。结果如下中位数单位毫秒用例Oracle崖山差异倍数C1 单键全量排序3183421.08C2 多键排序4555071.11C3 Top-N6.27.11.15C4 分页offset 10万2264101.81C5 分组排序3984361.10C6 排序写临时表7026890.98前三类纯排序差距很小1.1 倍以内基本可以接受。C4 这里出现了 1.8 倍差异说明两端的优化策略开始分化。继续看执行计划Oracle 对OFFSET 100000是“排序后直接跳过前 10 万行”并不会把前 10 万行逐行 fetch 完再丢崖山在兼容模式下走了另一个路径实际多读了不少行才跳到目标位置。这个问题不是排序算法本身慢是分页优化器的短路逻辑不同。开发如果遇到深分页慢优先改装为“keyset pagination游标式分页”或者把 OFFSET 改写成基于上一页最大值的WHERE col2 上一页末值形式。C6 反而是崖山略快推测原因是写临时表这段崖山的日志路径比 Oracle 少了一层 redo 相关内容纯 IO 写盘时占了点便宜。C6 也说明一个道理排序性能不能脱离“排序之后做什么”来谈。3.3 5000 万行大规模排序实测把数据量拉到 5000 万行单排序区 800MB必然会溢出一部分到临时表空间。执行结果用例Oracle崖山差异倍数C1 单键全量排序11240157301.40C2 多键排序17900231801.29C5 分组排序14520178201.23C6 排序写临时表27650252000.91C1/C2/C5 在溢出排序场景下差距放大到 1.2~1.4 倍。这个现象很典型内存排序阶段两边的算法和数据结构差异不大一旦数据落盘临时段 IO 的调度策略、批量写块大小、甚至临时表空间文件数量都会影响最终耗时。Oracle 的临时段 IO 做了很多年优化单进程写临时文件时能维持较大 IO 粒度崖山目前对临时表空间的排序写盘机制相对朴素一些多路径并行写、大块写等特性还没有完全对齐。C6 依然反常虽然排序量变大了但崖山整体耗时反而更低。为了排除偶然性我把 C6 换成INSERT INTO tmp SELECT ... ORDER BY再测一遍结果差距也只在 1 倍上下说明崖山的落临时表和最终表路径对大批量写比较友好这和它的存储引擎在 append 场景的优化有关。这也提示我们如果迁移后主要压力来自“大量排序后写表”崖山不一定吃亏不用盲目担心。看两个 5000 万行 C1 的执行计划细节OracleSORT ORDER BY算子tempSpill发生在第 2 次 get 前临时段使用了约 4.2GB崖山执行计划中排序算子标注了“spill_run3”临时空间统计大致与 Oracle 相当但读临时段的块大小偏小导致全过程 IO 次数更多。这说明单独看“执行时间差异”是不够的观察临时段读写次数会提供更细的解释。如果后续你还要优化优先给崖山临时表空间分配更多、更大的数据文件并且把排序区调大尽量让更多排序留在内存里这也是对这 1.4 倍差距最直接的解药。4. 结果差异分析与参数调优对照测完了不能只看一张表就收工。找出差异分析根因给出可落地的参数对照建议才是这次测试最有价值的部分。4.1 从执行计划看排序算子的“隐藏差距”前面说了 C4 深分页场景差距最大原因不在排序算法本身而在于优化器对“排完序后跳行”的实现方式。Oracle 的SORT ORDER BY / STOPKEY配合 FETCH FIRST 语义其实只对最终结果集那 100 行做完整排序前 10 万行在排序阶段就被跳过了——它知道不需要保留这部分数据。崖山在兼容执行时优化器更接近“先把整体结果排出来再做 LIMIT/OFFSET”导致前 10 万行虽然最终没有进入结果集却消耗了排序比较的开销。这一点在执行计划里能非常直观地看到Oracle 是SORT ORDER BY STOPKEY输入行数 100 万输出行数 10 万崖山则是普通SORT算子输入输出都显示 100 万直到 LIMIT 算子才截断。识别算子顺序和行数估算差异比起只盯最终耗时更能解释性能问题。C1 在 100 万行数据量下几乎没有差异到 5000 万行才出现 1.4 倍差距说明单纯比较 key 的比较函数或算法实现没有意义。真正差在临时排序段的管理策略。我看了两边的临时文件 IO 特征Oracle 临时段倾向于顺序写大块且在排序过程中可以同时做“外排序归并”和“预取下一块”崖山的临时表空间底层在写大块时表现尚可但在读回做归并时IO 次数多了一些。如果你在崖山侧把临时表空间文件规划成多个并确保它们分布在不同的物理盘或者 LUN 上归并并行度就会有可感知的提升。4.2 排序内存参数映射表这让“迁移后要不要调参”有了明确答案。两边排序内存相关的核心参数我是这么对标的参数作用Oracle 参数崖山参数备注工作区总预算PGA_AGGREGATE_TARGETworkarea_size_policy / 同类内存池参数崖山按会话或算子管理含义不完全等同单排序算子上限由_pga_max_size间接控制SORT_AREA_SIZE部分版本建议统一按算子内存对齐临时表空间配置TEMP tablespace 多个数据文件临时表空间文件数量与大小直接决定 spill 排序的 IO 并行度排序区自动/手动WORKAREA_SIZE_POLICY对应自动管理开关默认开建议都保持自动我不建议直接把 Oracle 的参数名套到崖山上比如SORT_AREA_SIZE这类在主流 Oracle 版本里已经被自动管理取代了崖山即使保留了兼容参数默认值和行为也未必一致。先查两边默认值。实测中我发现崖山默认单排序区只有 64MB这时 1000 万行的 C1 就会有可观的数据溢出到临时段。后来我把单排序区上限调到 800MB性能和第一次测相比有明显提升。这个操作在 Oracle 上对应的是提高 PGA 大小和_pga_max_size在崖山上就调整对应自动管理内存参数。4.3 统计信息与优化器行为对排序的影响排序测试特别容易踩到一个坑统计信息不新鲜优化器行数估算错了选错排序策略。比如一个表实际 100 万行统计信息却显示 10 万行优化器就可能选择一个合并排序的连接而不是 hash join计划形态完全变了。所以每次造完数据我都立即收集统计信息。Oracle 用DBMS_STATS.GATHER_TABLE_STATS崖山用它的ANALYZE TABLE或等价命令。收集完之后我会查一下优化器估算的行数和实际行数是否一致误差控制在 5% 以内再继续。在 5000 万行场景下我在崖山发现过一个问题直方图缺失导致ORDER BY 偏态列时优化器认为排序结果可以提前终止实际却要全排。造数据时第一字段是高度偏态的这非常符合真实业务里“少数状态值对应大量记录”的情况。缺直方图优化器无法判断每个值的选择性于是计划选择了错误的算子。加完直方图排序耗时下来大约 22%。这个结论很重要如果迁移后你发现某个排序查询突然慢了第一反应不该是吐槽内核而是检查统计信息和直方图是否完整迁移。为了让文章里这部分更有指导价值我把统计信息收集的几条检查都写出来后面排查问题好对照。细节见下一节。5. 常见问题与排查技巧实录这轮测试里也踩了不少坑有些是环境问题有些是兼容性问题单独写一节省得你再走弯路。这里直接罗列问题、现象和解决方案。5.1 问题速查表问题现象排查思路解决办法ORA-01652 临时表空间不足大规模排序直接报错查看临时表空间使用率扩容临时表空间数据文件或调大排序区减少溢出深度分页超时C4offset 越大越慢对比执行计划算子改写成 keyset 分页或增大排序区减少 IO统计信息缺失造成计划异常排序耗时忽高忽低对比优化器估算行数和实际行数收集直方图与表统计信息校验行数误差绑定变量 peeking 差异同 SQL 首次快二次慢查看执行计划是否变化固定计划或清理共享池重新测试兼容模式下排序结果不稳多次执行差异 30%查 OS 内存、swap、page cache 冷热预热后测试必要时锁定 buffer cache临时表空间文件过少5000 万行 spill 排序卡在 IO观察临时文件读写频次多建临时文件分散 IO或分配独立存储5.2 一个典型的“排序慢”排查过程我挑一个印象最深的来复盘。测试 C4 深分页时崖山第一次跑 410ms比 Oracle 的 226ms 慢了不少。我第一反应是“排序算子的归并实现有差距”差点直接下结论。后来我把执行计划调出来仔细看发现崖山的计划里有一个SORT算子吃掉了全量输入而 Oracle 那边是SORT ORDER BY STOPKEY。进一步做 10053 事件和等价 trace 分析Oracle 这边确认问题出在优化器对 LIMIT 的“短路传递”。崖山对ORDER BY ... OFFSET ... LIMIT的整体优化能力还没有完全对齐于是它老老实实把中间结果集全部排完。这不是排序算子的锅是优化器规则层的差异。我尝试的修改方案是把这个 SQL 改成WHERE col2 上一页最大值 ORDER BY col2 LIMIT 100的 keyset 写法。改写后崖山耗时降到了 260ms 左右。这个实操经验非常值得记下来。以后如果迁移中遇到深分页慢优先改写 SQL 而不是调一大堆内存参数效果又快又稳。还有一个和存储过程有关的坑。原系统里有个存储过程循环 5000 次执行“SELECT ID FROM t ORDER BY SCORE LIMIT 1”累计要跑 5000 遍小排序。测试时我一开始用 Python 一条条循环每次通过 JDBC 提交结果慢得离谱。后来改成存储过程内部一次性把候选 ID 集抓出来应用层再取第 N 条性能反而提升明显。这说明排序性能在客户端循环场景下瓶颈往往不在排序本身而在网络往返与每条 SQL 的解析执行开销。测试和优化的时候要分清楚这两个层面。5.3 关于“排序写临时表”的额外观察C6 这个场景我在测试过程中还发现一个小差异Oracle 建临时表的方式有 GLOBAL TEMPORARY TABLEGTT和普通表两种用 GTT 时因为临时段机制不同写盘路径和普通表不一样。崖山对 GTT 的兼容也做了实现但两个库的 GTT 排序写临时文件策略仍有区别。如果你的业务场景就是“存储过程里CREATE TABLE tmp AS SELECT ... ORDER BY”我建议在崖山上把临时表改成“会话级临时表”让它走内部临时段路径尽量不产生 redo如果直接建普通表再删除会消耗不必要的日志和磁盘空间排序总耗时也会更慢。这个细节我在迁移评估报告里单独给开发标红了。6. 测试结论与迁移建议补充一段结论性质的内容但不是“综上所述”式空洞总结而是从我这轮测试里提炼出的、可以直接指导后续迁移工作的几个要点。测试数据互相印证不多放跑分表重点讲规律。6.1 核心发现整理纯内存排序场景数据量远小于排序区Oracle 与崖山的差距在 1.1 倍左右属于可接受范围大结果集 spill 排序场景崖山比 Oracle 慢 1.2~1.4 倍主要差异在临时表空间 IO 调优空间而不是排序算法本身ORDER BY ... OFFSET ... LIMIT深分页是现阶段差距最大的场景建议直接改 SQL 写法不要依赖参数调优“排序 写表”场景崖山表现不差甚至在部分用例里反超说明迁移中这种批处理场景可以放宽心统计信息、直方图、workarea 参数的对齐对结果影响巨大。再强调一遍测试前先说清楚两边参数基线否则结论不可信。6.2 对你后续迁移测试的三个可操作建议第一搭建测试环境时不要把时间省在“关闭自适应特性”上。我曾试过不关 Oracle 的自适应计划结果同一条 SQL 换了绑定变量后执行时间从 300ms 跳到 800ms导致我误判了两边差异。测试收敛之后再打开自适应特性做一轮“默认配置下的表现摸底”日常业务跑在默认配置上这也是真实性能的一部分。第二把测试用例按“内存排序 / spill 排序 / 深分页 / 排序写表”四类分开不要只盯一个ORDER BY用例的耗时。每一类背后对应的是不同的优化空间内存排序看算子实现spill 排序看临时段 IO深分页看优化器短路排序写表看日志与存储引擎。分开测才方便迁移时按场景分别处理。第三务必把 Oracle 的自适应特性、统计信息、workarea 参数和崖山的对应项列成一张映射表贴在测试报告开头。如果后续要复测或者换了新版本直接对照这张表确认环境没变。我这次就靠这张映射表在复测时快速发现了某个崖山版本把排序区默认值调小了避免了把“参数差异”误读成“内核差异”。整轮测下来我的个人体会是崖山的排序性能已经不是“能不能用”的问题而是“要不要针对性调优”的问题。如果你正在做同类型的兼容性评估别被单一场景的慢查询吓到先按我上面的方法把场景拆细了再下结论。一个复杂的大查询慢能有几十种原因排序算子往往只是背锅的那个。