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

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

问题

数据库的版本是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 条评论
登录 后参与评论

相关文章

来自专栏Java面试通关手册

MySQL常见的两种存储引擎:MyISAM与InnoDB的爱恨情仇

Java面试通关手册(Java学习指南,欢迎Star,会一直完善下去,欢迎建议和指导):https://github.com/Snailclimb/Java_G...

2037
来自专栏领域驱动设计DDD实战进阶

关于领域对象业务逻辑中条件判断的最佳实践

1174
来自专栏蘑菇先生的技术笔记

探索c#之一致性Hash详解

2305
来自专栏杨建荣的学习笔记

Oracle Data Guard压缩归档测试(二)(r12笔记第27天)

昨天对Data Guard的归档压缩进行了一个初步的测试,我今天又做了一些补充。 1.昨天测试的是默认50M的redo,如果redo增大,在IO boun...

3318
来自专栏蓝天

改进型MapReduce

本文通过对MapReduce的分析,列出MapReduce存在的问题,然后提出一种解决这些问题的改进型MapReduce,这种改进型的MapReduce暂且取名...

762
来自专栏数据和云

你真的会用索引吗?来看看COUNT(*)到底能有多快

作者简介 ? 案例说明 一个大表的COUNT,究竟能有多快?除类似物化视图的做法,我们所能做到的极限能有多快?这不是一个真实的案例,而是根据笔者在网上发的一篇帖...

3326
来自专栏张狗蛋的技术之路

数据库内部存储结构探索

 本文是左耳耗子推荐的Medium上的一篇关于MySQL的文章Some study on database storage internals,本人觉得文章十分...

1322
来自专栏数据和云

【新书连载】诊断Cache buffers chains案例一则

题记:这是某移动运营商在SQL线下审核项目中,协助开发商完善数据库性能的过程。以往开发商遇到此问题总是怀疑是数据库的Bug,试图尝试重启Tuxedo、Weblo...

3248
来自专栏码匠的流水账

分布式id生成方案概述

对于每个标识,都需要有一个命名空间(namespace),来保证其相对唯一性。 分布式的ID生成,以Twitter Snowflake为代表的, Flake 系...

502
来自专栏数据和云

SQL查询提速秘诀,避免锁死数据库的数据库代码

由于数据库领域仍相对不成熟,每个平台上的 SQL 开发人员都在苦苦挣扎,一次又一次犯同样的错误。当然,数据库厂商在取得一些进展,并继续在竭力处理较重大的问题。

1143

扫码关注云+社区