SQL> explain plan for insert /*+ append parallel(my 2) */ into my select * from ac01;
已解释。
已用时间: 00: 00: 00.16
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
--------
Plan hash value: 2164050840
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1902K| 335M| 14813 (20)| 00:02:58 |
| 1 | LOAD AS SELECT | MY | | | | |
| 2 | TABLE ACCESS FULL| AC01 | 1902K| 335M| 14813 (20)| 00:02:58 |
---------------------------------------------------------------------------
已选择9行。
已用时间: 00: 00: 01.77
貌似insert部分，不能使用并行。select 部分是可以并行的。
SQL> explain plan for insert /*+ append */ into my select /*+ parallel(ac01 2) */ * from ac01;
已解释。
已用时间: 00: 00: 00.11
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
--------
Plan hash value: 2135288090
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1902K| 335M| 8068 (18)| 00:01:37 | | | |
| 1 | LOAD AS SELECT | MY | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM)| :TQ10000 | 1902K| 335M| 8068 (18)| 00:01:37 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 1902K| 335M| 8068 (18)| 00:01:37 | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| AC01 | 1902K| 335M| 8068 (18)| 00:01:37 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------
已选择12行。
已用时间: 00: 00: 00.64
和书里（Troubleshooting Oracle Performance）所说的不一样：
Execute the SQL statement in parallel. Note that in this case, both the INSERT and the
SELECT can be parallelized independently. To take advantage of direct-path inserts, at
least the INSERT part must be parallelized.
书里说，insert 和select都是可以并行的。
已解释。
已用时间: 00: 00: 00.16
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
--------
Plan hash value: 2164050840
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1902K| 335M| 14813 (20)| 00:02:58 |
| 1 | LOAD AS SELECT | MY | | | | |
| 2 | TABLE ACCESS FULL| AC01 | 1902K| 335M| 14813 (20)| 00:02:58 |
---------------------------------------------------------------------------
已选择9行。
已用时间: 00: 00: 01.77
貌似insert部分，不能使用并行。select 部分是可以并行的。
SQL> explain plan for insert /*+ append */ into my select /*+ parallel(ac01 2) */ * from ac01;
已解释。
已用时间: 00: 00: 00.11
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
--------
Plan hash value: 2135288090
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1902K| 335M| 8068 (18)| 00:01:37 | | | |
| 1 | LOAD AS SELECT | MY | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM)| :TQ10000 | 1902K| 335M| 8068 (18)| 00:01:37 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 1902K| 335M| 8068 (18)| 00:01:37 | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| AC01 | 1902K| 335M| 8068 (18)| 00:01:37 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------
已选择12行。
已用时间: 00: 00: 00.64
和书里（Troubleshooting Oracle Performance）所说的不一样：
Execute the SQL statement in parallel. Note that in this case, both the INSERT and the
SELECT can be parallelized independently. To take advantage of direct-path inserts, at
least the INSERT part must be parallelized.
书里说，insert 和select都是可以并行的。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/22034023/viewspace-664136/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
这条语句简直匪夷所思啊
下一篇：
请教，关于自动运行JOB，该怎么写。
请登录后发表评论
登录
全部评论
