Oracle列直方图的问题隐患 / 开普饭

第一章 Oracle列直方图介绍

众所周知，列的直方图主要用于针对数据倾斜的情况，能帮助数据库更准确的了解数据的分布情况，从而选择更高效的执行计划。

经过测试，直方图也是存在很多问题隐患的。大致包括：

1.默认的收集参数FOR COLUMNS SIZE AUTO导致未使用列无法准确收集直方图；

2.自动采样率AUTO_SAMPLE_SIZE导致对大表的直方图收集信息不完全；

3.过长的列导致直方图记录信息不完整，从而导致预估返回行数不准的问题。

下面分别进行分析及测试：

第二章 FOR COLUMNS SIZE AUTO参数问题

FOR COLUMNS SIZE AUTO参数主要有如下特点：

由Oracle自动根据数据的分布情况，确定是否收集直方图；
根据相关列的使用情况来决定是否收集直方图。

因此，只有当sys.col_usage$视图中记录了相关列的使用情况时，才会根据列上数据的分布情况对该列收集直方图。

查看当前的默认参数：系统采用默认的AUTO参数。

select dbms_stats.get_param('METHOD_OPT') from DUAL;

实验脚本如下：

--创建测试表 CREATE TABLE T1 AS SELECT * FROM DBA_OBJECTS; --查看相应列是否倾斜： SELECT OBJECT_TYPE, COUNT(*) FROM T1 GROUP BY OBJECT_TYPE ORDER BY 2 DESC;

SELECT NAMESPACE, COUNT(*) FROM T1 GROUP BY NAMESPACE ORDER BY 2 DESC

数据倾斜严重。由于col_usage$的内容并不是实时反映相关列的使用情况，所以在对sys.col_usage$查询前，需要执行exec dbms_stats.flush_database_monitoring_info 的语句，以确保相关列上的使用情况信息已经刷出到col_usage$表中。

通过以下视图查询：

select object_id from dba_objects where object_name='T1'; SELECT name,intcol# FROM sys.col$ where obj#='94350'; exec dbms_stats.flush_database_monitoring_info; select obj#,intcol#,equality_preds from sys.col_usage$ where obj#='94350';

2.1 直接AUTO参数收集直方图

在列未使用的情况下使用AUTO参数收集直方图：

