1. 前言

在我们的日常维护中受理一些一直以来运行得很好的系统，突然有一天用户反馈没有做任何操作，系统的某个功能模块或者是某个报表以前只需要几秒，但现在需要几分钟或更长的时间都没有返回结果。在这样的情况下，我们通常会分析SQL语句，会发现这个SQL的执行计划已经发生改变，在硬件环境未发生变化的情况下，执行计划发生变化多数原因是由于表的统计信息发生了变化，原本使用的某个索引突然间没有被使用，或者使用了较差的索引，这都是由于统计信息不准备确引起的，因此我们有必要了解统计信息的管理和维护，更好的优化SQL和性能问题处理。

2. 什么是统计信息

统计信息是描述该数据库中的数据和数据库中的对象的集合，这些统计数据所使用的优化选择对每个SQL语句的最佳执行计划。统计信息存储在数据字典，并且可以使用数据字典视图来访问诸如USER_TAB_STATISTICS。优化统计数据是从通过V $视图中显示的性能统计信息不同。在V$视图中的信息涉及该系统的状态和SQL负载上执行它。

Oracle 10g之后，Query Optimizer就已经将CBO作为默认优化器，并且Oracle官方不再支持10G以前的RBO服务。但是，通过优化器参数OPTIMIZER_MODE，我们可以控制Oracle优化器生成不同模式下的执行计划。数据库中的对象可以是不断变化的，统计数据必须定期更新，以便准确地描述这些数据库对象，统计都是由Oracle自动地或手动使用DBMS_STATS包完成，DBMS_STATS还提供了用于管理统计程序。可以保存和恢复副本统计。可以从一个系统导出统计数据到另一个系统。例如，你可以从一个生产数据库将统计信息导出到一个测试系统。此外，我们还可以锁定统计信息，防止这些统计数据的变化统计。

图1存储统计信息字典视图

2.1. 表统计信息

表统计信息包括表中行的数量信息，表使用数据块的数量，以及行在表中的平均行长度。优化器使用这些信息，结合其它统计信息，以计算各种操作的执行计划成本，并估计行的操作将产生的数量。例如，一个表存取的成本是使用数据块和参数DB_FILE_MULTIBLOCK_READ_COUNT的值进行计算，表的统计信息可以在字典视图USER_TAB_STATISTICS查看。

2.2. 列统计信息

列统计信息包括列上不同值的数量，以及在该列中的最小和最大值。可以在字典视图USER_TAB_COL_STATISTICS查看列统计信息。优化器使用列统计信息和表统计信息的行数估计SQL操作返回的结果。例如，如果一个表有100条记录，并且访问表列有10个不同的相等谓词，那么优化器，假定数据分布是均匀的，估计的基数为表中的行数不同的值除以该列，即100 / 10 = 10。

图2使用基本表和列统计基数计算

2.3. 索引统计信息

索引统计信息提供索引中不同值的数据信息，叶块中的索引数，索引的深度和集群因子数目的数量。优化程序会使用这些信息连同其他统计信息来确定索引访问的成本。例如优化器使用B树索引，将会使用叶块数和表统计中的num_rows确定索引范围扫描的成本。

2.4. 直方图统计信息

直方图是一种对被管理对象某一方面质量进行管理的描述工具，在Oracle中自然它也是对Oracle中某个对象质量的描述工具，这个对象就是Oracle中最重要的东西――“数据”。

在Oracle中直方图是一种对数据分布质量情况进行描述的工具。它会按照某一列不同值出现数量多少，以及出现的频率高低来绘制数据的分布情况，以便能够指导优化器根据数据的分布做出正确的选择。在某些情况下，表的列中的数值分布将会影响优化器使用索引还是执行全表扫描的决策。当WHERE子句的值具有不成比例数量的数值时，将出现这种情况，使得全表扫描比索引访问的成本更低。这种情况下如果WHERE子句的过滤谓词列之上有一个合理的，正确的直方图，将会对优化器使用索引还是全表扫描发挥巨大的作用，使得SQL语句执行成本最低从而提升性能。

在分析表或索引时，直方图用于记录数据的分布。通过获得该信息，基于成本的优化器就可以决定使用将返回少量行的索引，而避免使用基于限制条件返回许多行的索引。直方图的使用不受索引的限制，可以在表的任何列上构建直方图。

构造直方图最主要的原因就是帮助优化器在表中数据严重偏斜时做出更好的规划：例如，如果一到两个值构成了表中的大部分数据(数据偏斜)，相关的索引就可能无法帮助减少满足查询所需的I/O数量。创建直方图可以让基于成本的优化器知道何时使用索引才最合适，或何时应该根据WHERE子句中的值返回表中80％的记录。

通常情况下在以下场合中建议使用直方图：

1) 当Where子句引用了列值分布存在明显偏差的列时：当这种偏差相当明显时，以至于 WHERE子句中的值将会使优化器选择不同的执行计划。这时应该使用直方图来帮助优化器来修正执行路径。（注意：如果查询不引用该列，则创建直方图没有意义）

2) 当列值导致不正确的判断时：这种情况通常会发生在多表连接时，例如，假设我们有一个五项的表联接，其结果集只有 10行。Oracle 将会以一种使第一个联接的结果集（集合基数）尽可能小的方式将表联接起来。通过在中间结果集中携带更少的负载，查询将会运行得更快。为了使中间结果最小化，优化器尝试在 SQL执行的分析阶段评估每个结果集的集合基数。在偏差的列上拥有直方图将会极大地帮助优化器做出正确的决策。如优化器对中间结果集的大小做出不正确的判断，它可能会选择一种未达到最优化的表联接方法，因此向该列添加直方图经常会向优化器提供使用最佳联接方法所需的信息。

Oracle利用直方图来提高非均匀数据分布的选择率和技术的计算精度。但是实际上Oracle会采用另种不同的策略来生成直方图：其中一种是针对包含很少不同值的数据集；另一种是针对包含很多不同的数据集。Oracle会针对第一种情况生成频率直方图，针对第二种情况生成高度均衡直方图。通常情况下当BUCTET < 表的NUM_DISTINCT值得到的是HEIGHT BALANCED（高度平衡）直方图，而当BUCTET > 表的NUM_DISTINCT值的时候得到的是FREQUENCY（频率）直方图。

3. 管理统计信息

Oracle 建议启用自动优化程序统计信息收集。在这种情况下，数据库会自动收集没有被收集过的统计信息或过时的统计信息。如果一个新表需要收集统计信息，数据库会收集表和关联的索引。

自动优化统计信息收集程序运行DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC调用，数据统计信息收集过程DBMS_STATS.GATHER_DATABASE_STATS，该过程使用GATHER AUTO选项收集数据库表的信息和索引信息，使统计信息为最新状态。GATHER_DATABASE_STATS_JOB_PROC过程进行统计信息收集，过程会优先数据库对象统计收集，所以在收集统计信息期间要对数据库的对象更新操作时，需要关闭自动统计收集作业。

3.1. 启用和禁用自动统计信息收集

Oracle自动维护任务基础结构（称为 AutoTask）调度程序会在维护窗口中自动运行该任务。默认情况下，每周六的晚上，自动优化程序统计信息收集作为 AutoTask 的一部分运行，默认启用的在所有的预定义的维护窗口中运行。