oracle执行计划相关

sql执行计划对于sql执行效率是至关重要的，所以对于程序开发人员和DBA而言，了解一下sql执行计划都是很有好处的。

什么是执行计划

sql是一种声明性语言，它和我们平时常用的c，java这些命令性语言差别很大，声明性语言是告诉计算机我想要什么，而命令性语言是告诉计算机我想你这样去做。具体来讲，sql是告诉数据库系统，我要满足这些条件的数据，具体数据库系统怎么去获取sql想要的这些数据，就是数据库系统的事情了。
执行计划就是描述数据库是怎样去获取这些数据的过程。

怎样获取执行计划

执行计划根据是不是真实运行情况，我将执行计划分为预期执行计划和实际执行计划计划两种，explain plan得到的计划是根据当前的环境，统计等信息得出的，真正执行的时候是不是使用这个计划是不一定的（当然绝大部分情况，就是使用这个执行计划），实时计划就是真实运行中使用的计划。

explain plan（预期执行计划）

如explain plan for select * from emp;
然后执行
select * from table(dbms_xplan.display);
获取执行计划。

sqlplus里的autotrace （实时计划）

配置autotrace

--以system用户登录sqlplus
sqlplus system/system_password@orcl
--然后执行脚本
@?rdbmsadminutlxplan

grant all on plan_table to public;

以sysdba登录sys用户
conn sys/sys_password as sysdba
--执行脚本
@?sqlplusadminplustrce

grant plustrace to public;

ps:
在sqlplus里?代表ORACLE_HOME,不用自己写一长串字符了。

使用autotrace

在sqlplus里执行一条sql语句前，使用以下命令控制autotrace

set autot[race] off 关闭autotrace功能 set autot on 显示sql执行结果，执行计划，统计信息 set autot on explain 显示sql执行结果，执行计划 set autot on statistics 显示sql执行结果，统计信息 set autot traceonly 显示 执行计划，统计信息

sql跟踪（实时计划）

准备
设置参数(可以在会话级或者系统级)
timed_statistics=true
max_dump_file_size=unlimited

跟踪文件的路径
在11g可以直接使用
select sid from v$mystat where rownum=1得到会话sid，然后通过v$session和v$process获取v$process的tracefile获得跟踪文件。

select t1.sid,t2.tracefile from v$session t1,v$process t2 where t1.paddr=t2.addr and t1.sid=xxx 其中xxx为从测试会话里获取的sid select distinct sid from v$mystat

或者查看v$process的spid，一般跟踪文件为sid_ora_spid.trc

使用alter session set tracefile_identifier=’xxxx’ 然后查找有该助记符的跟踪文件即可

启动跟踪的方法
1.sql_trace方法(已经不推荐使用，应使用更方便和强大的dbms_monitor包)
alter session set sql_trace=true
执行需跟踪的sql
alter session set sql_trace=FALSE

2.使用dbms_monitor包
可以根据基于以下内容设置跟踪的范围
会话 基于会话id(sid)和序列号(serial number)

客户端标识符 允许跨越多个会话设置跟踪，使用dbms_session.set_identifier设置标识符，然后oracle会跟踪所有设置了该标识符的会话

实例 基于实例名

服务 指定一组相关的应用程序，使用dbms_service.create_service创建服务

模块名 开发人员在其程序代码里使用dbms_application_info.set_module设置该值

操作名 开发人员在程序代码里设置dbms_application_info.set_action设置该值

可以设置跟踪的内容
等待 true表示等待信息写入跟踪文件
绑定 true表示绑定信息写入跟踪文件
计划统计 行源统计信息写入跟踪文件的频率，包括 never，first_execution(默认)，all_executions，设成all_executions可以确保关于执行计划的所有信息写入跟踪文件。

基于会话id和序列号设置跟踪
select sid，serial#，username from v$session获取sid和serial#

exec dbms_monitor.session_trace_enable(123，4567,true,false)
表示跟踪123:4567会话跟踪等待，不跟踪绑定
exec dbms_monitor.session_trace_disable(123，4567)

exec dbms_monitor.session_trace_enable(null，null)
表示跟踪当前会话

查看跟踪情况
使用dba_enableed_traces和dba_enabled_aggregations可以查看启用的跟踪和搜集的统计信息，使用这些确保跟踪已被全部禁用。

使用tkprof工具转化跟踪文件为可读格式
简单用法 tkprof tracefile outputfile explain=system/manager
具体查看帮助