exec dbms_stats.gather_table_stats(ownname=>'SZT',tabname=>'T1',METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO');

查询列的统计信息：

col owner for a20 col table_name for a25 col column_name for a20 col HISTOGRAM for a15 col LOW_VALUE for a15 col HIGH_VALUE for a15 SELECT OWNER,TABLE_NAME,COLUMN_NAME,NUM_DISTINCT ,HISTOGRAM,LAST_ANALYZED FROM DBA_TAB_COL_STATISTICS WHERE TABLE_NAME in ('T1') and OWNER = upper('SZT') and column_name in ('OBJECT_TYPE','NAMESPACE');

可以看到，相应列上均没有收集直方图。

2.2 使用列后收集直方图

SELECT count(*) FROM T1 WHERE OBJECT_TYPE='INDEX'; SELECT count(*) FROM T1 WHERE NAMESPACE=1;

是否是使用了列就一定可以收集成功直方图了？答案是否定的。

AUTO参数的判断标准之一是看sys.col_usage$视图中是否有相应列的使用记录。可以看到该视图中没有目标列的使用记录。

sys.col_usage$视图的数据是每隔一段由SMON进程进行刷新。为了确定系统是否记录了列的使用情况，使用如下刷新语句：

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; select obj#,intcol#,equality_preds from sys.col_usage$ where obj#='94350';

重新收集直方图：

exec dbms_stats.gather_table_stats(ownname=>'SZT',tabname=>'T1',METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO');

查询列的统计信息：

系统成功收集的Frequency类型的直方图。

第三章 AUTO_SAMPLE_SIZE参数问题

AUTO_SAMPLE_SIZE参数主要有如下优点：

指定AUTO抽样大小时,系统会自动判断一个合适的抽样百分比;
固定的抽样百分比在有些时候是好的,但是表的数据分布发生变化后可能就不合适了。

但是对大表而言，合适的抽样百分比意味着收集的统计信息并不足够准确，尤其是对直方图而言，默认的BUCKETS统计针对部分值的返回值估算不够准确。

查看当前的默认参数：系统采用默认的AUTO_SAMPLE_SIZE参数。

实验脚本如下：

--创建测试表：分别创建大表及小表 CREATE TABLE T1 AS SELECT * FROM DBA_OBJECTS; CREATE TABLE T2 AS SELECT * FROM DBA_OBJECTS WHERE ROWNUM<10000;

大表直接采用上述T1表，查询小表上OBJECT_TYPE列和NAMESPACE列列上的数据分布：

--查看相应列是否倾斜： SELECT OBJECT_TYPE, COUNT(*) FROM T2 GROUP BY OBJECT_TYPE ORDER BY 2 DESC;

SELECT NAMESPACE, COUNT(*) FROM T2 GROUP BY NAMESPACE ORDER BY 2 DESC;

数据倾斜严重。为了减少列使用情况的影响。直接对上述两个表指定大于最大唯一值数量的BUCKETS，数据库会根据实际的数据分布选择建立对应的BUCKETS值。

3.1 分别对两表收集直方图

实验脚本如下：

exec dbms_stats.gather_table_stats(ownname=>'SZT',tabname=>'T1',METHOD_OPT=>'FOR all COLUMNS SIZE 100'); exec dbms_stats.gather_table_stats(ownname=>'SZT',tabname=>'T2',METHOD_OPT=>'FOR all COLUMNS SIZE 100');

查询列的统计信息：

系统均收集了Frequency类型直方图。查看对应的BUCKETS数量：

小表T2、OBJECT_TYPE列：

col owner for a20 col table_name for a25 col column_name for a20 col ENDPOINT_NUMBER for 999999 col ENDPOINT_VALUE for 99999999999999999999999999999999999999 col ENDPOINT_ACTUAL_VALUE for a30 select * from dba_tab_histograms where TABLE_NAME = upper('T2') and column_name in(upper('OBJECT_TYPE')) ORDER BY ENDPOINT_VALUE;

小表T2、NAMESPACE列：

大表T1、OBJECT_TYPE列：

大表T1、NAMESPACE列：

大表的OBJECT_TYPE唯一值：45、NAMESPACE唯一值：21
小表的OBJECT_TYPE唯一值：37、NAMESPACE唯一值：15。

分别对上述两张表按照AUTO_SAMPLE_SIZE收集直方图。小表收集了列中全部唯一值的BUCKETS，大表仅收集了一部分唯一值的BUCKETS。同时查看最大BUCKETS，小表记录了全部行数9999，大表仅记录到5587行。

直方图中不同的记录方式也势必会对预估行数产生一定的影响。

3.2 测试对预估行数的影响：

首先采用如下SQL获得相应条件值的所有数据分布情况：

SELECT OBJECT_TYPE,COUNT(*) AS CARDINALITY,SUM(COUNT(*)) OVER (ORDER BY OBJECT_TYPE RANGE UNBOUNDED PRECEDING) AS CUM_CARDINALITY FROM T1 GROUP BY OBJECT_TYPE;

分别对上述表选择对应条件过滤，查看预估行数是否准确：

大表T1，选择popular value:

select count(*) from t1 where object_type='FUNCTION';

执行计划如下：

预估行数314。与实际行数基本一致。

由于只采集了一部分数据，因此分析预估值时需要按比例扩大。

带入计算公式：

(28-8)*(87726/5587)=314.03。符合预估值。

大表T1，选择非popular value:

select count(*) from t1 where object_type='DESTINATION';

执行计划如下：

预估行数8。与实际行数存在一定差异。

带入计算公式：

1/2*(87726/5587)=7.85≈8。符合预估值。

如果是对采用了100%采用的小表T2，做上述查询。则能避免预估不准确的情况：

可以看到预估与实际保持一致。主要来源于计算公式的差异：

FUNCTION:(122-44)(87726/87726)=78
DESTINATION:(32-30)(87726/87726)=2

分析结论：AUTO_SAMPLE_SIZE采样比例的不同，对直方图可能造成预估行数的差异。

第四章列宽过长导致的问题

对于文本型超过32位、数量型超过15位的列，直方图收集后都会产生一定的问题，从而导致可选择率的不准确问题。

4.1 文本型列过宽

如果针对文本型的列收集直方图，Oracle只会将该文本型字段的头32个字节给取出来（实际上只取头15个字节），并转换为一个浮点数。这种机制的缺陷就在于，对于那些超过32个字节的文本型字段，只要其头32个字节相同，直方图中记录的值就会是相同的，从而影响CBO对可选择率即返回结果的预估值。

实验脚本如下：

ALTER TABLE T1 ADD LONG_VARCHAR VARCHAR2(40); UPDATE T1 SET LONG_VARCHAR =LPAD(OBJECT_TYPE,40,0); COMMIT; --对LONG_VARCHAR列收集直方图 exec dbms_stats.gather_table_stats(ownname=>'SZT',tabname=>'T1',METHOD_OPT=>'FOR COLUMNS SIZE 100 LONG_VARCHAR',ESTIMATE_PERCENT=>100); --查看该列统计信息： col owner for a20 col table_name for a25 col column_name for a20 col HISTOGRAM for a15 col LOW_VALUE for a15 col HIGH_VALUE for a15 SELECT OWNER,TABLE_NAME,COLUMN_NAME,NUM_DISTINCT ,HISTOGRAM,LAST_ANALYZED FROM DBA_TAB_COL_STATISTICS WHERE TABLE_NAME in ('T1') and OWNER = upper('SZT') and column_name in ('LONG_VARCHAR');

该列包含45个唯一值，收集了Frequency类型直方图。

查看直方图的BUCKETS情况：

col owner for a20 col table_name for a25 col column_name for a20 col ENDPOINT_NUMBER for 999999 col ENDPOINT_ACTUAL_VALUE for a33 select * from dba_tab_histograms where TABLE_NAME = upper('T1') and column_name in(upper('LONG_VARCHAR')) ORDER BY ENDPOINT_NUMBER;

该列包含45个唯一值，采用率100，但是确仅使用了22个BUCKETS。

该列为40个字符，而直方图仅按照前32个字符进行记录。

对该列取前32个字符：

select distinct substr(LONG_VARCHAR,0,32) from t1 order by 1;

正好符合直方图中记录的22个BUCKETS。

如果在拼接上后面的8个字符：

col LONG_VARCHAR for a40 col LONG_32 for a32 select substr(LONG_VARCHAR,0,32)||substr(LONG_VARCHAR,33,40) LONG_VARCHAR,substr(LONG_VARCHAR,0,32) as LONG_32,count(*) from t1 group by substr(LONG_VARCHAR,0,32)||substr(LONG_VARCHAR,33,40),substr(LONG_VARCHAR,0,32) order by 2,3 desc;

正好对应与表中的全部唯一值数量。但有部分数据的前32个字符完全相同。

分析相应的数据选择性：

可以看到，由于在dba_tab_histograms视图中仅记录了前32个字符的信息，

而前32个字符中，如果其对应的ENDPOINT_ACTUAL_VALUE完全一致，就造成了数据分布差异很大的两个值的选择率完全一样。从而造成了评估行数的不准确问题。

因此，对列宽过长的列（大于32个字符）收集直方图时，要注意其可能对可选择率造成的影响。

4.2 数量型列过宽

如果针对数量型的列收集直方图，Oracle只会针对该数量型字段的前15位取ROUND。记录在直方图中。这种机制的缺陷同上，对于那些超过15个字节的数量型字段，只要其头15个字节相同，直方图中记录的值就会是相同的，从而影响CBO对可选择率即返回结果的预估值。

实验脚本如下：

ALTER TABLE T1 ADD LONG_NUMBER NUMBER(20,0); update t1 set LONG_NUMBER =LPAD(TRUNC(dbms_random.value(1,100)),16,100000000000000); update t1 set LONG_NUMBER =LPAD(TRUNC(dbms_random.value(60,70)),16,100000000000000) where rownum<60000; COMMIT; --对LONG_VARCHAR列收集直方图 exec dbms_stats.gather_table_stats(ownname=>'SZT',tabname=>'T1',METHOD_OPT=>'FOR COLUMNS SIZE 100 LONG_NUMBER',ESTIMATE_PERCENT=>100); --查看该列统计信息： col owner for a20 col table_name for a25 col column_name for a20 col HISTOGRAM for a15 col LOW_VALUE for a15 col HIGH_VALUE for a15 SELECT OWNER,TABLE_NAME,COLUMN_NAME,NUM_DISTINCT ,HISTOGRAM,LAST_ANALYZED FROM DBA_TAB_COL_STATISTICS WHERE TABLE_NAME in ('T1') and OWNER = upper('SZT') and column_name in ('LONG_NUMBER');

该列包含99个唯一值，收集了Frequency类型直方图。

查看直方图的BUCKETS情况：

col table_name for a25 col column_name for a20 col ENDPOINT_NUMBER for 999999 col ENDPOINT_VALUE for 999999999999999999999 select column_name, ENDPOINT_NUMBER, ENDPOINT_NUMBER - LAG(ENDPOINT_NUMBER, 1) OVER(ORDER BY ENDPOINT_NUMBER) cardinality, ENDPOINT_VALUE from dba_tab_histograms where TABLE_NAME = upper('T1') and column_name in (upper('LONG_NUMBER')) ORDER BY ENDPOINT_NUMBER;

该列为20个整数位，而直方图仅按照前15位取ROUND进行记录。

下面验证一下：

col LONG_NUMBER for 9999999999999999999999 col ROUND_15 for 9999999999999999999999 select LONG_NUMBER,round(LONG_NUMBER,-1) as ROUND_15,count(*) from t1 group by LONG_NUMBER,round(LONG_NUMBER,-1) order by 1,2 desc;