如上说明当OFE为11.2.0.3的时候,sql语句并行执行自动调整level值(默认为2).如果OLAP系统中出现这样的情况,那么如果是大对象,数据
最近遇到一个动态采样带来的性能问题,让我着实有点不可理解,通过查看文档以及做实验,现总结如下:
我们先来看看Online Document关于动态采用的解释
Oracle 10GR2 documentation:
This dynamic sampling feature is controlled by the OPTIMIZER_DYNAMIC_SAMPLING parameter.
For dynamic sampling to automatically gather the necessary statistics, this parameter should be set to a value of 2 or higher.
The default value is 2. See "Dynamic Sampling Levels" for information about the sampling levels that can be set.
Oracle 11GR2 documentation:
When the Optimizer Uses Dynamic Sampling
During compilation, the optimizer decides whether to use dynamic sampling based on a number of factors,
including whether the statements use parallel processing or serial processing.
For parallel statements, the optimizer automatically decides whether to use dynamic sampling and which level to use.
The decision depends on the size of the tables and the complexity of the predicates. The optimizer expects parallel statements to be
resource-intensive,so the additional overhead at compile time is worth it to ensure the best plan. The database ignores the OPTIMIZER_DYNAMIC_SAMPLING setting
unless set to a nondefault value, in which case the value is honored.
For serially processed SQL statements, the dynamic sampling level depends on the value of the OPTIMIZER_DYNAMIC_SAMPLING parameter and
is not triggered automatically by the optimizer. Serial statements are typically short-running, so that any overhead at compile time
could have a huge impact on their performance.
注:大家注意到没有11.2.0.*中如果语句是并行执行,那么是否采用dynamic sampling以及level将以表的大小,sql中table join的复杂度来决定,也就是又CBO来决定采样的level值。
CBO将忽略OPTIMIZER_DYNAMIC_SAMPLING的默认值。对于Oracle的各种特性,如果有自动的功能,多多少少都不太稳定,总会出问题的。当然，对于串行执行的语句依旧动态采样的level 将仍然有OPTIMIZER_DYNAMIC_SAMPLING决定,其实大部分时候level 2已经完全足够。
下面来看看11.2.0.3中动态采用的情况
八梦企业网站源码v1.0 是由八梦网络工作室开发的一款企业网站源码，适合一般中心企业使用。网站功能完善，操作简单。后台可以直接发布文章、图片。网站采用DIV+css布局、可以生成静态，符合SEO优化。目前主要的板块有 关于我们、联系我们、新闻动态、产品中心、客户案例。如果需要可以进行增加，不懂可以联系我们。程序采用asp+access搭建，空间必须要支持ASP，都可以访问。一般100M就够用了。
注:不要收集统计信息
