SQL调优技巧:统计信息

作者 : 开心源码 本文共2845个字,预计阅读时间需要8分钟 发布时间: 2022-05-12 共41人阅读

统计信息相似于战争中的侦察兵,假如情报工作没有做好,打仗就会输掉战争。同样的道理,假如没有正确地收集表的统计信息,或者者没有及时地升级表的统计信息,SQL的执行计划就会跑偏,SQL也就会出现性能问题。收集统计信息是为了让优化器选择最佳执行计划,以最少的代价(成本)查询出表中的数据。

统计信息主要分为表的统计信息、列的统计信息、索引的统计信息、系统的统计信息、数据字典的统计信息以及动态性能视图基表的统计信息。

关于系统的统计信息、数据字典的统计信息以及动态性能视图基表的统计信息本文不做探讨,本文重点探讨表的统计信息、列的统计信息以及索引的统计信息。

表的统计信息主要包含表的总行数(num_rows)、表的块数(blocks)以及行平均长度(avg_row_len),我们可以通过查询数据字典DBA_TABLES获取表的统计信息。

现在我们创立一个测试表T_STATS。

SQL调优技巧:统计信息

我们查看表T_STATS常使用的表的统计信息。

SQL调优技巧:统计信息

由于T_STATS是新创立的表,没有收集过统计信息,所以从DBA_TABLES查询数据是空的。

现在我们来收集表T_STATS的统计信息。

SQL调优技巧:统计信息

我们再次查看表的统计信息。

SQL调优技巧:统计信息

从查询中我们可以看到,表T_STATS一共有72?674行数据,1?061个数据块,平均行长度为97字节。

列的统计信息主要包含列的基数、列中的空值数量以及列的数据分布情况(直方图)。我们可以通过数据字典DBA_TAB_COL_STATISTICS查看列的统计信息。

现在我们查看表T_STATS常使用的列统计信息。

SQL调优技巧:统计信息

上面查询中,第一个列表示列名字,第二个列表示列的基数,第三个列表示列中NULL值的数量,第四个列表示直方图的桶数,最后一个列表示直方图类型。

在工作中,我们经常用下面脚本查看表和列的统计信息。

SQL调优技巧:统计信息

索引的统计信息主要包含索引blevel(索引高度-1)、叶子块的个数(leaf_blocks)以及集群因子(clustering_factor)。我们可以通过数据字典DBA_INDEXES查看索引的统计信息。

我们在OBJECT_ID列上创立一个索引。

SQL调优技巧:统计信息

创立索引的时候会自动收集索引的统计信息,运行下面脚本查看索引的统计信息。

SQL调优技巧:统计信息

在以后章节中,我们会详细详情表的统计信息、列的统计信息以及索引的统计信息是如何被应使用于成本计算的。

统计信息重要参数设置

我们通常用下面脚本收集表和索引的统计信息。

SQL调优技巧:统计信息

ownname表示表的拥有者,不区分大小写。

tabname表示表名字,不区分大小写。

granularity表示收集统计信息的粒度,该选项只对分区表生效,默认为AUTO,表示让Oracle根据表的分区类型自己判断如何收集分区表的统计信息。对于该选项,我们一般采使用AUTO方式,也就是数据库默认方式,因而,在后面的脚本中,省略该选项。

estimate_percent 表示采样率,范围是0.000 001~100。

我们一般对小于1GB的表进行100%采样,由于表很小,即便100%采样速度也比较快。有时候小表有可能数据分布不均衡,假如没有100%采样,可能会导致统计信息不准。因而我们建议对小表100%采样。

我们一般对表大小在1GB~5GB的表采样50%,对大于5GB的表采样30%。假如表特别大,有几十甚至上百GB,我们建议应该先对表进行分区,而后分别对每个分区收集统计信息。

一般情况下,为了确保统计信息比较精确,我们建议采样率不要低于30%。

我们可以用下面脚本查看表的采样率。

SQL调优技巧:统计信息

从上面查询我们可以看到,对表T_STATS是100%采样的。现在我们将采样率设置为30%。