理解执行计划

执行计划主要有三个因素，访问路径，连接和执行顺序。

优化器访问路径

访问路径指的是从数据库获取数据的方法。总体来说，索引访问路径在获取少量行时有用，全表扫描对于访问一个表里大部分行时更有效率。

优化器根据一下几个因素决定访问路径:
- 语句的可用访问路径
- 使用每个访问路径或者合并路径估计执行语句的估计成本

优化器首先根据查询语句里where子句和from子句决定那些访问路径是可用的。优化器使用可用的路径生成所有可能得执行计划，使用索引，列和表的统计信息，计算执行计划的成本，然后优化器选择成本最低的执行计划作为该语句的执行计划。

优化器访问路径有如下几种:

全表扫描(full table scans)

全表扫描读取一个表的全部行然后使用过滤条件滤除不满足条件的数据。(全表扫描，会扫描高水位下的所有数据块)。全表扫描时，oracle顺序读取数据块，因为数据块是相邻的，所以数据库可以使用比单块读更加大的io调用。使用db_file_multiblock_read_count控制一次最大可以读取多少块。

有趣的是，全表扫描有可能比索引范围扫描更加的高效，这是因为在访问一个表大部分数据时，全表扫描可以使用更大的io调用，比多次小的io调用更加的高效。

优化器什么时候使用全表扫描
- 缺少索引
- 访问大量的数据
- 访问小表(数据块数小于20)
- 高并发度的并行执行
- 全表扫描提示

rowid扫描(rowid scans)

rowid是一行数据的精确物理存储位置(在哪个数据文件，在数据文件的那个块，在数据块的哪一行)，是获取一行数据的最快方式。

rowid扫描是先获取指定行的rowid(通过where条件过滤或者对索引的扫描)，然后根据rowid定位选定的数据行。

优化器什么时候使用rowid扫描
rowid扫描是获取rowid后的第二步，利用rowid扫描获取索引中不存在的列信息，因此如果索引中存在语句所需的所有列，则不会发生rowid扫描。

索引扫描(index scans)

索引扫描使用语句中的索引列的值在索引中查找一行数据的方法。oracle以两种方式使用索引扫描，一种是索引里包含了语句所需的所有信息，此时访问索引就够了，不需要再去访问表了。另一种是语句还需要表里其余列的信息，此时需要通过索引列值对应的rowid，再通过rowid扫描或者聚簇扫描访问相应的表，获取其余列的信息。

索引使用块io来评价而不是使用行

oracle以块为单位进行io，故优化器
决定使用全表扫描还是索引扫描是由需要访问的块的比例，而不是行的比例。这个因子成为索引聚簇因子，是反应一个表在该索引上的分布情况，由相同索引值对应的数据行在表里的数据块分布情况决定。，如果该因子接近表的块数，则分布非常的有序，即相同的索引值对应的行集中在很少的数据块里，反之如果接近行数，则分布非常的无序，即相同索引值对应的行随机分散在表的数据块里，这时使用索引范围扫描获取数据行就需要付出更高的代价。

索引扫描方式

• 索引唯一扫描(index unique scans)

索引唯一扫描只返回一个rowid，当在语句中使用了unique索引或者主键约束的对应的列进行约束，确保只返回一行数据。这种扫描出现在语句中对unique索引或主键的所有列使用等号条件。

• 索引范围扫描(index range scans)

索引范围扫描是根据索引获取选定数据的一种常用方式。可以使用在unique索引或者非unique索引里。当order by或者group by里只有索引的列时，oracle会根据情况省略排序过程。

索引范围扫描出现在对索引里的列的范围查找情况。

• 索引范围降序扫描(index range scans descending)

和索引范围扫描相对应的方式，索引范围扫描是按升序进行扫描的，索引范围降序扫描是按降序。当语句里有对索引列的order by desc子句时。

• 索引跳跃扫描(index skip scans)

索引跳跃扫描出现在使用组合索引时，在索引的前序列没有指定的情况下，使用了后续索引列。
当索引的前序列只有很少的几个值时，将索引看成是若干个子索引。

• 索引全扫描(index full scans)

省略一次排序操作，oracle使用索引全扫描时是使用单块读取进行io的。

• 快速全索引扫描(fast full index scans)

查询包含的列全在索引列里，且至少一列有not null约束，快速全索引扫描只访问索引而不访问表，且oracle是以多块进行io的。该访问模式下不能省略order by子句

