Oracle锁定表统计信息防止执行计划改变：何时与如何操作？

引言：从“被动修复”到“主动干预”

作为一名资深DBA，我们的工作不应仅仅是“救火队员”，更应该是“架构师”和“规划师”。对于统计信息管理，这意味着我们不能完全依赖自动化机制，有时必须进行主动干预。

锁定统计信息 (Locking Statistics) 就是一种重要的主动干预手段，其核心目标常常是为了锁定表统计信息以防止执行计划改变，从而保障核心业务的性能稳定。

本篇，我们将聚焦于三个核心问题：

1. 锁定的动机：在哪些真实场景下，锁定统计信息是明智之举？
2. 锁定的艺术：如何精确地执行锁定与解锁操作？
3. 全局的视野：如何像雷达一样扫描整个数据库，找出所有被锁定的对象？

（一）锁定的动机：为何要给CBO“戴上镣铐”？

锁定统计信息绝非常规操作，必须有充分且合理的理由。以下是几个典型的适用场景：

场景一：确保核心SQL的“绝对稳定”，防止执行计划改变

• 背景：在金融交易、电信计费等核心系统中，有一批“黄金SQL”（Golden SQLs）。这些SQL的性能表现直接关系到业务的生死存亡。它们已经被DBA和开发团队千锤百炼，找到了一个最优的执行计划。
• 风险：即使数据只是发生微小的、符合预期的变化，自动统计信息收集后，也可能导致CBO“自作聪明”地选择一个新的、未经充分测试的执行计划，从而引发性能抖动甚至衰退。这种现象被称为“执行计划翻转”，也是我们决定要锁定表统计信息以防止执行计划改变的最主要原因。
• 解决方案：
  1. 首先，在一个最能代表业务高峰期数据状态的时间点，为相关表收集一次最精准的统计信息。
  2. 然后，立即锁定这些表的统计信息。
  3. 最后，将这个最优的执行计划通过SQL Profile或SQL Plan Baseline等更高级的手段固化下来。

graph TD
    subgraph 核心SQL稳定性保障流程
        A[1. 找到最优执行计划] --> B{2. 在最佳数据状态下<br/>收集统计信息};
        B --> C[3. <b>锁定相关表的统计信息</b><br/>(dbms_stats.lock_table_stats)];
        C --> D[4. (可选)固化执行计划<br/>(SQL Plan Baseline)];
        D --> E((实现执行计划的长期稳定));
    end

锁定在这里的作用，是杜绝一切可能引起计划改变的源头，为执行计划的长期稳定提供最坚实的基础。

场景二：应对特殊的ETL数据加载模式

• 背景：在数据仓库（Data Warehouse）环境中，经常有这样的操作：先将一张巨大的事实表 TRUNCATE 清空，然后通过ETL工具重新加载数亿行数据。
• 问题：
  1. TRUNCATE 操作会立即使得表的统计信息失效。
  2. 在数据加载过程中，如果自动统计信息任务恰好运行，它可能会在一个“半成品”状态（比如只加载了10%数据）时收集统计信息。
  3. 这份不完整的统计信息将会误导后续所有基于该表的查询，直到下一次数据完全加载完毕且重新收集，整个周期内的查询性能都会非常糟糕。
• 解决方案：
  1. 在ETL流程开始前，锁定该表的统计信息（使用一份数据加载完成时的“黄金”统计信息）。
  2. 执行 TRUNCATE 和数据加载。
  3. ETL流程的最后一步，是解锁统计信息，并手动执行一次完整的统计信息收集。
  4. 再次锁定统计信息，为下一个查询周期做好准备。

这就像在进行一场大手术时，先给病人打上“麻药”（锁定），手术完成后再“唤醒”（解锁并更新）。

场景三：临时表与全局统计的冲突

• 背景：Oracle的全局临时表（Global Temporary Table），其数据是会话（Session）私有的，但其结构是共享的。默认情况下，临时表的统计信息也是共享的。
• 问题：会话A向临时表插入了100万行数据，并收集了统计信息。随后会话B使用了同一个临时表，但只插入了10行数据。此时，会话B的查询优化器看到的却是“100万行”的统计信息，这必然导致它做出错误的判断。
• 解决方案：对于这种会话间数据量差异极大的临时表，一种策略是锁定其统计信息，使其NUM_ROWS等指标为一个相对中性或较小的值，或者干脆不收集。然后依赖Oracle 12c及以上版本的动态采样（Dynamic Sampling）或会话级私有统计信息（Session-Private Statistics）来获得更准确的即时估算。

（二）锁定的艺术：DBMS_STATS的精确操作

了解了为何要锁定统计信息后，我们来看看如何通过 DBMS_STATS 包精确地执行这一操作，从而有效地防止执行计划改变。

1. 锁定表的统计信息

-- 锁定指定表的统计信息
EXEC DBMS_STATS.LOCK_TABLE_STATS(ownname => 'SCHEMA_NAME', tabname => 'TABLE_NAME');

执行此命令后，任何自动或手动的 GATHER_TABLE_STATS 尝试都会失败，并报 ORA-20005 错误。

2. 解锁表的统计信息

-- 解锁指定表的统计信息
EXEC DBMS_STATS.UNLOCK_TABLE_STATS(ownname => 'SCHEMA_NAME', tabname => 'TABLE_NAME');

解锁后，该表恢复正常，可以被自动或手动收集。

3. 分区表的精细控制

对于分区表，我们甚至可以锁定整个表，或者只锁定特定分区的统计信息。这在只读的历史分区管理中非常有用。

-- 锁定特定分区的统计信息
EXEC DBMS_STATS.LOCK_PARTITION_STATS(ownname => 'SCHEMA_NAME', tabname => 'PARTITIONED_TABLE', partname => 'PARTITION_2023_Q1');

-- 解锁特定分区的统计信息
EXEC DBMS_STATS.UNLOCK_PARTITION_STATS(ownname => 'SCHEMA_NAME', tabname => 'PARTITIONED_TABLE', partname => 'PARTITION_2023_Q1');

（三）全局视野：扫描数据库中的“定时炸弹”

锁定统计信息后，最重要的管理工作就是文档化和定期巡检。因为一个被遗忘的锁，可能会在未来数据发生巨大变化时，从防止执行计划改变的‘保护锁’，变成导致次优计划的‘性能枷锁’。DBA需要定期扫描整个数据库，找出所有被锁定的对象。

巡检利器：DBA_TAB_STATISTICS 视图

这个视图是发现统计信息锁的关键。

SELECT 
    owner,
    table_name,
    stattype_locked
FROM 
    DBA_TAB_STATISTICS
WHERE 
    stattype_locked IS NOT NULL;

STATTYPE_LOCKED 字段解读：

通过定期执行上述查询，DBA可以生成一份“锁定对象报告”，并与运维文档进行核对，确保每一个锁的存在都是合理且受控的。