Oracle执行分析详细
我们以下面的一个例子来讲解
这里做个补充：trace的类型一共有以下几种
序号 | 命令 | 解释 |
1 | SET AUTOTRACE OFF | 此为默认值，即关闭Autotrace |
2 | SET AUTOTRACE ON EXPLAIN | 只显示执行计划 |
3 | SET AUTOTRACE ON STATISTICS | 只显示执行的统计信息 |
4 | SET AUTOTRACE ON | 包含2,3两项内容 |
5 | SET AUTOTRACE TRACEONLY | 与ON相似，但不显示语句的执行结果 |
我喜欢SET AUTOTRACE TRACEONLY，我们以后的例子都是基于这种方式的
SQL> select * from departments a where a.department_id in (select b.department_id from employees b where b.employee_id=205);
Execution Plan
----------------------------------------------------------
Plan hash value: 2782876085
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | 2 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 27 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 7 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 540 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("B"."EMPLOYEE_ID"=205)
5 - access("A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
749 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
让我们来一行一行的看：
一、表部分
1、Plan hash value:
Plan hash value: 2782876085
这一行是这一条语句的hash值，我们知道Oracle对每条语句产生的执行计划放在share pool里面，第一次要经过硬解析，产生hash值。下次再执行该语句时候比较hash值，如果相同就不要执行硬解析。
2、Operation（ 操作）
3、Name（被操作的对象）
比如上例中的第二行operation（TABLE ACCESS BY INDEX ROWID）这里的TABLE对象为EMPLOYEES
4、Row，有的地方也叫Cardinality（用plsqldev里面解释计划窗口）
这里是数据查询的行数，比如说上个例子第4行， departments 这张表就要扫描27行，然后和子查询（select b.department_id from employees b where b.employee_id=205）的值进行比较。如果使用=（注：大部分时候是不能用=来替换，这里是特例）就不一样了。
SQL> select * from departments a where a.department_id = (select b.department_id from employees b where b.employee_id=205);
Execution Plan
----------------------------------------------------------
Plan hash value: 3449260133
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 7 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."DEPARTMENT_ID"= (SELECT /*+ */ "B"."DEPARTMENT_ID" FROM "EMPLOYEES"
"B" WHERE "B"."EMPLOYEE_ID"=205))
4 - access("B"."EMPLOYEE_ID"=205)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
749 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
5、Byte
扫描的数据的字节数
6、Cost
这里上次讲过了，这里简单说下吧。
cost没有单位，是一个相对值，是sql文以cbo方式解析执行时，供oracle用来评估cbo成本，选择执行计划用的。
公式：Cost=（Single block I/O cost+ Multiblock I/O cost+ CPU cost）/sreadtim
没有明确的含义，不过对比时就非常有用了。
7、Time
每段执行的时间