Oracle执行分析详细 - 数据库编程

Plan hash value: 2782876085
```
Plan hash value: 2782876085
```
这一行是这一条语句的hash值，我们知道oracle对每条语句产生的执行计划放在share pool里面，第一次要经过硬解析，产生hash值。下次再执行该语句时候比较hash值，如果相同就不要执行硬解析。

2、Operation（操作）

这里的东西就多了，就是把sql进行分解，让我一起看看上的sql，这段sql的第一步是employee_id=25，这里我们employee_id上面建了主键，建主键默认创建唯一索引。这里是用“=”进行限制的，所以走的unique scan方式。其他方式参考Oracle执行计划讲解（一）内容

还有一个知识点，就是要知道表链接操作，见我的另外一篇文章（）

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] view plaincopyprint
1. SQL> select * from departments a where a.department_id = (select b.department_id from employees b where b.employee_id=205);
2. Execution Plan
3. ---------------------------------------------------------- Plan hash value: 3449260133
4. -----------------------------------------------------------------------------------------------
5. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -----------------------------------------------------------------------------------------------
6. | 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 1 | 20 | 1 (0)| 00:00:01 |
7. |* 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 |
8. |* 4 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 | -----------------------------------------------------------------------------------------------
9. Predicate Information (identified by operation id):
10. ---------------------------------------------------
11. 2 - access("A"."DEPARTMENT_ID"= (SELECT /*+ */ "B"."DEPARTMENT_ID" FROM "EMPLOYEES" "B" WHERE "B"."EMPLOYEE_ID"=205))
12. 4 - access("B"."EMPLOYEE_ID"=205)
13. Statistics
14. ---------------------------------------------------------- 0 recursive calls
15. 0 db block gets 4 consistent gets
16. 0 physical reads 0 redo size
17. 749 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client
18. 2 SQL*Net roundtrips to/from client 0 sorts (memory)
19. 0 sorts (disk) 1 rows processed
20. ```
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"."EMP
            
```

Oracle执行分析详细(二)