07系列文章
Oracle优化07-分析及动态采样-DBMS_STATS 包
概述
获取准确的段对象（表、表分区、索引等）的分析数据，是CBO存在的基石。所以数据段的分析对于CBO来讲非常的重要。
在本篇博文中我们重新梳理一下，从头开始，再一次走进CBO的世界。
我们知道CBO的机制是手机尽可能多的对象信息和系统信息，通过对这些信息进行计算、分析、评估，最终得出一个成本最低的执行花来，这就是CBO的全部。 为了让CBO总是能做出最正确的SQL执行计划，就需要给CBO提供尽可能多的信息。
举个简单的例子：
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
Connected as xxx@xgj
##通过 1=2 不成立的条件，创建一个空表（后续建索引会快一点儿）
SQL> create table t as select object_id ,object_name from dba_objects where 1=2;
Table created
##创建索引
SQL> create index ind_t on t(object_id);
Index created
##插入数据
SQL> insert into t select object_id ,object_name from dba_objects;
35260 rows inserted
SQL> commit;
Commit complete
## 没有对表进行分析，可以通过下面两个视图来确认
SQL> select a.NUM_ROWS,a.AVG_ROW_LEN ,a.BLOCKS,a.LAST_ANALYZED from user_tables a where a.TABLE_NAME='T';
NUM_ROWS AVG_ROW_LEN BLOCKS LAST_ANALYZED
---------- ----------- ---------- -------------
SQL> select a.blevel ,a.leaf_blocks ,a.distinct_keys,a.last_analyzed from user_indexes a where a.table_name='T';
BLEVEL LEAF_BLOCKS DISTINCT_KEYS LAST_ANALYZED
---------- ----------- ------------- -------------
0 0 0 2017-01-08 11
SQL> 表的行数、行长、占用的数据块数以及最后分析时间都是空的，索引的相关信息也都为0，说明这个表和索引都没有被分析过。
如果此时又一条SQL对表做查询，CBO由于无法获取这些信息，很可能生成错误的执行计划。如下所示：
查看SQL的执行计划：
执行SQL
SQL> select /*+ dynamic_sampling(t 0) */ * from t where t.object_id>30;
...省略输出
SQL> select a.SQL_ID,a.CHILD_NUMBER from v$sql a where a.SQL_TEXT like 'select /*+ dynamic_sampling(t 0) */ * from t where t.object_id>30%';
SQL_ID CHILD_NUMBER
------------- ------------
f5q92sydyqc4z 0
##查看执行计划
SQL> select * from table(dbms_xplan.display_cursor('f5q92sydyqc4z',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID f5q92sydyqc4z, child number 0
-------------------------------------
select /*+ dynamic_sampling(t 0) */ * from t where t.object_id>30
Plan hash value: 4013845416
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| T | 4 | 316 | 0 (0)|
|* 2 | INDEX RANGE SCAN | IND_T | 1 | | 0 (0)|
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."OBJECT_ID">30)
10g以后，如果一个表没有做过分析，ORACLE会自动对它做动态采用分析， 我们使用/+ dynamic_sampling(t 0) /这种Hint 将动态采样的级别设置为0，即不使用动态采样.
CBO估算出满足条件的记录为4条，所以选择了索引。 实际情况呢？ 我们先对表做个分析操作。
9i开始，Oracle推荐使用DBMS_STATS包对表进行分析操作。
为CBO收集信息
SQL> exec dbms_stats.gather_table_stats(user,'t');
PL/SQL procedure successfully completed
SQL> select a.NUM_ROWS,a.AVG_ROW_LEN ,a.BLOCKS,a.LAST_ANALYZED from user_tables a where a.TABLE_NAME='T';
NUM_ROWS AVG_ROW_LEN BLOCKS LAST_ANALYZED
---------- ----------- ---------- -------------
35260 24 244 2017-01-08 14
SQL> select a.blevel ,a.leaf_blocks ,a.distinct_keys,a.last_analyzed from user_indexes a where a.table_name='T';
BLEVEL LEAF_BLOCKS DISTINCT_KEYS LAST_ANALYZED
---------- ----------- ------------- -------------
1 98 35257 2017-01-08 14
SQL> dbms_stats.gather_table_stats(user,’t’)默认对表和索引都进行了分析.
清掉shared_pool 重新看下执行计划。
## 清掉 shared_pol
SQL> alter system flush shared_pool;
System altered
SQL> select * from t where t.object_id>30;
省略输出...
SQL> select a.SQL_ID,a.CHILD_NUMBER from v$sql a where a.SQL_TEXT like 'select * from t where t.object_id>30%';
SQL_ID CHILD_NUMBER
------------- ------------
fcvjyr3skfj5b 0
SQL> select * from table(dbms_xplan.display_cursor('fcvjyr3skfj5b',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID fcvjyr3skfj5b, child number 0
-------------------------------------
select * from t where t.object_id>30
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 68 (100)| |
|* 1 | TABLE ACCESS FULL| T | 35234 | 825K| 68 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."OBJECT_ID">30)
SQL> 我们看到T表在做完分析后，CBO估算出的结果集为35234 ，和实际情况相差很小。
所以使用全表扫描更优。 因为这种情况下，如果先访问索引，然后根据索引的键值去寻找表的记录，势必会导致读取更多的数据块，走全表反而会更快一些。
直方图 Histogram
直方图 (Histogram), 是数据分析分析当中的一个内容，但它对CBO的影响非常大。
DBMS_STATS 包对段表的分析有三个层次：
- 表自身的分析： 包括表中的行数，数据块数，行长等信息。
- 列的分析：包括列值的重复数，列上的空值，数据在列上的分布情况。
- 索引的分析： 包括索引叶块的数量，索引的深度，索引的聚合因子等。
我们现在说的直方图，单指第二项的最后一种 列分析中 数据在列上的分布情况。
当 Oracle 做直方图分析时，会将要分析的列上的数据分成很多数量相同的部分，每一部分称为一个 bucket,这样 CBO 就可以非常容易地知道这个列上的数的分布情况，这种数据的分布将作为一个非常重要的因素纳入到执行计划成本的计算当中。
对于数据分部非常倾斜的表，做直方图分析是非常有用的。
来看下面两个例子：
图一的数据分布非常均匀的直方图模式，每一个数值范围（bucket）内的数据记录都基本上一样。
图二，数据分部严重倾斜，数值小于20的记录占到了总记录的70%。
直方图有时候对于CBO非常的重要，特别是对于字段数据非常倾斜的表，做直方图分析尤为重要。
举例说明：
SQL> create table t as select 1 id ,object_name from dba_objects;
Table created
SQL> update t set t.id=99 where rownum=1;
1 row updated
SQL> commit;
Commit complete
SQL> create index ind_t on t(id);
Index created
##查看数据分布情况
SQL> select id ,count(1) from t group by id;
ID COUNT(1)
---------- ----------
1 35256
99 1
## 表和索引分析
SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true);
PL/SQL procedure successfully completed
SQL> 查看分析后的情况：
SQL> select a.num_rows,a.avg_row_len ,a.blocks,a.last_analyzed from user_tables a where a.table_name='T';
NUM_ROWS AVG_ROW_LEN BLOCKS LAST_ANALYZED
---------- ----------- ---------- -------------
35257 22 144 2017-01-08 18
SQL> select a.blevel ,a.leaf_blocks ,a.distinct_keys,a.last_analyzed from user_indexes a where a.table_name='T';
BLEVEL LEAF_BLOCKS DISTINCT_KEYS LAST_ANALYZED
---------- ----------- ------------- -------------
1 69 2 2017-01-08 18
SQL> DISTINCT_KEYS ： 2
我们创建了一张表，id字段倾斜非常严重，除了一条id=99的数据之外，其余的id全部为1。
默认情况下，dbms_stats包会对所有的列做直方图分析。
查看直方图的信息视图 user_histogram
SQL> select * from user_histograms a where a.TABLE_NAME = 'T' ;
查看如下SQL的执行计划
select * from t where id=1;
select * from t where id=99;
现在我们将直方图信息删除，但是保留表和索引的分析信息
SQL> exec dbms_stats.delete_column_stats(user,'t',colname => 'id');
PL/SQL procedure successfully completed再此查看T表直方图的视图信息
我们可以看到ID字段的信息已经被删除掉了。
查看表和索引的信息
我们可知索引和表的信息依然存在，并且索引中甚至可以找到distinct_keys=2。但是CBO却无法得到这两个数值的分布情况，所以依然没法选出一个正确的执行计划。
下面看实例：
select * from t where id=1;
select * from t where id=99;
CBO在id =1 时，估算返回的结果是353行，比较全部表的记录35257（这个信息可以从表的分析数据中得到user_tables.NUM_ROWS字段）， CBO认为选择索引是合适的，但是我们知道实际上id=1的记录数基本上等于表的全部记录。在这种情况下CBO没法得到数据的具体分布情况，所以做出了错误的执行计划
同样的CBO在id=99的情况下，CBO估算出返回值是6条。
因此我们可以断定，如果一个裂伤的数据有比较严重的倾斜，对这个列做直方图是有必要的。
那是不是每个表的每个列都应该做直方图分析呢？
其实是一个没有定论的话题。因为首先要知道，Oracle对数据分析是需要消耗资源的，特别是对于一些很大的段对象，分析的时间尤其长。 所以权衡一下，既要避免分析导致系统性能下降而对业务产生影响，同时又要保证CBO获取足够的信息来产生正确的执行计划。
