1、情况描述
这次是元旦前的一则SQL优化经历。年前12.25收到一则告警信息如下图。告警内容表示Oracle数据库此时出现大排序操作，大小超过低水位。
除此之外，告警信息没有说明是哪条SQL、哪个对象发生了大排序操作，只是知道发生了大排序操作这么个事儿。
起初因为觉得可能某张报表偶然查了大量数据，且是次要告警，并没有过多关注。但是后面几天又频繁发出了几次告警，客户要求排查此问题。
2、排查分析
2.1、理解大排序和低水位
告警信息说出现大排序操作，直接联想到的是 ORDER BY 操作处理的数据量太大。
告警信息说的低水位在我理解应该就是设置了一个阈值，至于是什么阈值，能够和排序操作相关联的就是PGA内存、临时表空间。
Oracle在进行排序操作时会先为其在PGA内存中分配空间进行数据排序，当需要排序的数据量太大超过了分配的内存空间时，会使用临时表空间进行排序操作。所以这里说的超过低水位大概率指的是SQL的排序操作占用的临时表空间过大。
2.2、定位占用临时表空间过大的SQL
最后一次出现告警的时间是在 2025-12-29 18:02 ，进入机房时告警已经消除，所以根据ash查询这个时间点前后的 temp_space_allocated 较大的SQL。
select
to_char(sample_time,'yyyy-mm-dd hh24:mi:ss') as stime,
sql_id,
round(temp_space_allocated/1024/1024,2) as tempmb
from v$active_session_history
where sample_time>=to_date('20251229175000','yyyy-mm-dd hh24:mi:ss')
and sample_time<=to_date('20251229181000','yyyy-mm-dd hh24:mi:ss')
and temp_space_allocated/1024/1024 > 10
order by 3,2,1;
查询结果看下图。可以发现在此时间范围内 244jfww9mk6mr、4d3txhq61y0vc 两个SQL占用的临时表空间超过了1G。
2.3、分析大排序SQL
根据 sql_id 抓出对应的 sql 文本。
set lines 200 pages 200
set long 2000;
select
sql_fulltext
from v$sql
where sql_id='4d3txhq61y0vc';
因为隐私保密，所以将表和字段替换后展示如下。
select * from(
select a.*,rownum rn from(
select
st1.hid,
st1.cid,
st1.lid,
st1.cl,
st1.stime,
st1.etime,
st1.ctime,
st2.sname,
st2.pl
from st1,st2
where st2.sno=st1.sno
and st1.is_b=1
and st1.ucode=:B1
and to_date(st1.stime,'yyyy-mm-dd hh24:mi:ss') >= to_date(:C1,'yyyy-mm-dd hh24:mi:ss')
and to_date(st1.stime,'yyyy-mm-dd hh24:mi:ss') <= to_date(:D1,'yyyy-mm-dd hh24:mi:ss')
order by st1.stime desc
) a where rownum <= 10
) where rn >= 1;
这是一个典型的使用了分页架构的SQL，其分页架构不存在问题。
基本情况如下：
（1）st1：大表，1亿行数据
（2）st2：小表，几万行数据
（3）关联字段 sno 有索引：
st1.sno 字段索引：st1_idx1
st2.sno 字段索引：st2_idx1
（4）st1 表有 to_date(st1.stime,‘yyyy-mm-dd hh24:mi:ss’) 表达式的函数索引，索引名为：st1_idx2 。
SQL本身存在的问题：
SQL对 st1 表的 stime 进行了 to_date 转换，又对 stime 进行了 desc 降序排序。这会导致无法利用索引本身的有序性，而产生额外的排序。
所以这是第一个需要优化的点，不对 stime 进行 to_date 转换，使用 stime 本身进行谓词条件过滤，并为stime创建相关索引（不要着急创建，接下来先分析执行计划）。
查看该SQL执行计划。
set lines 200 pages 200
select * from table(dbms_xplan.display_awr('4d3txhq61y0vc'));
（1）位图转换
Id=10至 Id=17这个范围的执行计划是Oracle优化器进行了位图转换。关于位图转换的知识可以从官方文档查看。
SQL Tuning Guide
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/optimizer-ref.html#GUID-CD61854E-639B-4F06-8CD2-C1980D6975DE)
再结合执行计划来看，Id=10至 Id=17这个范围的操作内容是：
（1）对st1.idx1进行索引范围扫描(Id=14)，再 SORT ORDER BY(Id=13)，然后进行 BITMAP CONVERSION FROM ROWIDS(Id=12)； 将st1.idx1索引范围扫描后并进行排序的ROWID转换成位图。
（2）对st1.idx2进行索引范围扫描(Id=17)，再 SORT ORDER BY(Id=16)，然后进行 BITMAP CONVERSION FROM ROWIDS(Id=15)； 将st1.idx2索引范围扫描后并进行排序的ROWID转换成位图。
（3）进行 BITMAP AND(Id=11) 与运算。
（4）BITMAP COVERSION TO ROWIDS(Id=10)，将位图转换为ROWID去访问表
这段执行计划中分别出现了对 st1.idx1、st1.idx2 范围扫描后的 SORT ORDER BY。前文已知st1 是张一亿行的大表，当st1.ucode、st1.stime两个谓词字段返回的数据量较多的时候，SORT ORDER BY需要操作的数据量会非常大，将会占用更多的临时表空间进行排序操作，这是大排序操作告警的关键点。
2.4、总结问题原因
根据前文的排查分析，可以得到该SQL存在以下问题：
（1）没有利用索引自身的有序性
SQL对 st1 表的 stime 进行了 to_date 转换，又对 stime 进行了 desc 降序排序，就肯定无法利用索引自身的有序性进行排序了。
（2）位图转换操作导致对两条索引都进行了排序
Oracle 会在谓词条件较多，列基数较低场景下使用位图索引。
该SQL涉及的表中不存在位图索引，不过因为该SQL场景与优化器选择位图索引类似，所以Oracle执行了位图转换；又因为SQL有排序操作，所以在每次位图转换前都进行了SORT ORDER BY。
这就使得排序操作消耗的资源变得更多。
3、解决方案
解决方案比较简单，分成两步走：
（1）DBA这边进行索引的调整
在st1表创建ucode、stime的组合索引。
create index idx_ucodestime on st1(ucode,stime);
（2）研发那边进行SQL调整
将SQL中的 to_date(st1.stime,‘yyyy-mm-dd hh24:mi:ss’) > to_date(:C1,‘yyyy-mm-dd hh24:mi:ss’) 改成 st1.time > ‘2026-01-21 10:43:00’，即去掉to_date转换。
调整后，大排序告警未在发生。
