分析及动态采样

CBO的机制是收集尽可能多的对象信息和系统信息，通过对这些信息进行计算，分析，评估，最终得出一个成本最低的执行计划。为了让CBO总是做出最正确的执行计划，就需要给CBO提供尽可能多的段对象(表，表分区，索引等)信息。

1. 如果一个表没有做分析，数据库将自动对它做动态采样分析，如果想模拟在没有分析数据的情况下，CBO是如何产生执行计划的，可以通过Hint方式将动态采样的级别设置为0，就不使用动态采样。如：select /*+ dynamic_sampling(t 0) */ * from t where id>3;

2. 如果要分析表，使用DBMS_STATS包来分析表。 exec dbms_stats.gather_table_stats(user, '[table_name]');

查看是否对表进行分析，可以通过user_tables和user_indexes视图来确认。

• select num_rows,avg_row_len,blocks,last_analyzed from user_tables where table_name='[table_name]'; 注：[table_name]要大写
• select blevel,leaf_blocks,distinct_keys,last_analyzed from user_indexes where table_name='[table_name]'; 注：[table_name]要大写

 3. DBMS_STATS包对段表的分析有三个层次：

• 表自身的分析：表中的行数，数据块数，行长等
• 列的分析：列植重复数，列上的空值，数据在列上的分布
• 索引的分析：索引叶块的数量，索引的深度，索引的聚合因子

4. 直方图(Histogram)对CBO的影响很大，也就是数据在列上的分布情况。默认情况下，dbms_stats会对所有的列做直方图分析，可以用下面sql查询:

　　　　select table_name,column_name,endpoint_number,endpoint_value from user_histograms where table_name='[table_name]'; 注：[table_name]要大写

　　删除直方图信息，但保留表和索引的分析信息：exec dbms_stats.delete_column_stats(user,'[table_name]','列名');

 5. DBMS_STATS包不仅可以对表进行分析，还可以对数据库进行分析，按照功能分为：

• 性能数据的收集
• 性能数据的设置
• 性能数据的删除
• 性能数据的备份和恢复

DBMS_STATS包里常用gather_table_stats存储过程来对某个表进行性能分析，包括表的分区和索引，当设置cascade=>true时，就会同时执行gather_index_stats这个存储过程对索引进行分析。gather_schema_stats对某用户下的所有对象进行分析,gather_index_stats对索引进行分析。

6. 动态采样(Dynamic sampling)：当段对象(表，索引，分区)没有统计信息即没有被分析的情况下，动态采样分析可以通过直接从需要分析的对象上收集一些数据块(采样)来获得CBO需要的信息。

动态采样的作用：

• 从Oracle10g开始RBO(基于规则的优化器)退出历史舞台，CBO依赖的是充分的统计分析信息，但是并不是每一个用户都会认真，及时的对每一个表进行分析，为了保证执行计划都尽可能的正确，Oracle需要采用动态采样技术来获得更多信息
• 全局临时表。通常来说，临时表的数据时不做分析的，因为它存放的数据时临时性的，可能很快被释放了，但是当一个查询关联到这样的临时表时，CBO想要获得临时表上的统计分析数据，就只能依赖于动态采样了。
• 动态采样除了可以在段对象没有分析时给CBO提供分析数据外，还可以对不同列之间的相关性做统计。相对的，表分析的信息都是独立的，如：1.表行数，平均行长，2.表每列的最大，最小值，重复率，也可能包含直方图，3.索引的聚合因子，索引叶的块数目，索引的高度等。这些信息相互之间都是独立的，当查询涉及列之间的相关性时，这些信息就不够了，所以可以使用动态采样来对列的相关性分析。

既然动态采样那么多优点，为什么Oracle没有用动态采样来代替数据分析呢？

动态采样的缺点：

• 采样的数据块有限，对于海量数据的表，结果难免会有偏差
• 采样会消耗系统资源，特别是OLTP数据库尤其不推荐使用动态采样

动态采样的级别(0-10), 由低到高。采样的级别越高，采样的数据块越多，得到的分析数据越接近于真实，但同时也伴随着大的资源消耗。

什么时候采用采样：

在这样一个环境：sql被反复执行，变量被绑定，硬分析很少，就像OLTP系统，动态采样发生在硬分析时，如果很少硬分析发生，动态采样意义不大。OLAP或数据仓库环境中，将动态采样的级别设置为3或者4比较好。在OLTP环境中，则不应该使用动态采样。

 

转载于:https://www.cnblogs.com/PerOpt/p/3734047.html