初始化参数optimizer_dynamic_sampling的一些问题

    一直奇怪在RBO环境下多表连接时出现CBO的情况感到困惑，最近看了一些讨论再仔细看了一下oracle手册，对动态采样又有了新的理解。

    dynamic sampling主要是用来改善oracle的性能，使其得到更为正确的执行计划（总觉得oracle把自己的优化方式说的太好了）。oracle是通过optimizer_dynamic_sampling这个参数实现动态采样，在9i里面dynamic sampling需要在多表关联的语句里面才会起作用。在10g里面optimizer_dynamic_sampling在2及以上级别时单表就能dynamic sampling。

oracle文档上对于该参数的介绍如下：

You control dynamic sampling with the OPTIMIZER_DYNAMIC_SAMPLING parameter, which can be set to a value from 0 to 10.

• A value of 0 means dynamic sampling will not be done.
• A value of 1 (the default) means dynamic sampling will be performed if all of the following conditions are true:
  • There is more than one table in the query.
  • Some table has not been analyzed and has no indexes.
  • The optimizer determines that a relatively expensive table scan would be required for this unanalyzed table.
• Increasing the value of the parameter results in more aggressive application of dynamic sampling, in terms of both the type of tables sampled (analyzed or unanalyzed) and the amount of I/O spent on sampling.

Dynamic sampling is repeatable if no rows have been inserted, deleted, or updated in the table being sampled.

The parameter OPTIMIZER_FEATURES_ENABLE turns off dynamic sampling if set to a version prior to 9.0.2.

另外使用DYNAMIC_SAMPLING hint同样可以做动态采样：

SELECT /*+ dynamic_sampling(1) */ * 
FROM ...

enables dynamic sampling if all of the following conditions are true:

• There is more than one table in the query.
• Some table has not been analyzed and has no indexes.
• The optimizer determines that a relatively expensive table scan would be required for this table that has not been analyzed.

    其实，从个人来讲，我不认为oracle的CBO方式比RBO要强很多，虽然在大多数情况下数据库可以选择良好的执行计划，但对于复杂的系统来说，依靠定时收集信息来选择的执行计划并不能很好的适应完全实时运行的系统。同时，9i中对于大表的信息收集效率很低，即使部分采样也很慢。10G中似乎对于这样的问题有了更好的解决办法，不过与oracle工程师闲聊时，他们并不推荐10G用于OLTP系统，但在OLAP中会有很好的表现。

    以上只是自己的一下想法，正确性有待验证。