Oracle 12c数据库优化器统计信息收集的最佳实践（一）

原文链接 http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bp-for-stats-gather-12c-1967354.pdf 译者 刘金龙

导 语

Oracle优化器会为SQL语句产生所有可能的访问路径（执行计划），然后从中选择一条COST值最低的执行路径，这个cost值是指oracle估算执行SQL所消耗的资源。为了让优化器能够精确计算的每一条执行计划的COST值，这就需要被执行SQL语句所需访问的所有对象（表和索引等）和系统有必要的描述信息。

这些必要的信息通常被称为optimizer statistics（优化器统计信息）。理解和管理优化器统计信息是优化SQL执行的关键。知道何时、如何以及快速的方式收集优化器统计信息对于维持系统良好性能是至关重要的。本文将详细讨论，在Oracle常见的场景中何时以及如何收集统计信息，文章大致分如下几个部分：

• 如何收集统计信息
• 何时收集统计信息
• 提高统计信息质量
• 快速收集统计信息
• 何时不用收集统计信息
• 收集其他类型统计信息

如何收集统计信息

在Oracle中优选的方式是统计信息自动收集。如果系统已经有完善的手动收集统计信息程序，那么可以优选手动统计信息收集。无论选择哪种收集方式，首先需要考虑的是默认的全局参数设置是否满足您的需求。

在大多数情况下这些默认参数是能够满足的，但是如果我们想根据自己的系统的实际情况作出修改，那么我们可以通过设置SET_GLOBAL_PREFS.参数值。一旦我们选择这样做，我们可以通过使用DBMS_STATS“setpreference”工具覆盖默认设置。例如，使用SET_TABLE_PREFS参数设置表统计信息收集时使用incremental方式或者收集直方图信息。使用这种方式，我们将会指定哪些指定统计信息被默认收集，而不需要在收集统计信息的时候调整参数。我们可以自由的使用默认参数收集表/用户/数据库级别的统计信息，并且确定这些统计信息收集策略已经被使用。更重要的是，我们可以在自动和手动统计信息收集之间自由切换。

自动统计信息收集

oracle数据库需要收集那些缺少或者已经“stale”过期统计信息的对象统计信息。这是在预定义的维护窗口中执行的自动任务完成的。对于 oracle内部优先级高的对象，这些对象的统计信息需要最先被收集更新。

自动统计信息收集job会使用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC过程，该过程使用和DBMS_STATS.GATHER_*_STATS 过程相同的默认参数设置。这些默认设置在大多数场景是足够的。然而，某些场景下需要更改其中一个或者多个默认参数值，我们可以使用DBMS_STATS.GATHER_*_STATS 过程完成设置。参数值应该在尽可能小的范围内进行更改，最好是以每个对象为基础。例如，如果我们想修改指定表的统计信息过期阈值，我们希望阈值由原来的10%更改为5%，我们可以使用DBMS_STATS.SET_TABLE_PREFS过程改变指定表的STALE_PERCENT属性。

·execdbms_stats.set_table_prefs(user,'SALES','STALE_PERCENT','5')

在修改完成后我们可以使用DBMS_STATS.GET_PREFS查看属性值修改情况。需要三个选项，参数名、用户名、表名：

selectdbms_stats.get_prefs('STALE_PERCENT',user,'SALES') stale_percent from dual;STALE_PERCENT
------------- 
5

Setting DBMS_STATS Preferences

如上所述，我们可能需要通过DBMS_STAT过程设置指定对象和表在自动统计信息收集时候的收集策略。我们可以通过DBMS_STATS.GATHER_*_STATS 过程自定义收集策略，但是oracle还是推荐的方法是使用 DBMS_STATS.SET_*_PREFS过程进行设置。

参数可以在表级别、对象级别、数据库或者全局级别被修改（AUTOSTATS_TARGET和 CONCURRENT只能在全局级别被更改）：

SET_TABLE_PREFS 
SET_SCHEMA_PREFS
SET_DATABASE_PREFS 
SET_GLOBAL_PREFS

通常情况下，我们最常修改的参数是ESTIMATE_PERCENT（控制采样百分比）和METHOD_OPT（控制直方图信息的创建），但是估算的百分比现在已经比默认的值更好，由于本节后面所述的原因而保留其缺省值

对于表的统计信息收集时，允许DBMS_STATS.GATHER_*_STATS过程修改SET_TABLE_PREFS过程指定的参数的默认值。

在使用DBMS_STATS.GATHER_*_STATS过程收集指定对象所有已存在的表的统计信息时，我们可以使用SET_SCHEMA_PREFS过程修改默认的参数配置。这个过程实际上调用SET_TABLE_PREFS过程来为指定对象的所有表设置默认参数。所以当我们使用该过程设置完成后，用户新创建的表收集统计信息使用的参数是依据GLOBAL配置指定的参数。

同样，SET_DATABASE_PREFS过程可以修改使用DBMS_STATS.GATHER_*_STATS过程收集用户定义对象统计信息时候的默认参数。事实上这个过程调用的也是SET_TABLE_PREFS过程来为指定对象的所有表设置默认参数。对于默认参数修改完后创建的对象，他会选择GLOBAL过程指定的默认参数配置。如果设置ADD_SYS参数为TRUE，那么Oracle自己的用户（SYS，SYSTEM等）也可以被包括进去。

SET_GLOBAL_PREFS过程可以指定所有没有设置表优先级对象的统计信息收集过程的默认参数，在使用SET_GLOBAL_PREFS过程修改完默认参数后，所有的新建对象都会使用修改完后的默认收集参数，除非使用GATHER_*_STATS过程明确指定了参数或者设置了表的优先级。

