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轉換。
調整后,大排序告警未在發生。




