oracle有类似explain,Oracle-SQL Explain Plan解读

概述

执行计划：一条查询语句在ORACLE中的执行过程或访问路径的描述。

基于ORACLE的应用系统很多性能问题，是由应用系统sql性能低劣引起的，所以，sql的性能优化很重要，分析与优化sql的性能我们一般通过查看该sql的执行计划。

常用方法

1.Explain Plan For sql

•不要实际执行sql语句，生成的计划未必是真实执行的计划

•必须要有plan_table

2.sqlPLUS AUTOTRACE

•除set autotrace traceonly explain外均实际执行sql，但仍未必是真实计划

•必须要有plan_table

3.sql TRACE

•需要启用10046戒者sql_TRACE

•一般用tkprof看的更清楚些，当然10046里本身也有执行计划信息

4.V$sql和V$sql_PLAN

•可以查询到多个子游标的计划信息了，但是看起来比较费劲

5.Enterprise Manager

•可以图形化显示执行计划，但并非所有环境有EM可用

6.其他第三方工具

•注意 PL/sql developer之类工具F5看到的执行计划未必是真实的

最靠谱的方法-Display_cursor

DBMS_XPLAN包概述

DBMS_XPLAN这个包最初是在ORACLE 9i R2中引入的，用来查看Explain Plan生成的执行计划。DBMS_XPLAN 在ORACLE 10g 以及ORACLE 11g中，功能都有所增长。

官方文档

10g和11g的函数说明

在ORACLE 10g提供了下面4个函数的功能:

在ORACLE 11g提供了下面5个函数的功能:

相比10g增加了 DISPLAY_sql_PLAN_BASELINE

DISPLAY_sql_PLAN_BASELINE - to display one or more execution plans for the sql statement identified by sql handle

display_cursor的定义

我们可以在dbms_xplan这个包中看到 display_cursor的定义如下：

入参说明：

sql_id:

指定位于库缓存执行计划中sql语句的父游标。默认值为null。

当使用默认值时当前会话的最后一条sql语句的执行计划将被返回

可以通过查询V$sqlAREA的sql_ID列来获得sql语句的sql_ID。

cursor_child_no:

指定父游标下子游标的序号。即指定被返回执行计划的sql语句的子游标。默认值为0。

如果为null，则sql_id所指父游标下所有子游标的执行计划都将被返回。

format：

控制sql语句执行计划的输出部分，即哪些可以显示哪些不显示。使用与display函数的format参数与修饰符在这里同样适用。

除此之外当在开启statistics_level=all时或使用gather_plan_statistics提示可以获得执行计划中实时的统计信息。

TYPICAL：这个是默认值，显示执行计划中大部分信息(operation id,name and option,#rows,#bytes and optimizer cost)，并行、谓词信息等， 除了别名，提纲和字段投影外。

还有

BASIC：仅显示最少信息。基本上只包括操作ID、操作名称和操作对象。

SERIAL：跟TYPICAL类似，除了并行操作信息没有显示，即使执行计划有并行处理。

ALL：显示所有信息。

display_cursor说明

DISPLAY_CURSOR函数，它显示存储在库缓存(library cache)中的实际执行计划，当然你要查询某个sql语句的实际执行计划，前提是这个sql的执行计划还在库缓存中，如果它已经被刷出库缓存，就无法获取其实际执行计划。

执行方法

select * from table(DBMS_XPLAN.display_cursor(.....));

栗子

--执行sql

select e.ename from emp e,dept d where e.deptno=d.deptno and e.empno=7499 ;

--获取sql_id 9uz917qhd6dv0

select * from v$where a.sql_TEXT like '%select e.ename from emp e,dept d where e.deptno=d.deptno%';

--说明：查询v$sql视图，找到该语句的sql_id，有可能该sql语句不在Share Pool里面了，此时表明该sql已经被踢出Share Pool。

--查询执行计划

select * from table(DBMS_XPLAN.display_cursor('9uz917qhd6dv0'));

--说明：不传递任何参数给display_cursor函数，表示显示当前会话最后一条sql语句的执行计划