【DB笔试面试644】在Oracle中，如何并发地收集统计信息？

原创

小麦苗123 2021-04-16 22:37:29 ©著作权

©著作权归作者所有：来自51CTO博客作者小麦苗123的原创作品，请联系作者获取转载授权，否则将追究法律责任

【DB笔试面试644】在Oracle中，如何并发地收集统计信息？_Oracle

♣ 题目部分

在Oracle中，如何并发地收集统计信息？

♣ 答案部分

对于大表的统计信息收集可以通过DEGREE参数使得扫描大表的时候进行并行扫描，从而加快扫描速度，缩短了收集统计信息的时间。但是，即使加了DEGREE参数，在收集统计信息的时候，还是进行一个表一个表的扫描，并没有并发的同时扫描多个表。在Oracle 11.2.0.2之后，有了一个参数，可以并发扫描表，这就是CONCURRENT参数。可以通过以下SQL语句查询数据库是否启用了CONCURRENT收集统计信息，默认为FALSE，表示没有开启并发收集统计信息：

1SELECT DBMS_STATS.GET_PREFS('CONCURRENT') FROM DUAL;

开启方式为:

1EXEC  DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','TRUE');

开启CONCURRENT之后，收集统计信息就会以并发的形式进行，会并发出多个JOB进程。在并发收集统计信息时，数据库生成的JOB数会根据具体情况来分配。在大多数情况下，DBMS_STATS程序会给每个对象分配一个JOB；但如果对象（表或者分区）的大小太小，为了节省资源，Oracle会合并多个表和分区在一个JOB中执行。为了防止同时处理多个分区表的分区时发生死锁，所以，对于分区表的处理机制是每次只能处理一个分区表，其它的分区表需要等待，待前一个分区表处理完后再处理下一个。在Oracle 11.2.0.2到11.2.0.4的版本上，CONCURRENT可取的值为TRUE（开启并发）和FALSE（关闭并发）。在Oracle 12c的版本上，可以设置以下的值：

1MANUAL：只有当手动收集时，并发有效
2AUTOMATIC：只有当自动收集时，并发有效
3ALL：当手动/自动收集，并发都有效
4OFF：并发无效

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

1DBA_OPTSTAT_OPERATION_TASKS：当前和历史的统计信息收集的执行任务
2DBA_OPTSTAT_OPERATIONS：当前和历史的统计信息收集的执行操作
3DBA_SCHEDULER_JOBS：SCHEDULER JOBS信息

监控并发收集统计信息JOB的SQL代码如下：

1SELECT JOB_NAME, STATE, COMMENTS
2FROM DBA_SCHEDULER_JOBS
3WHERE JOB_CLASS LIKE 'CONC%';
4SELECT STATE,COUNT(*)
5FROM DBA_SCHEDULER_JOBS
6WHERE JOB_CLASS LIKE 'CONC%'
7GROUP BY STATE;

对于并发收集统计信息需要注意如下几点：

（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设置的太高（在Oracle 11.2.0.3之后，这个值的默认值是1000，所以就可能并发出1000个JOB），那么对数据库的性能影响较大。所以开启CONCURRENT的另外一个建议就是使用Resource Manager来控制资源的使用。