Oracle里收集与查看统计信息的方法_DBA Fighting!的技术博客_51CTO博客

标签: | 发表时间:2021-06-08 08:51 | 作者:
出处:https://blog.51cto.com

Oracle数据库里的统计信息是这样的一组数据:它存储在数据字典里,且从多个维度描述了Oracle数据库里对象的详细信息。CBO会利用这些统计信息来计算目标SQL各种可能的、不同的执行路径的成本,并从中选择一条成本值最小的执行路径来作为目标SQL的执行计划。

Oracle数据库里的统计信息可以分为如下6种类型:

  • 表的统计信息

  • 索引的统计信息

  • 列的统计信息

  • 系统统计信息

  • 数据字典统计信息

  • 内部对象统计信息

表的统计信息用于描述Oracle数据库里表的详细信息,它包含了一些典型的维度,如记录数、表块(表里的数据块)数量、平均行长度等。

索引的统计信息于描述Oracle数据库里索引的详细信息,它包含了一些典型的维度,如索引的层级、叶子块的数量、聚簇因子等。

列的统计信息于描述Oracle数据库里列的详细信息,它包含了一些典型的维度,如列的distinct值的数量、列的NULL值的数量、列的最小值、列的最大值以及直方图等。

系统统计信息于描述Oracle数据库所在的数据库服务器的系统处理能力,它包含了CPU和I/O这两个维度,借助于系统统计信息,Oracle可以更清楚地知道目标数据库服务器的实际处理能力。

数据字典统计信息用于热核Oracle数据库里数据字典基表(如TAB$、IND$等)、数据字典基表上的索引,以及这些数据字典的列的详细信息,描述上述数据字典基表的统计信息与描述普通表、索引、列的统计信息没有本质区别。

内部对象统计信息用于描述Oracle数据库里的一些内部表(如X$系列表)的详细信息,它的维度和普通表的统计信息的维度类似,只不过其表块的数量为0,因为X$系统表实际上只是Oracle自定义的内存结构,并不占用实际的物理存储空间。

1、收集统计信息

在Oracle数据库里,通常有两种方法可以用来收集统计信息:一种是使用ANALYZE命令;另一种是使用DBMS_STATS包。表、索引、列的统计信息和数据字典统计信息用ANALYZE命令或者DBMS_STATS包收集均可,但系统统计信息和系统内部对象统计信息只能使用DBMS_STATS包来收集。

对系统内部表若使用ANALYZE命令来收集统计信息,会报错ORA-02030

1.1用ANALYZE命令收集统计信息

从Oracle7开始,ANALYZE命令就可以用来收集表、索引、列的统计信息,以及系统统计信息。

典型用法如下:

[email protected]>create table t2 as select * from dba_objects;

Table created.

[email protected]>create index idx_t2 on t2(object_id);

Index created.

[email protected]>analyze index idx_t2 delete statistics;

Index analyzed.

从Oracle 10g开始,创建索引后Oracle会怎么收集目标索引的统计信息,出现演示的目的,这里删除索引IDX_T2的统计信息:

执行sosi脚本,从输出内容可以看到表T2、表T2的列和索引IDX_T2均没有相关的统计信息

wKiom1iq7RLjGuX7AACqPsxAz2k185.png

[email protected]>select count(*) from t2;

  COUNT(*)
----------
     86852

只对表T2收集统计信息,并且以估算模式,采样的比例为15%:

[email protected]>analyze table t2 estimate statistics sample 15 percent for table;

Table analyzed.

再次执行sosi脚本,可以看出现在只用表T2有统计信息,表T2的列和索引IDX_T2均没有相关的统计信息。而且因为采用的是估算模式所以估算结果和实际结果并不一定会完全匹配,比如表T2的实际数量与估算出的数量不一致。

wKioL1iq7cTgWd0PAACu-EBdQjE462.png

只对表T2收集统计信息,并且以计算模式:

[email protected]>analyze table t2 compute statistics for table;

Table analyzed.

再次执行sosi脚本,可以看出现在只用表T2有统计信息,表T2的列和索引IDX_T2均没有相关的统计信息。而且因为采用的是计算模式,计算模式会扫描目标对象的所有数据,所以统计结果和实际结果是匹配的。

