Oracle技巧之批处理中的统计信息采集

背景信息

在很多系统业务流程中，特别是数据仓库的ETL应用和批处理业务中，设计和使用了大量中间表或临时表。对这些表经常进行truncate、大批量数据insert、delete等DML操作，即这些表的记录经常会在0或大容量两种极端情况下变化。

这些表或者没有统计信息，或者在每天夜间的固定时间窗口采集的统计信息，并不能正确反映数据的真实情况，从而导致CBO优化器对这些表访问的SQL语句不一定能保证最优化。为此，可采取如下策略。

解决方案

（1）锁住统计信息

可采取上述锁住统计信息技术，将这些表在批处理业务中的典型数据状态的统计信息进行采集和锁定。这样无论这些表的信息如何变化，Oracle始终根据典型数据状态的统计信息产生SQL语句执行计划。

该策略的优点是资源消耗较少，基本上能保持SQL语句执行计划的稳定，但缺点是不能完全根据变化的数据，去选择最优的执行计划。

（2）实时采集统计信息

与上述策略不同的是，在批处理流程中，在这些表数据发生极端情况变化之后，实地时进行统计信息采集。例如：

execute immediate 'truncate table  &TNAME'; 
commit; 
 
execute immediate ' exec DBMS_STATS. GATHER_TABLE_STATS 
    (ownname=>'&OWNER', tabname=>'&TNAME',estimate_percent=>10, 
     Degree=>8, Cascade=>TRUE, Granularity=>'ALL'); 

该策略的优缺点与上述策略正好相反，即优点是能根据每次变化的数据去选择最优的执行计划，缺点是资源消耗较大。

（3）使用HINT技术（老土方法，不推荐！）

通过在SQL语句中使用HINT技术，确保执行计划处于稳定的最优化状态。

该策略的优点是充分利用开发人员自己的经验和水平，而不依赖于Oracle统计信息采集；缺点是万一开发人员用错HINT就麻烦了，而且这种策略太死板，在程序里写死了执行计划，而数据是千变万化的。

（4）全局临时表（最佳方案）

•全局临时表优点
　　DML操作日志少、 高效删除记录、不同会话独立
•全局临时表缺点
　　从数据安全性考虑，数据丢失无法恢复
•全局临时表适用场景
　　运行过程中临时处理的中间结果集
详细参考如下文章：
Oracle程序优化之全局临时表