之前在说Oracle Optimizer中的CBO时讲到，当表没有做分析的时候，Oracle 会使用动态采样来收集统计信息。 获取准确的段对象(表，表分区，索引等)的分析数据，是CBO存在的基石，CBO的机制就是收集尽可能多的对象信息和系统信息，通过对这些信息进行计算，分析，评估，最终得出一个成本最低的执行计划。 所以对于CBO，数据段的分析就非常重要。
Oracle Optimizer CBO RBO
一．先演示一个示例，来理解分析的作用
1.1创建表
SQL> create table t as select object_id,object_name from dba_objects where 1=2;
表已创建。
SQL> create index index_t on t(object_id);
索引已创建。
SQL> insert into t select object_id,object_name from dba_objects;
已创建72926行。
SQL> commit;
提交完成。
1.2查看分的分析及执行计划
SQL> select num_rows,avg_row_len,blocks,last_analyzed fromuser_tables where table_name='T';
NUM_ROWS AVG_ROW_LEN BLOCKS LAST_ANALYZED
---------- ----------- ---------- --------------
SQL> select blevel,leaf_blocks,distinct_keys,last_analyzed from user_indexes where table_name='T';
BLEVEL LEAF_BLOCKS DISTINCT_KEYS LAST_ANALYZED
---------- ----------- ------------- --------------
0 0 0 25-8月 -10
从查询结果看出，表的行数，行长，占用的数据块数及最后的分析时间都是空。 索引的相关信息也没有，说明这个表和说因都没有被分析，如果此时有一条SQL 对表做查询，CBO 由于无法获取这些信息，很可能生成错误的执行计划，如：
SQL> set linesize 200
SQL> set autot trace exp;
SQL> select /*+dynamic_sampling(t 0) */ * from t where object_id>30;
执行计划
----------------------------------------------------------
Plan hash value: 80339723
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 316 | 0 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 4 | 316 | 0 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDEX_T | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">30)
SQL>
在Oracle 10g以后，如果一个表没有做分析，数据库将自动对它做动态采样分析，所以这里采用hint的方式将动态采样的级别设置为0，即不使用动态采样。
从这个执行计划，看书CBO 估计出表中满足条件的记录为4条，索引使用了索引。 我们对表做一下分析，用结果比较一下。
1.3分析表及查看分析之后的执行计划
分析可以通过两中方式：
一种是analyze 命令，如：
analyze table tablename compute statistics for all indexes;
还有一种就是通过DBMS_STATS包来分析，从9i 开始，Oracle 推荐使用DBMS_STATS包对表进行分析操作，因为DBMS_STATS 提供了更多的功能，以及灵活的操作方式。
SQL>exec dbms_stats.gather_table_stats('SYS','T');
PL/SQL 过程已成功完成。
SQL> select blevel,leaf_blocks,distinct_keys,last_analyzed from user_indexes where table_name='T';
BLEVEL LEAF_BLOCKS DISTINCT_KEYS LAST_ANALYZED
---------- ----------- ------------- --------------
1 263 72926 25-8月 -10
SQL> select num_rows,avg_row_len,blocks,last_analyzed from user_tables where table_name='T';
NUM_ROWS AVG_ROW_LEN BLOCKS LAST_ANALYZED
---------- ----------- ---------- --------------
72926 29 345 25-8月 -10
从上面的结果，可以看出DBMS_STATS.gather_table_stats已经对表和索引都做了分析。 现在我们在来看一下执行计划。
SQL> set autot trace exp;
SQL> select * from t where object_id>30;
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 72899 | 2064K| 96 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 72899 | 2064K| 96 (2)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID">30)
从这个计划，我们看出CBO 估算出的结果是72899 条记录，与实际的72926很近。 此时选择全表扫描更优。 通过这个例子，我们也看出了分析对执行计划的重要性。
二．直方图(Histogram)
DBMS_STATS 包对段表的分析有三个层次：
(1)表自身的分析： 包括表中的行数，数据块数，行长等信息。
(2)列的分析：包括列值的重复数，列上的空值，数据在列上的分布情况。
(3)索引的分析： 包括索引叶块的数量，索引的深度，索引的聚合因子等。
直方图就是列分析中数据在列上的分布情况。
当Oracle 做直方图分析时，会将要分析的列上的数据分成很多数量相同的部分，每一部分称为一个bucket,这样CBO就可以非常容易地知道这个列上的数的分布情况，这种数据的分布将作为一个非常重要的因素纳入到执行计划成本的计算当中。
对于数据分布非常倾斜的表，做直方图是非常有用的。 如：1,10,20,30,40,50. 那么在一个数值范围(bucket)内，它的数据记录基本上一样。 如果是：1,5,5,5,5,10,10,20,50,100. 那么它在bucket内，数据分布就是严重的倾斜。
直方图有时对于CBO非常重要，特别是对于有字段数据非常倾斜的表，做直方图分析尤为重要。 可以用dbms_stats包来分析。 默认情况下，dbms_stats 包会对所有的列做直方图分析。 如：
SQL>exec dbms_stats.gather_table_stats('SYS','T',cascade=>true);
PL/SQL 过程已成功完成。
然后从user_histograms视图上查看到相关的信息：
SQL> select table_name,column_name,endpoint_number,endpoint_value fromuser_histograms where table_name='T';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ -------------------- --------------- --------------
T OBJECT_ID 0 2
T OBJECT_NAME 0 2.4504E+35
T OBJECT_ID 1 76685
T OBJECT_NAME 1 1.0886E+36
如果一个列上的数据有比较严重的倾斜，对这个列做直方图是必要的，但是，Oracle 对数据分析是需要消耗资源的，特别是对于一些很大的段对象，分析的时间尤其长。对于OLAP系统，可能需要几个小时才能完成。
所以做不做分析就需要DBA 权衡好了。 但有一点要注意， 不要在生产环境中随便修改分析方案，除非你有十足的把握。 否则可能导致非常严重的后果。
三．DBMS_STATS包
DBMS_STAS包不仅能够对表进行分析，它还可以对数据库分析进行管理。 按照功能可以分一下几类：
(1)性能数据的收集
(2)性能数据的设置
(3)性能数据的删除
(4)性能数据的备份和恢
更多信息参考Oracle联机文档：
11g DBMS_STATS
10g DBMS_STATS
3.1 DBMS_STATS包的几个常用功能：性能的手机，设定和删除
性能数据的收集包含这样几个存储过程：
从名字也可以看出各自的作用，这些存储过程用来收集数据库不同级别对象的性能数据，包括：数据库，数据字典，表，索引，SCHEMA的性能等。
在gather_table_stats 存储过程的所有参数中，除了ownname和tabname，其他的参数都有默认值。 所以我们在调用这个存储过程时，Oracle 会使用参数的默认值对表进行分析。如：
SQL> exec dbms_stats.gather_table_STATS('SYS','T');
PL/SQL 过程已成功完成。
如果想查看当前的默认值，可以使用dbms_stats.get_param函数来获取：
SQL> select dbms_stats.get_param('method_opt') from dual;
DBMS_STATS.GET_PARAM('METHOD_OPT')
------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO
结合上面对参数的说明：
- AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
我们可以看出，就是对所有的列做直方图分析，直方图设置的bucket值由Oracle自己决定。
3.1.1.1 estimate_percent参数
这个参数是一个百分比值，它告诉分析包需要使用表中数据的多大比例来做分析。
理论上来讲，采样的数据越多，得到的信息就越接近于实际，CBO做出的执行计划就越优化，但是，采样越多，消耗的系统资源必然越多。 对系统的影响也越大。 所以对于这个值的设置，要根据业务情况来。 如果数据的直方图分布比较均匀，就可以使用默认值：AUTO_SAMPLE_SIZE，即让Oracle 自己来判断采样的比例。有时，特别是对于批量加载的表，我们可以预估表中的数据量，可以人工地设置一个合理的值。 一般，对于一个有1000万数据的表分区，可以把这个参数设置为0.000001.
3.1. 1.2 Method_option参数
这个参数用来定义直方图分析的一些值。
FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size clause] column|attribute [size_clause] [,column|attribute [size_clause]...]
这里给出了4种指定哪些列进行分析的方式：
(1)所有列：for all column
(2)索引列：只对有索引的列进行分析，for all indexed columns
(3)影藏列：只对影藏的列进行分析，for all hidden columns
(4)显示指定列：显示的指定那些列进行分析，for columns columns_name
该参数默认值：for all columns size auto.
3.1. 1.3 degree参数
用来指定分析时使用的并行度。 有以下这些设置：
(1)Null： 如果设置为null，Oracle 将使用被分析表属性的并行度，比如表在创建时指定的并行度，或者后者使用alter table 重新设置的并行度。
(2)一个数值： 可以显示地指定分析时使用的并行度。
(3)Default_degree: 如果设置为default，Oracle 将根据初始化参数中相关参数的设置来决定使用的并行度。
这个参数的默认值是Null，即通过表上的并行度属性来决定分析使用的并行度。 当需要分析的表或表分区非常大，并且系统资源比较充分的时候，就可以考虑使用并行的方式来做分析，这样就会大大提高分析的速度。 相反，如果你的系统资源比较吃紧，那么启用并行可能会适得其反。
3.1. 1.4 Granularity
分析的粒度，有以下几个配置：
(1)ALL : 将会对表的全局(global)，分区，子分区的数据都做分析
(2)AUTO: Oracle 根据分区的类型，自动决定做哪一种粒度的分析。
(3)GLOBAL：只做全局级别的分析。
(4)GLOBAL AND PARTITION: 只对全局和分区级别做分析，对子分区不做分析，这是和ALL的一个区别。
(5)PARTITION: 只在分区级别做分析。
(6)SUBPARTITION: 只在子分区做分析。
在生产环境中，特别是OLAP 或者数据仓库的环境中，这个参数的设置会直接影响到CBO的执行计划选择。
在OLAP或者数据仓库系统中，经常有这样的事情，新创建一个分区，将批量的数据(通常是很大的数据)加载到分区中，对分区做分析，然后做报表或者数据挖掘。 在理想的情况下，对表的全局，分区都做分析，这样才能得到最充足的数据，但是通常这样的表都非常大，如果每增加一个分区都需要做一次全局分析，那么会消耗极大的系统资源。 但是如果只对新加入的分区进行分区而不做全局分析，oracle 在全局范围内的信息就会不准确。
该参数在默认情况下，DBMS_STATS 包会对表级(全局)，分区级(对应参数partition)都会进行分析。 如果把cascade 设置为true，相应索引的全局和分区级别也都会被分析。 如果只对分区级进行分析，而全局没有分析，那么全局信息没有更新，依然会导致CBO 作出错误的执行计划。
所以当一些新的数据插入到表中时，如果对这些新的数据进行分析，是一个非常重要的问题。 一般参考如下原则：
(1)看一下新插入的数据在全表中所占的比例，如果所占比例不是很大，那么可以考虑不做全局分析，否则就需要考虑，一句是业务的实际运行情况。
(2)采样比例。 如果载入的数据量非常大，比如上千万或者更大，就要把采样比例压缩的尽可能地小，但底线是不能影响CBO做出正确的执行计划，采样比例的上线是不能消耗太多的资源而影响到业务的正常运行。
(3)新加载的数据应该要做分区级的数据分析。 至于是否需要直方图分析，以及设置多少个buckets(size参数指定)，需要DBA一句数据的分布情况进行考虑，关键是视数据的倾斜程度而定。
3.2 DBMS_STATS包管理功能
3.2.1获取分析数据
这四个存储过程分别为用户获取字段，索引，表和系统的统计信息。 它的用法是首先定义要获取性能指标的变量，然后使用存储过程将性能指标的值赋给变量，最后将变量的值输出。 如：
SQL> set serveroutput on
SQL> declare
2 dist number;
3 dens number;
4 ncnt number;
5 orec dbms_stats.statrec;
6 avgc number;
7 begin
8 dbms_stats.get_column_stats('SYS','T','object_ID',distcnt=>dist,density=>dens,nullcnt=>ncnt,srec=>orec,avgclen=>avgc);
9 dbms_output.put_line('the distcnt is:' ||to_char(dist));
10 dbms_output.put_line('the density is:' ||to_char(dens));
11 dbms_output.put_line('the nullcnt is:' ||to_char(ncnt));
12 dbms_output.put_line('the srec is:' ||to_char(ncnt));
13 dbms_output.put_line('the avgclen is:' ||to_char(avgc));
14 end;
15 /
the distcnt is:72926
the density is:.0000137125305103804
the nullcnt is:0
the srec is:0
the avgclen is:5
PL/SQL 过程已成功完成。
更多信息参考：
3.2.2设置分析数据
这几个存储过程允许我们手工地为字段，索引，表和系统性能数据赋值。 它的一个用处是当相应的指标不准确导致执行计划失败时，可以使用这种方法手工地来为这些性能数据赋值。 在极端情况下，这也不失为一个解决问题的方法。
关于这4个存储过程的绝提用法参考 oracle 联机文档：
3.2.3删除分析数据
当性能数据出现异常导致CBO判断错误时，为了立刻修正这个错误，删除性能数据也是一种补救的方法，比如删除了表的数据，让CBO重新对表做动态采样分析，得到一个正确的结果。
它可以删除字段，数据库，数据字典，基表，索引，表等级别的性能数据。
具体参考oracle 联机文档：
3.2.4保存分析数据
可以用这两个存储过程创建一个表，用于存放性能数据，这样有利于对性能数据的管理，也可以删除这个表。
具体参考oracle 联机文档：
3.2.5导入和导出分析数据
这些存储过程可以将已经有的性能指标导入到用户创建好的表中存放，需要时，可以从表中倒回来。
具体参考oracle 联机文档：
3.2.6锁定分析数据
TheLOCK_*procedures either freeze the current set of the statistics or to keep the statistics empty (uncollected).When statistics on a table are locked, all the statistics depending on the table, including table statistics, column statistics, histograms and statistics on all dependent indexes, are considered to be locked.
可能在某些时候，我们觉得当前的统计信息非常好，执行计划很准确，并且表中数据几乎不变化，那么可以使用来锁定表的统计信息，不允许对表做分析或者设定分析数据。当表的分析数据被锁定之后，相关的所有分析数据，包括表级，列级，直方图，索引的分析数据都将被锁定，不允许被更新。
具体参考oracle 联机文档：
3.2.7分析数据的恢复
Whenever statistics in dictionary are modified, old versions of statistics are saved automatically for future restoring. The old statistics are purged automatically at regular intervals based on the statistics history retention setting and the time of recent statistics gathering performed in the system. Retention is configurable using theALTER_STATS_HISTORY_RETENTION Procedure.
比如我们重新分析了表，发现分析的数据导致了CBO选择了错误的执行计划，为了挽救这种局面，可以将统计信息恢复到从前的那个时间点，也就是CBO执行计划正确的时间点，先解决这个问题，再来分析问题的原因。
具体参考oracle 联机文档：
四．动态采样
4.1什么是动态采样
动态采样(Dynamic Sampling)技术的最初提出是在Oracle 9i R2，在段(表，索引，分区)没有分析的情况下，为了使CBO 优化器得到足够的信息以保证做出正确的执行计划而发明的一种技术，可以把它看做分析手段的一种补充。
当段对象没有统计信息时(即没有做分析)，动态采样技术可以通过直接从需要分析的对象上收集数据块(采样)来获得CBO需要的统计信息。
一个简单的例子：
创建表：
SQL> create table t
2 as
3 select owner,object_type from all_objects;
表已创建。
查看表的记录数：
SQL> select count(*) from t;
COUNT(*)
----------
72236 --记录数
这里创建了一张普通表，没有做分析，我们在hint中用0级来限制动态采样，此时CBO 唯一可以使用的信息就是表存储在数据字典中的一些信息，如有多少个extent，有多少个block，但是这些信息是不够的。
SQL> set autot traceonly explain
SQL> select /*+dynamic_sampling(t 0) */ * from t;
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15928 | 435K| 55 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 15928 | 435K| 55 (0)| 00:00:01 |
在没有做动态分析的情况下，CBO 估计的记录数是15928条，与真实的72236相差甚远。
我们用动态分析来查看一下：
SQL> select * from t;
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 80232| 2193K| 56 (2)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 80232 | 2193K| 56 (2)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
在Oracle 10g中默认对没有分析的段做动态采样，上面的查询结果显示使用了Level 2级的动态采样，CBO 估计的结果是80232 与72236很接近了。
注意一点：
在没有动态采样的情况下，对于没有分析过的段，CBO也可能错误地将结果判断的程度扩大话。 如：
SQL> delete from t;
已删除72236行。
SQL> commit;
提交完成。
SQL> select /*+dynamic_sampling(t 0) */ * from t;
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |15928 | 435K| 55 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 15928 | 435K| 55 (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL> select * from t;
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1| 28 | 55 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 1 | 28 | 55 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
如果细心一点，可能看出2个执行计划的差别。 在没有采用动态分析的情况下，CBO 对t表估计的还是15928行记录，但是用动态分析就显示1条记录。 而表中的数据在查询之前已经删除掉了。 出现这种情况的原因是因为高水位。 虽然表的数据已经删除，但是表分配的extent 和block 没有被回收，所以在这种情况下CBO 依然认为有那么多的数据在那。
通过这一点，我们可以看出，此时CBO能够使用的信息非常有限，也就是这个表有几个extent，有几个block。 但动态采样之后，Oracle 立即发现，原来数据块中都是空的。
关于Oracle高水位，参考我的blog：Oracle高水位(HWM)
动态采样有两方面的作用：
(1)CBO 依赖的是充分的统计分析信息，但是并不是每个用户都会非常认真，及时地去对每个表做分析。 为了保证执行计划都尽可能地正确，Oracle 需要使用动态采样技术来帮助CBO 获取尽可能多的信息。
(2)全局临时表。 通常来讲，临时表的数据是不做分析的，因为它存放的数据是临时性的，可能很快就释放了，但是当一个查询关联到这样的临时表时，CBO要想获得临时表上的统计信息分析数据，就只能依赖于动态采样了。
动态采样除了可以在段对象没有分析时，给CBO提供分析数据之外，还有一个独特的能力，它可以对不同列之间的相关性做统计。
相对的，表分析的信息是独立的。 如：
(1)表的行数，平均行长。
(2)表的每个列的最大值，最小值，重复率，也可能包含直方图。
(3)索引的聚合因子，索引叶的块数目，索引的高度等。
尽管看到动态采样的优点，但是它的缺点也是显而易见，否则Oracle一定会一直使用动态采样来取代数据分析：
(1)采样的数据块有限，对于海量数据的表，结果难免有偏差。
(2)采样会消耗系统资源，特别是OLTP数据库，尤其不推荐使用动态采样。
4.2动态采样的级别
4.2.1 Level 0
不做动态分析
4.2.2 Level 1
Oracle 对没有分析的表进行动态采样，但需要同时满足以下4个条件。
(1)SQL中至少有一个未分析的表
(2)未分析的表出现在关联查询或者子查询中
(3)未分析的表没有索引
(4)未分析的表占用的数据块要大于动态采样默认的数据块(32个)
4.2.3 Level 2
对所有的未分析表做分析，动态采样的数据块是默认数据块的2倍。
4.2.4 Level 3
采样的表包含满足Level 2定义的所有表，同时包括，那些谓词有可能潜在地需要动态采样的表，这些动态采样的数据块为默认数据块，对没有分析的表，动态采样的默认块为默认数据块的2倍。
4.2.5 Level 4
采样的表包含满足Level 3定义的表，同时还包括一些表，他们包含一个单表的谓词会引用另外的2个列或者更多的列；采样的块数是动态采样默认数据块数；对没有分析的表，动态采样的数据块为默认数据块的2倍。
4.2.6 Level 5，6，7，8，9
采样的表包含满足Level 4定义的表，同时分别使用动态采样默认数据块的2，4，8，32，128 倍的数量来做动态分析。
4.2.7 Level 10
采样的表包含满足Level 9定义的所有表，同时对表的所有数据进行动态采样。
采样的数据块越多，得到的分析数据就越接近与真实，但同时伴随着资源消耗的也越大。
4.3什么时候使用动态采样
动态采样也需要额外的消耗数据库资源，所以，如果 SQL 被反复执行，变量被绑定，硬分析很少，在这样一个环境中，是不宜使用动态采样的，就像OLTP系统。 动态采样发生在硬分析时，如果很少有硬分析发生，动态采样的意义就不大。
而在OLAP或者数据仓库环境下，SQL执行消耗的资源要远远大于SQL解析，那么让解析在消耗多一点资源做一些动态采样分析，从而做出一个最优的执行计划是非常值得的。 实际上在这样的环境中，硬分析消耗的资源几乎是可以忽略的。
所以，一般在OLAP 或者数据仓库环境中，将动态采样的level 设置为3或者4 比较好。 相反，在OLTP系统下，不应该使用动态采样。
206
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
