关于直方图统计信息的两个有趣的知识点

有朋友问了我如下这样一个问题,最后的解决过程挺有意思的,让我发现了直方图统计信息里我之前没有注意到的两个知识点,这里跟大家分享一下。

问题

数据库的版本是11.2.0.3:

创建一个测试表T1:

SQL> create table t1 as select * from dba_users; Table created

从如下查询结果中我们可以看到,表T1的OBJECT_ID是104192:

SQL> select object_id from dba_objects where owner=’SCOTT’ and object_name=’T1′; OBJECT_ID ——————- 104192

表T1的列user_id所对应的INTCOL#是2:

SQL> select name,intcol# from sys.col$ where obj#=104192 and name=’USER_ID’; NAME INTCOL# —————— ———- USER_ID 2

从如下结果里可以看到,SYS.COL_USAGE$现在还没有列USER_ID的使用记录:

SQL> select obj#,intcol#, equality_preds from sys.col_usage$ where obj#=104192; OBJ# INTCOL# EQUALITY_PREDS

我们现在来使用一下列USER_ID:

SQL> select count(*) from t1 where user_id=5; COUNT(*) —————– 1

使用完后,我们发现SYS.COL_USAGE$还是没有列USER_ID的使用记录:

SQL> select obj#,intcol#, equality_preds from sys.col_usage$ where obj#=104192; OBJ# INTCOL# EQUALITY_PREDS ———- ———- ————–

这个是正常的,这里不是没有列USER_ID的使用记录,是已经有了但只是还没有被持久化到SYS.COL_USAGE$中,这里需要我们手工执行一下dbms_stats.gather_table_stats,这样就能将USER_ID的使用记录flush到SYS.COL_USAGE$中了,然后我们就能看到了:

但现在的问题是无论我们怎么执行dbms_stats.gather_table_stats,列user_id上的直方图统计信息就是没有(这也是那位朋友问的问题):

这里除非我们手工指定user_id列所用的bucket的数量:

手工指定了直方图统计信息的bucket的数量为39后,明明列user_id的distinct值的数量也是39,为什么这里直方图的类型居然是HEIGHT BALANCED?按道理讲应该是FREQUENCY啊!

当看到上述测试结果的时候,我意识到一定是什么地方出了问题。

因为上述现象的出现已经颠覆了我之前对直方图统计信息的如下两个认识:

1、我原先一直以为如果METHOD_OPT的值是默认的“FOR ALL COLUMNS SIZE AUTO”的话,那么只要SYS.COL_USAGE$中有目标列的使用记录,则Oracle在自动收集直方图统计信息的时候就会去收集该列的直方图统计信息;

2、在手工收集直方图统计信息的时候,如果我手工指定的bucket的数量等于目标列的distinct值的数量,且这个值是小于等于254的话,那么Oracle此时收集的直方图统计信息的类型应该是FREQUENCY。

到底是什么地方出了问题?

我们来复习一下Oracle关于自动收集直方图统计信息的定义:

Oracle在“SIZE Clause in METHOD_OPT Parameter of DBMS_STATS Package (Doc ID 338926.1)”中明确指出,METHOD_OPT的值中的AUTO的含义为如下所示:

AUTO: Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.

这里的“workload of the columns”指的应该就是目标列是否在SYS.COL_USAGE$中有使用记录。

注意到Oracle这里还提到了另外一个条件——“based on data distribution”(这也是我之前没有注意到的条件),但这里的具体含义是什么?

“based on data distribution”直译过来就是目标列数据的分布。说白了就是目标列的数据分布确实得是倾斜的,只有满足这个前提条件,再加上该目标列在SYS.COL_USAGE$中有使用记录,Oracle在自动收集直方图统计信息的时候才会对该列收集直方图统计信息。

Oracle是怎么来判断某列的数据分布是否是倾斜的呢?

Oracle采用了一种很简单的方法

就是判断目标列的distinct值的数量是否和目标表的数据量相同,如果相同,Oracle就认为该列的数据分布不是倾斜的,否则就是倾斜的。

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

搞清楚了上述知识点,那位朋友问的问题自然就有答案了——对于表T1的列user_id而言,其distinct值的数量和表T1的数据量相同,所以这里即使user_id在SYS.COL_USAGE$中有使用记录,Oracle在自动收集直方图统计信息的时候也不会对user_id收集直方图统计信息:

