【DB笔试面试636】在Oracle中，如何收集直方图信息？在收集直方图时有哪些注意事项？

• 2019 年 10 月 10 日
• 笔记

题目部分

在Oracle中，如何收集直方图信息？在收集直方图时有哪些注意事项？

♣

答案部分

默认情况下，数据库会为列收集基本统计信息，但不会收集直方图信息。Oracle通过指定DBMS_STATS的METHOD_OPT参数来创建直方图。METHOD_OPT参数可以接受如下的输入值：

FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]  FOR COLUMNS [size_clause] column|attribute [size_clause] [,column|attribute [size_clause]...]  

其中的size_clause必须符合如下的格式：

SIZE {integer | REPEAT | AUTO | SKEWONLY}  

含义如下所示：

l SKEWONLY：只对数据分布不均衡的列收集直方图统计信息。

l REPEAT：只对己经有直方图统计信息的列收集直方图统计信息。

l AUTO：让Oracle自行决定是否对目标列收集直方图统计信息，以及使用哪种类型的直方图。

l integer：直方图的Bucket的数量，必须是在1~254的范围内，1表示删除该目标列上的直方图统计信息。

METHOD_OPT参数的默认值为“FOR ALL COLUMNS SIZE AUTO”，“FOR ALL COLUMNS SIZE 1”表示删除所有列直方图统计信息。下面是一些常用的收集方法：

l 对T表上所有有索引的列以自动收集的方式收集直方图：FOR ALL INDEXED COLUMNS SIZE AUTO

l 对T表上的列A和列B以自动收集的方式收集直方图：FOR COLUMNS SIZE AUTO A B

l 对T表上的列A和列B收集直方图统计信息，同时指定BUCKET数量均为10：FOR COLUMNS SIZE 10 A B

l 对T表上的列A和列B收集直方图统计信息，同时指定列A的BUCKET数量为10，列B的BUCKET数量为5：FOR COLUMNS A SIZE 10 B SIZE 5

l 只删除表T上列A的直方图统计信息：FOR COLUMNS A SIZE 1

l 删除表T上所有列的直方图统计信息：FOR ALL COLUMNS SIZE 1

如果需要删除某个列SAL的直方图信息，在Oracle 10g中可以通过设置“METHOD_OPT=>'FOR COLUMNS SAL SIZE 1'”，但这却得再次收集表的统计信息，十分不合理，所以，在Oracle 11g中，有如下方法可以直接删除直方图信息：

EXEC DBMS_STATS.DELETE_COLUMN_STATS(USER,'T_ST_20170604_LHR','SAL',COL_STAT_TYPE => 'HISTOGRAM');  

其中，COL_STAT_TYPE默认为ALL，表示删除列的基本统计信息和直方图信息。

SKEWONLY的示例如下：

BEGIN    DBMS_STATS.GATHER_TABLE_STATS(OWNNAME           => '',                                  TABNAME          => '',                                  ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,                                  METHOD_OPT       => 'for all columns size skewonly',                                  CASCADE          => TRUE,                                  DEGREE           => 7);  END;  

其中，DEGREE指定了并行度视主机的CPU个数而定，ESTIMATE_PERCENT指定了采样比率，此处使用了AUTO目的是让Oracle来决定采样收集的比率，绘制直方图时会根据采样的数据分析结果来绘制，当然也可以人为指定采样比率。如：ESTIMATE_PERCENT=>20指定采样比率为20%，CASCADE=>TRUE指定收集相关表的索引的统计信息，该参数默认为FALSE，因此使用DBMS_STATS收集统计信息时默认是不收集表的索引的统计信息的。

关于直方图的一些注意事项：

①　对于超过32个字符的字符型列，超出的那一部分无法在直方图中体现，这种先天性的缺陷会直接影响CBO对相关文本型字段的可选择率及返回结果集的Cardinality的评估。

②　数字和日期在直方图上被精确表示。

③　如果目标列的数据是均匀分布的（例如，主键列、唯一索引列），那么就不需要对这些列收集直方图统计信息。

④　对于那些从来没有在WHERE条件中出现过的列，无论其数据分布是否均匀，都无须对这些列收集直方图统计信息。

⑤　如果某个列从未在WHERE条件中出现过，那么SYS.COL_USAGE$中就不会有这个列的使用记录，那么Oracle在以默认方式自动收集统计信息时就不会收集这个列的直方图统计信息。

⑥　如果目标列的DISTINCT值的数量和目标表的记录数据量相同，即使该目标列在SYS.COL_USAGE$中有使用记录，那么Oracle在自动收集直方图统计信息的时候也不会对该列收集直方图统计信息。

⑦　Oracle是怎么来判断某列的数据分布是否是倾斜的呢？Oracle采用了一种很简单的方法来判断某列是否倾斜，就是判断目标列的DISTINCT值的数量是否和目标表的记录数量相同，如果相同，那么Oracle就认为该列的数据分布不是倾斜的，否则就是倾斜的。

⑧　如果目标列的DISTINCT值的数量和目标表的数据量相同（即数据分布不倾斜），即使该目标列在SYS.COL_USAGE$中有使用记录，那么Oracle在自动收集直方图统计信息的时候也不会对该列收集直方图统计信息。

⑨　“在手工收集直方图统计信息的时候，如果手工指定的Bucket的数量等于目标列的DISTINCT值的数量，且这个值是小于等于254的话，那么Oracle此时收集的直方图统计信息的类型应该是FREQUENCY”——这个结论成立的前提条件是该列的数据分布是倾斜的。

本文选自《Oracle程序员面试笔试宝典》，作者：小麦苗