达梦8 使用并行提高收集统计信息的速度

单位600G数据库收集统计信息用了一天时间还没结束，在家测试一下是否可以设置并行度。

#创建测试用户
create user liu identified by liu password_policy 0;
executed successfully
used time: 44.634(ms). Execute id is 519.

#创建测试样本表
create table liu.a(a int,b int , c int,d int,e int,f int);
executed successfully
used time: 41.131(ms). Execute id is 520.

#根据样本表循环创建10000张表
SQL> declare
2   begin
3   for i in 1..10000 loop
4   execute immediate 'create table liu.t_' || i || ' as select * from a;';
5   end loop;
6   end;
7   /
DMSQL executed successfully
used time: 00:07:29.149. Execute id is 524.

#设置并行度为4
SQL> sp_set_para_value(1,'MAX_PARALLEL_DEGREE',4);
DMSQL executed successfully
used time: 10.313(ms). Execute id is 502.

#收集统计信息
dbms_stats.gather_schema_stats('LIU',100,TRUE,'FOR ALL COLUMNS AUTO SIZE');
DMSQL executed successfully
used time: 00:08:44.125. Execute id is 504.

#设置并行度为1
sp_set_para_value(1,'MAX_PARALLEL_DEGREE',1);
DMSQL executed successfully
used time: 72.683(ms). Execute id is 505

#收集统计信息
dbms_stats.gather_schema_stats('LIU',100,TRUE,'FOR ALL COLUMNS AUTO SIZE');
DMSQL executed successfully
used time: 00:04:42.974. Execute id is 506

通过测试发现，我笔记本电脑的虚拟机上设置并行度反而降低性能。反复测试结果一样

分析原因是我的笔记本虚拟机只有1C，并行job需要拆分任务再合并，有一些代价。

不过通过本次测试证明并行参数是起作用的。相信在拥有多颗CPU的机器上是可以提高性能的。