oracle12 保存动态采样,Oracle 12c Dynamic statistics与 之前版本Dynamic Sampling动态采样的区别...

动态统计信息(Dynamic Statistics)是一个新的概念。在11g的数据库，我们知道的动态采样(dynamic sampling)是在优化sql语句之前收集最基本的对象的统计信息。

12c优化器会判断当前有效的统计信息是否足够，否则使用动态统计信息。动态统计信息是一个持久的统计信息，会存储在统计仓储中，因此可能会被其他的查询语句使用。在12c中，

优化器会判断是否动态统计信息是有用的，是否动态采样是正确的方法，并能自动决定动态采样的级别。

动态统计信息 ：

During the compilation of a SQL statement, the optimizer decides whether to use dynamic statistics by considering whether the available statistics are sufficient to generate a good execution plan. If the available statistics are not enough, then dynamic statistics are used. Dynamic statistics are persistent and may be used by other queries. One type of dynamic statistic is the information gathered by dynamic sampling. Traditionally, dynamic sampling would automatically occur only if one or more of the tables in the query did not have statistics. Dynamic sampling gathered basic statistics on these tables before optimizing the statement. Now, the optimizer automatically decides if dynamic statistics are useful for all SQL statements and if dynamic sampling is the right approach.

动态采样：

If it is, the optimizer also determines what dynamic sampling level is used

Dynamic statistics are automatically used when the optimizer deems it necessary and the resulting statistics are persistent in the statistics repository making them available to other queries.

“the optimizer automatically decides if dynamic statistics are useful for all SQL statements and if dynamic sampling is the right approach.If it is, the optimizer also determines what dynamic sampling level is used.”

动态统计信息的级别说明，可以参照下面的文档：

http://docs.oracle.com/database/121/TGSQL/tgsql_astat.htm#TGSQL451

这个新的特性是缺省的行为。 这个特征在12c上主要体现在优化器更加智能，动态统计信息可以持久化影响后来的sql执行上，这些都是11g所没有的，。

收集动态统计信息，可以通过设置OPTIMIZER_DYNAMIC_SAMPLING=0禁止掉。谢谢。