Oracle执行计划 讲解(二)
Oracle执行计划 讲解(二)
现在我们讲讲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
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> 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
每段执行的时间
|
评论暂时关闭