使用ORDER BY子句时查询最后一批数据出现长时间卡顿问题

问题现象 #

执行带ORDER BY子句的查询时，获取最后一批数据会出现长时间卡顿；移除ORDER BY子句后，卡顿问题完全消失。

对应的PL/SQL测试代码如下：

begin
  for r in (select id from table where timestamp > to_date('2024.08.01') order by id desc) loop
    pragma_log(r.id);
  end loop;
end;

查询结果共23011条数据，前23000条能在数秒内完成日志记录，但最后11条却耗时数十分钟才处理完成。直接执行该SQL查询也会出现相同情况——数据似乎按每批100条的方式返回，仅在使用ORDER BY子句时，最后一批数据的获取会异常卡顿。

可能原因 #

• 排序资源瓶颈：ORDER BY id desc要求数据库对过滤后的全量数据完成排序后再返回结果。前23000条数据能快速返回，大概率是因为排序初期使用了内存缓冲区，而最后一批数据需要等待磁盘排序的收尾操作——当内存不足以容纳全部排序数据时，数据库会将部分数据写入临时表空间，最后阶段的磁盘IO开销大，导致卡顿。
• 索引缺失：如果仅在timestamp字段上有索引，数据库需要先通过索引过滤数据，再对结果集进行排序。当结果集规模较大时，排序的收尾阶段需要合并大量磁盘上的临时排序片段，耗时剧增。
• 临时表空间问题：临时表空间不足、碎片化严重，会导致磁盘排序的IO效率极低，拖慢最后一批数据的返回速度。

解决建议 #

• 创建复合索引：建立(timestamp, id desc)的复合索引，让数据库可以直接通过索引获取已经按id desc排序好的数据，无需额外执行排序操作，从根源上解决卡顿问题。
• 调整排序内存参数：增大PGA_AGGREGATE_TARGET（Oracle 10g及以上版本）或SORT_AREA_SIZE（旧版本）的配置值，让排序操作尽量在内存中完成，减少磁盘IO的依赖。
• 优化临时表空间：检查临时表空间的大小和使用情况，若存在不足则扩容；若碎片化严重，可重建临时表空间来提升磁盘排序效率。
• 分批查询优化：如果无法创建索引，可尝试按id范围分段查询，每段内单独排序后合并结果，但这种方式的优化效果远不如索引方案直接。

内容的提问来源于stack exchange，提问作者Simas.B