degree 仅对单表执行并行统计,例如:
exec dbms_stats.gather_table_stats(ownname=>'<owner_name>',tabname=>'<table_name>',cascade=>true,degree=><degree_number>,force=>true);
对多表设置并行收集的方法如下:
-- 检查当前并发设置
select dbms_stats.get_prefs('CONCURRENT') from dual;
-- 手动收集时并发有效
exec dbms_stats.set_global_prefs('CONCURRENT','MANUAL');
-- 自动收集时并发有效
exec dbms_stats.set_global_prefs('CONCURRENT','AUTOMATIC');
-- 手动和自动收集时并发都有效
exec dbms_stats.set_global_prefs('CONCURRENT','ALL');
-- 关闭并发
exec dbms_stats.set_global_prefs('CONCURRENT','OFF');
备注 1 : 执行统计信息收集的用户应显式授权如下权限:create job/manage scheduler/manage any queue,另外需注意 job_queue_processes 设置不要太高。
备注 2 : Oracle 11g 版本只有 true ( 启用并发 )和 false ( 关闭并发 )两个选项。
示例:
SQL> select dbms_stats.get_prefs('CONCURRENT') from dual;
DBMS_STATS.GET_PREFS('CONCURRENT')
--------------------------------------------------------------------------------
OFF
SQL> exec dbms_stats.set_global_prefs('CONCURRENT','MANUAL');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.set_global_prefs('CONCURRENT','AUTOMATIC');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.set_global_prefs('CONCURRENT','ALL');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.set_global_prefs('CONCURRENT','OFF');
PL/SQL procedure successfully completed.
SQL>