• 索引连接(index joins)

索引连接指的是若干个索引使用hash连接在一起，包含了查询所需的所有列，这样就不需要去访问表了，这种方式访问不能省略order by子句。

• 位图索引(bitmap indexes)

位图索引使用一个键值，然后使用映射函数将每一位转化为rowid。bitmap能够很方便的合并where里的多个条件。

聚簇访问(cluster access)

oracle使用聚簇扫描从索引聚簇表里获取具有相同聚簇键值的数据行。在索引聚簇表里oracle将相同的聚簇键值的行被放在相同的数据块里。聚簇访问首先扫描聚簇索引获取指定行的rowid，然后使用rowid定位到指定行。

哈希访问(hash Access)

oracle使用hash扫描在hash聚簇表里定位数据行。
在hash聚簇表里，具有相同hash值的行被存储在相同的数据库块里。oracle首先获取聚簇键值的hash值，然后扫描具有该hash值的数据库块，找到该行数据。

抽样表扫描(sample table scans)

抽样表扫描是从一个表或者select语句里获取抽样数据的访问方式。
抽样方式可以抽样行或者抽样数据库块，分别以sample或者sample block子句。
select * from emp sample block (1);抽样1%的数据块。

表连接

连接是语句从多个表里获取数据的一种方式。连接分为内连接和外连接两种。

对于一个有连接的语句，主要有三个因素影响执行计划的生成
- 每个连接表的访问路径
- 连接的方式，有nested loop，sort merge，Cartesian和hash joins
- 连接顺序

嵌套循环连接(nested loop joins)

优化器使用nested loop joins，当满足以下条件时:
- 当连接的数据行数较小
- 两个表有好的驱动条件。内部循环是对外部循环返回的每一行进行迭代，理想情况是使用索引扫描。如果内部表的访问路径是独立于外部表的，则外部循环的每次循环相同的行会被反复获取，导致性能急剧下降，这种情况下，hash joins是更好的选择。

nested loop joins包含以下几个步骤:
- 优化器决定驱动表，指定为外部表
- 另一个指定为内部表
- 对外部表的每一行，oracle访问内部表的所有行。

hash连接(hash joins)

oracle使用hash joins来连接大数据集合，优化器选择两者中较小的表，在内存上使用连接键构造hash表，然后扫描较大的表，探测hash表找到连接行。

这种连接当较小的表可以在内存里放下时有最好的效果，这样成本被控制在两个表读取一次。

当满足连接使用等号连接，且满足下面两个条件之一
- 大量的数据需要连接
- 小表的大部分数据需要连接

sort merge joins

sort merge joins连接两个独立的行源，一般来说hash joins性能更好，但是如果满足以下两个条件时sort merge joins有更好的性能:
- 行源已经排好序了
- 没有排序操作？？？

sort merge joins一般用于不等连接条件时，对于大的数据集来说，性能优于nested loop joins。

sort merge joins由两个步骤组成，
- 两个输入行源排序，如果输入行源已经排序好了，则不需要再次排序了。
- 将两个排序后的行源合并在一起

Cartesian joins

没有连接条件时执行Cartesian joins，形成两个表的笛卡尔积集，用得很少。

outer joins

外部连接扩展了简单连接的结果，包含满足连接条件的数据，同时包含部分或全部不满足条件的其中一个表的数据

nested loop outer joins

在这种外连接，连接条件决定连接顺序，外层表是需要返回所有行的表，内层表是另一个表。

当满足以下条件时，使用这种连接方式:
- 可以从外层表驱动内层表
- 数据要足够小

hash outer joins

当满足以下条件时，优化器使用该连接方式:
- 数据量比较大
- 不能从外部表驱动内部表
那个表作为驱动表是基于成本的。

sort merge outer joins

当nested loop outer joins和hash outer joins的成本都较高时，使用sort merge outer joins。

full outer joins

全外连接相当于左连接和右连接的合集，包括满足连接条件的行，也包括不满足条件的所有行。

阅读执行计划

执行计划里的步骤

• 识别和分割父子组，从缩进上很容易看出。

• 先从缩进最多的开始，同样缩进的，从上往下的顺序

执行计划里的每一行代表一个步骤，缩进越多的步骤越先执行，步骤id旁边有*的步骤在谓语信息段里有相应的谓语信息。 执行计划里的每一步返回一个行集，它的父亲步骤要么使用该行集要么是最后步骤，返回结果集。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!