Oracle 12c 并发统计信息收集功能：技能剖析与实践指南 ...

一、功能背景与焦点代价

在Oracle数据库的早期版本中，统计信息收集（如通过DBMS_STATS包）是串行执行的，即一次仅处置惩罚一个表或分区。对于大规模数据库或数据仓库环境，这种串行模式可能导致统计信息收集耗时过长，影响维护窗口效率。Oracle 12c R1引入了并发统计信息收集（Concurrent Statistics Collection）功能，允许同时处置惩罚多个表、分区或子分区的统计信息，显著缩短收集时间。


焦点优势

• 并行化处置惩罚：通过并发作业（Job）同时处置惩罚多个对象，提升整体效率。
  

• 资源优化：联合Resource Manager控制并发资源分配，避免过分斲丧体系资源。
  

• 机动性：支持手动和主动统计信息收集模式的并发执行。
  

二、配置过程

启用并发统计信息收集需进行以下配置：
1. 体系参数设置

• 启用资源管理器：指定资源管理筹划以控制并发作业的资源分配。
  

1. ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'default_plan';  

复制代码

• 调整并发作业数：设置JOB_QUEUE_PROCESSES参数（建议根据体系负载调整）。
  

1. ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 4;  

复制代码

注：过高的并发可能影响性能，需联合Resource Manager限制。 

2. 开启并发收集功能

通过DBMS_STATS.SET_GLOBAL_PREFS设置全局参数：

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

复制代码

参数可选值：

• ALL：手动和主动收集均启用并发。
  

• AUTOMATIC：仅主动收集时启用。
  

• MANUAL：仅手动收集时启用。
  

• OFF：禁止。
  

3. 执行统计信息收集

以并发模式收集模式（Schema）级别的统计信息：

1. EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');  

复制代码

三、使用限制与留意事项

1. 权限要求

• 用户需显式授予CREATE JOB、MANAGE SCHEDULER和MANAGE ANY QUEUE权限。
  

2. 分区表处置惩罚机制

• 串行化处置惩罚分区表：为避免死锁，Oracle在并发模式下每次仅处置惩罚一个分区表的分区，其他分区表需等候当前表处置惩罚完成。
  

3. 资源与性能管理

• 并发度控制：JOB_QUEUE_PROCESSES参数需合理设置（默认值可能过高），建议通过Resource Manager限制并发作业的资源使用。
  

• 小对象合并：Oracle可能将多个小表或分区合并至单个Job中执行，以减少资源斲丧。
  

4. 监控与诊断

• 查看并发使命状态：
  

1. SELECT JOB_NAME, STATE, COMMENTS
2. FROM DBA_SCHEDULER_JOBS
3. WHERE JOB_CLASS LIKE 'CONC%';  

复制代码

• 汗青使命分析：通过视图DBA_OPTSTAT_OPERATION_TASKS和DBA_OPTSTAT_OPERATIONS跟踪使命执行详情。
  

四、最佳实践

• 负载窗口选择：在高并发模式下，建议在体系低负载时段执行统计信息收集。
  
• 主动收集优化：联合主动统计信息收集功能（通过auto optimizer stats collection使命），设置合理的维护窗口时间。
  
• 分区表策略：对大型分区表优先使用并行参数（DEGREE），联合并发模式进一步提升效率。
  

五、常见问题与解决方案

• 权限报错（ORA-27486）：检查用户权限是否包含CREATE JOB、MANAGE SCHEDULER和MANAGE ANY QUEUE。
  

示例如下：

1. CONN test/test@pdb1
3. DROP TABLE tab1 PURGE;
5. CREATE TABLE tab1 AS
6. SELECT level AS id,
7.        'Description for ' || level AS description
8. FROM   dual
9. CONNECT BY level <= 10000;
11. EXEC DBMS_STATS.delete_table_stats(USER, 'TAB1');
13. EXEC DBMS_STATS.gather_table_stats(USER, 'TAB1');
14. *
15. ERROR at line 1:
16. ORA-20000: Unable to gather statistics concurrently: insufficient privileges
17. ORA-06512: at "SYS.DBMS_STATS", line 34634
18. ORA-06512: at line 1
20. SQL>

复制代码

必须授予用户DBA角色，大概更明智地授予CREATE JOB、MANAGE SCHEDULER和MANAGE ANY QUEUE权限。

1. CONN sys@pdb1 AS SYSDBA
2. GRANT CREATE JOB, MANAGE SCHEDULER, MANAGE ANY QUEUE TO test;
4. PL/SQL procedure successfully completed.

复制代码

• 资源管理器报错（ORA-20000）：确认RESOURCE_MANAGER_PLAN参数是否设置
  

示例如下：

1. conn test/test@pdb1
2. EXEC DBMS_STATS.gather_table_stats(USER, 'TAB1');
4. *
5. ERROR at line 1:
6. ORA-20000: Unable to gather statistics concurrently: Resource Manager is not
7. enabled.
8. ORA-06512: at "SYS.DBMS_STATS", line 34634
9. ORA-06512: at line 1
11. SQL>

复制代码

解决方案：

1. CONN sys@pdb1 AS SYSDBA
2. ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = default_plan;
4. conn test/test@pdb1
5. EXEC DBMS_STATS.gather_table_stats(USER, 'TAB1');
7. PL/SQL procedure successfully completed.
9. SQL>

复制代码

六、写在末了

Oracle 12c的并发统计信息收集功能通过多Job并行处置惩罚机制，显著提升了大规模数据库的统计信息收集效率。合理配置资源管理策略、权限及并发参数是关键。用户需联合实际场景衡量并发度与体系负载，以实现性能与稳固性的平衡。 

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。