现在我们来验证一下上述理论,往表T1中插入一条记录,使得user_id的distinct值的数量小于表T1的数据量,这样当我们再次对表T1收集统计信息的时候,user_id列的直方图统计信息应该就有了。

先把之前对user_id列手工指定bucket数量收集的直方图统计信息删掉:

对表T1插入一条user_id列的值和现有值重复的记录:

SQL> insert into t1 select * from t1 where user_id=5; 1 row inserted SQL> commit; Commit complete

现在user_id列的distinct值的数量已经小于表T1的数据量了:

此时对表T1再次收集统计信息:

SQL> exec dbms_stats. gather_table_stats (ownname=>’SCOTT’, tabname=>’T1′,estimate_percent=>100);

PL/SQL procedure successfully completed

从如下查询结果里我们可以看到,现在user_id列上终于有了直方图统计信息,且其类型就是FREQUENCY,这就和我们以前的认知匹配上了,同时也验证了我们刚才的分析结论:

再次删除user_id列上的直方图统计信息:

我们再次以手工指定bucket数量的方式收集user_id列上的直方图统计信息:

SQL> exec dbms_stats.gather_table_stats (ownname=>’SCOTT’,tabname=>’T1′, method_opt=>’for columns size 39 USER_ID‘,estimate_percent=>100); PL/SQL procedure successfully completed

从如下查询结果我们可以看到,现在user_id列上的直方图统计信息的类型已经不是之前的HEIGHT BALANCED了,而是变成了FREQUENCY:

这说明我们之前的认识(在手工收集直方图统计信息的时候,如果我手工指定的bucket的数量等于目标列的distinct值的数量,且这个值是小于等于254的话,那么Oracle此时收集的直方图统计信息的类型应该是FREQUENCY)成立的前提条件是该列的数据分布是倾斜的。

总结

通过这篇文章,我们介绍了如下两个关于直方图统计信息的有趣知识点:

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

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

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2017-09-27

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏张红林的专栏

大规模机器学习框架的四重境界(下)

同步协议本节假设读者已经对随机梯度优化算法比较熟悉,如果不熟悉的同学请参考吴恩达经典课程机器学习中对SGD的介绍,或者我之前多次推荐过的书籍《最优化导论》。

9000
来自专栏数据和云

算法分析:Oracle 11g 中基于哈希算法对唯一值数(NDV)的估算

1 为什么引入新 NDV 算法 字段的统计数据是 CBO 优化器估算执行计划代价的重要依据。而字段的统计数据可以分为两类: 1. 概要统计数据:如 NDV 字段...

3377
来自专栏CreateAMind

ray框架及ray-rllab

772
来自专栏数据和云

深入内核丨12C 新特性之 TOP - N 频率柱状图原理和算法

作者简介 ? 黄玮(Fuyuncat) 资深 Oracle DBA,致力于数据库底层技术的研究,其作品获得广大同行的高度评价。 个人网站 www.HelloDB...

27112
来自专栏刁寿钧的专栏

10 分钟梳理关系数据库基础知识(六) : 连接的算法与代价

每天10分钟,用去食堂吃饭的时间解决一个知识点。本文复习下做等值JOIN时不同的连接方式与代价,通过粗略的估算给大家一个直观的认识。

1600
来自专栏利炳根的专栏

学习笔记DL001 : 数学符号、深度学习的概念

深度学习是机器学习拉出的分支,它试图使用包含复杂结构或由多重非线性变换构成的多个处理层对数据进行高层抽象的算法。本文主要介绍深度学习中的数学符号、数和数组。

3410
来自专栏郭耀华‘s Blog

知乎问题代码

802
来自专栏斑斓

使用Python Pandas处理亿级数据

在数据分析领域,最热门的莫过于Python和R语言,此前有一篇文章《别老扯什么Hadoop了,你的数据根本不够大》指出:只有在超过5TB数据量的规模下,Hado...

5154
来自专栏大数据挖掘DT机器学习

使用Python Pandas处理亿级数据

原文:http://www.justinablog.com/archives/1357?utm_source=tuicool&utm_medium=refer...

3007
来自专栏Spark学习技巧

Kylin及数据仓库的技术概念详解

一 cube 1, Table cube数据源的hive表的定义,在build cube之前需要进行同步。 2, Data Model 这描述了一个星型...

2228

扫描关注云+社区