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

导语

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')

何时收集统计信息

为了选择最佳执行计划，优化器必须可以获得有代表性的统计信息。有代表性的统计数据不必是最新的，而是一组能够帮助优化器确定执行计划中每个操作所能返回的行数。

自动统计信息收集任务

Oracle会在预定义维护窗口期间 (工作日10pm 到2am 和周末6am 到2am )收集数据库中所有缺失统计信息或者统计信息过期对象的统计信息，您可以在Oracle企业管理器或使用DBMS_SCHEDULER和DBMS_AUTO_TASK_ADMIN软件包来更改维护窗口。

请点击此处输入图片描述

图3: 更改自动收集统计信息作业运行时间的维护窗口

如果你已经有一个行之有效的统计收集程序，或者您想要禁用自动收集统计信息任务，您可以使用如下命令:

begin
dbms_auto_task_admin.disable(
client_name=>'auto optimizer stats collection',
operation=>null,
window_name=>null);
end;
/

手工统计收集

如果您打算手动维护优化器的统计信息，则需要确定何时去收集统计信息。基于过期的信息您可以决定什么时候统计信息应该被收集，您可以根据统计信息的失效性确定何时收集统计信息，就像自动作业一样，或者基于您的环境中新数据加载的时间。如果基本的数据没有明显变化，则不建议重新收集统计信息，因为这将不必要地浪费系统资源。

如果数据仅在预先定义的 ETL 或 ELT 作业期间加载到您的环境中, 则可以将统计信息收集操作安排为此过程的一部分。

在线收集统计信息

在Oracle Database 12 c中，在线收集统计信息"piggybacks"作为直接路径数据加载操作的一部分进行收集, 例如, 像使用CTAS的方式创建表，以及IAS方式插入数据。收集统计数据作为数据加载操作的一部分，这意味着不需要额外的全表扫描，就可以在加载数据后立即提供统计信息。

请点击此处输入图片描述

图4:在线收集统计信息为新创建的sales2表提供表和列的统计信息收集

在线收集统计信息并不会收集直方图和索引统计信息，因为这些种类的统计信息需要额外的数据扫描，这可能在数据加载时对性能产生较大影响。

如果要收集直方图或者索引的统计信息，而不重新收集列的基本统计信息，请使用 DBMS_STATS.GATHER_TABLE_STATS 中新的选择”OPTIONS”参数设置成GATHER AUTO。

请注意，出于性能原因，GATHER AUTO在生成直方图时，使用行中的样本数据而不是表中的所有数据。

请点击此处输入图片描述

图5 : 设置选项为GATHER AUTO来创建 SALES2 表上的直方图, 而不涉及基本统计信息

注意列“HISTOGRAM_ONLY”指示在不重新收集列基本统计信息的情况下收集了直方图。有两种方法确认在线收集统计信息是否发生: 一种方式是检查执行计划，查看执行计划中是否出现”OPTIMIZER STATISTICS GATHERING”，另外一种方式是查看USER_TAB_COL_STATISTICS表中notes字段的状态是否为stats_on_load。

请点击此处输入图片描述

图6:在线统计信息收集操作的执行计划

在线统计信息收集为了减少对直接路径加载时性能上的影响，只有在被加载的对象为空时在线统计信息收集才会被触发。要确保在加载现有表的新分区时进行在线收集统计信息，请使用扩展语法明确指定分区。在这种情况下, 将创建分区级别统计信息, 但不会更新全局级别 (表级别) 统计信息。如果在分区表上启用增量统计信息，则会在数据加载操作中创建”synopsis”。

可以使用HINT: NO_GATHER_OPTIMIZER_STATISTICS来禁用单条SQL语句去使用在线收集统计信息。

增量统计和分区交换数据加载

