oracle执行计划相关

最新推荐文章于 2024-09-18 09:30:26 发布

置顶 lldustc

最新推荐文章于 2024-09-18 09:30:26 发布

阅读量1.4k

点赞数

分类专栏： oracle 文章标签： oracle

本文链接：https://blog.csdn.net/lldustc_blog/article/details/52820045

版权

oracle 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

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
--然后执行脚本
@?\rdbms\admin\utlxplan

grant all on plan_table to public;

以sysdba登录sys用户
conn sys/sys_password as sysdba
--执行脚本
@?\sqlplus\admin\plustrce

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

跟踪文件的文件名和路径可以通过下面的sql获取

select t1.sid,t2.tracefile from v$session t1,v$process t2 where t1.paddr=t2.addr and t1.sid=(select sid from v$mystat where rownum=1)

或者查看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访问内部表的所有行。