1.以非并行方式运行

SQL> update test set name='xxxx' where rownum<10000001;

已更新10000000行。

已用时间:? 00: 34: 59.32
SQL> commit;

提交完成。

已用时间:? 00: 00: 00.00

2 打开dml并行模式
SQL> alter session enable parallel dml;

会话已更改。

已用时间:? 00: 00: 00.01

查看SID
SQL> select sid from v$mystat where rownum=1;
?????? SID
----------
?????? 159

已用时间:? 00: 00: 00.01
SQL> update? test set name='xxxx1' where rownum<10000001;
update test set name='xxx1' where rownum<10000001
*
第 1 行出现错误:
ORA-12801: 并行查询服务器 P001 中发出错误信号
ORA-01652: 无法通过 128 (在表空间 TEMP 中) 扩展 temp 段

已用时间:? 00: 00: 07.89

?3.遇到temp表空间无法自动扩展问题

SQL> select file_name,autoextensible from dba_temp_files;
FILE_NAME??????????????????????????????????????????????????? AUTOEX
------------------------ ------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\HUICHES\TEMP01.DBF????????? NO

SQL> ALTER DATABASE TEMPFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\HUICHES\TEMP01.DBF' AUTOEXTEND ON NEXT 200K MAXSIZE UNLIMITED;

数据库已更改。

已用时间:? 00: 00: 00.20
SQL>

4.从新运行更新
SQL> update test set name='xxxxxx1' where rownum<10000001;

已更新10000000行。

已用时间:? 00: 13: 15.56
SQL>

?5 .运行时,检查并行后台运行情况

SQL>? select * from v$px_session where qcsid=159;
SADDR?????????? SID??? SERIAL#????? QCSID? QCSERIAL#? QCINST_ID SERVER_GROUP SERVER_SET??? SERVER#???? DEGREE REQ_DEGREE
-------- ---------- ---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ----------
25B2ED7C??????? 130??????? 212??????? 159????????? 3????????? 1??????????? 1????????? 1????????? 1????????? 4????????? 4
25B2D994??????? 129???????? 31??????? 159????????? 3????????? 1??????????? 1????????? 1????????? 2????????? 4????????? 4
25B2C5AC??????? 128????????? 1??????? 159????????? 3????????? 1??????????? 1????????? 1????????? 3????????? 4????????? 4
25B364EC??????? 136???????? 62??????? 159????????? 3????????? 1??????????? 1????????? 1????????? 4????????? 4????????? 4
25B2B1C4??????? 127????????? 1??????? 159????????? 3????????? 1??????????? 2????????? 1????????? 1????????? 4????????? 4
25B2760C??????? 124????????? 1??????? 159????????? 3????????? 1??????????? 2????????? 1????????? 2????????? 4????????? 4
25B289F4??????? 125????????? 1??????? 159????????? 3????????? 1??????????? 2????????? 1????????? 3????????? 4????????? 4
25B29DDC??????? 126????????? 1??????? 159????????? 3????????? 1??????????? 2????????? 1????????? 4????????? 4????????? 4
25B52EC4??????? 159????????? 3??????? 159

已选择9行。

已用时间:? 00: 00: 06.57

?结论证明:在操作大批量数据时选择使用并行模式,能明显的执行效率,从而缩短执行时间