Oracle直方图
直方图是一种按数据出现的频率来进行分类存储的方法.在oracle中直方图是用来描述表中列数据的分布情况.每一个sql在被执行前都要经过优化这一步骤那么在优化器给出一个最优执行计划之优化器应该要知道sql语句中所引用的底层对象的详细信息.
直方图描述的对象包括列中不同值的数量和它们出现的频率.现在存储每一个不同值和它出现的频率是不可行的,特别是对于大表来说列中有上万个不同值,oracle使用直方图来存储关于列中数据分布的有用信息而且oracle的CBO使用直方图信息来计算出一个最优的执行计划.
CBO与直方图histograms
从一个行源中评估返回行数所占的比例这就是选择率,选择率在CBO的查询优化中起着重要作用.选择率的取值范围是0到1之间.粗略的讲,如果满足谓词条件的只有少量的行记录那么CBO将更喜欢使用索引扫描,如果谓词条件要从表中获取大量数据那么CBO将更喜欢使用全表扫描.比如下面的查询获取deptno等于10的所有雇员信息如果返回少量的记录查询将会更倾向于使用索引扫描:
select * from emp where deptno=10;
为了评估选择率(或者换句话说计算出最优执行计划),CBO会使用各种形式的统计信息,配置参数等.以表中列的角度来说,CBO会收集以下统计信息:
列中不同值的数量也就是NDV
列中的最小值/最大值
列中null值的数量
数据分布或直方图信息
在没有直方图时优化器使用基表中记录的列中不同值的数量,列中最小值/最大值和列中null值的数量来计算统计信息.使用这些信息优化器假设数据在列中的最小值和最大值之间是均匀分布的或者说列中每一个不同值的出现次数是相同的.
下面举列说明.创建一个测试表t1它有10000行记录,有两个列,列all_distinct包含不同值的范围从1到10000.列skew对于前10行记录的值从1到10,余下的9990行记录都是10000.
[oracle@jingyong ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 4 06:05:14 2014 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> create table t1 as select rownum all_distinct,10000 skew from dual connect by level < =10000; Table created. SQL> update t1 set skew=all_distinct where rownum< =10; 10 rows updated. SQL> commit; Commit complete. SQL> select skew,count(*) from t1 group by skew order by skew; SKEW COUNT(*) ---------- ---------- 1 1 2 1 3 1 4 1 5 1 6 1 7 1 8 1 9 1 10 1 10000 9990 11 rows selected.
使用dbms_stata.gather_table_stats来收集统计信息是生成直方图是由参数method_opt来控制的method_opt参数的语法是由多个部分组成的.前两个部分是强制性的:
FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size clause] column [size_clause] [,column...]
method_opt语法中的主要部分控制哪此列将收集列的统计信息(min,max,ndv,nulls).缺省是for all columns,它将会对表中所有的列(包括隐藏列)收集基本的列统计信息.
for all indexed columns将只对哪些包含索引的列进收集列统计信息.
for all hidden columns将只会对哪些虚拟列收集列统计信息.这意味着在对表收集统计时真实列是不会生成列统计信息的.这个值不能用于通常的统计信息收集.它只能用在当基表列的统计信息精确收集后在表中创建新的虚拟列.然后对新的虚拟列收集列统计信息时才使用它.
注意如果列不在统计信息收集列表中那么只会收集列的平均长度.
size用来指定直方图的桶数SIZE {integer | REPEAT | AUTO | SKEWONLY}
auto:基于列的使用信息(sys.col_usage$)和是否存在数据倾斜来收集直方图
integer:人为的指定创建直方图的桶数范围是1到254,如果size 1意味着不创建直方图
repeat:只会对已经存在直方图的列重新生成直方图.如果是一个分区表,repeat会确保对在全局级别存在直方图的列重新生成直方图.这是不被推荐的设置的.当前直方图的桶数将会作为重新生成直方图所使用的桶数的最大值.比如,当前直方图的桶数是5,那么生成的直方图最大桶数就是5,说的直白点就是刷新现有直方图的列上的统计信息.
skewonly:对任何数据分布出现倾斜列的自动创建直方图
现在来对表t1收集统计信息但不创建直方图
SQL> exec dbms_stats.gather_table_stats(user,'t1',method_opt=>'for all columns size 1'); PL/SQL procedure successfully completed. SQL> select column_name,num_distinct,density from user_tab_col_statistics where 2 table_name='T1'; COLUMN_NAME NUM_DISTINCT DENSITY ------------------------------ ------------ ---------- ALL_DISTINCT 10000 .0001 SKEW 11 .090909091
如果没有直方图,列的density统计信息代表了它的选择率它是通过去时1/num_distinct=1/11=0.09090901来计算出来的.在有直方图的情况下,density的计算依赖于直方图的类型和oracle的版本.density值的范围是0到1之间.当查询使用这个列作谓词条件时优化器将会使用这个列的density统计信息来评估将要返回的行数.所以 cardinality(基数)=selectivity(选择率)* number of rows(表的行数)
下面来检查一下在谓词条件中列的数据分布存在倾斜而没有直方图的情况下其基数评估的情况:
SQL> explain plan for select * from t1 where skew=1;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 909 | 6363 | 7 (15)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 909 | 6363 | 7 (15)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("SKEW"=1)
SQL> explain plan for select * from t1 where skew=10000;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 909 | 6363 | 7 (15)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 909 | 6363 | 7 (15)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("SKEW"=10000)
因为oracle假设列skew中的数据是均匀分布的所以基数评估cardinality=density*num_rows=0.09090901*10000=909.09,四舍五入就是909行.但是我们知道skew=1的记录只有1行而skew=10000的记录有9990行.这种假设必然导致错误的执行计划.例如,如果我们在列skew上创建一个B树索引,oracle将使用对谓词skew=10000行使用索引扫描并返回909行记录.
SQL> create index skew_idx on t1(skew);
Index created.
SQL> exec dbms_stats.gather_index_stats(user,'skew_idx');
PL/SQL procedure successfully completed.
SQL> explain plan for select * from t1 where skew=10000;
Explained.
SQL> select * from table(dbms_xplan.display);
Plan hash value: 3994350891
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 909 | 6363 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 909 | 6363 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SKEW_IDX | 909 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SKEW"=10000)
因为我们知道没有给出关于数据分布的额外信息,CBO假设列中的数据在最小值和最大值之间是均匀分布的所以选择了错误的执行计划.
oracle直方图
一旦对列创建直方图后,它将告诉CBO列中数据出现的频率.所以在上面的例子中如果对列skew创建直方图它将告诉优化顺skew=1的值只出现一次,skew=10000的值出现了9990次.因此它能让优化器选择最优的执行计划.
在oracle中有两种类型的直方图.第一种是oracle会选择存储列中每一个不同值以及其出现的频率,称这种为宽度平衡直方图或频率直方图.这对于列有少量的不同值来说是有效和可能的方式.然而当列有大量不同值时要存储每一个不同值以及其出现的频率是不可能的.当然在无限资源(存储空间和计算能力和解析时间)的情况下,可以在任何情况下对每一个不同值存储其出现的频率来对优化器提供最终的信息,但是在真实的环境中这是不可能的.所以oracle使用高度平衡直方图来存储这样的数据.oracle会根据列中不同值的数量来自动判断所要创建直方图的类型,不同类型的直方图所描述的信息是不同的.
频率直方图(frequence histograms)
频率直方图列中的不同值被划到相同数量的桶中.每一个桶中存储的都是相同的值,也就是说频率直方图的桶数等于列的不同值的个数.buckets=ndv
下面的图表代表了列skew的数据分布情况.从图表中可以看出以下信息:
在x轴有11个桶,每一个桶代表了一个不同的值
Y轴显示了每一个不同值出现的频率.skew的1到10的频率是1,值10000的频率是9990
通过查看这样的信息可以很容易的说出一个特定值出现的频率
下面来对列skew创建一个频率直方图并查看数据是怎样存储在数据字典视图中的.现在对参数method_opt使用’for column column_name size n’来创建指定桶数的直方图.
SQL> exec dbms_stats.gather_table_stats(user,'t1',method_opt=>'for columns skew size 11'); PL/SQL procedure successfully completed. SQL> select column_name,endpoint_number,endpoint_value from user_tab_histograms where 2 table_name='T1' and column_name='SKEW'; COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ------------ --------------- -------------- SKEW 1 1 SKEW 2 2 SKEW 3 3 SKEW 4 4 SKEW 5 5 SKEW 6 6 SKEW 7 7 SKEW 8 8 SKEW 9 9 SKEW 10 10 SKEW 10000 10000
第一个语句对列skew创建了有11个桶的直方图,因为我们知道列skew有11个不同的值.第二个语句显示了存储在数据字典视图中的直方图数据.直方图中存储的信息依赖于直方图的桶数小于列不同值的个数或者相等会有不同的解释,也就是说直方图中存储的信息依赖于直方图的类型会有不同的解释.下面解释频率直方图所代表的信息.
Endpoint_value显示的是真实的列值,endpoint_number显示的是累积的行数或者是累积的频率.为了计算一个特定列值的频率需使用与它相关的endpoint_number值减去它之前的累积值.
例如,对于endpoint_value为5的值,它的endpoint_number为5,之前的endpoint_number为4,因上skew=5的记录只有5-4=1行.类似的对于endpoint_value为10000的值它的endpoint_number为10000它之前的endpoint_number为10,所以skew=10000的记录有10000=10=9990行.
使用下面的sql来解释说明存储在数据字典中的直方图信息:
SQL> select endpoint_value as column_value, 2 endpoint_number as cummulative_frequency, 3 endpoint_number - lag(endpoint_number,1,0) over (order by endpoint_number) as frequency 4 from user_tab_histograms 5 where table_name ='T1' and column_name='SKEW'; COLUMN_VALUE CUMMULATIVE_FREQUENCY FREQUENCY ------------ --------------------- ---------- 1 1 1 2 2 1 3 3 1 4 4 1 5 5 1 6 6 1 7 7 1 8 8 1 9 9 1 10 10 1 10000 10000 9990
存储总的或累积频率来代替单个频率在范围扫描时是特别有用的对于象where skew< =10这样的谓词基数就现成的.
现在因为我们对更skew创建了直方图再来查看之前的查询有什么不同:
SQL> select column_name,num_distinct,density,histogram from user_tab_col_statistics where table_name='T1';
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM
------------------------------ ------------ ---------- ---------------
ALL_DISTINCT 10000 .0001 NONE
SKEW 11 .00005 FREQUENCY
SQL> explain plan for select * from t1 where skew=10000;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9990 | 69930 | 7 (15)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 9990 | 69930 | 7 (15)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SKEW"=10000)
13 rows selected.
SQL> explain plan for select * from t1 where skew=1;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 3994350891
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SKEW_IDX | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SKEW"=1)
14 rows selected.
现在优化器对于谓词skew=10000选择了全表扫描且能精确计算出它的基数9990.注意现在skew列的density是变成了0.00005也就是1/(2*num_rows)或者0.5/num_rows.
高度平衡直方图(height-balanced histograms)
在频率直方图中oracle给每一个不同值分配一个桶,然而桶的最大个数是254,因此如果表中的列有大量的不同值(超过254),将会创建一个高度平衡的直方图.
在高度平衡直方图中,因为我们的不同值超过了桶的个数,因此oracle首先分对列数据进行排序然后将数据集按桶数进行分类且除了最后一桶可能包含的数据比其它的桶少以外,所有其它的桶包含相同数量的值(这就是为什么叫高度平等直方图的原因).
这是有一个单独的语句用来创建高度平衡直方图.当请求的桶数少于列中不同值的个数时,oracle就会创建一个高度平衡直方图且这意味着endpoint_value和endpoint_number是不相同的.为了解释这种类型直方图的信息先看一个列有23个值且有9个不同值的例子.假设我们指定直方图的桶数是5,下面的图表显示了这些数据是如何存储在直方图中的: 基于上面的图表可以得出以下信息:
直方图的桶数比列中的不同值的个数小
因为我们指定了直方图的桶数是5,所以整个数据集除了最后一个桶(在这里只有3个值)其它按相同的大小分配到每一个桶中.
每一个桶中的endpoints和第一个桶中的first point被标记因为它们有特殊意义.
数据3被标记为红色,它是一种特殊情况它的endpoint出现在多个桶中.
下面的图表是直方图的另一种显示方式: 使用5个桶且列有23个值这意味着除了最后一个桶只有3个值以外其它每一个桶都有5个值.实际上这是oracle在数据字典视图中存储高度平衡直方图信息的方式.因为bucket 1和2都使用3作为一个endpoint,oracle为了节省空间将不会存储bucket 1.所以当桶被合并时只会存储单个条目. 下面我们来对列skew创建一个高度平衡直方图,这一次让桶数小于列的不同值的个数11:
SQL> select column_name,endpoint_number,endpoint_value from 2 user_tab_histograms where table_name='T1' and column_name='SKEW'; COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE --------------- -------------- -------------- SKEW 0 1 SKEW 5 10000
这里buckets 1到5都是用10000作为它的endpoint所以bucket 1到4为了节省空间没有被存储.下面的查询能用来显示桶数和它的endpoinit值
SQL> SELECT bucket_number, max(skew) AS endpoint_value 2 FROM ( 3 SELECT skew, ntile(5) OVER (ORDER BY skew) AS bucket_number 4 FROM t1) 5 GROUP BY bucket_number 6 ORDER BY bucket_number; BUCKET_NUMBER ENDPOINT_VALUE ------------- -------------- 1 10000 2 10000 3 10000 4 10000 5 10000
这里ntile(5)是一个分析函数,它将一个有序的数据集划分到5个桶中.
所以简而言之,在高度平衡直方图中,数据被划分到不同的桶中除了最后一个桶每一个桶包含相同的数据.每一个桶中的最大值被记录为endpoint_value而第一个桶中的最小值也被记录(bucket 0).endpoint_number代表桶数.一旦数据被记录到桶中将会识别为2种类型的数据:
Non popular values和popular values.
Popular values是哪些作为endpoint value出现多次的值.例如在前面的例子中3是一个popular值,在上面的例子中skew 10000是一个popular value.non popular value是哪些没有作为endpoint values出现或者只作为endpoint values出现一次的值.popular value和non popular value不是固定的它依赖于直方图桶的大小,改变桶的大小会出现不同的popular值.
小结:
列中不同值的个数小于直方图的桶数:当不同值的个数小于桶数时,endpoint_value列包含的是不同值本身,endpoint_number列包含是小于列值的累积行数.(频率直方图)
列中不同值的个数大于直方图的桶数:当不同值的个数大于桶数时,endpoint_number包含的是bucekt id且endpoint_value是显示的每一个桶中的最大值.bucket 0是一个特殊它显示的是列中的最小值(高度平衡直方图).
二． 直方图（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 from user_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
http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10577/d_stats.htm#ARPLS68486
10g DBMS_STATS
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1036461
3.1 DBMS_STATS包的几个常用功能：性能的手机，设定 和删除
性能数据的收集包含这样几个存储过程：
GATHER_DATABASE_STATS Procedures
GATHER_DICTIONARY_STATS Procedure
GATHER_FIXED_OBJECTS_STATS Procedure
GATHER_INDEX_STATS Procedure
GATHER_SCHEMA_STATS Procedures
GATHER_SYSTEM_STATS Procedure
GATHER_TABLE_STATS Procedure
从名字也可以看出各自的作用，这些存储过程用来收集数据库不同级别对象的性能数据，包括：数据库，数据字典，表，索引，SCHEMA的性能等。
3.1.1 GATHER_TABLE_STATS Procedure 存储过程
在10g中, GATHER_TABLE_STATS的参数如下：
DBMS_STATS.GATHER_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER DEFAULT to_estimate_percent_type
(get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
到了11g，对参数做了调整：
DBMS_STATS.GATHER_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER DEFAULT to_estimate_percent_type
(get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
对参数的说明：
Parameter |
Description |
ownname |
Schema of table to analyze |
tabname |
Name of table |
partname |
Name of partition |
estimate_percent |
Percentage of rows to estimate (NULL means compute) The valid range is [0.000001,100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics. This is the default.The default value can be changed using the SET_PARAM Procedure. |
block_sample |
Whether or not to use random block sampling instead of random row sampling. Random block sampling is more efficient, but if the data is not randomly distributed on disk, then the sample values may be somewhat correlated. Only pertinent when doing an estimate statistics. |
method_opt |
Accepts: FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause] FOR COLUMNS [size clause] column|attribute [size_clause] [,column|attribute [size_clause]...] size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}
The default is FOR ALL COLUMNS SIZE AUTO.The default value can be changed using theSET_PARAM Procedure. |
degree |
Degree of parallelism. The default for degree is NULL. The default value can be changed using the SET_PARAM Procedure NULL means use the table default value specified by the DEGREE clause in the CREATE TABLE or ALTER TABLE statement. Use the constant DBMS_STATS.DEFAULT_DEGREE to specify the default value based on the initialization parameters. The AUTO_DEGREE value determines the degree of parallelism automatically. This is either 1 (serial execution) or DEFAULT_DEGREE (the system default value based on number of CPUs and initialization parameters) according to size of the object. |
granularity |
Granularity of statistics to collect (only pertinent if the table is partitioned). 'ALL' - gathers all (subpartition, partition, and global) statistics 'AUTO'- determines the granularity based on the partitioning type. This is the default value. 'DEFAULT' - gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality. Note that the default value is now 'AUTO'. 'GLOBAL' - gathers global statistics 'GLOBAL AND PARTITION' - gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object. 'PARTITION '- gathers partition-level statistics 'SUBPARTITION' - gathers subpartition-level statistics. |
cascade |
Gather statistics on the indexes for this table. Index statistics gathering is not parallelized. Using this option is equivalent to running the GATHER_INDEX_STATS Procedure on each of the table's indexes. Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle determine whether index statistics to be collected or not. This is the default. The default value can be changed using theSET_PARAM Procedure. |
stattab |
User statistics table identifier describing where to save the current statistics |
statid |
Identifier (optional) to associate with these statistics within stattab |
statown |
Schema containing stattab (if different than ownname) |
no_invalidate |
Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure. |
force |
Gather statistics of table even if it is locked |
在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.1.2 GATHER_SCHEMA_STATS 存储过程
这个存储过程用于对某个用户下所有的对象进行分析。如果你的数据用户对象非常多，单独对每个对象进行分析设定会非常不方便，这个存储过程就很方便。 它的好处在于如果需要分析的对象非常多，将可以大大降低DBA的工作量，不足之处是所有分析使用相同的分析策略，可能会导致分析不是最优。 所以要根据实际情况来决定。
该存储过程参数如下：
DBMS_STATS.GATHER_SCHEMA_STATS (
ownname VARCHAR2,
estimate_percent NUMBER DEFAULT to_estimate_percent_type
(get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
options VARCHAR2 DEFAULT 'GATHER',
objlist OUT ObjectTab,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE,
obj_filter_list ObjectTab DEFAULT NULL);
参数说明如下：
Parameter |
Description |
ownname |
Schema to analyze (NULL means current schema) |
estimate_percent |
Percentage of rows to estimate (NULL means compute): The valid range is [0.000001,100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics. This is the default.The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure andSET_TABLE_PREFS Procedure. |
block_sample |
Whether or not to use random block sampling instead of random row sampling. Random block sampling is more efficient, but if the data is not randomly distributed on disk, then the sample values may be somewhat correlated. Only pertinent when doing an estimate statistics. |
method_opt |
Accepts: FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause] size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO |SKEWONLY}
The default is FOR ALL COLUMNS SIZE AUTO.The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure,SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure. |
degree |
Degree of parallelism. The default for degree is NULL. The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure. NULLmeans use the table default value specified by the DEGREE clause in theCREATE TABLE or ALTER TABLE statement. Use the constantDBMS_STATS.DEFAULT_DEGREE to specify the default value based on the initialization parameters.The AUTO_DEGREE value determines the degree of parallelism automatically. This is either 1 (serial execution) or DEFAULT_DEGREE (the system default value based on number of CPUs and initialization parameters) according to size of the object. |
granularity |
Granularity of statistics to collect (only pertinent if the table is partitioned). 'ALL' - gathers all (subpartition, partition, and global) statistics 'AUTO'- determines the granularity based on the partitioning type. This is the default value. 'DEFAULT' - gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality. Note that the default value is now 'AUTO'. 'GLOBAL' - gathers global statistics 'GLOBAL AND PARTITION' - gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object. 'PARTITION '- gathers partition-level statistics 'SUBPARTITION' - gathers subpartition-level statistics. |
cascade |
Gather statistics on the indexes as well. Using this option is equivalent to running the GATHER_INDEX_STATS Procedure on each of the indexes in the schema in addition to gathering table and column statistics. Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle determine whether index statistics to be collected or not. This is the default. The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure. |
stattab |
User statistics table identifier describing where to save the current statistics |
statid |
Identifier (optional) to associate with these statistics within stattab |
options |
Further specification of which objects to gather statistics for: GATHER: Gathers statistics on all objects in the schema. GATHER AUTO: Gathers all necessary statistics automatically. Oracle implicitly determines which objects need new statistics, and determines how to gather those statistics. When GATHER AUTO is specified, the only additional valid parameters are ownname, stattab, statid, objlist andstatown; all other parameter settings are ignored. Returns a list of processed objects. GATHER STALE: Gathers statistics on stale objects as determined by looking at the *_tab_modifications views. Also, return a list of objects found to be stale. GATHER EMPTY: Gathers statistics on objects which currently have no statistics. also, return a list of objects found to have no statistics. LIST AUTO: Returns a list of objects to be processed with GATHER AUTO. LIST STALE: Returns list of stale objects as determined by looking at the *_tab_modifications views. LIST EMPTY: Returns list of objects which currently have no statistics. |
objlist |
List of objects found to be stale or empty |
statown |
Schema containing stattab (if different than ownname) |
no_invalidate |
Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. UseDBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure,SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure. |
force |
Gather statistics on objects even if they are locked |
obj_filter_list |
A list of object filters. When provided, GATHER_SCHEMA_STATS will gather statistics only on objects which satisfy at least one object filter in the list as needed. In a single object filter, we can specify the constraints on the object attributes. The attribute values specified in the object filter are case- insensitive unless double-quoted. Wildcard is allowed in the attribute values. Suppose non-NULL values s1, s2, ... are specified for attributes a1, a2, ... in one object filter. An object o is said to satisfy this object filter if (o.a1 like s1) and (o.a2 like s2) and ... is true. See Applying an Object Filter List. |
3.1.3 DBMS_STATS.GATHER_INDEX_STATS 存储过程
该存储过程用于对索引的分析，如果我们在使用DBMS_STATS.GATHER_TABLES_STATS的分析时设置参数cascade=>true。 那么Oracle会同时执行这个存储过程来对索引进行分析。
存储过程参数：
DBMS_STATS.GATHER_INDEX_STATS (
ownname VARCHAR2,
indname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER DEFAULT to_estimate_percent_type
(GET_PARAM('ESTIMATE_PERCENT')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type
(GET_PARAM('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
Parameter |
Description |
ownname |
Schema of index to analyze |
indname |
Name of index |
partname |
Name of partition |
estimate_percent |
Percentage of rows to estimate (NULL means compute). The valid range is[0.000001,100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics. This is the default.The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure andSET_TABLE_PREFS Procedure. |
stattab |
User statistics table identifier describing where to save the current statistics |
statid |
Identifier (optional) to associate with these statistics within stattab |
statown |
Schema containing stattab (if different than ownname) |
degree |
Degree of parallelism. The default for degree is NULL. The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure. NULLmeans use of table default value that was specified by the DEGREE clause in the CREATE/ALTER INDEX statement. Use the constantDBMS_STATS.DEFAULT_DEGREE for the default value based on the initialization parameters. The AUTO_DEGREE value determines the degree of parallelism automatically. This is either 1 (serial execution) orDEFAULT_DEGREE (the system default value based on number of CPUs and initialization parameters) according to size of the object. |
granularity |
Granularity of statistics to collect (only pertinent if the table is partitioned). 'ALL' - gathers all (subpartition, partition, and global) statistics 'AUTO'- determines the granularity based on the partitioning type. This is the default value. 'DEFAULT' - gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality. Note that the default value is now 'AUTO'. 'GLOBAL' - gathers global statistics 'GLOBAL AND PARTITION' - gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object. 'PARTITION '- gathers partition-level statistics 'SUBPARTITION' - gathers subpartition-level statistics. |
no_invalidate |
Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. UseDBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure,SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure. |
force |
Gather statistics on object even if it is locked |
上面讨论了三个常用的存储过程。 分析对CBO 来说非常重要，如果不能按照自己的系统指定出切合实际的数据分析方案，可能会导致如下问题的发生：
（1） 分析信息不充分导致CBO 产生错误的执行计划，导致SQL执行效率低下。
（2） 过多的分析工具带来系统性能的严重下降。
3.2 DBMS_STATS包管理功能
3.2.1 获取分析数据
GET_COLUMN_STATS Procedures
GET_INDEX_STATS Procedures
GET_SYSTEM_STATS Procedure
GET_TABLE_STATS Procedure
这四个存储过程分别为用户获取字段，索引，表和系统的统计信息。 它的用法是首先定义要获取性能指标的变量，然后使用存储过程将性能指标的值赋给变量，最后将变量的值输出。 如：
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 过程已成功完成。
更多信息参考：
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1036461
3.2.2 设置分析数据
SET_COLUMN_STATS Procedures
SET_INDEX_STATS Procedures
SET_SYSTEM_STATS Procedure
SET_TABLE_STATS Procedure
这几个存储过程允许我们手工地为字段，索引，表和系统性能数据赋值。 它的一个用处是当相应的指标不准确导致执行计划失败时，可以使用这种方法手工地来为这些性能数据赋值。 在极端情况下，这也不失为一个解决问题的方法。
关于这4个存储过程的绝提用法参考 oracle 联机文档：
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1036461
3.2.3 删除分析数据
DELETE_COLUMN_STATS Procedure
DELETE_DATABASE_STATS Procedure
DELETE_DICTIONARY_STATS Procedure
DELETE_FIXED_OBJECTS_STATS Procedure
DELETE_INDEX_STATS Procedure
DELETE_SCHEMA_STATS Procedure
DELETE_SYSTEM_STATS Procedure
DELETE_TABLE_STATS Procedure
当性能数据出现异常导致CBO判断错误时，为了立刻修正这个错误，删除性能数据也是一种补救的方法，比如删除了表的数据，让CBO重新对表做动态采样分析，得到一个正确的结果。
它可以删除字段，数据库，数据字典，基表，索引，表等级别的性能数据。
具体参考oracle 联机文档：
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1036461
3.2.4 保存分析数据
CREATE_STAT_TABLE Procedure
DROP_STAT_TABLE Procedure
可以用这两个存储过程创建一个表，用于存放性能数据，这样有利于对性能数据的管理，也可以删除这个表。
具体参考oracle 联机文档：
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1036461
3.2.5 导入和导出分析数据
EXPORT_COLUMN_STATS Procedure
EXPORT_DATABASE_STATS Procedure
EXPORT_DICTIONARY_STATS Procedure
EXPORT_FIXED_OBJECTS_STATS Procedure
EXPORT_INDEX_STATS Procedure
EXPORT_SCHEMA_STATS Procedure
EXPORT_SYSTEM_STATS Procedure
EXPORT_TABLE_STATS Procedure
IMPORT_COLUMN_STATS Procedure
IMPORT_DATABASE_STATS Procedure
IMPORT_DICTIONARY_STATS Procedure
IMPORT_FIXED_OBJECTS_STATS Procedure
IMPORT_INDEX_STATS Procedure
IMPORT_SCHEMA_STATS Procedure
IMPORT_SYSTEM_STATS Procedure
IMPORT_TABLE_STATS Procedure
这些存储过程可以将已经有的性能指标导入到用户创建好的表中存放，需要时，可以从表中倒回来。
具体参考oracle 联机文档：
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1036461
3.2.6 锁定分析数据
LOCK_SCHEMA_STATS Procedure
LOCK_TABLE_STATS Procedure
UNLOCK_SCHEMA_STATS Procedure
UNLOCK_TABLE_STATS Procedure
The LOCK_* 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.
可能在某些时候，我们觉得当前的统计信息非常好，执行计划很准确，并且表中数据几乎不变化，那么可以使用LOCK_TABLE_STATS Procedure 来锁定表的统计信息，不允许对表做分析或者设定分析数据。 当表的分析数据被锁定之后，相关的所有分析数据，包括表级，列级，直方图，索引的分析数据都将被锁定，不允许被更新。
具体参考oracle 联机文档：
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1036461
3.2.7 分析数据的恢复
RESET_PARAM_DEFAULTS Procedure
RESTORE_DICTIONARY_STATS Procedure
RESTORE_FIXED_OBJECTS_STATS Procedure
RESTORE_SCHEMA_STATS Procedure
RESTORE_SYSTEM_STATS Procedure
RESTORE_TABLE_STATS Procedure
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 the ALTER_STATS_HISTORY_RETENTION Procedure.
比如我们重新分析了表，发现分析的数据导致了CBO选择了错误的执行计划，为了挽救这种局面，可以将统计信息恢复到从前的那个时间点，也就是CBO执行计划正确的时间点，先解决这个问题，再来分析问题的原因。
具体参考oracle 联机文档：
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1036461
DROP TABLE t1;
create table t1 as select * FROM dba_users;
SELECT OBJECT_ID
FROM DBA_OBJECTS
WHERE OWNER = USER
AND OBJECT_NAME = 'T1';
SELECT NAME, INTCOL#
FROM SYS.COL$
WHERE OBJ# = 166666
AND NAME = 'USER_ID';
SELECT OBJ#, INTCOL#, EQUALITY_PREDS
FROM SYS.COL_USAGE$
WHERE OBJ# = 166666;
SELECT COUNT(*) FROM T1 WHERE USER_ID = 5;
SELECT OBJ#, INTCOL#, EQUALITY_PREDS
FROM SYS.COL_USAGE$
WHERE OBJ# = 166666;
SELECT * FROM dba_tab_col_statistics d WHERE d.table_name='T1' AND d.column_name='USER_ID';
EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'LHR',TABNAME => 'T1');
SELECT COUNT(DISTINCT t1.user_id) FROM T1;
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'LHR',TABNAME => 'T1',method_opt => 'FOR COLUMNS SIZE 39 USER_ID');
数据库的版本是11.2.0.3：
请点击此处输入图片描述
创建一个测试表T1：
SQL> create table t1 as select *
from dba_users;
Table created
从如下查询结果中我们可以看到，表T1的OBJECT_ID是104192：
SQL> select object_id from dba_objects
where owner=’SCOTT’
and object_name=’T1′;
OBJECT_ID
——————-
104192
表T1的列user_id所对应的INTCOL#是2：
SQL> select name,intcol# from sys.col$
where obj#=104192
and name=’USER_ID’;
NAME INTCOL#
—————— ———-
USER_ID 2
从如下结果里可以看到，SYS.COL_USAGE$现在还没有列USER_ID的使用记录：
SQL> select obj#,intcol#,
equality_preds
from sys.col_usage$
where obj#=104192;
OBJ# INTCOL# EQUALITY_PREDS
我们现在来使用一下列USER_ID：
SQL> select count(*) from t1
where user_id=5;
COUNT(*)
—————–
1
使用完后，我们发现SYS.COL_USAGE$还是没有列USER_ID的使用记录：
SQL> select obj#,intcol#,
equality_preds
from sys.col_usage$
where obj#=104192;
OBJ# INTCOL# EQUALITY_PREDS
———- ———- ————–
这个是正常的，这里不是没有列USER_ID的使用记录，是已经有了但只是还没有被持久化到SYS.COL_USAGE$中，这里需要我们手工执行一下dbms_stats.gather_table_stats，这样就能将USER_ID的使用记录flush到SYS.COL_USAGE$中了，然后我们就能看到了：
请点击此处输入图片描述
但现在的问题是无论我们怎么执行dbms_stats.gather_table_stats，列user_id上的直方图统计信息就是没有（这也是那位朋友问的问题）：
请点击此处输入图片描述
请点击此处输入图片描述
这里除非我们手工指定user_id列所用的bucket的数量：
请点击此处输入图片描述
手工指定了直方图统计信息的bucket的数量为39后，明明列user_id的distinct值的数量也是39，为什么这里直方图的类型居然是HEIGHT BALANCED？按道理讲应该是FREQUENCY啊！
当看到上述测试结果的时候，我意识到一定是什么地方出了问题。
因为上述现象的出现已经颠覆了我之前对直方图统计信息的如下两个认识：
1、我原先一直以为如果METHOD_OPT的值是默认的“FOR ALL COLUMNS SIZE AUTO”的话，那么只要SYS.COL_USAGE$中有目标列的使用记录，则Oracle在自动收集直方图统计信息的时候就会去收集该列的直方图统计信息；
2、在手工收集直方图统计信息的时候，如果我手工指定的bucket的数量等于目标列的distinct值的数量，且这个值是小于等于254的话，那么Oracle此时收集的直方图统计信息的类型应该是FREQUENCY。
请点击此处输入图片描述
到底是什么地方出了问题？
我们来复习一下Oracle关于自动收集直方图统计信息的定义：
Oracle在“SIZE Clause in METHOD_OPT Parameter of DBMS_STATS Package (Doc ID 338926.1)”中明确指出，METHOD_OPT的值中的AUTO的含义为如下所示：
AUTO: Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
这里的“workload of the columns”指的应该就是目标列是否在SYS.COL_USAGE$中有使用记录。
注意到Oracle这里还提到了另外一个条件——“based on data distribution”（这也是我之前没有注意到的条件），但这里的具体含义是什么？
“based on data distribution”直译过来就是目标列数据的分布。说白了就是目标列的数据分布确实得是倾斜的，只有满足这个前提条件，再加上该目标列在SYS.COL_USAGE$中有使用记录，Oracle在自动收集直方图统计信息的时候才会对该列收集直方图统计信息。
Oracle是怎么来判断某列的数据分布是否是倾斜的呢？
Oracle采用了一种很简单的方法
就是判断目标列的distinct值的数量是否和目标表的数据量相同，如果相同，Oracle就认为该列的数据分布不是倾斜的，否则就是倾斜的。
如果目标列的distinct值的数量和目标表的数据量相同，即使该目标列在SYS.COL_USAGE$中有使用记录，Oracle在自动收集直方图统计信息的时候也不会对该列收集直方图统计信息。
搞清楚了上述知识点，那位朋友问的问题自然就有答案了——对于表T1的列user_id而言，其distinct值的数量和表T1的数据量相同，所以这里即使user_id在SYS.COL_USAGE$中有使用记录，Oracle在自动收集直方图统计信息的时候也不会对user_id收集直方图统计信息：
请点击此处输入图片描述
现在我们来验证一下上述理论，往表T1中插入一条记录，使得user_id的distinct值的数量小于表T1的数据量，这样当我们再次对表T1收集统计信息的时候，user_id列的直方图统计信息应该就有了。
先把之前对user_id列手工指定bucket数量收集的直方图统计信息删掉：
请点击此处输入图片描述
对表T1插入一条user_id列的值和现有值重复的记录：
SQL> insert into t1 select *
from t1 where user_id=5;
1 row inserted
SQL> commit;
Commit complete
现在user_id列的distinct值的数量已经小于表T1的数据量了：
请点击此处输入图片描述
此时对表T1再次收集统计信息：
SQL> exec dbms_stats.
gather_table_stats
(ownname=>’SCOTT’,
tabname=>’T1′,estimate_percent=>100);
PL/SQL procedure successfully completed
从如下查询结果里我们可以看到，现在user_id列上终于有了直方图统计信息，且其类型就是FREQUENCY，这就和我们以前的认知匹配上了，同时也验证了我们刚才的分析结论：
请点击此处输入图片描述
再次删除user_id列上的直方图统计信息：
请点击此处输入图片描述
我们再次以手工指定bucket数量的方式收集user_id列上的直方图统计信息：
SQL> exec dbms_stats.gather_table_stats
(ownname=>’SCOTT’,tabname=>’T1′,
method_opt=>’for columns size 39 USER_ID‘,estimate_percent=>100);
PL/SQL procedure successfully completed
从如下查询结果我们可以看到，现在user_id列上的直方图统计信息的类型已经不是之前的HEIGHT BALANCED了，而是变成了FREQUENCY：
请点击此处输入图片描述
这说明我们之前的认识（在手工收集直方图统计信息的时候，如果我手工指定的bucket的数量等于目标列的distinct值的数量，且这个值是小于等于254的话，那么Oracle此时收集的直方图统计信息的类型应该是FREQUENCY）成立的前提条件是该列的数据分布是倾斜的。
总结
通过这篇文章，我们介绍了如下两个关于直方图统计信息的有趣知识点：
1、如果目标列的distinct值的数量和目标表的数据量相同，即使该目标列在SYS.COL_USAGE$中有使用记录，Oracle在自动收集直方图统计信息的时候也不会对该列收集直方图统计信息；
2、在手工收集直方图统计信息的时候，如果我手工指定的bucket的数量等于目标列的distinct值的数量，且这个值是小于等于254的话，那么Oracle此时收集的直方图统计信息的类型应该是FREQUENCY——这个结论成立的前提条件是该列的数据分布是倾斜的。