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;

直方图中的ENDPOINT_NUMBER值正好对应于表中数据的前15位取ROUND值。

继续分析相应列上的选择性:
采用上述数据分布出现差异的50-70区间段。

采用非popular值计算:

采用非popular值计算,进而与实际的差异较大。造成预估行数不准。

采用popular值计算,与实际值比较接近。

采用popular值计算,与实际值比较接近。

总结结论:

1.过长的数量型导致直方图中只会记录数量型的前15位ROUND值。

2.进而可能出现更多的非popular值,大大增加可选择率不准的问题。

第五章 总结

通过上述的测试,列的直方图容易受很多方面的影响:

AUTO_SAMPLE_SIZE、FOR COLUMNS SIZE AUTO等参数都可能给需要收集直方图的列带来性能隐患。

同时,还要注意相应列的列类型和列宽。很可能造成预估行数远远小于实际行数的情况,从而造成SQL的低效执行计划。定位出问题并分析列分布后,可以采用:删除列上的直方图、HINT固定执行计划等方式,来避免后续产生低效的查询。

(0)

相关推荐

  • mysql最大字段数量及 varchar类型总结

    mysql最大字段数 一直对mysql最大字段数不明确有人说是1024 还有人说 Max columns per row 4096 InnoDB is limited to 1000 columns ...

  • mysql索引笔记

    MYSQL索引 一.索引的优缺点 优点:   1.通过创建唯一索引,可以保证数据库表中每一行的唯一性. 2.可以大大加快查询速度,这是创建索引的最主要原因 3.在实现数据参考完整性方面,可以加速表和表 ...

  • MySQL快速回顾:数据库和表操作

    前提要述:参考书籍<MySQL必知必会> 利用空闲时间快速回顾一些数据库基础. 4.1 连接 在最初安装MySQL,可能会要求你输入一个管理登录(通常为root)和一个口令(密码). 连接 ...

  • MySQL基础入门使用和命令的使用

    数据库了解 概念 数据库就是一种特殊的文件,其中存储着需要的数据 一个数据库可以有多张表 MySQL是一种关系型数据库 具有关联性数据的就是关系型数据库 MySQL是一种软件可以用来创建mysql数据 ...

  • Oracle的直方图试验

    直方图有两种类别,频率直方图与高度均衡直方图. 直方图有两种类别,频率直方图与高度均衡直方图. 默认的,如果一个倾斜列上的唯一值超过了254个,那么Oracle会对此列建立高度均衡直方图,否则建立频率 ...

  • Oracle行转列语法总结大全

    墨天轮主页:https://www.modb.pro/u/372619 注:本文测试以Oracle 11g下的scoot schema为例. 需求:求emp表各个岗位的工资之和,若无,用0代替. 一. ...

  • 细思极恐!上港隐患浮现,多名主力仍未续约,颜骏凌吕文君在列

    日前,根据国内媒体消息,今年多名上港主力球员进入了合同年,但至今未完成续约.其中不乏像吕文君.颜骏凌.石柯这些本土功勋球员,他们的合同都将在12月31日到期.上港在多名主力都未续约的情况下,开始新赛季 ...

  • oracle 11g 新特性应用--虚拟列&ora_hash() 在性能提升的作用(merge)

    情景说明:数据仓库运行以每天或一固定频率同步刷新增量数据后,需要对增量数据插入全量数据的过程,当数据量达到超大数量级时, 数据的更新操作的性能会严重不足, 产生这种情况的原因多是因为程序中直接使用. ...

  • 安全气囊存隐患,大众车型将召回,迈腾途观在列

    安全气囊的作用以及无需多言了,驾驶员及乘员保命必备配置之一.由于安全气囊只能在乘客和方向盘或仪表板之间的有限空间内发挥作用,并且必须在几分之一秒的时间内完成使命,所以安全气囊的设计及安装要求十分苛刻. ...

  • 近日,在青岛开往成都的列车上,一位买了无...

    近日,在青岛开往成都的列车上,一位买了无座票的女乘客强行霸座,对于乘警的再三规劝,置之不理,拒不让座.期间还强词夺理,金句不断,怪话连篇,让人很无语! 当乘警让她腾出座位时,这位女乘客居然说:&quo ...

  • 肿瘤及肿瘤治疗发展简史2-21世纪的突破及隐患

    - 上期回顾 - 肿瘤及肿瘤治疗发展简史-1 精准医学不同于以往的治疗模式,它会深入最微小的分子和基因组信息,为患者提供个性化的治疗方案.这和中医所提出的"同病异治"理念相似.也就 ...

  • Oracle 外部表

    外部表是指不存在于数据库中的表.通过向Oracle 提供描述外部表的元数据,可以把一个操作系统文件当成一个只读的数据库表,就像这些数据存储在一个普通数据库表中一样来进行访问.外部表是对数据库表的延伸. ...

  • 最高人民法院关于第二审人民法院因追加、更换当事人发回重审的民事裁定书上,应如何列当事人问题的批复(1...

    最高人民法院关于第二审人民法院因追加.更换当事人发回重审的民事裁定书上,应如何列当事人问题的批复 (法民[1990]8号) 山东省高级人民法院: 你院鲁法(经)函(1990)19号<关于在二审追 ...