wKioL1iq7iTD_PR5AACuQdP-vaQ696.png

对表T2收集完统计信息后,现在对表T2的列OBJECT_NAME和OBJECT_ID以计算模式收集统计信息:

[email protected]>analyze table t2 compute statistics for columns object_name,object_id;

Table analyzed.

再次执行sosi脚本,可以看出,现在列OBJECT_NAME和OBJECT_ID确实已经有统计信息了

wKioL1iq7vuRFPShAACwW3OTl3E340.png

注:在崔华老师的《基于Oracle的SQL优化》一书中提到T2原有的统计信息已经被抹掉了,也就是说对同一个对象而言,新执行的ANALYZE命令会抹掉之前ANALYZE的结果。但是在我实际的执行结果是表T2原有的统计信息没有被抹掉。我用到的环境是10.2.0.4和11.2.0.4,暂时没有11.2.0.1的环境。

可以使用如下的命令同时以计算模式对表T2和列OBJECT_NAME、OBJECT_ID收集统计信息:

[email protected]>analyze table t2 compute statistics for table for columns object_name,object_id;

Table analyzed.

再次执行sosi脚本,可以看到表T2和列OBJECT_NAME、OBJECT_ID上都有统计信息了。

wKioL1iq7vuRFPShAACwW3OTl3E340.png

使用如下命令可以以计算模式收集索引IDX_T2的统计信息

[email protected]>analyze index idx_t2 compute statistics;

Index analyzed.

再次执行sosi脚本,从输出可以看到,现在索引IDX_T2已经有了统计信息,并且之前收集的表T2和列OBJECT_NAME、OBJECT_ID上的统计信息并没有被抹掉,这是因为我们刚才执行的ANALYZE命令和之前执行的ANALYZE命令针对的不是同一个对象。

wKioL1iq79ixHno0AAC5g1rPsjY367.png

使用如下命令可以删除表T2、表T2的所有列及表T2的所有索引的统计信息:

[email protected]>analyze table t2 delete statistics;

Table analyzed.

再次执行sosi脚本,从输出可以看到,刚才收集的表T2、表T2的列OBJECT_NAME、OBJECT_ID以及索引IDX_T2的统计信息已经全部被删除了。

wKiom1iq7RLjGuX7AACqPsxAz2k185.png

如果想一次性以计算模式收集表T2、表T2的所有列和表T2上的所有索引的统计信息,执行如下的语句就可以了:

[email protected]>analyze table t2 compute statistics;

Table analyzed.

再次执行sosi脚本,从输出可以看到,现在表T2、表T2的所有列和索引IDX_T2的统计信息都有了。

wKioL1iq8njhcRkaAADiiUZNjSo403.png

1.2用DBMS_STATS包收集统计信息

从Oracle 8.1.5开始,DBMS_STATS包被广泛用于统计信息的收集,用DMBS_STATS包收集统计信息也是Oracle官方推荐的方式。在收集CBO所需要的统计信息方面,可以简单的将DBMS_STATS包理解成是ANALYZE命令的增加版。

DBMS_STATS包里最常用的就是如下4个存储过程:

  • GATHER_TABLE_STATS:用于收集目标表、目标表的列和目标表上的索引的统计信息。

  • GATHER_INDEX_STATS:用于收集指定索引的统计信息。

  • GATHER_SCHEMA_STATS:用于收集指定schema下所有对象的统计信息。

  • GATHER_DATABASE_STATS:用于收集全库所有对象的统计信息。

现在介绍DBMS_STATS包在收集统计信息时的常见用法,还是针对上面的测试表T2,这里使用DBMS_STATS包实现了和ANALYZE命令一模一样的效果。

先删除表T2上的所有统计信息

analyze table t2 delete statistics;

只对表T2收集统计信息,并且以估算模式,采用的比例同样为15%:

[email protected]>exec dbms_stats.gather_table_stats(ownname=>'ZX',tabname=>'T2',estimate_percent=>15,method_opt=>'FOR TABLE',cascade=>false);

PL/SQL procedure successfully completed.

