现在的位置: 首页 > 数据库 > 正文

Oracle执行计划介绍

2014年11月10日 数据库 ⁄ 共 3260字 暂无评论 ⁄ 阅读 1,620 次
文章目录

解释计划

语句EXPLAIN PLAN FOR用来显示优化器预期为SQL语句选择的执行计划,得到的并非实际的执行计划以及相关的数据源执行统计信息,这些都是优化器的预估值,而不是实际值。在此,我们将估算信息称为解释计划输出,而实际信息称为执行计划输出。

使用解释计划

当使用EXPLAIN PLAN来为一个查询生成预期的执行计划时,输出将包括以下几种:

  • SQL语句中所引用到的每一张表;
  • 访问每张表所用的方法;
  • 每一对需要联结的数据源所用的联结方法;
  • 按次序列出的所有需要完成的运算;
  • 计划中各步骤的谓语信息列表;
  • 对于每个运算,估计出该步骤所要操作的数据行数和字节数;
  • 对于每个运算,估算出成本值;
  • 所访问的分区信息;
  • 并行执行的相关信息;

生成解释计划

生成解释计划有两种方法:使用EXPLAIN PLAN命令或SQL*Plus AUTOTRACE命令。

使用EXPLAIN PLAN命令需要配合dbms_xplan.display函数,具体举例如下:

explain plan for

select * from emp where empno = ‘7566’;

查看解释计划使用语句:

select * from table(dbms_xplan.display);

使用AUTOTRACE命令有以下参数可供选择:

SET AUTOTRACE {OFF|ON|TRACEONLY} [EXPLAIN|STATISTICS]

其中OFF|ON|TRACEONLY必须选择其一,EXPLAIN|STATISTICS可选,其具体选项和效果如下:

1)set autotrace on;

显示:记录集+explain+statistics

2)set autotrace on explain

显示:记录集+explain

3)set autotrace on statistic

显示 :记录集+statistics

4)set autotrace traceonly

显示: expain+statistics(不显示数据)

5)set autotrace traceonly statistics

显示: statistics

6)set autotrace traceonly explain

显示: explain

7:set autotrace off

显示:只显示记录集,这也就是默认的SQL PLUS的窗口效果

EXPLAINPLAN和TRACLEONLY EXPLAIN都不实际执行查询,它只产生预期的执行计划。

EXPLAIN PLAN命令生成会默认存储在表PLAN_TABLE中,需要手工查询输出结果。

AUTOTRACE命令从所提供的dbms_xplan包中调用display函数来自动生成输出,不需要手工查询。

dbms_xplan.display函数的一个非常好的特性是它可以基于每一个特定的SQL语句所生成的执行计划而自动显示适当的列。

解释计划偏差

解释计划可能与实际的执行计划不一致,以下3点原因就可能造成其不一致的产生:

  • 解释计划是基于你使用它的时候的环境来生成的;
  • 解释计划不考虑绑定变量的数据类型,默认字符串型,这可能造成隐式转换而导致索引失效,但在解释计划里则显示索引未失效,实际执行时却会采用全表扫描;
  • 解释计划不“窥视”绑定变量的值;

阅读解释计划

阅读和理解解释计划可以通过3种途径:

  • 学会识别和分割父子组;
  • 掌握计划中运算执行顺序;
  • 学会以叙述的形式来阅读计划;

首先,看计划的执行顺序

计划是按照运算的顺序ID号来显示的,但是,每个运算执行的顺序并不是严格按照自上而下来进行的。通过运算的缩进这一视觉线索,你可以很快地浏览整个计划并寻找缩进最多的运算。缩进最多的运算实际上是执行过程中首先进行的运算,如果在同一层次上有多个运算,则按照自上而下的顺序来依次执行。

执行计划中还有一栏predicates information,这是谓语信息。其中,会显示access和filter及具体WHERE子句条件。access表示访问时仅访问指定的数据行,filter表示访问后再过滤到不合要求的数据行,一般而言对子查询再限定通常是filter。在谓语词之前有ID,这是解释计划中的ID。而解释计划中每一个相关的访问或筛选谓语的运算,在其ID的旁边都有一个星号(*)。看到星号时我们可以在谓语信息部分寻找ID号来确定哪个谓语(WHERE子句的条件)是与该运算相关的。

执行计划

当一条SQL语句被执行后,可以通过查询V$SQL_PLAN来查看计划运算。

查看已执行过的SQL语句的执行计划,可以在查询中使用gather_plan_statistics提示。为了为计划抓取行数据源执行统计信息,必须告诉Oracle在语句执行时收集这些信息。否则,默认是不收集的,因为收集这些信息增加了语句执行的成本。查看收集的信息则使用dbms_xplan.display_cursor函数,收集信息语句如:

select /*+ gather_plan_statistics */ empno,ename from emp where ename = ‘KING’;

查看收集到的信息使用语句如:

select * from table(dbms_xplan.display_cursor(null,null,’ALLSTATS LAST’));

行数据源的执行统计信息包括行数、一致性读取次数、物理读取次数、物理写入次数,以及每一个运算在一行数据上的运行时间。需要注意的是,如果不使用hint提示gather_plan_statistics则无法获取这些信息。另外,需要注意的是,使用信息的人必须具有对v$session的权限。

收集执行计划统计信息

要想知道计划的效果,需要知道行数据源执行统计信息。该数据是从V$SQL_PLAN_STATISTICS视图中取出的,这个视图将计划的每一个运算行与一行统计数联系起来。而V$SQL_PLAN_STATISTICS_ALL的复合视图包括了V$SQL_PLAN的所有列加上V$SQL_PLAN_STATISTICS中的列以及一些包含内存使用信息的附加列。

dbms_xplan.display_cursor()函数有3个参数,SQL_ID、CURSOR_CHILD_ID和FORMAT。SQL_ID和CURSOR_CHILD_ID参数使用空值表明了需要取出上一条执行语句的执行计划,而FORMAT参数使用ALLSTATS LAST表示输出格式。

想从库高速缓存中取出计划SQL_ID和CURSOR_CHILD_ID,可以通过在SQL中加注释的方法从V$SQL中取出,如加注释:

select /* KM-EMPTEST1 */ empno,ename from emp where job = ‘MANAGER’;

取ID:

select sql_id,child_number,sql_text from v$sql where sql_text like ‘%KM-EMPTEST1%’;

在查看执行计划时,我们可以关注,在不同的过滤步骤中,有多少数据被收集后“过滤”掉了。这很重要,因为我们获取数据需要成本,过滤数据又需要成本。这时,我们可以考虑如果在获取信息时就少获取那些需要被过滤掉的信息。另外,我们需要关注的是全表扫描,在表小时可能效率看不出来,但当表极大时,全表扫描是性能的一个障碍。

执行计划常见关键字

【数据访问方法】

1) 全表扫描(Full Table Scans, FTS)

2) 通过ROWID的表存取(Table Access by ROWID或rowid lookup)

3) 索引扫描(Index Scan或index lookup)有4种类型的索引扫描:

  • 索引唯一扫描(index unique scan)
  • 索引范围扫描(index range scan)
  • 索引全扫描(index full scan)
  • 索引快速扫描(index fast full scan)

【表联结方法】

1) 排序 - 合并连接(Sort Merge Join, SMJ)

2) 嵌套循环(Nested Loops, NL)

3) 哈希连接(Hash Join, HJ)

4) 笛卡儿乘积(Cartesian Product)

» 声明:本站文章源于个人经验总结或书籍、互联网转载,内容仅用于个人学习,请勿转载,否则后果自负!

给我留言

留言无头像?