ITPub博客

首页 > 数据库 > Oracle > 直方图使用技巧及analyze table操作对直方图统计的影响

直方图使用技巧及analyze table操作对直方图统计的影响

Oracle 作者:ericwen2015 时间:2017-08-25 17:37:38 0 删除 编辑

原文:http://www.cnblogs.com/yumiko/p/6060485.html

前言

  • 针对索引列,尤其是存在严重数据倾斜的索引列,直方图的统计信息,对于CBO优化器更准确地选择执行计划至关重要。
  • 对于初心者,可以从这篇文章中,了解到直方图对于索引列的价值、作用,以及使用技巧。
  • 对于经验者,同样可以从文章中,了解到不同的analyze table操作,对于直方图信息统计的影响。该部分也可以直接查阅本篇文章最后的总结篇。
  • 先强调一句:analyze table table_name compute statistics这个操作要谨慎


1、直方图概述

  • 直方图作为一种计量数据分布的统计工具,并非ORACLE专有。
  • 对于ORACLE而言,直方图主要用于在分析表以及索引时,统计相关列上的数据,记录该列整体的数据分布情况。


2、直方图的分类

  • ORACLE的直方图主要有两种,等频直方图以及等高直方图
  • 默认情况下,当列上的唯一值数量低于254个,ORACLE会建立等频直方图。
  • 默认情况下,当列上的唯一值数量高于254个,ORACLE会建立等高直方图。
  • 可以在执行dbms_stats.gather_table_stats收集统计信息时,通过method_opt参数,设置SIZE低于目标列的唯一值数量,从而使用等高直方图。

3、直方图的优势

对于ORACLE而言,CBO优化器可以根据直方图收集的列值分布信息,让选择性高(返回数据行比例少)的列值使用索引,而选择性低(返回数据行比例多)的列值不使用索引。尤其对于存在数据倾斜严重的列而言,直方图很重要。

注:数据倾斜,主要指某列上的一个数值,相较于该列其他数值,出现比例高,如:“性别”列,“男性”占到该列整体数值(男性、女性)的80%,存在明显的数据倾斜现象。


4、直方图适用范围 

一般而言,直方图不受是否使用索引的限制,即可以用来统计索引列,也可以统计非索引列。但对于非索引列的统计,意义不大。


5、直方图涉及的主要视图

直方图类型的视图:DBA_TAB_COL_STATISTICS,USER_TAB_COL_STATISTICS,ALL_TAB_COL_STATISTICS

直方图具体信息的视图:DBA_TAB_HISTOGRAMS,USER_TAB_HISTOGRAMS,ALL_TAB_HISTOGRAMS


6、直方图对于执行计划选择影响的示例说明

本示例中使用的数据库版本为ORACLE 11.2.0.4。


首先,准备一张测试表TEST,其中OWNER列存在严重的数据倾斜,具体如下。
 Yumiko_sunny@OA01> select distinct owner ,count(*) as col_rows,
 (select count(*) from test) as tab_rows,
 to_char(round(count(*)/(select count(*) from test)*100,2),'90.99')||'%'
 as data_ratio
 from test group by owner;

 OWNER             COL_ROWS   TAB_ROWS  DATA_RATIO
 -------------------- --------------- ---------------- ----------
 HR                    476             535164         0.09%
 OE                    1988           535164         0.37%
 ORDDATA          3598           535164         0.67%
 SCOTT               98              535164         0.02%
 SYS                   529004       535164         98.85%

从上图中可以看到,该列的SYS值分布占到了整体的98%,表明存在严重的倾斜。


为OWNER列创建索引,并使用ANALYZE TABLE的方法收集统计信息。

--收集统计信息
Yumiko_sunny@OA01> analyze table test compute statistics;
Table analyzed.