对分区表的统计信息收集包括表级别(global)和(sub)分区级别的统计信息。如果分区表的”incremental”首选项设置为true，则DBMS_STATS.GATHER_*_STATS参数中GRANULARITY包含global，ESTIMATE_PERCENT设置成AUTO_SAMPLE_SIZE，Oracle将只扫描那些添加或修改的分区，而不是整个表，从而准确的得出所有全局级别的统计信息。增量全局统计信息通过存储表中每个分区的“synopsis”来工作，“synopsis”是用于该分区和分区中的列的统计元数据，从而消除了扫描整个表的需要。将分区级别统计信息和每个分区的“synopses”聚合，将准确地生成全局统计信息，从而无需扫描整个表。

当新分区添加到表中时，您只需要收集新分区的统计信息，使用新的分区“synopsis”和现有分区的“synopses”, 将自动、准确地计算表级别的统计信息。

请注意，当启用增量统计信息时，分区统计信息不会从子分区统计信息进行聚合计算。

如果您正使用分区交换并希望利用增量统计信息，则需要在非分区表上设置DBMS_STATS首选项INCREMENTAL_LEVEL，以确定它将在分区交换过程中使用。设置INCREMENTAL_LEVEL为TABLE，当在其上收集统计信息，Oracle会自动创建“synopsis”，此表级的“synopsis”在分区交换后会变成分区级别的“synopsis”。

但是，如果一天当中在您的环境存在很多插入少量数据的在线事务，您需要确定统计数据何时过期，然后触发自动统计数据收集任务。

如果您计划依赖user _ tab _ statistics中的stale_stats列来确定统计信息是否过期，则应注意此信息仅按每日更新。

如果您需要及时地了解在您的表上发生了哪些DML, 您需要查看USER_TAB_MODIFICATIONS 表, 其中列出了在每个表上发生的INSERT、UPDATE和DELETE的数量, 该表是否已被TRUNCATED (TRUNCATE column) 并自行计算是否过期。

再次，您应该注意到此信息是自动从内存中更新而来，如果需要最新的信息，则需要使用DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO来手动刷新信息。

防止“超出范围”条件

无论您使用自动统计信息收集任务还是手动收集统计信息，如果最终用户在收集统计信息之前开始查询新插入的数据，即使表中不到10%的行被更改，也可能会由于陈旧的统计信息而获得次优执行计划，其中最常见的情况之一发生在当 where 子句谓词中提供的值超出 [最小、最大] 列统计的值域时，就会发生这种情况。这通常被称为“范围外”错误。在这种情况下，优化器根据谓词值之间的距离和最大值(假设值高于最大值)对选择性进行按比例分配，即，最大值或最小值，从而降低选择性。

此场景与范围分区表非常常见。一个新的分区被添加到一个现有的范围分区表中，行被插入到这个分区中。在此新分区收集统计信息之前用户开始查询此新数据。对于分区表，可以使用DBMS_STATS.COPY_TABLE_STATS(从Oracle数据库10.2.0.4开始)，以防止“超出范围”条件。此过程将具有代表性的源 [sub] 分区的统计信息复制到新创建的和空的目标 [子] 分区中。

它还会拷贝相关对象的统计信息:列、本地(分区)索引等。并将最高界限值作为分区列的最大值和前一个分区的最高界限值作为该分区列的最小值。

拷贝的统计信息应该仅视为临时解决方案，直到可以收集分区的准确统计信息。不应该将拷贝的统计信息用作实际收集统计信息的替代方法。

默认情况下，DBMS_STATS.COPY_TABLE_STATS仅用来调整分区统计信息，而不是全局或表级别统计信息。如果希望将分区列的全局级别统计信息作为副本的一部分进行更新, 则需要将 DBMS_STATS.COPY_TABLE_STATS 的参数设置为8。

对于非分区表，可以使用DBMS_STATS.SET_COLUMN_STATS手动设置列的最大值。但是一般不建议使用这种方法来代替实际收集统计信息。

提高统计信息质量

