众所周知 ，列的直方图主要用于针对数据倾斜的情况，能帮助数据库更准确的了解数据的分布情况，从而选择更高效的执行计划。
经过测试，直方图也是存在很多问题隐患的。大致包括：
1.默认的收集参数FOR COLUMNS SIZE AUTO导致未使用列无法准确收集直方图；
2.自动采样率AUTO_SAMPLE_SIZE导致对大表的直方图收集信息不完全；
3.过长的列导致直方图记录信息不完整，从而导致预估返回行数不准的问题。
下面分别进行分析及测试：
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';
在列未使用的情况下使用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');
可以看到，相应列上均没有收集直方图。
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抽样大小时,系统会自动判断一个合适的抽样百分比;
固定的抽样百分比在有些时候是好的,但是表的数据分布发生变化后可能就不合适了。
但是对大表而言，合适的抽样百分比意味着收集的统计信息并不足够准确，尤其是对直方图而言，默认的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值。
实验脚本如下：
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行。
直方图中不同的记录方式也势必会对预估行数产生一定的影响。
首先采用如下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。与实际行数基本一致。
由于只采集了一部分数据，因此分析预估值时需要按比例扩大。
带入计算公式：
大表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个字符）收集直方图时，要注意其可能对可选择率造成的影响。
如果针对数量型的列收集直方图，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;
直方图中的ENDPOINT_NUMBER值正好对应于表中数据的前15位取ROUND值。
继续分析相应列上的选择性：
采用上述数据分布出现差异的50-70区间段。
采用非popular值计算：
采用非popular值计算，进而与实际的差异较大。造成预估行数不准。
采用popular值计算，与实际值比较接近。
采用popular值计算，与实际值比较接近。
总结结论：
1.过长的数量型导致直方图中只会记录数量型的前15位ROUND值。
2.进而可能出现更多的非popular值，大大增加可选择率不准的问题。
通过上述的测试，列的直方图容易受很多方面的影响：
AUTO_SAMPLE_SIZE、FOR COLUMNS SIZE AUTO等参数都可能给需要收集直方图的列带来性能隐患。
同时，还要注意相应列的列类型和列宽。很可能造成预估行数远远小于实际行数的情况，从而造成SQL的低效执行计划。定位出问题并分析列分布后，可以采用：删除列上的直方图、HINT固定执行计划等方式，来避免后续产生低效的查询。
联系客服
