1. 直方图有两种类别,等频直方图与等高直方图。
默认的,如果一个倾斜列上的唯一值超过了254个,那么ORACLE会对此列建立等高直方图,否则建立等频直方图。
何谓倾斜列(或者说是列值偏差较大):例如一个表TAB有10000行记录,列A前10行的值分别1-10,
剩下的9900行值全部都为999,则该列称为倾斜列,意思就是列的值分布不均匀。
2. 使用场景:通常情况下,如果TAB的A列上建有普通索引,在A列上进行等值查询的时候,都是索引范围扫描。
这时候,如果等值查询条件为A = '999',索引范围扫描花费的成本要远远大于全表扫描的成本,
那么我们想要的情况是:当等值查询条件为A = '999'时候走全表扫描,其他条件如A='1'时候走索引,
直方图正是为了解决这个问题的。我们可以在TAB的A列上建立直方图,那么优化器就会选择我们期望的最低成本
的路径执行。
3. 语法,
begin
dbms_stats.gather_ table _stats(
ownname => '',
tabname =>'',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto',
cascade =>true,
degree => 7
);
end;
其中degree指定了并行度视主机的CPU个数而定,estimate_percent指定了采样比率,此处使用了auto目的是让oracle来决定采样收集的比率,绘制直方图时会根据采样的数据分析结果来绘制,当然也可以人为指定采样比率。如:estimate_percent=>20指定采样比率为20%,cascade=>true指定收集相关表的索引的统计信息,该参数默认为false,因此使用dbms_stats收集统计信息时抹人事部收集表的索引信息的。
method_opt: size 1不搜集,size 2~255会搜集,size auto自动判断,size skewonly只要数据倾斜则搜集
示例如下(转自itpub)
4. 问题是:如果使用位图索引,是否可以解决同样的问题呢?
ORACLE的直方图的一些试验
直方图有两种类别,等频直方图与等高直方图。
默认的,如果一个倾斜列上的唯一值超过了254个,那么ORACLE会对此列建立等高直方图,否则建立等频直方图。
通过如下方式,建立表TAB,更新字段B,让列B产生倾斜。并在B列上创建索引。
SQL> spool d:\hist.txt
SQL> create table tab (a number, b number);
表已创建。
SQL>
SQL> begin
2 for i in 1..10000 loop
3 insert into tab values (i, i);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> update tab set b=5 where b between 6 and 9995;
已更新9990行。
SQL> commit;
提交完成。
SQL> create index ix_tab_b on tab(b);
索引已创建。
然后分析表,强制使列B不产生直方图。
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT',
TABNAME => 'TAB',
CASCADE => TRUE,
METHOD_OPT => 'FOR COLUMNS B SIZE 1 ');
END;
查看视图USER_TAB_HISTOGRAMS,列B上只有最大值,最小值两条记录分别对应端点号(endpoint_number)0和1,这种显示说明列B没有直方图信息。
SQL>SELECT table_name,column_name,endpoint_number,endpoint_value FROM USER_TAB_HISTOGRAMS WHERE TABLE_NAME='TAB' ;
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------------------------------------- --------------- --------------
TAB B 0 1
TAB B 1 10000
在没有直方图的情况下,在B列上进行等值查询的时候,都是索引范围扫描。
SQL> select * from tab where b=1;
执行计划
----------------------------------------------------------
Plan hash value: 439197569
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 6000 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB | 1000 | 6000 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_TAB_B | 1000 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
SQL> select * from tab where b=5;
已选择9991行。
执行计划
----------------------------------------------------------
Plan hash value: 439197569
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 6000 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB | 1000 | 6000 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_TAB_B | 1000 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
收集直方图信息。看看是什么效果。由于列B唯一值的个数没有超过254因此产生的是等频直方图。
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT',
TABNAME => 'TAB',
CASCADE => TRUE,
METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO ');
END;
在B=1时候采用索引扫描,而B=5时候,已经采用全表扫描了,说明直方图起了作用。
SQL> select * from tab where b=1;
执行计划
----------------------------------------------------------
Plan hash value: 439197569
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB | 1 | 6 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_TAB_B | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
SQL> select * from tab where b=5;
已选择9991行。
执行计划
----------------------------------------------------------
Plan hash value: 1995730731
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9991 | 59946 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TAB | 9991 | 59946 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
查看此时的直方图信息:
SQL>SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE FROM USER_TAB_HISTOGRAMS
WHERE TABLE_NAME = 'TAB';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------------------------------------- --------------- --------------
TAB B 1 1
TAB B 2 2
TAB B 3 3
TAB B 4 4
TAB B 9995 5
TAB B 9996 9996
TAB B 9997 9997
TAB B 9998 9998
TAB B 9999 9999
TAB B 10000 10000
其中EDNPOINT_NUMBER是累计值。EDNPOINT_VALUE是列的值。可以看出这种等频直方图统计的 列的信息是非常精确的。它为每一个列值分配了一个桶。从执行计划的ROWS部分也可以看出ORACLE计算出来的cardinality是9991,和实 际的情况完全吻合。
如果想知道每一个列值对应的数量是多少,需要做一下简单的减法运算:
假如想知道列值等于5的个数,那么可以通过:
9995-4=9991得到。这就是ENDPOINT_NUMBER累计值的含义。
在看看等高直方图的情况。
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT',
TABNAME => 'TAB',
CASCADE => TRUE,
METHOD_OPT => 'FOR COLUMNS B SIZE 8 ');
END;
由于列B有10个唯一值,通过上面的size 8可以强制ORACLE使用等高直方图。
查看直方图信息.
SQL>SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE FROM USER_TAB_HISTOGRAMS
WHERE TABLE_NAME = 'TAB';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------------------------------------- --------------- --------------
TAB B 0 1
TAB B 7 5
TAB B 8 10000
从查询结果惊奇的发现只有三个桶0 7 8,原来ORACLE会自动省去EDNPOINT_VALUE值相同且ENDPOINT_NUMBER相邻的桶的值。
省去了桶(EDNPOINT_NUMBER)为1 2 3 4 5 6 ,EDNPOINT_VALUE为5的六条内容。
说明:在等高直方图中,EDNPOINT_NUMBER代表桶号,这一点与等频直方图不同。
再看等高直方图下的执行计划:
SQL> select * from tab where b=5;
已选择9991行。
执行计划
----------------------------------------------------------
Plan hash value: 1995730731
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9982 | 59892 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TAB | 9982 | 59892 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
有没有发现什么?
执行计划的ROWS部分,ORACLE计算出来的cardinality不是特别精确的。9991才是精确值。而等频直方图可以精确到9991,因此可以说等频直方图比等高直方图稳定,精确。
可是现实很多时候,列的唯一值是超过254的。只能使用等高直方图了。
默认的,如果一个倾斜列上的唯一值超过了254个,那么ORACLE会对此列建立等高直方图,否则建立等频直方图。
何谓倾斜列(或者说是列值偏差较大):例如一个表TAB有10000行记录,列A前10行的值分别1-10,
剩下的9900行值全部都为999,则该列称为倾斜列,意思就是列的值分布不均匀。
2. 使用场景:通常情况下,如果TAB的A列上建有普通索引,在A列上进行等值查询的时候,都是索引范围扫描。
这时候,如果等值查询条件为A = '999',索引范围扫描花费的成本要远远大于全表扫描的成本,
那么我们想要的情况是:当等值查询条件为A = '999'时候走全表扫描,其他条件如A='1'时候走索引,
直方图正是为了解决这个问题的。我们可以在TAB的A列上建立直方图,那么优化器就会选择我们期望的最低成本
的路径执行。
3. 语法,
begin
dbms_stats.gather_ table _stats(
ownname => '',
tabname =>'',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto',
cascade =>true,
degree => 7
);
end;
其中degree指定了并行度视主机的CPU个数而定,estimate_percent指定了采样比率,此处使用了auto目的是让oracle来决定采样收集的比率,绘制直方图时会根据采样的数据分析结果来绘制,当然也可以人为指定采样比率。如:estimate_percent=>20指定采样比率为20%,cascade=>true指定收集相关表的索引的统计信息,该参数默认为false,因此使用dbms_stats收集统计信息时抹人事部收集表的索引信息的。
method_opt: size 1不搜集,size 2~255会搜集,size auto自动判断,size skewonly只要数据倾斜则搜集
示例如下(转自itpub)
4. 问题是:如果使用位图索引,是否可以解决同样的问题呢?
ORACLE的直方图的一些试验
直方图有两种类别,等频直方图与等高直方图。
默认的,如果一个倾斜列上的唯一值超过了254个,那么ORACLE会对此列建立等高直方图,否则建立等频直方图。
通过如下方式,建立表TAB,更新字段B,让列B产生倾斜。并在B列上创建索引。
SQL> spool d:\hist.txt
SQL> create table tab (a number, b number);
表已创建。
SQL>
SQL> begin
2 for i in 1..10000 loop
3 insert into tab values (i, i);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> update tab set b=5 where b between 6 and 9995;
已更新9990行。
SQL> commit;
提交完成。
SQL> create index ix_tab_b on tab(b);
索引已创建。
然后分析表,强制使列B不产生直方图。
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT',
TABNAME => 'TAB',
CASCADE => TRUE,
METHOD_OPT => 'FOR COLUMNS B SIZE 1 ');
END;
查看视图USER_TAB_HISTOGRAMS,列B上只有最大值,最小值两条记录分别对应端点号(endpoint_number)0和1,这种显示说明列B没有直方图信息。
SQL>SELECT table_name,column_name,endpoint_number,endpoint_value FROM USER_TAB_HISTOGRAMS WHERE TABLE_NAME='TAB' ;
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------------------------------------- --------------- --------------
TAB B 0 1
TAB B 1 10000
在没有直方图的情况下,在B列上进行等值查询的时候,都是索引范围扫描。
SQL> select * from tab where b=1;
执行计划
----------------------------------------------------------
Plan hash value: 439197569
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 6000 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB | 1000 | 6000 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_TAB_B | 1000 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
SQL> select * from tab where b=5;
已选择9991行。
执行计划
----------------------------------------------------------
Plan hash value: 439197569
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 6000 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB | 1000 | 6000 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_TAB_B | 1000 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
收集直方图信息。看看是什么效果。由于列B唯一值的个数没有超过254因此产生的是等频直方图。
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT',
TABNAME => 'TAB',
CASCADE => TRUE,
METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO ');
END;
在B=1时候采用索引扫描,而B=5时候,已经采用全表扫描了,说明直方图起了作用。
SQL> select * from tab where b=1;
执行计划
----------------------------------------------------------
Plan hash value: 439197569
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB | 1 | 6 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_TAB_B | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
SQL> select * from tab where b=5;
已选择9991行。
执行计划
----------------------------------------------------------
Plan hash value: 1995730731
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9991 | 59946 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TAB | 9991 | 59946 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
查看此时的直方图信息:
SQL>SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE FROM USER_TAB_HISTOGRAMS
WHERE TABLE_NAME = 'TAB';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------------------------------------- --------------- --------------
TAB B 1 1
TAB B 2 2
TAB B 3 3
TAB B 4 4
TAB B 9995 5
TAB B 9996 9996
TAB B 9997 9997
TAB B 9998 9998
TAB B 9999 9999
TAB B 10000 10000
其中EDNPOINT_NUMBER是累计值。EDNPOINT_VALUE是列的值。可以看出这种等频直方图统计的 列的信息是非常精确的。它为每一个列值分配了一个桶。从执行计划的ROWS部分也可以看出ORACLE计算出来的cardinality是9991,和实 际的情况完全吻合。
如果想知道每一个列值对应的数量是多少,需要做一下简单的减法运算:
假如想知道列值等于5的个数,那么可以通过:
9995-4=9991得到。这就是ENDPOINT_NUMBER累计值的含义。
在看看等高直方图的情况。
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT',
TABNAME => 'TAB',
CASCADE => TRUE,
METHOD_OPT => 'FOR COLUMNS B SIZE 8 ');
END;
由于列B有10个唯一值,通过上面的size 8可以强制ORACLE使用等高直方图。
查看直方图信息.
SQL>SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE FROM USER_TAB_HISTOGRAMS
WHERE TABLE_NAME = 'TAB';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------------------------------------- --------------- --------------
TAB B 0 1
TAB B 7 5
TAB B 8 10000
从查询结果惊奇的发现只有三个桶0 7 8,原来ORACLE会自动省去EDNPOINT_VALUE值相同且ENDPOINT_NUMBER相邻的桶的值。
省去了桶(EDNPOINT_NUMBER)为1 2 3 4 5 6 ,EDNPOINT_VALUE为5的六条内容。
说明:在等高直方图中,EDNPOINT_NUMBER代表桶号,这一点与等频直方图不同。
再看等高直方图下的执行计划:
SQL> select * from tab where b=5;
已选择9991行。
执行计划
----------------------------------------------------------
Plan hash value: 1995730731
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9982 | 59892 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TAB | 9982 | 59892 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
有没有发现什么?
执行计划的ROWS部分,ORACLE计算出来的cardinality不是特别精确的。9991才是精确值。而等频直方图可以精确到9991,因此可以说等频直方图比等高直方图稳定,精确。
可是现实很多时候,列的唯一值是超过254的。只能使用等高直方图了。