Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
57150 consistent gets
39162 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
再次增加表t1的数据记录
SQL> insert into t1 select * from t1;
5510144 rows created.
SQL> commit;
Commit complete.
SQL> alter system flush buffer_cache;
System altered.
SQL> set autotrace on
SQL> select count(*) from t1;
COUNT(*)
----------
11020288
Elapsed: 00:00:09.05
Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42507 (1)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 11M| 42507 (1)| 00:00:03 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
- automatic DOP: Computed Degree of Parallelism is 1
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
223549 consistent gets
156619 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
可以看到执行时间为9.05秒，Oracle使用串行执行，继续向表t1增加记录
SQL> insert into t1 select * from t1;
11020288 rows created.
SQL> commit;
Commit complete.
SQL> alter system flush buffer_cache;
System altered.
SQL> show parameter parallel_degree_policy
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy string AUTO
SQL> select * from V$IO_CALIBRATION_STATUS;
STATUS CALIBRATION_TIME
------------- ---------------------------------------------------------------------------
READY 13-APR-16 10.12.58.413 PM
Elapsed: 00:00:00.08
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