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_