使用DBMS_STATS.GATHER_*_STATS收集统计信息的时候，以上过程参数设置是分优先级别。

oracle 12CR2引入了新的影响优先级的参数

REFERENCE_OVERRIDES_PARAMETER.当这个参数被设置成TRUE，那么优先级的顺序就会发生变化。如下图所示。

ESTIMATE_PERCENT

在收集统计信息过程中，可以使用ESTIMATE_PERCENT参数控制统计数据行的百分比。当表中的所有行都被统计（即100%采样），我们将会得到最准确的统计信息。Oracle数据库在11g引入了一个新的采样算法， hash-based算法来实现行信息统计，使用10%的采样频率采集到的信息精确度接近100%采样频率。在使用dbms_stats gather_ * _stats过程指定estimate_percent设置auto_sample_size（默认）时新的算法就会被启动。在Oracle数据库11g之前，数据库管理员往往设置estimate_precent参数为很低的值确保统计信息能被快速收集。oracle强烈建议在从11g开始保持默认参数auto_sample_size。这一点尤为重要，因为12C开始引入了新的直方图类型，混合和Top-Frequency，这些直方图只能在参数保持默认的auto_sample_size才能被收集。

现在很多的系统还保留着旧的统计信息收集脚本（手动设置百分比）。所以当数据库升级到12CR2后，可以考虑使用preference_overrides_parameter参数覆盖手动统计信息收集使用的默认参数。或者直接修改统计信息收集脚本。

METHOD_OPT

METHOD_OPT参数控制柱状图是否在收集过程中被创建。柱状图是oracle数据库中一类特殊类型的列统计数据，用户提供表中列数据分布的详细信息。默认情况下METHOD_OPT参数是'FOR ALL COLUMNS SIZE AUTO'，这种情况下当表中的列被用在等值或者范围where条件中比如WHERE col1= 'X'或者WHERE col1 BETWEEN 'A' and 'B'，并且这列数据是倾斜的。那么oracle就会对这些列进行收集直方图信息。优化器知道那些列用户查询谓词因为这些信息会被存储在数据字典表SYS.COL_USAGE$中。

一些DBA更倾向于自己控制直方图的创建。Oracle推荐使用的方式是通过set_table_prefs进行设置。例如，你可以人为指定只为SALES表的其中两列COL1和COL2创建直方图。

begin 
dbms_stats.set_table_prefs( 
user, 
'SALES',
'method_opt',
'for all columns size 1 for columns size 254 col1col2');
end; 
/

也可以指定列必须有直方图（COL1和COL2），此外，允许优化器决定是否在其他列上创建额外的直方图：

begin 
dbms_stats.set_table_prefs( 
user, 
'SALES',
'method_opt',
'for all columns size auto for columns size 254 col1col2');
end; 
/

如果将METHOD_OPT属性设置成'FOR ALL COLUMNS SIZE 1'.那么直方图将会被禁止创建。例如，可以修改DBMS_STATS全局属性中的METHOD_OPT使直方图信息不被创建。

begin 
dbms_stats.set_global_prefs(
'method_opt',
'for all columns size 1'); 
end;
/

我们也可以删除某些列上不需要的直方图信息。使用如下方式，DBMS_STATS.DELETE_COLUMN_STATS然后指定col_stat_type为‘HISTOGRAM’。

手工统计信息收集

如果已经有一个完善的统计信息收集过程或者因为某些原因想要对特定用户方案禁用自动统计信息收集而只保留收集数据字典的统计信息.可以使用dbms_stats.set_global_prefs过程来改变autostats_target参数为oracle来替代auto.

execdbms_stats.set_global_prefs('autostats_target','oracle')；

手动收集统计信息过程中应该使用dbms_stats包,用它来替找过时的analyze命令.dbms_stats包提供多个dbms_stats.gather_*_stats过程来收集用户方案对象,数据字典和固定对象的统计信息.理想情况下，除了模式名称和对象名之外，应该让这些过程的所有参数都默认为默认值。在大多数情况下默认和自适应参数设置是足够的：

exec dbms_stats.gather_table_stats('sh','sales')

正如上面所说，如果必须要修改统计参数默认值，那么使用DBMS_STATS.SET_*_PREF过程在最小影响范围下进行修改。

Pending Statistics

当我们决定修改dbms_stats_gather_*_stats过程的参数缺省值时，oracle强烈建议在生产系统中修改之前先验证这些变更.如果没有一个完整的测试环境，那么应该使用pending statistics.使用pending statistics代替常用的数据字典表,统计信息存储在pending表中，以便在系统发布和使用之前能够以受控的方式进行启用和测试.为了激活pending统计信息的收集需要对希望创建pending统计信息的对象使用dbms_stats.set_*_prefs过程将参数publish从缺省值true改变false.下面的例子中对sh用户下的sales表启用pending统计信息并对sales表收集统计信息.

execdbms_stats.set_table_prefs('sh','sales','publish','false')

通过将publish设置为false来启用pending统计信息。

正常的收集对象统计信息

exec dbms_stats.gather_table_stats('sh','sales')

对于这些对象收集的统计信息可以查询*_tab_pending_stats视图来显示:

可以通过一个alter session命令来设置初始化参数optimizer_use_pending_stats为true来使用pending统计信息.在启用pending统计信息之后任何在该会话运行的sql将使用这些新的没有发布的统计信息.对于其他会话中所访问的表没有pending统计信息时优化器将使用标准数据字典表中的当前统计信息.当验证这些pending统计信息之后可以使用

dbms_stats.publish_pending_stats过程来发布.

exec dbms_stats.publish_pending_stats('sh','sales')