执行sosi脚本,从输出内容可以看出,现在只有表T2有统计信息,表T2的列和索引IDX_T2均没有相关的统计信息。而且因为采用的估算模式,所以估算结果和实际结果并不一定会完全匹配。

wKioL1iq8OGy7M0dAACvYkd_o6U368.png

需要注意的是,这里Oracle数据库的版本是11.2.0.4,我们在调用DMBS_STATS.GATHER_TABLE_STATS时指定参数METHOD_OPT的值为'FOR TABLE',这表示只收集表T2的统计信息。这种收集表统计信息的方法并不适用于Oracle数据库所有的版本。例如这种方法就不适用于Oracle10.2.0.4和Oracle10.2.0.5,在这两个版本里,即使指定了'FOR TABLE',Oracle除了收集表统计信息之外还会对所有的列收集统计信息。

如果公对表T2收集统计信息,并且是以计算模式收集,用DBMS_STATS包实现的方法就是将估算模式的采样比例(即参数ESTIMATE_PERCENT)设置为100%或NULL;

exec dbms_stats.gather_table_stats(ownname=>'ZX',tabname=>'T2',estimate_percent=>100,method_opt=>'FOR TABLE',cascade=>false);

exec dbms_stats.gather_table_stats(ownname=>'ZX',tabname=>'T2',estimate_percent=>NULL,method_opt=>'FOR TABLE',cascade=>false);

[email protected]>exec dbms_stats.gather_table_stats(ownname=>'ZX',tabname=>'T2',estimate_percent=>100,method_opt=>'FOR TABLE',cascade=>false);

PL/SQL procedure successfully completed.

执行sosi脚本,从输出内容可以看出,现在只有表T2的统计信息,表T2的列和索引IDX_T2均没有相关的统计信息。而且因为采用的是计算模式,计算模式会扫描目标对象的所有数据,所以统计结果和实际结果是匹配的。

wKiom1iq8T7gOu9fAACvLFuy1y0524.png

对表T2收集完统计信息后,现在我们来对表T2的列OBJECT_NAME、OBJECT_ID以计算模式收集统计信息(不收集直方图):

[email protected]>exec dbms_stats.gather_table_stats(ownname=>'ZX',tabname=>'T2',estimate_percent=>100,method_opt=>'for columns size 1 object_name,object_id',cascade=>false);

PL/SQL procedure successfully completed.

执行sosi脚本,从输出内容可以看出,现在表T2的列OBJECT_NAME、OBJECT_ID上都有统计信息了,并且Oracle还会同时收集表T2上的统计信息(注意,这和ANALYZE命令有所区别)。

wKioL1iq8aKisiLnAAC0Sg2A2rA027.png

使用如下命令可以以计算模式收集索引IDX_T2的统计信息

[email protected]>exec dbms_stats.gather_index_stats(ownname=>'ZX',indname=>'IDX_T2',estimate_percent=>100);

PL/SQL procedure successfully completed.

执行sosi脚本,从输出内容可以看出,现在索引IDX_T2已经有了统计信息。

wKioL1iq8gXjvnkQAAC5dsF0kDQ879.png

使用如下命令可以删除表T2、表T2的所有列及表T2的所有索引的统计信息:

[email protected]>exec dbms_stats.delete_table_stats(ownname=>'ZX',tabname=>'T2');

PL/SQL procedure successfully completed.

执行sosi脚本,从输出内容可以看出,表T2、表T2的所有列及表T2的所有索引的统计信息已经全部被删除了。

wKiom1iq7RLjGuX7AACqPsxAz2k185.png

如果想一次性以计算模式收集表T2、表T2的所有列及表T2的所有索引的统计信息,执行如下语句就可以了

[email protected]>exec dbms_stats.gather_table_stats(ownname=>'ZX',tabname=>'T2',estimate_percent=>100,cascade=>true);

PL/SQL procedure successfully completed.

wKioL1iq8njhcRkaAADiiUZNjSo403.png

1.3 ANALYZE和DBMS_STATS的区别

从上面的演示中可以看出ANALYZE命令和DBMS_STATS包都可以用来收集表、索引和列的统计信息,看起来它们在收集统计信息方面的效果是一模一样的,为什么Oracle会推荐使用DBMS_STATS包来收集统计信息呢?

