实战排错：解决Oracle ORA-20005统计信息锁定导致的执行计划错误

引言：当“指挥家”开始胡言乱语

想象一下，你是一位经验丰富的DBA，运维着一个核心业务系统。一天，你对一张拥有数百万行数据的核心表 TABLE_NOTIFY 进行例行检查，想看看一个简单查询的执行计划。

explain plan for select * from TABLE_NOTIFY where be_id is not null;

你预期这个查询会返回上百万行数据，执行计划中预估的 Rows (行数) 也应该在这个量级。然而，你看到的却是这样一幅令人匪夷所思的景象：

Rows 预估只有 2 行！ 这就像你问地图App从北京到上海有多远，它却告诉你只有5公里一样荒谬。CBO这位本应睿智的“指挥家”，此刻正在胡言乱语。

这是一个典型的性能问题信号。这背后隐藏的，正是一个经典的由Oracle ORA-20005统计信息锁定导致执行计划错误的案例。面对这种情况，我们不能慌张，而是要像一名侦探一样，启动一套逻辑清晰的排错流程。

DBA的排错四部曲

我们将整个排查过程分解为四个逻辑步骤，这是一个可以应用于多数性能问题的通用模型。

graph TD
    subgraph DBA排错心法
        A[<b>第一步：确认症状</b><br/>发现执行计划预估行数异常] --> B[<b>第二步：追溯病根</b><br/>检查统计信息是否陈旧];
        B --> C[<b>第三步：深入探因</b><br/>为何自动化机制未生效？];
        C --> D[<b>第四步：直击要害</b><br/>手动干预并发现最终障碍];
        D --> E[<b>最终解决</b><br/>解锁并重新收集];
    end

第一步：确认症状 (Symptom Identification)

• 现象：执行计划中的 Rows (预估行数) 与我们基于业务逻辑的预期严重不符。
• 初步诊断：CBO的成本估算模型严重失准。
• 首要嫌疑人：为CBO提供决策依据的统计信息出了大问题。

一个准确的执行计划，其预估行数应该与实际情况大致相符。当出现数量级的巨大偏差时，90%以上的情况都与统计信息有关，这是排查Oracle执行计划错误的第一步。

第二步：追溯病根 (Root Cause Tracing)

既然怀疑统计信息，我们就要立刻去验证。最直接的检查点就是统计信息的“生产日期”——LAST_ANALYZED。

SELECT 
    OWNER,
    TABLE_NAME,
    NUM_ROWS,
    TO_CHAR(LAST_ANALYZED, 'YYYY-MM-DD HH24:MI:SS') AS LAST_ANALYZED
FROM 
    DBA_TABLES
WHERE 
    TABLE_NAME = 'TABLE_NOTIFY';

查询结果令人震惊：

我们从中捕获到两个关键线索：

1. LAST_ANALYZED: 上次分析时间是2018年！对于一个活跃的业务表来说，这份统计信息已经和“化石”无异。
2. NUM_ROWS: 表中记录的行数是 2。这完美解释了为什么执行计划会预估2行——它完全是基于这份古老而错误的情报做出的判断。

结论：病根已经找到，就是陈旧且错误的统计信息。这为我们后续发现Oracle ORA-20005统计信息锁定问题埋下了伏笔。

第三步：深入探因 (Deeper Investigation)

一个合格的DBA不会止步于此。我们会接着问一个更深层次的问题：为什么？

根据我们上一篇文章学到的知识，Oracle的GATHER_STATS_JOB会自动为数据变化超过10%的表更新统计信息。这张表既然数据量从2行增长到了数百万行，变化率早已是天文数字，为何自动任务会“视而不见”？

假设A：自动任务被全局关闭了？
我们来验证这个最大胆的猜测。

SELECT WINDOW_NAME, OPTIMIZER_STATS 
FROM DBA_AUTOTASK_WINDOW_CLIENTS;

结果显示所有窗口的OPTIMIZER_STATS均为ENABLED。假设A被排除。

假设B：有什么特殊原因阻止了对这张表的收集？
如果全局开关是好的，那问题就出在这张表本身。我们无法直接“看到”自动任务失败的原因，但我们可以模拟它的行为——手动收集一次统计信息，看看会发生什么。

第四步：直击要害 (The Breakthrough) - ORA-20005错误揭示真相

我们尝试手动为这张表收集统计信息，这既是解决问题的尝试，也是一种诊断手段。

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'PROD_USER', tabname => 'TABLE_NOTIFY');

执行后，Oracle没有像往常一样顺利完成，而是抛出了一个决定性的错误：

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname => 'PROD_USER', tabname => 'TABLE_NOTIFY');
END;

Error report -
ORA-20005: object statistics are locked (table: "PROD_USER"."TABLE_NOTIFY")
ORA-06512: at "SYS.DBMS_STATS", line ...
...

ORA-20005: object statistics are locked

这个ORA-20005错误，正是Oracle ORA-20005 统计信息锁定导致执行计划错误这一问题的直接证据，它像迷雾中的灯塔，瞬间照亮了真相！

• 真相大白：这张表的统计信息被人为地锁定了。
• 解开谜团：GATHER_STATS_JOB在每次运行时，都会检查对象的锁定状态。当它发现TABLE_NOTIFY被锁定时，它会遵循DBA的指令，礼貌地跳过它，并继续处理下一个对象。这就是为什么自动任务看起来“失灵”了。

至于当初是谁、为什么锁定了它，可能已随着时间流逝和人员变动而无从考证。但作为当前的DBA，我们的任务是解决问题。

解决方案与验证

既然问题已经定位得一清二楚，解决方案也变得非常直接。

第一步：解锁统计信息
使用DBMS_STATS包提供的解锁程序，解决ORA-20005问题。

-- 解锁表的统计信息
EXEC DBMS_STATS.UNLOCK_TABLE_STATS(ownname => 'PROD_USER', tabname => 'TABLE_NOTIFY');

第二步：重新收集统计信息
现在障碍已经清除，我们可以放心地收集最新的统计信息了。

-- 重新收集统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'PROD_USER', tabname => 'TABLE_NOTIFY', cascade => true);

(cascade => true会一并收集表的索引统计信息)

第三步：验证！验证！再验证！
永远不要想当然地认为问题已经解决。我们必须回到最初的起点，重新检查执行计划，确认执行计划错误的问题已经修复。

explain plan for select * from TABLE_NOTIFY where be_id is not null;

再次查看执行计划，景象焕然一新：

Rows现在预估为 1.8M (180万)，Cost也相应地调整为一个更合理的值。CBO的“大脑”终于恢复了正常！