Optimizer统计信息管理介绍

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 的一部分运行，默认启用的在所有的预定义的维护窗口中运行。

如果对于一些原因自动优化程序统计信息收集被禁用，可以使用DBMS_AUTO_TASK_ADMIN包手动启用：

BEGIN

DBMS_AUTO_TASK_ADMIN.ENABLE(

client_name => 'auto optimizer stats collection'

, operation => NULL

, window_name => NULL

);

END;

/

如果采用手动管理方式收集统计信息，同样可以使用可以使用DBMS_AUTO_TASK_ADMIN包手动禁用作业：

BEGIN

DBMS_AUTO_TASK_ADMIN.DISABLE(

client_name => 'auto optimizer stats collection'

, operation => NULL

, window_name => NULL

);

END;

/

3.2. DBMS_STATS统计信息管理

对于不断变化的数据库对象，必须定期收集统计数据以便他们准确地描述数据库对象。Oracle推荐使用DBMS_STATS过程 包收集统计信息，并取代现在已经过时的统计信息收集命令ANALYZE 。DBMS_STATS 包包含超过 50 不同的过程，用于收集和管理统计，而且最重要的过程是 GATHER_ * _STATS 程序。这些过程可以用于收集和管理表、 列和索引的统计信息，必须使用对象的所有者或有任何具有系统特权的DBA 角色运行这些程序，以下是DBMS_STATS包中收集统计信息涉及到的过程。

名称	用途描述
GATHER_INDEX_STATS	收集特定用户下指定索引列的统计信息
GATHER_TABLE_STATS	收集特定用户指定表上表行，列和索引列的统计信息
GATHER_SCHEMA_STATS	收集特定用户所有对像的统计信息
GATHER_DICTIONARY_STATS	收集数据库所有数据字典统计信息
GATHER_DATABASE_STATS	收集数据库所有对象统计信息

3.2.1. 手动采集表统计信息

这些程序使用的参数是几乎相同，因此这里列举 GATHER_TABLE_STATS 过程的参数作为说明，GATHER_TABLE_STATS包过程用于收集表，分区，索引和列的统计信息。这个过程拥有15个不同的参数。我们在收集表的统计信息时，只需指定ownname和tabname这两个参数，过程包就可以运行。如果表是分区表还需要指分区名称。例如我们对住院费用记录表进行统计信息进行收集，使用以下方式就可以收集到该表的统计信息。

SQL> begin

2 dbms_stats.gather_table_stats(ownname => 'ZLHIS',

3 tabname => '药品库存');

4 end;

5 /

PL/SQL procedure successfullycompleted

在进行数据采集时我们有时会使用到其它输入参数，在这里我们对过程中的其它输入参数介绍。

l ESTIMATE_PERCENT

ESTIMATE_PERCENT参数确定用来计算统计信息行数的百分比，最准确的统计信息收集处理是收集表中的所有行。Oracle 11g使用一种新的采样算法，基于哈希值并提供准确的统计信息。这种新方法精度接近所有行（100%）样品，但顶多消耗10%样品的成本。ESTIMATE_PERCENT的默认值设置为AUTO_SAMPLE_SIZE，将使用这种新算法。GATHER_ * _STATS 程序。我们在对ZLHIS对象收集统计信息时，将ESTIMATE_PRECENT参数设置为较低的值，通常是10%的方式收集，这样做以确保将收集统计数据的结果迅速。当然为了数据库得到更准备统计信息。Oracle强烈建议从 Oracle 11g 起使用ESTIMATE_PRECENT参数的默认值收集统计信息，该参数的取值范围为取值范围[0.000001-100]。

示例：参数ESTIMATE_PERCENT=10以病人医嘱发送数据表数据10%的比例进行数据收集。

begin

dbms_stats.gather_table_stats(ownname => 'ZLHIS',

tabname => '病人医嘱发送',

estimate_percent=> 10,

method_opt => 'for all columns size skewonly',

force => true,

cascade => true,

degree => 4);

end;

l METHOD_OPT

这个参数最常见的功能就是控制直方图的收集方式，但实际上它的功能远不及此，它的实际功能如下所示：

ü 控制哪些列收集基本的统计信息

ü 收集直方图,

ü 收集扩展的统计信息

Method_opt 参数用法分为两个部分,如下图所示：