因为ANALYZE命令和DMBS_STATS包相比,存在如下缺陷:

ANALYZE命令不能正确地收集分区表的统计信息,而DBMS_STATS包却可以。ANALYZE命令只会收集最低层次对象的统计信息,然后推导和汇总出高一级的统计信息,比如对于有子分区的分区表而言,它只会先收集子分区统计信息,然后再汇总,推导出分区或表级的统计信息。有的统计信息是可以从当前对象的下一级对象进行汇总后得到的,比如表的总行数,可以由各分区的行数相加得到。但有的统计信息则不能从下一级对象得到,比如列上的distinct值数量NUM_DISTINCT以及DESNSITY等。

ANALYZE命令不能并行收集统计信息,而DBMS_STATS包却可以。并行收集统计信息对数据量很大的表表而言,是非常有用的特性。对于数据量很大的表,如果不能并行收集统计信息,则意味着如果想精确地收集目标对象的统计信息,那么耗费的时间可能会非常长,这有可能是不能接受的。在Oracle数据库里,DBMS_STATS包收集统计信息可以并行执行,这在一定程度上缓解了对大表的统计信息收集过长所带来的一系列问题。

DBMS_STATS包的并行收集是通过手工指定输入参数DEGREE来实现的,比如对表T1进行收集统计信息,同时指定并行度为4:

exec dbms_stats.gahter_table_stats(ownname=>'SCOTT',tabname=>'T1',cascade=>true,estimate_percent=>100,degree=>4);

当然,DBMS_STATS包也不是完美的,它与ANALYZE命令相比,其缺陷在于DBMS_STATS包只能收集与CBO相关的统计信息,而与CBO无关的一些额外信息,比如行迁移/行链接的数量(CHAIN_CNT)、校验表和索引的结构信息等,DBMS_STATS包就无能为力了。而ANALYZE命令可以用来分析和收集上述额外的信息,比如analyze table xxx list chained rows intoyyy可以用来分析和收集行迁移/行链接的数量,analyzeindex xxx validate structure可以用来分析索引的结构。

2、查看统计信息

前面介绍了如何收集统计信息,那如何查看这些统计信息呢?Oracle数据库的统计信息会存储在数据字典里,我们只需要去查询相关的数据字典就好了。如果有充裕的时间,现写SQL去查询数据字典里的统计信息也没有什么,但当我们真正碰到有性能问题的SQL时,通常会希望能在第一时间就收集到与目标SQL相关的各种统计信息,以便于在第一时间定位问题所在,这时候写SQL去查询数据字典就已经来不及了,所以我们需要事先准备好通用的查询统计信息的脚本,出问题的时候只需要运行一下脚本,就能在第一时间获取目标对象的所有统计信息了。

sosi脚本(Show Optimizer Statistics Information)就是这样一种脚本,国内的Oracle数据库专家也一直在用这个脚本,它源于MOS上的文章:SCRIPT - Select to show OptimizerStatistics for CBO (文档ID 31412.1),用法很简单,只需要运行一下sosi脚本,并指定要查看统计信息的表名就可以了。它支持分区表,显示分为三部分,分别是表级别的统计信息,分区级别的统计信息和子分区级别的统计信息。前面做实验用到的也是这个脚本。

 附件是sosi脚本可以下载使用。

参考《基于Oracle的SQL优化》

相关 [oracle 统计 信息] 推荐:

Oracle 统计信息

- - 数据库 - ITeye博客
--Oracle 优化器统计信息 Oracle优化器统计信息描述了关于数据库和相关对象的统计信息,当执行SQL查询时,优化器会使用这些统计信息估算出各种不同的执行计划的资源消耗, ,从而选择最高效的执行计划. 当统计信息缺失或者陈旧时,Oracle可能会选择错误的执行计划,导致SQL执行效率低下.

[Oracle] 统计信息和dbms_stats包