良好质量的统计信息对生成最佳的SQL执行计划是至关重要的，但是有时统计信息的质量很差，而这种情况可能会被忽视。例如，“通过继承得到的”系统可能使用被数据库管理员所不能理解的脚本，或者可以理解但是却不愿意更改它们。但是，由于Oracle不断提高统计数据收集功能，因此可能忽略最佳做法和建议。出于这些原因，Oracle数据库12cR2包含了一个新的优化统计顾问，来帮助您提高数据库中的统计信息的质量。该优化统计顾问分析数据字典中的信息，评估统计数据的质量，并发现如何收集统计信息。它将报告质量较差和缺失的统计数字，并提出解决这些问题的建议。

其操作的原则是应用最佳实践规则来发现潜在的问题。这些问题作为一系列结果被报告，反过来又可以产生具体的建议。这些建议可以通过操作自动实现(立即或通过自动生成的脚本由数据库管理员执行)。

请点击此处输入图片描述

图7: 优化统计顾问

优化统计顾问任务是在维护窗口中自动运行，但也可以按需运行。可以随时查看由优化统计顾问生成的html或文本报告，并且可以随时执行进行操作。图8举例说明了一个特定规则的示例，它引出了查找、建议和解决问题的操作：

请点击此处输入图片描述

图8:规则、查找、建议和操作的示例。

优化统计顾问收集并将信息存储在数据字典中。它的性能开销较低，因为它对收集的统计信息 (已经保存在数据字典中) 进行分析，并且不对已经存储在应用 Schema中对象的统计信息进行二次分析。

请点击此处输入图片描述

图9:读取数据字典，通过过滤器执行任务并存储结果

任务完成后，可以用html或文本格式生成报告，也可以创建操作( SQL)脚本。

请点击此处输入图片描述

图10:报告advisor任务和生成操作SQL脚本。

查看自动化任务生成的报表非常简单：

select dbms_stats.report_advisor_task('auto_stats_advisor_task') as report from dual;

或者，具有使用顾问权限的用户可以手动执行任务，并使用以下三个步骤报告结果:

DECLARE
tname VARCHAR2(32767) := 'demo'; -- task name
BEGIN
tname := dbms_stats.create_advisor_task(tname);
END;
/
DECLARE
tname VARCHAR2(32767) := 'demo'; -- task name
ename VARCHAR2(32767) := NULL; -- execute name
BEGIN
ename := dbms_stats.execute_advisor_task(tname);
END;
/
SELECT dbms_stats.report_advisor_task('demo') AS report
FROM dual;

优化统计顾问生成的操作可以立即实施:

DECLARE
tname VARCHAR2 (32767) := 'demo'; -- task name
impl_result CLOB; -- report of
implementation
BEGIN
impl_result := dbms_stats.implement_advisor_task(tname);
END;
/

此外, Oracle12c Real Application Testing还包括实用的性能保证功能，如 SQL 性能顾问快速检查。见 Oracle 白皮书，《Database 12c Real Application Testing Overview》有关更多详细信息 (请参见21页上的参考资料)。

快速收集统计信息

随着数据的增长和维护窗口缩减，及时收集统计信息比以往任何时候都更重要。Oracle提供了各种加速统计数据收集的方法，从并行化统计信息收集到生成统计信息而不是收集统计信息。

使用并行法

可以通过几种方式利用并行性来进行统计收集

? 使用DEGREE参数

? 并发统计收集

? 结合DEGREE和并发收集

使用并行参数

DBMS_STATS中的”DEGREE”参数用于控制收集统计信息时并行执行进程的数量。默认情况下，Oracle使用与数据字典中表的属性(并行度)指定的相同数量的并行服务器进程。Oracle数据库中的所有具有此属性的表都默认设置为1，为了加快统计信息的收集，可以在对大表进行收集统计信息时显示指定该参数，或者你可以设置degree为auto_degree；Oracle将根据对象的大小自动确定应该用于收集统计信息的适当的并行服务器进程数量。该值可以介于 1 (串行执行)(小对象) 到 DEFAULT_DEGREE (PARALLEL_THREADS_PER_CPU XCPU_COUNT) (较大的对象)。

请点击此处输入图片描述

图11: 通过DEGREE参数使用并行性

您应该注意，设置分区表的DEGREE意味着多个并行服务器进程将用于收集每个分区的统计信息，但不会在不同分区上同时收集统计信息。统计信息将在每个分区上收集一次。

并发统计收集

并发统计信息收集功能可以在 Schema (或Database) 中的多个表上和多个 (子) 分区中并发的进行统计信息的收集。通过让Oracle充分利用多处理器环境，同时去收集多个表和(sub)分区的统计信息可以减少收集统计数据所需的总体时间。

并发统计信息收集是由全局选项CONCURRENT控制，可以设置MANUAL,AUTOMATIC, ALL, OFF，默认为OFF。当CONCURRENT被启用时，Oracle使用作业调度器和高级队列组件来创建和管理多个统计信息收集作业并发的执行。

调用DBMS_STATS.GATHER_TABLE_STATS在分区表上, 当 CONCURRENT 设置为MANUAL时，Oracle会为表中的每个(sub)分区创建单独的统计信息收集作业。其中有多少个作业并发执行，多少作业在队列中排队，是根据可用作业队列的进程数量 (JOB_QUEUE_PROCESSES 初始化参数、RAC 环境中的每个节点)和可用的系统资源来决定的。随着正在运行的作业完成，更多的作业将出现并执行，直到所有（子）分区都收集了其统计信息。

如果您使用DBMS_STATS.GATHER_DATABASE_STATS、DBMS_STATS.GATHER_SCHEMA_STATS或者DBMS_STATS.GATHER_DICTIONARY_STATS收集统计信息，那么Oracle将为每个非分区表以及分区表的每个（子）分区创建一个单独的统计信息收集作业。每个分区表还将有一个协调作业，用于管理其(sub)分区作业。然后，数据库将运行尽可能多的并发作业，并对其余作业进行排队，直到作业执行完成。但是，为了防止可能的死锁情况，不能同时处理多个分区表。因此, 如果已为已分区表运行了某些作业, 则Schema (或数据库或字典) 中的其他分区表将排入队列, 直到当前作业完成。对非分区表则没有这种限制。

图12显示了DBMS_STATS.GATHER_SCHEMA_STATS在不同级别创建作业。在Schema:sh上Oracle将为每个非分区表创建一个统计收集作业(图12中的级别1)；

? CHANNELS
? COUNTRIES
? TIMES

Oracle将为每个分区表创建一个协调作业: SALES和COSTS，然后分别为SALES和COSTS表中的每个分区创建一个统计数据收集作业(图12中的级别2)。

请点击此处输入图片描述

图12:在sh上的并发统计信息收集时的作业列表

如果指定了DEGREE参数，每个单独的统计数据收集作业也可以利用并行执行。如果表、分区表或子分区表非常小或为空，则Oracle可以自动将对象与其他小对象合成到一个单独作业中，以减少作业维护的开销。

配置并发统计数据收集

默认情况下，统计数据收集的并发设置关闭。它可以按照如下操作打开:

exec dbms_stats.set_global_prefs('concurrent', 'all')

您还需要一些额外的特权和收集统计信息所需的常规权限。用户必须具有以下Job Scheduler和AQ权限:

? CREATE JOB
? MANAGE SCHEDULER
? MANAGE ANY QUEUE

sysaux表空间应该处于在线状态，因为作业程序在sysaux表空间中存储其内部表和视图。最后，JOB_QUEUE_PROCESSES参数应该设置为充分利用可用于统计收集过程的所有系统资源。如果您不计划使用并行执行，则应将job _ queue _process设置为2*CPU核心总数(在RAC环境中为每个节点)。请确保在系统级别设置此参数 ( alter system...或在init.ora文件中)而不是在会话级别上( alter session)设置。

如果要将并行执行作为并发统计信息收集的一部分，则应该禁用并行自适应多用户:

ALTER SYSTEM SET parallel_adaptive_multi_user=false;

