-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1218 | 2 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 14 | 1218 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 14 | 1218 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| EMP | 14 | 1218 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
......
从执行计划中可以看出走的是并行。
取消当前session并行使用如下语句alter session disable parallel query;
scott@TEST>alter session disable parallel query;
Session altered.
scott@TEST>select * from emp;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1218 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 1218 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
......
4、11gR2的自动并行
Oracle在11gR2中引入了自动并行(Auto DOP),自动并行的开启受参数parallel_degree_policy的控制,其默认值为MANUAL,即自动并行在默认情况下并没有开启。如果通过更改PARALLEL_DEGREE_POLICY的值而开启了自动并行,那么后面执行的SQL的执行方式是串行还是并行,以及并行执行的并行度是多少等,就都是由Oracle自动来决定了。
scott@TEST>select table_name,degree from user_tables where table_name in ('EMP','EMP_TEMP');
TABLE_NAME DEGREE
------------------------------------------------------------------------------------------ ------------------------------------------------------------
EMP 1
EMP_TEMP 1
scott@TEST>alter session set parallel_degree_policy=AUTO;
Session altered.
scott@TEST>set autot