在10.2以上的版本，收集统计信息，如使用dbms_stats.gather_table_stats，还是在ebs中调用fnd_stats.GATHER_TABLE_STATS，有的时候虽然用了degree的参数，如degree=8，但是会发现仍然不使用并行。在10046的trace中，我们可以看到：
|
1 |
/* SQL Analyze(0) */ select /*+ full(t) no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad */to_char(count("A")),to_char(substrb(dump(min("A"),16,0,32),1,120)),to_char(substrb(dump(max("A"),16,0,32),1,120)),to_char(count("B")),to_char(substrb(dump(min("B"),16,0,32),1,120)),to_char(substrb(dump(max("B"),16,0,32),1,120)) from "TEST"."T2" t where TBL$OR$IDX$PART$NUM("TEST"."T2",0,4,0,"ROWID") = :objn /* SYN,NIL,NIL,SYN,NIL,NIL, B17161, C1, C1, C1, C2, C2, C2*/ |
这是因为在10.2以上的版本中，并行默认是最小3个slave，且每个px slave最小1000个block。（DBMS_STATS PARALLEL DEGREE IGNORED IN 10.2 (Doc ID 389851.1)）
|
1 |
In 10.2 there is a threshold value where the Parallelism will start. The minimum number of slaves is 3 and the minimum blocks per PX slave should be at least 1000. So, the table must have 3000 blocks before dbms_stats will consider executing in parallel, other wise the DEGREE is ignored. In 9.2 or earlier there was no such restriction. |
解决方法是，我们可以设置38028的event：
|
1 2 |
alter session set events '38028 trace name context forever'; 再次运行dbms_stats 的语句。 |
设置后，我们在10046的trace中可以看到已经走并行：
|
1 |
/* SQL Analyze(0) */ select /*+ full(t) parallel(t,8) parallel_index(t,8) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad */to_char(count("A")),to_char(substrb(dump(min("A"),16,0,32),1,120)),to_char(substrb(dump(max("A"),16,0,32),1,120)),to_char(count("B")),to_char(substrb(dump(min("B"),16,0,32),1,120)),to_char(substrb(dump(max("B"),16,0,32),1,120)) from "TEST"."T2" t where TBL$OR$IDX$PART$NUM("TEST"."T2",0,4,0,"ROWID") = :objn /* NDV,NIL,NIL,NDV,NIL,NIL*/ |