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轉換。

調整后，大排序告警未在發生。