Oracle并发（CONCURREMT）收集统计信息

对于大表的统计信息收集，我们可以加degree参数，使得扫描大表的时候，进行并行扫描，加快扫描速度。

但是这在收集的时候，还是进行一个表一个表的扫描。并没有并发的扫描各个表。在oracle 11.2.0.2之后，就有了一个参数，可以并发扫描表（或者分区），这就是CONCURRENT参数。

可以通过以下语句看到你的数据库是否启用了CONCURRENT收集统计信息。

SELECT DBMS_STATS.get_prefs('CONCURRENT') FROM dual;

开启方式为:

SQL> begin

  2   dbms_stats.set_global_prefs('CONCURRENT','TRUE');

  3  end;

  4  /

开启concurrent之后，收集统计信息就会以并发的形式进行，会并发出多个job进程。

其收集方式如下图：

从测试结果看，启用concurrent的收集统计信息速度对比,schema级别的收集，XXX_SCHEMA下有400个多segment，大约20多GB：

默认：

exec dbms_stats.gather_schema_stats(ownname => 'XXX_SCHEMA');

--263秒

开启8个并发：

exec dbms_stats.gather_schema_stats(ownname => 'XXX_SCHEMA',degree => 8);

--95秒。

开启concurrent+8个并发：

begin

dbms_stats.set_global_prefs('CONCURRENT','TRUE');

end;

 

exec dbms_stats.gather_schema_stats(ownname => 'XXX_SCHEMA',degree => 8);

--61秒

database级别的收集：（600多G数据，9万多个segment）

默认：

exec sys.dbms_stats.gather_database_stats;

--9小时

开启concurrent+8个并发：

begin

dbms_stats.set_global_prefs('CONCURRENT','TRUE');

end;

exec dbms_stats.sys.dbms_stats.gather_database_stats(degree => 8);

--4小时

需要注意的是：

1. 用concurrent收集统计信息，需要收集统计信息用户具有以下权限：

• CREATE JOB

• MANAGE SCHEDULER
  

• MANAGE ANY QUEUE
  

即使是该用户具有了dba角色，也还是需要显式授权上述权限。不然执行job的时候，可能会报错如下：
ORA-27486 insufficient privileges和ORA-20000: Statistics collection failed for 32235 objects in the database

2. concurrent不能控制多少的并发度，所以如果数据库的初始化参数job_queue_processes设置的太高，（注意，在11.2.0.3之后，这个值的默认值是1000，所以就可能并发出1000个job。）

如在测试时，某测试库设置了60个job_queue_processes的时候，数据库中就会并发出60个job来收集统计信息。此时的top情况，可以看到CPU的user部分基本已经在90%以上了。

所以开启concurrent的另外一个建议，就是使用resource manager。

3. 观察concurrent收集的进度：

select job_name, state, comments

from dba_scheduler_jobs

where job_class like 'CONC%';

 

select state,count(*)

from dba_scheduler_jobs

where job_class like 'CONC%';

group by state;

4. 当启用concurrent的时候，同时再使用并行，建议将PARALLEL_ADAPTIVE_MULTI_USER设置成false，关闭并发度的自适应调整。
默认值是true，当使用默认值时，使自适应算法，在查询开始时基于系统负载来自动减少被要求的并行度。实际的并行度基于默认、来自表或hints的并行度，然后除以一个缩减因数。该算法假设系统已经在单用户环境下进行了最优调整。表和hints用默认的并行度

5. EBS系统应用是采用自己的并发管理器（FND_STATS）来收集统计信息，而收集统计信息用户往往是没有显式授权CREATE JOB、MANAGE SCHEDULER、MANAGE ANY QUEUE的。且EBS中用户众多，不可能为这些应用用户都显式授权。

所以在EBS中不能开启concurrent参数。EBS的安装文档中（Doc ID 396009.1），也是说将数据上收集统计信息的功能关闭的（_optimizer_autostats_job=false）