--验证最后的统计收集的时间
Yumiko_sunny@OA01> select table_name,
 to_char(LAST_ANALYZED,'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZED 
 from dba_tables where TABLE_NAME='TEST';

TABLE_NAME                     LAST_ANALYZED
------------------------------ -------------------
TEST                           2016-11-13 21:23:19

--查看直方图的统计情况
Yumiko_sunny@OA01>  select column_name,histogram from dba_tab_col_statistics where table_name='TEST';

COLUMN_NAME          HISTOGRAM
-------------------- ---------------
OWNER                NONE
OBJECT_NAME          NONE
SUBOBJECT_NAME       NONE
OBJECT_ID            NONE
DATA_OBJECT_ID       NONE
OBJECT_TYPE          NONE
CREATED              NONE
LAST_DDL_TIME        NONE
TIMESTAMP            NONE
STATUS               NONE
TEMPORARY            NONE

COLUMN_NAME          HISTOGRAM
-------------------- ---------------
GENERATED            NONE
SECONDARY            NONE
NAMESPACE            NONE
EDITION_NAME         NONE
在上面的方法中,虽然通过analyze table table_name compute statistics的方法,收集了表的统计信息,但并未收集直方图的信息

这里先忽略,后面可以对比for all columns子句的情况再看下


查看此时索引列执行计划的选择情况,这里以倾斜数据SYS为条件进行检索。

Yumiko_sunny@OA01> select * from test where owner='SYS';
rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3856466897
----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |   107K|    10M|  1799   (1)| 00:00:22 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |   107K|    10M|  1799   (1)| 00:00:22 |
|*  2 |   INDEX RANGE SCAN          | IND_TEST |   107K|       |   228   (1)| 00:00:03 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
- access("OWNER"='SYS')

从上面返回的结果看,529K行的SYS数据,仅仅返回107K行,显然存在很大的误差。

此外,对于数据倾斜达到98%的SYS而言,显然全表扫描的效率应该更高,这里应该与错误的统计信息有关。


使用dbms_stats.gather_table_stats的方式再次收集表的统计信息。

Yumiko_sunny@OA01> exec dbms_stats.gather_table_stats('SCOTT','TEST',cascade=>true);
PL/SQL procedure successfully completed.

Yumiko_sunny@OA01> select table_name,
 to_char(LAST_ANALYZED,'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZED 
 from dba_tables where TABLE_NAME='TEST';

TABLE_NAME                     LAST_ANALYZED
------------------------------ -------------------
TEST                           2016-11-13 21:50:01

Yumiko_sunny@OA01>  select column_name,histogram from dba_tab_col_statistics where table_name='TEST';

COLUMN_NAME          HISTOGRAM
-------------------- ---------------
OWNER                FREQUENCY
OBJECT_NAME          NONE
SUBOBJECT_NAME       NONE
OBJECT_ID            NONE
DATA_OBJECT_ID       NONE
OBJECT_TYPE          NONE
CREATED              NONE
LAST_DDL_TIME        NONE
TIMESTAMP            NONE
STATUS               NONE
TEMPORARY            NONE

COLUMN_NAME          HISTOGRAM
-------------------- ---------------
GENERATED            NONE
SECONDARY            NONE
NAMESPACE            NONE
EDITION_NAME         NONE

从上图可以看到,此时完成了对表的最新统计,同时收集了索引列的直方图信息,且该直方图为”等频直方图“


再次查看此时索引列的执行计划选择情况,这里分别以选择性差的倾斜数据SYS为条件,以及以选择性好的SCOTT为条件分别进行检索。

--以SYS为条件进行查询
Yumiko_sunny@OA01> select * from test where owner='SYS';
rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   530K|    49M|  2098   (1)| 00:00:26 |
|*  1 |  TABLE ACCESS FULL| TEST |   530K|    49M|  2098   (1)| 00:00:26 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
- filter("OWNER"='SYS')

--以SCOTT为条件进行查询
Yumiko_sunny@OA01> select * from test where owner='SCOTT';
rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3856466897
----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |    98 |  9506 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |    98 |  9506 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_TEST |    98 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
- access("OWNER"='SCOTT')

如之前所说,通过直方图收集准确的数据分布信息,

对于选择性差的SYS值,CBO优化器采用了全表扫描的方式进行数据的访问

对于选择性好的SCOTT值,CBO优化器则采用了索引扫描的方式进行数据的访问


如果采用索引的方式访问SYS相关的数据行,真实的代价会是怎样呢,这里,通过hint的方式进行一次索引扫描的访问

Yumiko_sunny@OA01> select /*+index(test,ind_test) */* from test where owner='SYS';
rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3856466897
----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |   529K|    49M|  8885   (1)| 00:01:47 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |   529K|    49M|  8885   (1)| 00:01:47 |
|*  2 |   INDEX RANGE SCAN          | IND_TEST |   529K|       |  1115   (1)| 00:00:14 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
- access("OWNER"='SYS')

从上图中可以看到,在信息收集无误的情况下,若采用索引扫描,其真实开销是全表扫描的4倍

通过上面这个执行计划,也说明了,对于CBO优化器,准确无误的统计信息对于执行计划选择的重要性


通过DBA_TAB_HISTOGRAMS视图,查看此时直方图的详细信息

Yumiko_sunny@OA01> select TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,
 to_char(ENDPOINT_VALUE,'999999999999999999999999999999999999')
 as ENDPOINT_VALUE,ENDPOINT_ACTUAL_VALUE
 from DBA_TAB_HISTOGRAMS
  where TABLE_NAME='TEST' and COLUMN_NAME='OWNER';

TABLE_NAME  COLUMN_NAME  ENDPOINT_NUMBER ENDPOINT_VALUE             ENDPOINT_ACTUAL_VALU
----------------------------------------------------------------------------------------
TEST        OWNER                     19 41159093808690300000000000
TEST        OWNER                     61 41186001805076000000000000
TEST        OWNER                     62 43232584582496500000000000
TEST        OWNER                      5 37550853140200700000000000
TEST        OWNER                   5518 43277234965060400000000000

可以看到,虽然ENDPOINT_VALUE收集到了唯OWNER列唯一值的hash值,但真实列ENDPOINT_ACTUAL_VALUE显示为空

后面对比analyze table for all columns操作后再看。


删除SYS值相关的数据行,观察直方图统计的变化

Yumiko_sunny@OA01> delete from test where owner='SYS';
rows deleted.

Yumiko_sunny@OA01> commit;
Commit complete.

Yumiko_sunny@OA01> select TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,
 to_char(ENDPOINT_VALUE,'999999999999999999999999999999999999')
 as ENDPOINT_VALUE,ENDPOINT_ACTUAL_VALUE
 from DBA_TAB_HISTOGRAMS
  where TABLE_NAME='TEST' and COLUMN_NAME='OWNER';
TABLE_NAME  COLUMN_NAME  ENDPOINT_NUMBER ENDPOINT_VALUE             ENDPOINT_ACTUAL_VALU
----------------------------------------------------------------------------------------
TEST        OWNER                     19 41159093808690300000000000
TEST        OWNER                     61 41186001805076000000000000
TEST        OWNER                     62 43232584582496500000000000
TEST        OWNER                      5 37550853140200700000000000
TEST        OWNER                   5518 43277234965060400000000000

可以看到,对表数据的DML操作,直方图信息并未自动更改


再次使用dbms_stats.gather_table_stats收集统计信息,此时直方图得到了更新,如下图:

Yumiko_sunny@OA01> exec dbms_stats.gather_table_stats('SCOTT','TEST',cascade=>true);
PL/SQL procedure successfully completed.

Yumiko_sunny@OA01> select TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,
 to_char(ENDPOINT_VALUE,'999999999999999999999999999999999999')
 as ENDPOINT_VALUE,ENDPOINT_ACTUAL_VALUE
 from DBA_TAB_HISTOGRAMS
  where TABLE_NAME='TEST' and COLUMN_NAME='OWNER';
TABLE_NAME  COLUMN_NAME  ENDPOINT_NUMBER ENDPOINT_VALUE              ENDPOINT_ACTUAL_VALU
-------------------- ---------------------------- ---------------------------------------
TEST        OWNER                    476  37550853140200700000000000 
TEST        OWNER                   2464  41159093808690300000000000 
TEST        OWNER                   6062  41186001805076000000000000 
TEST        OWNER                   6160  43232584582496500000000000

上述说明了,对于直方图的信息,需要定期进行收集工作


7、ANALYZE TABLE操作对直方图统计影响的示例说明

本示例承接上面示例内容,数据库版本一致。


在上面内容中,已经演示了analyze table table_name compute statistics无法针对表进行直方图的信息收集。

那么,对于已存在直方图的表,该操作又会有何影响呢。

首先,承接上面内容,再次执行analyze table table_name compute statistics的操作,观察直方图信息的变化。

Yumiko_sunny@OA01> analyze table test compute statistics;
Table analyzed.

Yumiko_sunny@OA01> select column_name,histogram from dba_tab_col_statistics where table_name='TEST';

COLUMN_NAME          HISTOGRAM
-------------------- ---------------
OWNER                NONE
OBJECT_NAME          NONE
SUBOBJECT_NAME       NONE
OBJECT_ID            NONE
DATA_OBJECT_ID       NONE
OBJECT_TYPE          NONE
CREATED              NONE
LAST_DDL_TIME        NONE
TIMESTAMP            NONE
STATUS               NONE
TEMPORARY            NONE

COLUMN_NAME          HISTOGRAM
-------------------- ---------------
GENERATED            NONE
SECONDARY            NONE
NAMESPACE            NONE
EDITION_NAME         NONE

可以明显的发现,OWNER列的直方图信息消失了,说明该操作会删除已存在的直方图信息

如果这是一个生产环境,对于这样一张存在数据倾斜列的表,可能会带来不可预估的影响。


对于analyze table table_name compute statistics for all indexes的操作,这里不再演示,会在下面的总结中,直接给出对直方图影响的结论。


下面看一下analyze table table_name compute statistics for all indexes for all columns操作的影响。

Yumiko_sunny@OA01> analyze table test compute statistics for all indexes for all columns;
Table analyzed.

Yumiko_sunny@OA01> select column_name,histogram from dba_tab_col_statistics where table_name='TEST';

COLUMN_NAME          HISTOGRAM
-------------------- ---------------
OWNER                FREQUENCY
OBJECT_NAME          HEIGHT BALANCED
SUBOBJECT_NAME       NONE
OBJECT_ID            HEIGHT BALANCED
DATA_OBJECT_ID       HEIGHT BALANCED
OBJECT_TYPE          FREQUENCY
CREATED              FREQUENCY
LAST_DDL_TIME        FREQUENCY
TIMESTAMP            FREQUENCY
STATUS               FREQUENCY
TEMPORARY            FREQUENCY

COLUMN_NAME          HISTOGRAM
-------------------- ---------------
GENERATED            FREQUENCY
SECONDARY            FREQUENCY
NAMESPACE            FREQUENCY
EDITION_NAME         NONE

从上面可以看到,当执行for all columns子句的时候,不但收集了索引列的直方图信息,还收集了非索引列的直方图信息


再看下此时DBA_TAB_HISTOGRAMS视图的详细信息

Yumiko_sunny@OA01> select TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,
 to_char(ENDPOINT_VALUE,'999999999999999999999999999999999999')
 as ENDPOINT_VALUE,ENDPOINT_ACTUAL_VALUE
 from DBA_TAB_HISTOGRAMS
 where TABLE_NAME='TEST' and COLUMN_NAME='OWNER';

TABLE_NAME  COLUMN_NAME  ENDPOINT_NUMBER ENDPOINT_VALUE              ENDPOINT_ACTUAL_VALU
-------------------- ---------------------------- ---------------------------------------
TEST        OWNER                    476  37550853140200700000000000 HR
TEST        OWNER                   2464  41159093808690300000000000 OE
TEST        OWNER                   6062  41186001805076000000000000 ORDDATA
TEST        OWNER                   6160  43232584582496500000000000 SCOTT

可以看到,此时可以看见DBA_TAB_HISTOGRAMS视图上,ENDPOINT_ACTUAL_VALUE列真实值的信息。


8、总结

  • 直方图可以为CBO优化器提供准确的数据分布参考,以便选择正确的执行计划。


  • 默认情况下,使用dbms_stats.gather_table_stats得到的直方图信息,只会收集索引列的直方图信息。
  • 默认情况下,使用dbms_stats.gather_table_stats得到的直方图信息,无法在DBA_TAB_HISTOGRAMS视图中看到真实值,但不影响CBO优化器的选择。


  • 使用analyze table table_name compute statistics进行统计分析,对于尚未存在直方图信息的表,不会收集直方图信息。
  • 使用analyze table table_name compute statistics进行统计分析,对于已经存在直方图信息的表,会删除之前的直方图信息。


  • 使用analyze table table_name compute statistics for all indexes进行统计分析,对于尚未存在直方图信息的表,不会收集直方图信息。
  • 使用analyze table table_name compute statistics for all indexes进行统计分析,对于已经存在直方图信息的表,不会删除之前的直方图信息。


  • 使用analyze table table_name compute statistics for all indexes for all columns进行统计分析,对于尚未存在直方图信息的表,会收集直方图信息,且包括索引列以及非索引列。
  • 使用analyze table table_name compute statistics for all indexes for all columns进行统计分析,对于已经存在直方图信息的表,会收集最新的直方图信息。


最后一点,起码从直方图的收集情况看:analyze table table_name compute statistics并不等价于analyze table table_name compute statistics for all indexes for all columns


谨慎使用analyze table table_name compute statistics这个操作。

重要的事情说三遍!!!

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30373825/viewspace-2144171/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2015-07-26

  • 博文量
    41
  • 访问量
    24898