- - CSDN博客数据库推荐文章
Oracle基于CBO的优化器在生成执行计划时,很大程度上依赖于统计信息,你可以把CBO理解为一个复杂的数学模型,而统计信息是它最主要的输入,执行计划是输出,如果输入都不准确,输出还可能准确吗. 所有,统计信息是否及时有效对执行计划的好坏有着关键的影响. 2、dbms_stats包. Oracle里采用dbms_stats包分析统计信息(Analyze命令已过时,不建议使用),该包的使用方法,官方文档有详细说明( http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_stats.htm#i1036461),这里挑几个最常用的说说.

Oracle 统计信息收集 - Leohahah - 博客园

- -
查询表上一次收集统计信息的时间:. 收集统计信息主要有2种方法:. analyze可以用来收集表,索引,列以及系统的统计信息和直方图,以下为一些典型用法:. --收集所有的统计信息和直方图信息,包括表、列、索引. --收集emp表的统计信息,不含列、索引统计信息和直方图. --收集所有列的统计信息和直方图(超大表较耗资源,因为只要列中有非空值,那么就会收集这个列的统计信息和直方图).

Oracle 11g系统自动收集统计信息的一些知识

- - CSDN博客数据库推荐文章
---11g的是 周一到周五 22:00-2:00 周六周日 6:00-4:00. WINDOW_NAME          REPEAT_INTERVAL                                              DURATION. CLIENT_NAME                                                      STATUS.

Oracle里收集与查看统计信息的方法_DBA Fighting!的技术博客_51CTO博客

- -
Oracle数据库里的统计信息是这样的一组数据:它存储在数据字典里,且从多个维度描述了Oracle数据库里对象的详细信息. CBO会利用这些统计信息来计算目标SQL各种可能的、不同的执行路径的成本,并从中选择一条成本值最小的执行路径来作为目标SQL的执行计划. Oracle数据库里的统计信息可以分为如下6种类型:.

Oracle 收购 Ksplice

- feng823 - LinuxTOY
实现无需重启即可为 Linux 内核打安全补丁的 Ksplice 被 Oracle 收购. 在被收购前, Ksplice 为 Fedora, Ubuntu 免费提供该功能,对于 RHEL 和 CentOS 则需要订阅其产品. Oracle 表示将把 Ksplice 带来的零宕机安全更新功能添加到 Oracle 产品订阅服务中,同时停止对其他企业级 Linux 发行版的支持,将 Oracle Unbreakable Linux 打造成唯一具备零宕机安全更新功能的企业级 Linux 发行版.

Linux Ksplice,MySQL and Oracle

- Syn - DBA Notes
Oracle 在 7 月份收购了 Ksplice. 使用了 Ksplice 的 Linux 系统,为 Kernel 打补丁无需重启动,做系统维护的朋友应该明白这是一个杀手级特性. 现在该产品已经合并到 Oracle Linux 中. 目前已经有超过 700 家客户,超过 10 万套系统使用了 Ksplice (不知道国内是否已经有用户了.

oracle license计算

- Fenng - eagle's home
Oracle license的计算是基于CPU core的. 用core的数目乘以一个系数core factor就可以得到所需的oracle license的数目. 对于不同的CPU,core factor是不一样的,可以从oracle提供的这张列表中查到 Oracle Processor Core Factor Table.

Oracle Exadata初探

- - 技术改变世界 创新驱动中国 - 《程序员》官网
在我们看来,它是一个把硬件和软件根据合理的配置整合在一起的 Oracle数据库(在本文编写时是11gR2版本)平台. Exadata数据库机器包含了存储子系统,在存储层上运行着研发的新软件,这使得研发人员可以做一些在其他平台上无法完成的事情. 实际上,Exadata一开始是以一个存储系统形式诞生的,如果你跟参与研发此产品的人交谈,你经常会听到他们称存储组件为Exadata或者是SAGE (Storage Appliance for Grid Environments,网格环境存储设备),这是该产品研发项目的代码名称.

Oracle MySQL Or NoSQL续

- - Sky.Jian 朝阳的天空
接前面一篇,这里再将之前在“中国系统架构师大会”5周年的时候发布的纪念册“IT架构实录”上的一篇文章发出来,也算是前面博文中PPT的一个文字版解读吧. Oracle,MySQL 还是 NoSQL. 随着阿里系的“去IOE”运动在社区的宣传声越来越大,国内正在掀起一股“去xxx”的技术潮. 不仅仅是互联网企业,包括运营商以及金融机构都已经开始加入到这个潮流之中.