群里关于parallel的讨论，初始化参数parallel_max_servers限制parallel

今天在群里一个朋友询问并行化的问题，我觉得挺有意义的，也让我把原来不懂的搞的明白了一些。

群里朋友问：

安徽-宫保鸡丁(175778902) 16:12:18
有没有对pdml熟悉的？
我用alter session enable parallel dml;
之后执行insert，就报错：
ORA-12801: error signaled in parallel query server P057
ORA-00018: maximum number of sessions exceeded
如果直接在insert上加：insert /*+parallel(table)*/```````
就没问题

于是我自己做了个测试：

SQL> alter table ttt noparallel;

Table altered.

SQL> alter session enable parallel dml;

Session altered.

SQL> set autotrace trace explain;

首先确保环境，表的并行关闭，打开session parallel。

SQL> insert into ttt select * from ttt;

46159 rows created.

Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT ptimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TTT'

这是普通插入的执行计划，没有启用并行，那么也就是说，表的并行度是要打开的。

SQL> commit;

Commit complete.

SQL> insert /*+parallel(ttt)*/ into ttt select * from ttt;

92318 rows created.

Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel

SP2-0612: Error generating AUTOTRACE EXPLAIN report
SQL> rollback;

使用hint发现无法统计执行计划，于是，换方法。

SQL> explain plan for insert /*+parallel(ttt)*/ into ttt select * from ttt;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
----------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| TQ
|IN-OUT| PQ Distrib |

--------------------------------------------------------------------------------
----------------------

| 0 | INSERT STATEMENT | | 19522 | 1830K| 34 (27)|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| | |

| 1 | LOAD AS SELECT | | | | | Q4,01
| P->S | QC (RAND) |

| 2 | TABLE ACCESS FULL | TTT | 19522 | 1830K| 34 (27)| Q4,00
| S->P | RND-ROBIN |

--------------------------------------------------------------------------------
----------------------

格式很乱，大概能够看出起了两个parallel。

SQL> alter table ttt parallel;

Table altered.

SQL> explain plan for insert into ttt select * from ttt;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
----------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| TQ
|IN-OUT| PQ Distrib |

--------------------------------------------------------------------------------
----------------------

| 0 | INSERT STATEMENT | | 39044 | 3660K| 16 (19)|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| | |

| 1 | LOAD AS SELECT | | | | | Q5,01
| P->S | QC (RAND) |

| 2 | TABLE ACCESS FULL | TTT | 39044 | 3660K| 16 (19)| Q5,00
| P->P | RND-ROBIN |

--------------------------------------------------------------------------------
----------------------

8 rows selected.

SQL>

打开表并行以后，普通insert也会打开两个parallel了。

然后我尝试修改表的degree,

alter table ttt parallel 4;

但是查看后台SESSION也最多5个，那为什么宫保鸡丁会的6CPU服务器会出现ORA-00018的报错呢？

一哥们一语道破天机：

并行度是根据参数parallel_max_servers指定的

正确的应该是cpu_count+parallel_threads_per_cpu-2

不错，终于搞清楚了，原来是这个参数限制。宫保鸡丁的设置了135，sessions的总数才50，正常运行时就有86个session,那么只要一运行默认并行，立马超出数量。

SQL> alter system set parallel_max_servers=10;
alter system set parallel_max_servers=10
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set parallel_max_servers=10 scope=spfile;

System altered.

SQL>

此参数需要重启。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16628454/viewspace-678218/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16628454/viewspace-678218/