用二叉树的中序遍历来看Oracle执行计划的执行顺序

[img]http://dl2.iteye.com/upload/attachment/0095/2463/17ad62ae-bd7f-3c65-a63d-c9e373763919.jpg[/img]
我们先用一个小表来，来说明一下，Oracle执行计划该注意哪些地方。

hr@ORCL> set autotrace traceonly
hr@ORCL> select * from t;

模糊比较两条sql的优劣时，建议先查看以下两个值：
Cost (%CPU)：cpu代价，这个值要代数和。比如，这里是3+3=6
consistent gets：这个值一般要多执行几次！让他稳定下来，才有比较的意义。
Execution Plan ：
----------------------------------------------------------
Plan hash value: 1601196873
下面这个计划看的方法：由里到外，由上到下
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 1 | 6 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
228 recursive calls --访问数据字典得到元数据。第二次执行相同语句，递归调用基本为零。
0 db block gets -- 指DML语句所得到的数据块个数
33 consistent gets --重要！！指select语句所得到的数据块个数
8 physical reads --硬盘上读出的数据
0 redo size --产生的日志
414 bytes sent via SQL*Net to client --网络流量指标
385 bytes received via SQL*Net from client --网络流量指标
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
现在，我们用一个大表，来把执行计划列出来。

sys@ORCL> select * from dba_objects;

50393 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2127761497

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 48669 | 8412K| 145 (5)| 00:00:02 |
| 1 | VIEW | DBA_OBJECTS | 48669 | 8412K| 145 (5)| 00:00:02 |
| 2 | UNION-ALL | | | | | |
|* 3 | FILTER | | | | | |
|* 4 | HASH JOIN | | 51423 | 4670K| 143 (5)| 00:00:02 |
| 5 | TABLE ACCESS FULL | USER$ | 62 | 868 | 2 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | OBJ$ | 51423 | 3967K| 140 (4)| 00:00:02 |
|* 7 | TABLE ACCESS BY INDEX ROWID| IND$ | 1 | 8 | 2 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | LINK$ | 1 | 88 | 0 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 1 | 102 | 2 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | USER$ | 62 | 868 | 2 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | I_LINK1 | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------