1、情况描述

2026新年第二个工作日，收到Oracle告警如下。

看到以为只是简单的阻塞问题，排查后发现阻塞源头是一个执行了十几个小时还没结束的update操作。

与研发人员沟通后，得知该SQL是晚上8点半开始执行的批处理任务中的一个操作，正常情况下20分钟能执行完。这种情况之前也偶尔出现过，但之前没有造成什么影响就没有关注。不过这次已经影响到了正常的业务，所以先将该SQL kill掉，再进行排查。

SQL文本如下，因为保密和隐私，进行了表和字段的替换，sql_id为 73v0z2m8ypg8w。

update problem_table1 h
   set (h.stime,h.etime,h.clong,h.ctime)=(select t.stime,t.etime,t.clong,t.stime from problem_table2 t
        where h.cid=t.cid)
 where exists (select 1 from problem_table2 t where t.cid=h.cid)
   and substr(h.stime,0,10)=to_char(sysdate,'yyyy-mm-dd')
   and h.flag='1'

SQL的基本信息如下。

（1）problem_table1（别名 h）：1亿行数据；

（2）problem_table2（别名 t）：80万行数据；

（3）两表的关联字段cid有索引；problem_table1中有substr(h.stime,0,10)的函数索引；

2、排查过程

2.1、查看执行计划是否变化

因为该SQL之前跑着正常，这一次突然变慢了，所以首先怀疑是执行计划发生变化。

查询ASH，看执行计划更改时间。

select * from
(select
to_char(sample_time,'yyyy-mm-dd') as stime,
sql_plan_hash_value
from dba_hist_actice_sess_history ash)
group by stime,sql_plan_hash_value
order by 1;

输出结果如下。可以发现在2026-01-04日执行时，执行计划发生改变，与出问题的时间吻合。其中 926220318 是执行速度快的执行计划，415175573是执行速度慢的执行计划。

2.2、执行计划分析

查看SQL的2种执行计划。

select * from table(dbms_xplan.display_awr('73v0z2m8ypg8w'));

快plan：926220318

926220318 执行计划顺序：

4 —> 3 —> 5 —> 2 —> 7 —> 6 —> 1 —> 0

926220318 执行计划重点：

以problem_table1为驱动表，problem_table2为被驱动表，进行 nest loops semi连接。推测的伪代码如下：

declare
  cursor cur_problem_table1 is
    select cid from problem_table1 where substr(stime,0,10)=to_char(sysdate,'yyyy-mm-dd') and h.flag='1';
	v_cid problem_table2.cid%type;
begin
  for x in cur_problem_table1 loop
    select cid into v_cid from problem_table2;
	dbms_output.put_line(x.cid);
  end loop;
end;

其中problem_table1谓词过滤后返回的结果集数量在20万左右，problem_table2的数据量则在80万左右。即对被驱动表进行20万次索引扫描。

慢plan：415175573

415175573 执行计划顺序：

4 —> 3 —> 6 —> 5 —> 2—> 8 —> 7 —> 1 —> 0

415175573 执行计划重点：

415175573 以problem_table2为驱动表，problem_table1为被驱动表，进行 nest loops 连接。推测的伪代码如下：

declare
  cursor cur_problem_table2 is
    select cid from problem_table2;
	v_cid problem_table1.cid%type;
begin
  for x in cur_problem_table2 loop
    select cid into v_cid from problem_table1 where substr(stime,0,10)=to_char(sysdate,'yyyy-mm-dd') 
		and h.flag='1' and cid=x.cid;
	dbms_output.put_line(x.cid);
  end loop;
end;

其中，problem_table2有 75万 的数据量，problem_table1有 1亿 的数据量。与926220318执行计划相比，驱动表的数据量本身就增加了近4倍，而被驱动表的数据量较之前剧增至1亿，且数据量这么庞大的被驱动表要被扫描80万次，性能自然骤降，这是415175573计划的SQL一直执行不出来结果的核心原因。

造成这种驱动表变化的最大可能原因是统计信息发生了巨变。

Oracle优化器认为以problem_table2为驱动表效率会更高。所以需要进一步查看统计信息是否准确。

2.3、查看统计信息是否过旧

select
dts.owner,
dts.table_name,
to_char(dts.last_analyzed,'yyyy-mm-dd hh24:mi:ss') as last_analyzed,
num_rows
from dba_tab_statistics dts
where dts.owner=upper('tq')
  and dts.table_name=upper('problem_table1')

检查后发现problem_table1、problem_table2两张表的统计信息都没有过旧，最近一次收集时间是2025-01-05 01:00 收集的，num_rows也和实际情况对得上。

继续检查近一段时间内，两张表的统计信息收集情况。

set linesize 200 pagesize 200
col owner format a12
col object_name format a24
col analyzed format a18
col rowcnt format 9999999999
col blkcnt format 99999999
select a.owner,
       a.object_name,
       to_char(b.ANALYZETIME,'yyyymmdd hh24:mi:ss') as analyzed,
       b.rowcnt,
       b.blkcnt
  from dba_objects a,wri$_optstat_tab_history b
 where a.object_type = 'TABLE'
   and a.object_name  = upper('&table_name')
   and a.object_id = b.obj#
  order by 1,2,3;

输出结果如图所示。在图中我们可以看到problem_table2的统计信息基本平稳，但是在2026.1.3 收集的problem_table2表统计信息行数量是 0 ！这是一个大问题，是导致 2026 .1.4 晚上批处理执行SQL时，SQL执行计划更改驱动表的直接原因。

2.4、业务沟通

至于为什么 2026.1.3 problem_table2 的数据量是 0，这里在和业务研发方面进行沟通，终于真相大白：

Oracle数据库每天凌晨1点收集统计信息。

problem_table2 这张表只保留一天的数据量。在批处理任务中，先删除这张表所有数据，再插入数据，再执行这个sql_id为 73v0z2m8ypg8w 的SQL。
problem_table2 每天的数据量基本上是平稳的，但是遇到节假日、周末，problem_table2表的数据量很少甚至为0。
以这次为例，2026.1.4晚上20:30点执行批处理任务时，4号是工作日，当天的数据有70多万；但是统计信息此时还未更新，还是认为problem_table2表的数据量是0，Oracle 优化器认为以problem_table2为驱动表最优，此时执行计划变了，变成了最低效的执行计划。

3、解决方案

在排查清楚原因后，解决方案就比较清晰了：更改批处理任务执行时间或者绑定执行计划。在与研发人员沟通后，最终还是选择了绑定执行计划的解决方案。

使用sqlt工具进行执行计划绑定，该工具可以从MOS上获取。绑定执行计划步骤：

（1）上传sqlt压缩包并解压

（2）运行分析脚本

73v0z2m8ypg8w是目标SQL的sql_id

cd /home/oracle/sqlt/utl
sqlplus / as sysdba
@coe_xfr_sql_profile.sql 73v0z2m8ypg8wmh


SQL_ID (required)
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
926220318         xxx
415175573         xxx

--根据提示，输入人工判断最优的执行计划 hash value
Parameter 2:
PLAN_HASH_VALUE (required)
Enter value for 2: 926220318


--确认信息如下
Values passed:
~~~~~~~~~~~~~
SQL_ID         : "73v0z2m8ypg8wmh"
PLAN_HASH_VALUE: "926220318"

--上述步骤完成后，会提示执行生成的 sql profile脚本
Execute coe_xfr_sql_profile_cdwjdd67x27mh_2979024279.sql
on TARGET system in order to create a custom SQL Profile
with plan 2979024279 linked to adjusted sql_text.

（3）运行输出结果中的sql脚本

@coe_xfr_sql_profile_cdwjdd67x27mh_2979024279.sql

（4）检查绑定情况

select name, SQL_TEXT, status from dba_sql_profiles;

4、总结反思

优化慢SQL时，SQL往往都已经执行结束或者被动结束了，此时的统计信息不能完全说明SQL执行时的真实情况。

Oracle 执行计划的突然改变往往和统计信息脱不了关系，对于统计信息的检查不能只着眼于最近一次收集情况，需要纵向观察近期内统计信息的收集情况。