资源管理器也必须被激活，例如:

ALTER SYSTEM SET resource_manager_plan = 'DEFAULT_PLAN';

还建议启用并行语句队列。这需要激活资源管理器，并创建临时资源计划，并且其中的消费者组“others_groups”已启用。

默认情况下，资源管理器仅在维护窗口期间激活。以下脚本说明了创建临时资源计划( pqq _ test)的一种方法，并使资源管理器能够执行此计划。

-- connect as a user with dba privileges
begin
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_plan('pqq_test', 'pqq_test');
dbms_resource_manager.create_plan_directive(
'pqq_test',
'OTHER_GROUPS',
'OTHER_GROUPS directive for pqq',
parallel_target_percentage => 90);
dbms_resource_manager.submit_pending_area();
end;
/
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'pqq_test' SID='*';

如果希望自动统计数据收集任务利用并发性，请将CONCURRENT设置为AUTOMATIC或ALL。在维护窗口中使用的资源管理器计划中添加了一个新的 AUTOTASK $ 使用者组, 以确保并发统计信息的收集不会使用太多的系统资源。

虽然优化器需要准确的统计信息来选择最优的执行计划，但是有些场景下，收集统计信息比较困难，或消耗资源较高，或收集统计信息不能及时完成，那么就需要另一种备选策略。

不稳定的表

不稳定的表即随着时间的变化，数据会发生巨大变化的表。例如，一个订单队列表，一天的开始它是空的，随着时间推移，订单会填满这个表，一旦某一订单被处理又会从这个订单表中删除，一天的结尾表又会变为空。

如果你依赖于自动收集统计信息job来维护这类表的统计信息，那么统计信息会经常显示此表为空。因为晚上此表是空的，而收集统计信息的job也正是晚上才开始执行。然而，在白天的过程中，这个表可能有成百上千条记录。

在这种情况下，最好在表被填充时收集一组有的代表性的统计信息，并锁住。锁住统计信息会阻止自动收集的统计信息覆盖他们。另外，你可以依赖于动态采样来收集这些表的统计信息。优化器在优化一个语句之前编译sql语句的时候会使用动态采样来收集表的基本统计信息。尽管动态采样收集的统计信息没有完全由DBMS_STATS包收集的统计信息质量高，但在大多数情况下他们已经足够好了。

全局临时表

在应用程序上下文中，全局临时表经常被用于存储中间结果。全局临时表在系统级别与具有适当权限的所有用户共享其定义，但里面的数据内容在会话之间是相互独立和私有的。针对此表，直到有数据插入时才会分配物理存储。

一个全局临时表可以是事务特定的（提交时删除行记录），也可以是会话特定的（提交时保留行记录）。收集事务特定表的统计信息会导致此表被清空。相反，收集一个全局临时表的统计信息是可能的（会保留行记录），但是在之前的数据库版本这不是一个好方案，因为使用全局临时表的所有会话不得不共用同一组统计信息，以致于很多系统依赖于动态采样的统计信息。

然而，在oracle 12c版本，现在可以实现每个使用全局临时表的会话拥有自己独立的统计信息。全局临时表上的统计信息是否共享取决于DBMS_STATS包的一个新选项GLOBAL_TEMP_TABLE_STATS。默认情况此选项设置为会话，即每个使用全局临时表的会话都有自己独立的统计信息。优化器会首先使用会话的统计信息，如果会话统计信息不存在，才会使用共享的统计信息。

请点击此处输入图片描述

图13：改变默认方式：从全局临时表不共享统计信息到共享统计信息

如果你是从11g升级到12c,但数据库应用没有被修改去利用全局临时表的会话统计信息，你可能需要保持全局临时表默认的方式与升级之前一致，通过设置DBMS_STATS的GLOBAL_TEMP_TABLE_STATS选项为共享模式（或者至少等到应用被升级）。

