临时表(GLOBAL TEMPORARY TABLE)及统计信息收集，动态采样

最新推荐文章于 2025-07-15 11:38:12 发布

转载最新推荐文章于 2025-07-15 11:38:12 发布 · 478 阅读

本文探讨了在使用临时表与其他永久表联合查询时，如何通过DBMS_STATS提前收集统计信息来提高执行计划的准确性。重点介绍了GATHER_SCHEMA_STATS/GATHER_TABLE_STATS的使用方法，并解释了动态采样的概念及其在Oracle数据库中的应用，包括初始化参数optimizer_dynamic_sampling的配置和动态采样统计信息的存储机制。

我们对临时表进行查询时，分析执行计划会发现临时表估计的返回值很多情况下都是1，这显然
是不对的（因为可能在查询过程中临时表的数据达到几千甚至几万笔），但临时表的数据很快
消失，那么如何在临时表与其他永久表联合查询时能正确得到较好的执行计划呢？

在临时表上也是会执行动态采样的，但是动态采样在SQL解析时完成，所以存在相当大的代价。

所以一般可以使用DBMS_STATS提前收集统计信息。

1. 可以使用GATHER_SCHEMA_STATS或GATHER_TABLE_STATS调用来使用DBMS_STATS。传入
参数GATHER_TEMP为TRUE(默认为FALSE)。但是要保证在收集统计信息的会话中用代表
性数据填充全局临时表。

   此外，这个方法只对ON COMMIT PRESERVE ROWS全局临时表收集和存储统计信息。对
   于ON COMMIT DELETE ROWS全局临时表收集不到正确的统计信息。因为调用DBMS_STATS
   将导致提交，而这会擦除ON COMMIT DELETE ROWS表中的所有信息。

2. 如果我们不能保证在收集统计信息的会话中用代表性数据填充全局临时表，也可以
手动填充临时表的统计信息(注意是代表性数据)：
语法：
exec dbms_stats.set_table_stats(ownname,tabname,numrows,numblks,avgrlen);

begin
dbms_stats.set_table_stats(
   wnname => 'APPSYS',
   tabname => 'DIM_DEL',
   numrows => 300,
   numblks => 4,
   avgrlen => 20);
end;
或者
BEGIN dbms_stats.set_table_stats(ownname => 'SOAINFRA',tabname => 'DM_MID',numrows =>10000 );END;

最后使用
exec dbms_stats.lock_table_stats(ownname => 'XXXXX',tabname =>'XXXX');
这时会针对这个表对统计信息锁定，之后的analyze及dbms_stats分析对此表操作会报错
ORA-38029: object statistics are locked。

-- 最后简单说说动态采样。

动态采样在Oracle9i R2中被引入。
详细可参考博客： http://space.itpub.net/35489/viewspace-592375，这里初略说一下。

初始化参数optimizer_dynamic_sampling的值（或层级）指定动态采样的方式和时间。
动态采样等级以及含义，注意这个参数的默认值由另一个初始化参数optimizer_features_ enable决定。

如果optimizer_features_enable设置为10.0.0或更高，默认值为层级2。大部分时候level2足够了。
如果optimizer_features_enable设置为9.2.0，默认值为层级1。
如果optimizer_features_enable设置为9.0.1或更低，动态采样特性则被禁用。

关闭动态采样的方式：设置optimizer_dynamic_sampling=0;

动态采样的统计信息存在哪里？
动态采样得到的统计信息既不存储在数据字典中也不存储在其他地方，真正重用它们的唯
一方式是重用共享游标本身。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-748730/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/35489/viewspace-748730/