今天在群里一个朋友询问并行化的问题,我觉得挺有意义的,也让我把原来不懂的搞的明白了一些。
群里朋友问:
安徽-宫保鸡丁(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/