Statistics
----------------------------------------------------------
20 recursive calls
4 db block gets
470138 consistent gets
313225 physical reads
0 redo size
425 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
parallel_degree_policy=auto就会启用以下新功能:
并行度(DOP)将会基于SQL语句中的操作类型和表的大小来自动计算。例如对大表排序的并行度(DOP)可能比对小表操作的并行度高。
如果请求或请求的并行度(DOP)因为并行服务进程正处于繁忙状态而不能获得满足，那么Oracle直到有足够的并行子进程可用之前将不会执行语句，而不是降低并行度或串行执行SQL语句。在11gr2之前的版本中，当没有足够的并行进程服务进程满足所请求的并行度(DOP)时，可以会出现以下三种情况中的一种:
SQL语句将会降低并行度(DOP)来以并行方式执行
SQL语句以串行方式来执行
如果parallel_min_percent被设置将收到"ORA-12827:insufficient parallel query slaves available"
Oracle并行子进程可能使用buffered IO而不是直接IO。例如"in-memory parallel execution"
SQL> show parameter parallel_degree_policy
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy string AUTO
SQL> select degree,instances from user_tables where table_name = 'T1';
DEGREE INSTANCES
-------------------- --------------------
1 1
Elapsed: 00:00:00.00
SQL> set autotrace on
SQL> select count(*) from t1;
COUNT(*)
----------
22040576
Elapsed: 00:00:18.50
Execution Plan
----------------------------------------------------------
Plan hash value: 3110199320
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47183 (1)| 00:00:04 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 21M| 47183 (1)| 00:00:04 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| T1 | 21M| 47183 (1)| 00:00:04 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
- automatic DOP: Computed Degree of Parallelism is 2