用二叉树的中序遍历来看Oracle执行计划的执行顺序
用二叉树的中序遍历来看Oracle执行计划的执行顺序
我们先用一个小表来,来说明一下,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 |
- ----------------------------------------------------------------------------------------------
|
评论暂时关闭