当使用直接路径的方式填充一个全局临时表（提交时保留行记录）时，在线统计信息收集会自动创建会话级别的统计信息，这将减少运行额外统计信息收集的必要性，也不会影响其他会话的统计信息。

请点击此处输入图片描述

图14：使用直接路径方式填充一个全局临时表会导致会话级别的统计信息被自动收集

中间表

中间表通常被看做一个ETL进程或一个复杂事务的一部分。这些表只被写一次，读一次，然后被清空或删除。在这种情况下收集统计信息的成本大于好处，因为统计信息只被使用一次。反倒是动态采样应该用于这些场景。建议你锁住这些中间表上的统计信息以防止自动统计信息收集任务再次对他们收集统计信息。

收集其他类型的统计信息

自从基于成本的优化器是现在唯一被支持的优化器，数据库中所有的表需要有统计信息，包括所有的字典表（owner是sys、system等等，且位于system、sysaux表空间中的表），以及动态性能视图使用的x$表。

数据字典统计信息

数据字典表上的统计信息是通过运行在夜间维护窗口上的自动统计信息收集任务来维护的。强烈建议你允许自动统计信息收集任务来维护数据字典统计信息，即使你关掉主要应用账户上的自动统计信息收集job。你可以使用DBMS_STATS.SET_GLOBAL_PREFS存储过程修改AUTOSTATS_TARGET的值为ORACLE，以代替AUTO，来这样做。

exec dbms_stats.set_global_prefs('autostats_target','oracle')

内部对象统计信息

从oracle数据库12c开始，内部对象统计信息如果之前没有被收集过，那么它就会被自动统计信息收集任务收集。在此版本之前，数据库是不会收集内部对象统计信息的。不像其他的数据库表，当统计信息缺失时动态采样不会自动应用于包含x$表的sql语句，此时优化器会使用预定义的统计信息默认值。这些默认值可能没有代表性，可能会导致非最优的执行计划，这可能会导致严重的性能问题，正是因为这个原因，我们强烈建议你手动收集内部对象统计信息。

你可以使用DBMS_STATS.GATHER_FIXED_OBJECTS_STATS存储过程收集内部对象统计信息。因为x$表的瞬态性质，重要的是在系统有一定代表性负载时收集内部对象统计信息。在大型系统中，这并不总是可行的，因为收集统计信息需要占用额外的资源。你不能在系统高峰负荷时收集内部对象统计信息，你应该在系统预热之后，三种重要类型的内部对象表被填充时收集内部对象统计信息。

结构数据例如，涵盖数据文件，控制文件内容的视图等
基于会话的数据例如，v$session, v$access 等
工作负载数据例如，v$sql, v$sql_plan 等

如果你做了一个重大的数据库或应用的升级，或实现一个新的模块，或改变数据库的配置，强烈建议你重新收集内部对象统计信息。例如，你增加了SGA的大小，包含buffer cache和shared pool信息的所有x$表可能变化很大。例如用于v$buffer_pool或v$shared_pool_advice的x$表。

系统统计信息

系统统计信息使得优化器能更准确的计算执行计划中每一步操作的成本，通过使用实际硬件系统执行sql的信息，例如CPU的速度和IO的性能。

系统统计信息是默认开启的，会以默认值自动初始化，这些值对于大多数系统是有代表性的。

总结

为了使oracle优化器准确地确定执行计划的成本，那么sql语句中涉及到的全部对象（表和索引）必须有准确的统计信息，且必须有准确的系统统计信息。这两部分白皮书系列详细地解释了什么统计信息是必要的，以及这些统计信息怎么被使用，以及不同的统计信息收集方法。

通过自动统计信息收集任务和此白皮书中描述的其他技术手段的组合使用，一个DBA可以为他们的环境维护一组准确的统计信息，以确保优化器得到必要的信息去选择一个最优的执行计划。一旦一个统计信息收集策略被实施，如果要改变策略，必须要在一个可控的方式下进行，并利用关键的特性例如待定统计信息以确保对应用的性能没有不良的影响。

更多相关文章

随机推荐