Oracle-统计信息生效时间参数管理

Oracle 统计信息收集与 no_invalidate 参数详解

核心问题：为什么收集统计信息后执行计划不立即改变？

根本原因是 no_invalidate 参数的默认行为，它控制着统计信息更新后 SQL 游标失效（硬解析）的时机。

---

1 关键参数解析：no_invalidate

参数值	行为	影响
AUTO_INVALIDATE (默认)	Oracle 在5小时内随机选择时间点使相关游标失效	执行计划不会立即更新，可能延迟数小时
FALSE	立即失效所有依赖该对象的游标，强制下次执行时硬解析	执行计划立即更新，但可能引发短暂性能波动
TRUE	不失效任何游标，直到游标自然老化或被清除	执行计划可能长期不更新

---

2 统计信息收集流程与游标管理

 

2.1 典型场景分析

-- 默认收集方式（延迟生效）
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SCHEMA',
    tabname => 'T_GROUP_RULE'
  );
END;

-- 强制立即生效的方式
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SCHEMA',
    tabname => 'T_GROUP_RULE',
    no_invalidate => FALSE  -- 关键修改
  );
END;

---

3 FLUSH SHARED_POOL 的风险

当 DBA 发现执行计划未更新时，可能错误地执行：

 

ALTER SYSTEM FLUSH SHARED_POOL;

副作用分析：

1. 全局性影响：清空所有游标，不仅目标 SQL

2. 性能风暴：

   • OLTP 系统：瞬间硬解析风暴导致 CPU 飙升

   • 长事务：可能导致执行中断

3. 内存碎片：反复 flush 加剧 shared pool 碎片化

4. 安全风险：可能触发 ORA-04031 错误

---

4 专业优化建议

4.1. 统计信息收集策略

-- 推荐生产环境用法（立即生效+低优先级）
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => 'GROUPBUY',
    tabname          => 'T_GROUP_RULE',
    degree           => DBMS_STATS.AUTO_DEGREE,
    no_invalidate    => FALSE,       -- 强制立即生效
    force            => TRUE,        -- 即使锁表也收集
    options          => 'GATHER AUTO' -- 智能收集
  );
END;

3.2. 精准游标刷新（替代 flush shared_pool）

-- 查找特定SQL的SQL_ID
SELECT sql_id, sql_text 
FROM v$sql 
WHERE sql_text LIKE '%t_group_rule%';

-- 精准刷新单个游标
EXEC DBMS_SHARED_POOL.PURGE('&sql_id, CURSOR');

3.3. 自动化监控脚本

-- 检查统计信息生效延迟
SELECT o.obj#, s.sql_id, s.plan_hash_value,
       (SYSDATE - s.last_active_time)*24*60 delay_mins
FROM v$sql_plan p 
JOIN v$sql s ON p.sql_id = s.sql_id
JOIN obj$ o ON p.object# = o.obj#
WHERE o.name = 'T_GROUP_RULE'
  AND s.last_active_time < (SELECT last_analyzed 
                           FROM dba_tables 
                           WHERE table_name = 'T_GROUP_RULE');

---

5 进阶优化技巧

5.1. 绑定执行计划稳定性

-- 锁定优质执行计划
DECLARE
  v_plan pls_integer;
BEGIN
  v_plan := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id => 'g8m9z3s7xkw0a'
  );
END;

5.2. 增量统计信息收集

-- 针对分区表高效收集
BEGIN
  DBMS_STATS.SET_TABLE_PREFS(
    'GROUPBUY', 'T_GROUP_RULE', 
    'INCREMENTAL', 'TRUE'
  );
END;

5.3. 统计信息回退机制

-- 保存历史统计信息
EXEC DBMS_STATS.CREATE_STAT_TABLE('SYSTEM', 'STATS_HIST');

-- 收集前备份
BEGIN
  DBMS_STATS.EXPORT_TABLE_STATS(
    ownname   => 'GROUPBUY',
    tabname   => 'T_GROUP_RULE',
    stattab   => 'STATS_HIST'
  );
END;

-- 问题回退
BEGIN
  DBMS_STATS.IMPORT_TABLE_STATS(
    ownname   => 'GROUPBUY',
    tabname   => 'T_GROUP_RULE',
    stattab   => 'STATS_HIST'
  );
END;

---

6 性能影响对比

方法	执行计划更新延迟	CPU影响	风险等级	推荐场景
默认收集	5小时内随机	低	★☆☆☆☆	非关键业务表
no_invalidate=>FALSE	立即	中	★★☆☆☆	关键业务表
FLUSH SHARED_POOL	立即	高	★★★★★	严禁生产环境使用
DBMS_SHARED_POOL.PURGE	立即	低	★☆☆☆☆	精准优化

最佳实践：
对于交易核心表（如订单表），始终使用 no_invalidate=>FALSE + 低峰期收集
配合 SQL Plan Management 锁定关键执行计划