FOR ALL [indexed " hidden] columns这一部分控制着哪些列将会收集列的基本统计信息，目标列上的最小值，最大值，列上不同值的数量，空值的数量等等。系统默认值为FOR ALL COLUMNS，它将收集表上所有列(包括隐藏列)的基本的统计信息。指定FOR ALL INDEXED COLUMNS 只收集含有索引字段列的基本统计信息。一般不推荐使用这个选项值，因为在数据库环境中的所有 SQL语句所使用的字段，比如SELECT 后面的字段，WHERE后面字段，GROUP BY中的字段，并不只是会引用含有索引的字段。指定FOR ALL HIDDEN COLUMNS收集所有不可见字段基本统计信息，同样在收集统计信息时不推荐使用这个选项值。这个选项值通常只用于在一个所有列的统计信息都是准确的表中新增了一个或几个不可见或者说是虚拟的列，只需要收集这个或者这几个不可见列的统计信息，而不再重复去其他列的统计信息。

Size [size_clause]这一部分控制收集直方图的方式，SIZE 后面可以有以下选项：

AUTO Oracle自己决定根据列的统计信息(sys.col_usage$)以及列的数据倾斜程度(均匀分布程度)决定哪些列需要收集直方图。

INTEGER 指定收集直方图的桶数，桶数最小为 1最大为 254 (针对 11g及以前的版本， 12c后没有这个限制)。注意如果桶数为 1，即SIZE 1 意味着不建立直方图，如果已经有直方图的列则会删除该列的直方图。

REPEAT只在已经有直方图的列上重新收集直方图。REPEAT会确保在全局级别上对已经存在直方图的列重新收集直方图。一般不推荐使用这个选项，因为新的直方图使用的桶数将不能超过旧的直方图中的桶数。假设当前直方图中桶数为 5，当使用SIZE REPEAT重新收集直方图时，新的直方图使用的桶数将不能超过 5，这钟方式可能不会取得好的效果。

SKEWONLY 只在数据不均匀分布的列上收集直方图。

示例：参数METHOD_OPT = 'FOR ALL COLUMNS SIZESKEWONLY'收集病人医嘱分布不均匀列的直方图统计信息。

begin

dbms_stats.gather_table_stats(ownname => 'ZLHIS',

tabname => '病人医嘱发送',

estimate_percent => 10,

method_opt => 'for all columns size skewonly',

force => true,

cascade => true,

degree => 4);

end;

l DEGREE

DEGREE参数控制服务器并行收集统计数据的进程数。默认情况下，Oracle数据库中的所有表的DEGREE属性为1，我们可以更改这个参数值，加快统计数据的收集。当DEGREE设置值为DMBS_STATS.AUTO_DEGREE，Oracle根据并行服务器进程数参数（PARALLEL_MAX_SERVERS）值自动分配进程数收集统计信息。对一个数据量较小的对象，使用缺省值1即可。对大数据对象的可以使用DBMS_STAT.DEFAULT_DEGREE参数由数据库自动分配并行度。

示例：DEGREE=4以4个进程收集病人医嘱发送记录的统计信息。

begin

dbms_stats.gather_table_stats(ownname => 'ZLHIS',

tabname => '病人医嘱发送',

estimate_percent => 10,

method_opt => 'for all columns size skewonly',

force => true,

cascade => true,

degree => 4);

end;

l CASCADE

在系统上没有执行过索引统计信息收集。使用CASCADE选项相当于在除了收集表和列统计并同时运行 GATHER_INDEX_STATS收集索引统计信息，使用参数DBMS_STATS.AUTO_CASCADE由ORACLE确定是否收集索引统计信息要，参数设置为TRUE强制收集所有索引统计信息，在缺省情况下CASCADE参数值为FALSE。

示例：强制收集病人医嘱发送上的所有索引统计信息。

begin

dbms_stats.gather_table_stats(ownname => 'ZLHIS',

tabname => '病人医嘱发送',

estimate_percent => 10,

method_opt => 'for all columns size skewonly',

force => true,

cascade => true,

degree => 4);

end;

3.2.2. 锁定和解锁一个表统计信息

在某些情况下我们需要锁定一个特定表的统计息不被更新，以保证执行计划的准确性，我们需要使用DBMS_STATS.LOCK_TABLE_STATS锁定统计信息。要锁定一个表的统计信息我们只需要传入表的拥有者和表名就可以锁定该用户表的统计信息。反之我们要解锁一个锁定的用户对象使用DBMS_STAT.UNLOCK_TABLE_STATS解锁锁定的统计信息。

示例:锁定ZLHIS用户下药品收发记录统计信息。

begin

DBMS_STATS.LOCK_TABLE_STATS(ownname =>'ZLHIS' , tabname =>'药品收发记录' );

end;

示例:解锁被锁定的ZLHIS用户下药品收发记录统计信息。

begin

DBMS_STATS.UNLOCK_TABLE_STATS(ownname =>'ZLHIS' , tabname =>'药品收发记录' );

end;

3.2.3. 删除统计信息

有的时候我们在收集了统计信息却发现新的统计信息比旧的统计信息还要差，因此我们需要将统计信息删除重新收集，以下列出删除统计信息相关过程。

名称	用途描述
DELETE_INDEX_STATS	删除特定用户下指定索引列的统计信息
DELETE_TABLE_STATS	删除特定用户指定表上表行，列和索引列的统计信息
DELETE _SCHEMA_STATS	删除特定用户所有对像的统计信息
DELETE _DICTIONARY_STATS	删除数据库所有数据字典统计信息
DELETE _DATABASE_STATS	删除数据库所有对象统计信息

示例：删除ZLHIS用户药品收发记录表统计信息

begin

DBMS_STATS.delete_table_stats(ownname =>'ZLHIS' , tabname =>'药品收发记录' )

end;

3.3. 与统计相关的视图

所有与数据表，列和索引相关的统计信息都可以通过Oracle数据库字典视图查询，常使用的视图有：

名称	用途描述
DBA_TAB_COLUMNS ALL_TAB_COLUMNS USER_TAB_COLUMNS	DBA视图描述数据库中所有表列。用户视图仅限于由用户拥有的表，在这些视图中统计信息有关的列由DBMS_STATS 包或ANALYZE语句生成的统计信息。
DBA_TAB_STATISTICS ALL_TAB_STATISTICS USER_TAB_STATISTICS	描述表的统计信息
DBA_INDEXES ALL_INDEXES USER_INDEXES	DBA 视图描述在数据库中的所有表上的索引。所有视图都描述在用户可访问的所有表上的索引。用户视图仅限于由用户拥有的索引。在这些视图中的统计信息有关的列包含由DBMS_STATS 包或ANALYZE语句生成的统计

在这里我们使用USER_TAB_STATISTICS视图为例，先了解视图USER_TAB_STATISTICS字段的含义后，使用该视图查看表药品收发记录统计信息。

USER_TAB_STATISTICS

例名	描述
TABLE_NAME	表名
PARTITION_NAME	分区表名
PARTITION_POSITION	分区位置
SUBPARTITION_NAME	子分区表名
SUBPARTITION_POSITION	子分区位置
OBJECT_TYPE	对象类型（表，分区，子分区）
NUM_ROWS	对象中的行记录数
BLOCKS	对象使用的数据块数
EMPTY_BLOCKS	对象中的空块数
AVG_SPACE	对象中的平均可用空间
CHAIN_CNT	对象中的行连接数
AVG_ROW_LEN	对象中行记录的平均长度
AVG_SPACE_FREELIST_BLOCKS	在一个自由列表的所有块的平均可用空间
NUM_FREELIST_BLOCKS	在一个自由列表的块的数量
AVG_CACHED_BLOCKS	在缓冲区高速缓存中的平均块数
AVG_CACHE_HIT_RATIO	平均缓存对象的命中率
SAMPLE_SIZE	采样样本
LAST_ANALYZED	最后一次表分析时间
GLOBAL_STATS	没有合并的分区计算的统计？
USER_STATS	统计信息是否为用户输入
STATTYPE_LOCKED	锁定统计信息类型
STALE_STATS	统计信息是否过期

例如，我们现在查询药品收发记录的相关统计信息的行数，表分析的采样样本，统计信息是否被锁定，统计信息是否过期这几种情况，可以使用字段NUM_ROWS，SAMPLE_SIZE，LAST_ANALYZED，STATTYPE_LOCKED，STALE_STATS获取相关信息。

4. 结束语

通过我们对统计的了解，已经知道统计信息对Oracle是非常重要的，它会收集数据库中对象的详细信息，并存储在相应的数据字典里。根据这些统计信息，优化器可以对每个SQL去选择最好的执行计划，统计信息收集作业由Oracle定期自动收集，但某些特殊情况下还需要我们进行手动维护和管理，例如我们产品升级后及时手动采集数据库的统计信息是非常有必要的。