测试Oracle-崖山排序性能

TEST02数据来自DBA_OBJECTS，共8w行数据，不创建索引
TEST01数据来自TEST02，重复512次，共4500W行，不创建索引
TEST01在Oracle中占用5G左右磁盘空间

SQL> select bytes/1024/1024 size_mb from user_segments where segment_name='TEST01';

   SIZE_MB
----------
    4976.5

测试SQL如下：

select *
  from (select a.*, rownum rn from (select * from test01 order by rowid) a)
 where rn = 40000000;  

select *
  from (select a.*, rownum rn from (select * from test01 order by object_id) a)
 where rn = 40000000;  

select *
  from (select a.*, rownum rn from (select * from test01 order by object_name) a)
 where rn = 40000000;  
 
select *
  from (select a.*, rownum rn from (select * from test01 order by owner) a)
 where rn = 40000000;  
 
select *
  from (select a.*,
               row_number() over(partition by object_id order by rowid) rn
          from test01 a)
 where rn = 1;   
 
select *
  from (select min(rowid) over(partition by object_id) min_rowid, a.*
          from test01 a)
 where min_rowid = rowid;

注意：如果你用的是Oracle19c或者Oracle26ai进行测试，如果你发现上面SQL在PLSQL DEV中比SQLPLUS中跑得快，在SQLPLUS中设置set rowprefetch = 100就正常了

测试结果如下：

这个测试结果让人大跌眼镜，难道是遇到BUG了？或者是我参数设置不对？
先来看一下Oracle与排序相关参数

SQL> show parameter pga;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit                  big integer 8G
pga_aggregate_target                 big integer 4G

SQL> show parameter workarea;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy                 string      AUTO

workarea_size_policy=AUTO 表示让Oracle自动从pga_aggregate_target分配内存用于排序，看一下崖山有没有类似参数

SQL> show parameter pga;

NAME                                                             VALUE                                                            
---------------------------------------------------------------- ---------------------------------------------------------------- 

0 rows fetched.

SQL> show parameter workarea;

NAME                                                             VALUE                                                            
---------------------------------------------------------------- ---------------------------------------------------------------- 

0 rows fetched.

SQL> show parameter sort;

NAME                                                             VALUE                                                            
---------------------------------------------------------------- ---------------------------------------------------------------- 

0 rows fetched.

在崖山数据库中没找到pga，workarea，sort类似参数，查找崖山官方文档，学习Oracle一般是从阅读concepts开始，还好崖山也有concepts，也就是概念手册，我在崖山概念手册—>实例架构—>内存体系 找到了下面内容：

临时缓存是虚拟内存的纯内存部分，主要用于索引构建的排序、TEMP_LOB缓存以及单行执行算子物化区缓冲。
当该缓存区内存不足时，系统会将部分数据换出到SWAP表空间（即使用磁盘空间拓展内存），提供超过临时缓存大小的容量空间

继续搜索官方文档，在SWAP表空间管理章节看到了下面内容：
数据库操作（例如order by，hash join，统计信息收集等）首先会通过数据库虚拟内存（通过VM_BUFFER_SIZE参数控制）缓存计算的中间结果，但如果虚拟内存不足时，需要通过将虚拟内存交换到SWAP表空间来释放内存，必要时再将内存从SWAP表空间换入

查看当前VM_BUFFER_SIZE大小：

SQL> show parameter VM_BUFFER_SIZE;

NAME                                                             VALUE                                                            
---------------------------------------------------------------- ---------------------------------------------------------------- 
VM_BUFFER_SIZE                                                   2359M                                                           
COLUMNAR_VM_BUFFER_SIZE                                          2G                                                              

2 rows fetched.

Oracle pga_aggregate_target=4GB，单个会话用于排序的内存不会超过1GB(受隐含参数控制)，一般只会用到几十到几百MB，TEST01有5GB，内存肯定放不下，会被溢出到temp表空间
PG用于排序的内存叫work_mem，一般设置为64MB，PG用64MB来排序也跑得很快(这里PG有些场景比Oracle还快，我没贴出来)
VM_BUFFER_SIZE=2359M，难道设置小了? 把它设置为8G再测试看看速度

SQL> show parameter VM_BUFFER_SIZE;

NAME                                                             VALUE                                                            
---------------------------------------------------------------- ---------------------------------------------------------------- 
VM_BUFFER_SIZE                                                   8192M                                                           
COLUMNAR_VM_BUFFER_SIZE                                          2G                                                              

2 rows fetched.

测试结果如下：

设置VM_BUFFER_SIZE=8192M后，崖山测试结果比第一次好很多，但是还是比Oracle差，Oracle可是有ON DISK排序，崖山这次没有ON DISK排序，而且崖山优化器会自动添加COUNT STOPKEY，也就是说崖山只需要排序到4000W行就停了，Oracle不会添加COUNT STOPKEY，Oracle要排序完所4500W行数据，但是崖山反而比Oracle慢，应该更快才对，崖山的排序算法需要内核再深度优化一下

通过这次测试学到了崖山VM_BUFFER_SIZE参数用法，也知道了崖山SWAP表空间作用
本来还想测试批量模式的，但是测试过程中遇到了报错，所以不贴批量模式测试结果了

SQL> select /*+ batch_mode(2) */ *
  from (select a.*, rownum rn from (select * from test01 order by object_name) a)
 where rn = 40000000;   2    3 

YAS-00105 out of memory to allocate arena page data with OTHERS tag of size = 16384

Elapsed: 00:00:39.715

最后有个疑问，设置VM_BUFFER_SIZE=8192M之后，几乎监控不到磁盘I/O，但是我添加HINT /*+ batch_mode(2) */ 进行测试时，又能监控到大量磁盘I/O，难道批量模式还要设置啥参数？

—2026-03-16更新

MEX_POOL_SIZE 批量计算中的hash join、hash group by、order by等算子会使用该内存，报错时MEX_POOL_SIZE=128MB，现在设置到8G

SQL> show parameter mex_pool_size

NAME                                                             VALUE                                                            
---------------------------------------------------------------- ---------------------------------------------------------------- 
MEX_POOL_SIZE                                                    8G                                                              

1 row fetched.

使用批量模式(/*+ batch_mode(2) */)测试结果如下：

批量模式排序，有些场景更快，有些场景反而更慢，而且在使用批量模式的时候监控到大量IO，怎么说呢，针对排序场景，批量模式可能需要内核再继续优化