SQL调优技巧:统计信息

从上面查询我们可以看到采样率为30%,表的总行数被估算为73?067,而实际上表的总行数为72?674。设置采样率30%的时候,一共分析了21?920条数据,表的总行数等于round(21?920*100/30),也就是73?067。

除非一个表是小表,否则没有必要对一个表100%采样。由于表一直都会进行DML操作,表中的数据始终是变化的。

method_opt 使用于控制收集直方图策略。

method_opt => 'for all columns size 1'

表示所有列都不收集直方图,如下所示。

SQL调优技巧:统计信息

我们查看直方图信息。

SQL调优技巧:统计信息

从上面查询我们看到,所有列都没有收集直方图。

method_opt => 'for all columns size skewonly'

表示对表中所有列收集自动判断能否收集直方图,如下所示。

SQL调优技巧:统计信息

我们查看直方图信息,如下所示。

SQL调优技巧:统计信息

从上面查询我们可以看到,除了OBJECT_ID列和EDITION_NAME列,其他所有列都收集了直方图。由于EDITION_NAME列全是NULL,所以没必要收集直方图。OBJECT_ID列选择性为100%,没必要收集直方图。

在实际工作中千万不要用

method_opt => 'for all columns size skewonly'

收集直方图信息,由于并不是表中所有的列都会出现在where条件中,对没有出现在where条件中的列收集直方图没有意义。

method_opt => 'for all columns size auto'

表示对出现在where条件中的列自动判断能否收集直方图。

现在我们删除表中所有列的直方图。

SQL调优技巧:统计信息

我们执行下面SQL,以便将owner列放入where条件中。

SQL调优技巧:统计信息

接下来我们刷新数据库监控信息。

SQL调优技巧:统计信息

我们用method_opt => 'for all columns size auto'方式对表收集统计信息。

SQL调优技巧:统计信息

而后我们查看直方图信息。

SQL调优技巧:统计信息

从上面查询我们可以看到,Oracle自动地对owner列收集了直方图。

思考,假如将选择性比较高的列放入where条件中,会不会自动收集直方图?现在我们将OBJECT_NAME列放入where条件中。

SQL调优技巧:统计信息

而后我们刷新数据库监控信息。

SQL调优技巧:统计信息

我们收集统计信息。

SQL调优技巧:统计信息

我们查看OBJECT_NAME列能否收集了直方图。

SQL调优技巧:统计信息

从上面查询我们可以看到,OBJECT_NAME列没有收集直方图。由此可见,用AUTO方式收集直方图很智能。mothod_opt默认的参数就是 for all columns size auto。method_opt => 'for all columns size repeat'表示当前有哪些列收集了直方图,现在就对哪些列收集直方图。


本文摘自《SQL优化核心思想》

SQL调优技巧:统计信息

《SQL优化核心思想》

罗炳森 黄超 钟侥 著

点击封面购买纸书

结构化查询语言(Structured Query Language,SQL)是一种功能强大的数据库语言。它基于关系代数运算,功能丰富、语言简洁、用方便灵活,已成为关系数据库的标准语言。 本书旨在引导读者掌握SQL优化技能,以更好地提升数据库性能。本书基于Oracle进行编写,内容讲解由浅入深,适合各个层次的读者学习。

本书面向一线工程师、运维工程师、数据库管理员以及系统设计与开发人员,无论是初学者还是有肯定基础的读者,都将从中获益。

说明
1. 本站所有资源来源于用户上传和网络,如有侵权请邮件联系站长!
2. 分享目的仅供大家学习和交流,您必须在下载后24小时内删除!
3. 不得使用于非法商业用途,不得违反国家法律。否则后果自负!
4. 本站提供的源码、模板、插件等等其他资源,都不包含技术服务请大家谅解!
5. 如有链接无法下载、失效或广告,请联系管理员处理!
6. 本站资源售价只是摆设,本站源码仅提供给会员学习使用!
7. 如遇到加密压缩包,请使用360解压,如遇到无法解压的请联系管理员
开心源码网 » SQL调优技巧:统计信息

发表回复