oracle执行计划对我们并不陌生，往往我们在工作中只会在发生性能瓶颈时看一下，没有想过执行计划是如何生成的。下面用实例来模拟数据访问方式和数据处理方式的演变。
1.执行计划—通过表访问数据 TABLE ACCESS FULL
LEO1@LEO1> create table leo1 as select * from dba_objects; 我们创建一张表leo1
Table created.
LEO1@LEO1> select count(*) from leo1; 这张表有71955条记录
COUNT(*)
----------
71955
LEO1@LEO1> set autotrace trace exp; 启动执行计划
LEO1@LEO1> select * from leo1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2716644435
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 83162 | 16M| 287 (1)| 00:00:04 |
| 1 | TABLE ACCESS FULL| LEO1 | 83162 | 16M| 287 (1)| 00:00:04 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
数据访问方式：走的是全表扫描，因为没有创建索引，所以没办法走索引，这是一种效率不高的数据访问方式，在实际应用中较少。
2.执行计划—通过表并行访问数据 PARALLEL
LEO1@LEO1> select /*+ parallel */ count(*) from leo1; 自动评估并行度
Execution Plan
----------------------------------------------------------
Plan hash value: 452265093
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 159 (0)| 00:00:02 | | | |
| 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| | 71955 | 159 (0)| 00:00:02 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| LEO1 | 71955 | 159 (0)| 00:00:02 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- automatic DOP: Computed Degree of Parallelism is 2
如果不指定并行度，优化器自动评估并行度为2，因为我的小本本就是双核的，并行度最大只能是2
LEO1@LEO1> select /*+ parallel(leo1 4) */ count(*) from leo1; 指定4个并行度
Execution Plan
----------------------------------------------------------
Plan hash value: 452265093
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 80 (2)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |