知识&案例：并行和并发统计信息收集

2020 年 3 月 26 日
筆記

概述

随着应用数据的增多和表量的增加，为了增加统计信息收集的效率，Oracle推出了并行和并发收集统计信息的方法。

本文将针对并行和并发收集统计信息的相关知识内容以及部分案例进行介绍，并重点介绍并发统计信息收集。

并行收集统计信息(PARALLEL )

当某个表的Size特别大时，可以通过并行的Slave进程共同工作来加快统计信息收集的效率。默认情况下，数据库能够根据在表或者索引级别设置的并行度（默认：1）进行并行统计信息收集。但我们也可以通过显示地设置degree参数来控制并行统计信息收集的并行度。

DEGREE参数：

DEGREE参数用于控制统计信息收集的并行度。你可以通过以下的方式进行赋值：

例：

Oracle推荐指定degree参数为DBMS_STATS.AUTO_DEGREE，由Oracle根据对象的大小和并行参数的设置情况来决定统计信息收集的并行度。例：

※注意： Oracle不能并行收集某些类型的索引，如：cluster indexes, domain indexes, and bitmap join indexes。

并发收集统计信息(CONCURRENT)

并行的统计信息收集仅仅意味着：对某个对象进行统计收集时会采用多个并行Slave进行处理，但是对于多个对象(表、索引或分区)来讲，处理还是串行的。即：处理完一个对象后再去处理下一个对象。

从11.2.0.2 开始，Oracle为了能够使多个对象的统计信息收集也能够同时进行，推出了并发收集统计信息(CONCURRENT)模式，使多进程的环境更加有效率。即：同时启动多个JOB，并发地处理多个对象(表、索引或分区)。

从12.1.0.1 开始，Oracle进一步扩展了并发收集统计信息，使Oracle自动统计收集任务（automatic statistics gather task）也能从中受益使用并发收集统计信息。

并发收集统计信息主要使用了以下的几种技术：

要启用并发收集统计信息，需要设置以下的参数：

并发收集统计信息时，数据库生成的JOB数会根据具体情况来分配，大多数情况下，DBMS_STATS 程序会给每个对象分配一个JOB；但如果对象（表或者分区）的大小太小，为了节省资源，Oracle会合并多个表和分区在一个JOB中执行。

※注意：为了防止同时处理多个分区表的分区时发生死锁，对于分区表是通过Queue的机制进行处理的。即：每次只能处理一个分区表，其他的需要在Queue等待，待前一个分区表处理完后再处理下一个。

以下是Oracle白皮书中的一个并发收集统计信息的例图：

通过该例子我们可以看到，在针对并发收集统计信息时会有不同层级的JOB，对于分区表除了一个协调JOB外还会针对各个分区分配1个JOB。另外，如同前面所讲的，多个分区表不能同时处理。图中的COSTS表和SALES表的处理会被排序，即：COSTS表的JOB结束后SALES表的处理才会开始。

CONCURRENT参数：

CONCURRENT参数用于控制并发收集统计信息。需要通过DBMS_STATS.SET_GLOBAL_PREFS来进行全局设置。

在11.2.0.2~11.2.0.4的版本上，可以设置的值：

12c的版本上，可以设置以下的值：

并发执行例1（11.2.0.3）：

11.2.0.3环境上的测试：

1.测试数据的准备：

2.设置相关的参数：

3.执行统计信息收集

4.监视结果

并发执行例2（12.1.0.2 ）：

12.1.0.2 环境上的测试：

1.测试数据的准备：

2.设置相关的参数：

3.执行统计信息收集

4.监视结果

并发统计信息收集的监视

可以通过以下的视图，对并发统计信息收集进行监视

并行 VS 并发

有时候可能对并行和并发统计信息收集的概念有些混淆，下面我们通过一张表来总结对比一下并行和并发统计信息收集：

并发和并行执行统计信息收集组合

为了提高效率，可以使并发和并行执行统计信息收集同时有效，这种组合使用对于非常大的表和分区非常有效。要使组合有效，你需要再设置PARALLEL_ADAPTIVE_MULTI_USER参数为False，以防止自适应导致的并行无效。

例：

关于PARALLEL_ADAPTIVE_MULTI_USER参数，可以参考在线文档：

Database Reference PARALLEL_ADAPTIVE_MULTI_USER

咨询案例：

在笔者的经验中，曾遇到过以下的几个咨询案例，在这里对调查方法和结果进行一些分享。

SE版本并发统计信息收集是否有效？

虽然并发执行统计信息收集使用的技术中包括Resource Manager，并且Resource Manager是Enterprise Edition版本才能使用的功能，但是Standard Edition内部的一些动作也会用到Resource Manager功能，所以不明确使用Resource Manager的情况下，也能够进行并发执行统计信息收集。通过上面的例子在Standard Edition运行，也可以验证这个结论。

设定相关内容，并发统计信息收集却无效?

有用户咨询，为什么我的环境中设定了并发执行统计信息收集，查看相关的视图却发现，统计信息收集时并没有并发执行？这个问题，其实由于进行并发执行时，Oracle内部事实上是有一定临界值设定的。当这些表很小和其他一些环境因素时，Oracle会合并多个表和分区在一个JOB中批量执行，就会产生统计信息收集时没有并发执行的表象。

我们可以通过跟踪dbms_stats可以查看到相关的一些内容。例：

查看相关的跟踪文件：

我们可以看到，在DBMS_STATS执行过程中在做一些比较，如果预估值没有满足临界值的话，就会进行批量处理。

※注意：这个临界值（batching threshold）由多方面因素影响，会根据环境不同而不同。

如何限定仅对一部分表进行并发统计信息收集？

在过去的咨询案件中，确实有些客户希望仅对某Schema的一部分表进行并发统计信息收集。针对这种需求可以通过DBMS_STATS的“obj_filter_list”参数来实现。

例：

关于这点，详细可以参考 Maria Colgan-Oracle写的一个Blog： Oracle Optimizer Blog >How do I restrict concurrent statistics gathering to a small set of tables from a single schema?

参考：

FAQ: Gathering Concurrent Statistics Using DBMS_STATS Frequently Asked Questions (Doc ID 1555451.1)

Database SQL Tuning Guide >Gathering Optimizer Statistics Concurrently

Oracle Optimizer Blog >Concurrent Statistics Gathering >How do I restrict concurrent statistics gathering to a small set of tables from a single schema?

★ White Papers Understanding Optimizer Statistics with Oracle Database 12c – Part 1 Best Practices for Gathering Optimizer Statistics with Oracle Database 12c – Part 2