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标识符区分大小写。
分享到:
相关推荐
oracle执行计划,oracle explain plan,在ORACLE数据库中,需要对SQL语句进行优化的话需要知道其执行计划,从而针对性的进行调整.ORACLE的执行计划的获得有几种方法,下面就来总结下
1. 预估执行计划 – Explain PlanExplain plan以SQL语句作为输入,得到这条SQL语句的执行计划,并将执行计划输出存储到计划表中。 首先,在你要执行的SQL语句前加explain plan for,此时将生成的执行计划存储到计划...
1.如何分析SQL语句 2.选用适合的ORACLE优化器 3.用EXPLAIN PLAN 分析SQL语句 4.使用TKPROF 工具来查询SQL性能状态 5.表分区的应用
优化器的选择、共享SQL语句、访问Table的方式、减少访问数据库的次数、使用DECODE函数、减少对表的查询、通过内部函数提高SQL效率、用NOT EXISTS替代NOT IN、使用EXPLAIN PLAN分析SQL语句、索引及索引的效率、使用...
SQL*PlusAUTOTRACE可以用来替代SQLTrace使用,AUTOTRACE的好处是不必设置跟踪文件的格式,并且它将自动为SQL语句显示执行计划。AUTOTRACE与执行计划的区别是AUTOTRACE分析和执行语句;而EXPLAINPLAN仅分析语句,而不...
一、什么是执行计划(explain plan) 执行计划:一条查询语句在ORACLE中的执行过程或访问路径的描述。 二、如何查看执行计划 1: 在PL/SQL下按F5查看执行计划。第三方工具toad等。 很多人以为PL/SQL的执行计划只能...
EXPLAIN PLAN是一个很好的分析SQL语句的工具,它可以在不执行SQL的情况下分析语句 通过分析,我们就可以知道ORACLE是怎样连接表,使用什么方式扫描表(索引扫描或全表扫描),以及使用到的索引名称 按照...
<7> 运行命令 "explain plan for SQL" 或按菜单项 "Explain Plan",能快速地显示 SQL 的执行计划; "Export" 按钮或菜单项,能直接将 SELECT 语句结果转化为 INSERT 语句,方便于数据移植、备份等 <8> 运行命令...
+218 01537 因为SQL语句引用一个远程对象,不能为该SQL语句执行EXPLAIN +219 01532 命名的PLAN TABLE不存在 +220 01546 不正确定义PLAN TABLE,检查命名列的定义 +236 01005 SQLDA中的SQLN的值至少应于所描述...
(2)“Explain Plan” 按钮能快速地显示语句的执行计划; (3)“Export” 按钮能将SQL编辑器中的 SELECT 语句块直接导出为 XLS /CSV /INSERT SQL /HTML /XML 等格 式,方便于数据移植、备份、打印及存档等; (4)...
explain plan for select ename,job,sal,comm from empcon where (sal-700);--已解释 desc plan_table; col id for 999 col operation for a16 col option for a16 col object_name for a16 Select id,operation,...
9. 让 SQL 语句用上合理的索引,合理让 SQL 语句使用索引的原则如下: 首先,看是否用上了索引,对于该使用索引而没有用上索引的 SQL 语句,应该想办法用上索引。 其次,看是否用上正确的索引了,特别复杂的 ...
(2)“Explain Plan” 按钮能快速地显示语句的执行计划; (3)“Export” 按钮能将SQL编辑器中的 SELECT 语句块直接导出为 XLS /CSV /INSERT SQL /HTML /XML 等格式,方便于数据移植、备份、打印及存档等; (4)...
非常容易对SQL语句的分析其执行计划:单击工具栏上的 按钮就可以看到Explain Plan的结果 支持部分SQL*Plus命令,可以方便的执行大量的为SQL*Plus编写的数据库脚本 可以通过SQLab xpert来对当前SQL语句...
(2)“Explain Plan” 按钮能快速地显示语句的执行计划; (3)“Export” 按钮能将 SELECT 语句块直接导出为 INSERT 语句,方便于数据移植、备份等; (4)支持列块操作; (5)加上/去掉注释标记、加上/去掉...
在 SQLite 语句之前,可以使用 “EXPLAIN” 关键字或 “EXPLAIN QUERY PLAN” 短语,用于描述表的细节。 如果省略了 EXPLAIN 关键字或短语,任何的修改都会引起 SQLite 语句的查询行为,并返回有关 SQLite 语句如何...
第3章 sql的执行计划(explain plan)74 3.1 sql和优化器75 3.1.1 优化器的作用和人的作用77 3.1.2 优化器的类型80 3.1.2.1 基于规则的优化器82 3.1.2.2 基于成本的优化器86 3.1.2.3 优化器目标的选择93 3.1.2.4 执行...
第3章 sql的执行计划(explain plan)74 3.1 sql和优化器75 3.1.1 优化器的作用和人的作用77 3.1.2 优化器的类型80 3.1.2.1 基于规则的优化器82 3.1.2.2 基于成本的优化器86 3.1.2.3 优化器目标的选择93 3.1.2.4 执行...