Oracle 优化统计数据之直方图(histograms)
直方图是一种按数据出现的频率来进行分类存储的方法.在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是一个特殊它显示的是列中的最小值(高度平衡直方图).