Oracle⾥收集与查看统计信息的⽅法
Oracle数据库⾥的统计信息是这样的⼀组数据：它存储在数据字典⾥，且从多个维度描述了Oracle数据库⾥对象的详细信息。CBO会利⽤这些统计信息来计算⽬标SQL各种可能的、不同的执⾏路径的成本，并从中选择⼀条成本值最⼩的执⾏路径来作为⽬标SQL的执⾏计划。
Oracle数据库⾥的统计信息可以分为如下6种类型：
表的统计信息
索引的统计信息
列的统计信息
系统统计信息
数据字典统计信息
内部对象统计信息
表的统计信息⽤于描述Oracle数据库⾥表的详细信息，它包含了⼀些典型的维度，如记录数、表块(表⾥的
数据块)数量、平均⾏长度等。
索引的统计信息于描述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命令就可以⽤来收集表、索引、列的统计信息，以及系统统计信息。
典型⽤法如下：
zx@ORCL>create table t2 as select * from dba_objects;
Table created.
zx@ORCL>create index idx_t2 on t2(object_id);
Index created.
zx@ORCL>analyze index idx_t2 delete statistics;
执⾏sosi脚本，从输出内容可以看到表T2、表T2的列和索引IDX_T2均没有相关的统计信息
zx@ORCL>select count(*) from t2;
COUNT(*)
----------
86852
只对表T2收集统计信息，并且以估算模式，采样的⽐例为15%：
zx@ORCL>analyze table t2 estimate statistics sample 15 percent for table;
Table analyzed.
再次执⾏sosi脚本，可以看出现在只⽤表T2有统计信息，表T2的列和索引IDX_T2均没有相关的统计信息。⽽且因为采⽤的是估算模式所以估算结果和实际结果并不⼀定会完全匹配，⽐如表T2的实际数量与估算出的数量不⼀致。
只对表T2收集统计信息，并且以计算模式：
zx@ORCL>analyze table t2 compute statistics for table;
Table analyzed.
再次执⾏sosi脚本，可以看出现在只⽤表T2有统计信息，表T2的列和索引IDX_T2均没有相关的统计信息。⽽且因为采⽤的是计算模式，计算模式会扫描⽬标对象的所有数据，所以统计结果和实际结果是匹配的。
对表T2收集完统计信息后，现在对表T2的列OBJECT_NAME和OBJECT_ID以计算模式收集统计信息：
zx@ORCL>analyze table t2 compute statistics for columns object_name,object_id;
Table analyzed.
再次执⾏sosi脚本，可以看出，现在列OBJECT_NAME和OBJECT_ID确实已经有统计信息了
注：在崔华⽼师的《基于Oracle的SQL优化》⼀书中提到T2原有的统计信息已经被抹掉了，也就是说对同⼀个对象⽽⾔，新执⾏的ANALYZE命令会抹掉之前ANALYZE的结果。但是在我实际的执⾏结果是表T2原有的统计信息没有被抹掉。我⽤到的环境是10.2.0.4和11.2.0.4，暂时没有11.2.0.1的环境。
可以使⽤如下的命令同时以计算模式对表T2和列OBJECT_NAME、OBJECT_ID收集统计信息：
zx@ORCL>analyze table t2 compute statistics for table for columns object_name,object_id;
Table analyzed.
再次执⾏sosi脚本，可以看到表T2和列OBJECT_NAME、OBJECT_ID上都有统计信息了。
使⽤如下命令可以以计算模式收集索引IDX_T2的统计信息
zx@ORCL>analyze index idx_t2 compute statistics;
Index analyzed.
再次执⾏sosi脚本，从输出可以看到，现在索引IDX_T2已经有了统计信息，并且之前收集的表T2和列OBJECT_NAME、OBJECT_ID上的统计信息并没有被抹掉，这是因为我们刚才执⾏的ANALYZE命令和之前执⾏的ANALYZE命令针对的不是同⼀个对象。
使⽤如下命令可以删除表T2、表T2的所有列及表T2的所有索引的统计信息：
zx@ORCL>analyze table t2 delete statistics;
Table analyzed.
再次执⾏sosi脚本，从输出可以看到，刚才收集的表T2、表T2的列OBJECT_NAME、OBJECT_ID以及索引IDX_T2的统计信息已经全部被删除了。
如果想⼀次性以计算模式收集表T2、表T2的所有列和表T2上的所有索引的统计信息，执⾏如下的语句就可以了：
zx@ORCL>analyze table t2 compute statistics;
Table analyzed.
再次执⾏sosi脚本，从输出可以看到，现在表T2、表T2的所有列和索引IDX_T2的统计信息都有了。
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%：
zx@ORCL>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均没有相关的统计信息。⽽且因为采⽤的估算模式，所以估算结果和实际结果并不⼀定会完全匹配。
需要注意的是，这⾥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,metho
d_opt=>'FOR
TABLE',cascade=>false);
zx@ORCL>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均没有相关的统计信息。⽽且因为采⽤的是计算模式，计算模式会扫描⽬标对象的所有数据，所以统计结果和实际结果是匹配的。
对表T2收集完统计信息后，现在我们来对表T2的列OBJECT_NAME、OBJECT_ID以计算模式收集统计信息(不收集直⽅图)：
zx@ORCL>exec dbms_stats.gather_table_stats(ownname=>'ZX',tabname=>'T2',estimate_percent=>100,method_opt=>'for columns size 1 object_name,o
PL/SQL procedure successfully completed.
执⾏sosi脚本，从输出内容可以看出，现在表T2的列OBJECT_NAME、OBJECT_ID上都有统计信息了，并且Oracle还会同时收集表T2上的统计信息(注意，这和ANALYZE命令有所区别)。
使⽤如下命令可以以计算模式收集索引IDX_T2的统计信息
zx@ORCL>exec dbms_stats.gather_index_stats(ownname=>'ZX',indname=>'IDX_T2',estimate_percent=>100);
PL/SQL procedure successfully completed.
执⾏sosi脚本，从输出内容可以看出，现在索引IDX_T2已经有了统计信息。
使⽤如下命令可以删除表T2、表T2的所有列及表T2的所有索引的统计信息：
zx@ORCL>exec dbms_stats.delete_table_stats(ownname=>'ZX',tabname=>'T2');
PL/SQL procedure successfully completed.
执⾏sosi脚本，从输出内容可以看出，表T2、表T2的所有列及表T2的所有索引的统计信息已经全部被删
除了。
如果想⼀次性以计算模式收集表T2、表T2的所有列及表T2的所有索引的统计信息，执⾏如下语句就可以了
zx@ORCL>exec dbms_stats.gather_table_stats(ownname=>'ZX',tabname=>'T2',estimate_percent=>100,cascade=>true);
PL/SQL procedure successfully completed.
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优化》

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。