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

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

译者  沃趣科技 杨禹航

何时收集统计信息

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

自动统计信息收集任务

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 $ 使用者组, 以确保并发统计信息的收集不会使用太多的系统资源。