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] view plaincopyprint

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

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:

[sql] view plaincopyprin

Oracle执行分析详细(一)