使用 EXPLAIN PLAN 获取SQL语句执行计划
SQL查询语句的性能从一定程度上影响整个数据库的性能。很多情况下,数据库性能的低下差不多都是不良SQL语句所引起。而SQL语句的执行
计划则决定了SQL语句将会采用何种方式从数据库提取数据并返回给客户端,本文描述的将是如何通过EXPLAIN PLAN 获取SQL语句执行计划来获
取SQL语句的执行计划。
一、获取SQL语句执行计划的方式
1. 使用explain plan 将执行计划加载到表plan_table,然后查询该表来获取预估的执行计划
2. 查询动态性能视图v$sql_plan,v$sql_plan_statistics,v$sql_workarea 等来获取已缓存到库缓存中的真实执行计划
3. 查询自动工作量资料库(Automatic Workload Repository)或查询Statspack,即从资料库中获取执行计划
4. 启用执行计划跟踪功能,即autotrace功能
5. 使用PL/SQL Developer提供的获取执行计划方法
6. 使用Toad工具来获取执行计划
下面主要讨论使用explain plan获取执行计划的方法
二、explain plan工作实质、前提及操作方法
1. 工作实质
将SQL语句预估的执行计划加载到表plan_table,是对表plan_table 执行了DML操作,故不会执行隐式提交
可以对select,insert,update,merge,delete,create table, create index,alter index等加载执行计划到plan_table
2. 前提条件
需要先创建plan_table,创建方法:@?/rdbms/admin/utlxplan
对当前的SQL语句有执行权限以及对依赖的对象有相应操作的权限
3. 使用方法:
explain plan for select * from scott.emp where ename='SCOTT'; --未设置标记位
explain plan set statement_id='TEST' for select * from scott.emp where ename='SCOTT' --设置标记位为TEST
三、实战演习
1.环境
2.创建测试表演示获取执行计划
3.使用自顶向下的读取方法获取执行计划
上面的例子的读取方法:
执行4.1的索引唯一扫描
将4.1的结果集返回给3.1
执行3.2的全表扫描
将3.1和3.2步骤的结果集返回给2.1
执行2.1的嵌套循环
返回最终结果集
注意嵌套循环的查询方法
Oracle 从第一个行源中读取第一行,然后和第二个行源中的所有记录行进行比对,所有匹配的记录放在结果集中,然后Oracle 将读第一
个行源中的下一行。依次类推,直到第一行源中的所有行处理完毕。
4.使用构建树方式查看执行计划
查询结果中的order列与opt列
order
order列的指名了ID,父ID,以及执行计划中这一步骤的位置。
ID列标识了这个步骤,但并没有说明执行的顺序
父ID表明了这个步骤中的父步骤
位置信息说明了父ID相同的子操作的执行顺序
opt
说明当前优化器使用的模式
分析
首先会从步骤3开始执行,步骤3通过索引唯一扫描PK_EMP将得到的结果集返回给父步骤2
步骤2根据上一子步骤3得到的rowid访问表EMP并将结果集返回给父步骤1
对于步骤2检索到的每一行数据,步骤1会将deptno传递给步骤5
步骤5根据得到的deptno执行索引唯一扫描并将结果集返回给步骤4
步骤4根据步骤5得到的rowid 访问表dept,并将结果集返回给父步骤1
对于步骤3中剩余的行依次按上述方式将所有结果集返回给步骤1
步骤1将获得的最终结果集返回给步骤0,SQL完成查询
根据查询返回的结果来构建执行计划树
从ID为1的列开始,作为根节点
寻找所有父ID为1的所有子ID,如本例为2和4,将其纳入树中
分别寻找以2和4为父ID的所有子ID,将其纳入树中
如此循环直到所有的ID没有父ID
---------------
NESTED LOOP (1)
---------------
- -
- -
- -
--------- ----------
EMP (2) DEPT(4)
--------- ----------
- -
- -
--------- ----------
PK_EMP(3) PK_DEPT(5)
--------- ----------
5.通过Oracle 自带的SQL语句执行计划
可以通过Oracle提供的SQl语句来获得当前会话最后一条SQL语句的执行计划
utlxpls.sql -->用于查看串行执行计划
utlxplp.sql -->用于查看并行执行计划
四、总结:
1. explain plan并不执行当前的SQL语句,而是根据数据字典中记录的统计信息获取最佳的执行计划并加载到表plan_table。
2. 由于统计信息,执行环境的变化,explain plan与实际的执行计划可能会有差异。
3. 对于运行时将较长的SQL语句,不需要等到结果输出即可提前获得该SQL的执行计划,对于生产环境调试情况会减轻数据库负荷。
4. 注意set statement_id标识符区分大小写。