ORACLE执行计划的介绍

2021-11-03 23:50:55

1. 执行计划的介绍

执行计划描述了SQL引擎为执行SQL语句进行的操作；分析SQL语句相关的性能问题或仅仅质疑查询优化器的决定时，必须知道执行计划；

2. 执行计划的获取方法

ORACLE提供了四种方法来获取执行计划。

? 执行SQL语句EXPLAIN PLAN，然后查询结果输出表。

? 查询一张动态性能视图，它显示缓存中库缓存中的执行计划。

? 查询自动工作量资料库或查询Statspack表，它显示存储在资料库中的执行计划。

? 启动提供执行计划的跟踪功能。

1) SQL语句EXPLAIN PLAN

SQL语句EXPLAIN PLAN的目的是以一条SQL语句作为输入，得到这条语句的执行计划和相关信息，并将它们作为输出存储在计划表中，原理是通过它可以询问查询优化器，对给定的是SQL语句会采用怎样的执行计划；

? 支持的SQL语句：SELECT 、INSERT、UPDATA、MERGE、DELETE、CREATE TABLE、CREATE INDEX和ALTER INDEX;

? 语句的语法：

1、输入语句：EXPLAIN PLAN FOR SELECT * FROM TABLE;

2、查看查询优化器的执行计划：SELECT * FROM TABLE(dbms_xplan.display);

? 使用缺陷：绑定变量的陷阱；

? 避免办法：

A）在解析执行计划的时候也用绑定变量输入，但是绑定变量默认的是用VARCHAR2类型的，因此，为了避免饮食转换，数据库引擎自动添加一个显示转换。

B）对所有不是VARCHAR2类型的绑定变量使用显示转换。

? 该方法没有真正执行相应的SQL语句。

2) 动态性能视图

有四个动态性能视图展示库缓存中当前的游标信息；

? V$sql_plan视图提供最基础的信息和计划表相同，主要包括执行计划和查询优化器提供的一些相关信息。此视图和计划表唯一显著的不同点在于，它有一些字段可用来标识与库缓存中的执行计划和相关游标；

? V$sql_plan_statistics。视图为v$sql_plan视图中的每一个操作，诸如用掉的时间和产生的记录数，它提供了执行计划的运行时行为。

? 查看动态性能视图：

? 根据sql语句查找相应的sql_id:

SQL > select * from v$sql where sql_text like 'select * from ';

? 查看相应的执行计划：

SQL > SELECT * FROM TABLE(dbms_xplan.display_cursor(‘sql_id’,child number)

3) AWR和Statspack

当采集快照（snapshot）的时候，awr和statspack就能够收集执行计划。为了得到执行计划，需要查询前面一节提到的动态性能视图。一旦完成，执行计划通过企业管理器用报告或awr展示出来；

存放在awr报告中的执行计划，都是一段时间内比较消耗资源的sql语句；

? Awr报告可以在视图dba_hist_sql_plan中查到；select * from dba_hist_sql_plan;

? 执行计划可以通过display_awr进行查看；select * from table(dbms_xplan.display_awr('sql_id'));

4) 跟踪trace功能

? 10053事件

10053可以产生跟踪文件，详细记录语句的产生内容；

Alter session set events ‘10053 trace name context forever’; 打开跟踪；

Alter session set events ‘10053 trace name context off’;关闭跟踪；

? 10132事件

通过10132事件也可以产生一个跟踪文件，文件将包含每一个硬解析的执行计划。

Alter session set events ‘10132 trace name context forever’; 打开跟踪；

Alter session set events ‘10132 trace name context off’;关闭跟踪；

2. 解读执行计划

执行计划大家都知道这个东西，关于执行计划的获取我们也知道了主要的几种方法，现在和大家学习执行计划解读；

1) 关键术语：父子关系

执行计划是一个树形结构，不仅阐述了SQL引擎执行操作的顺序，也阐明了他们之间的关系。树的每一个节点都代表一个操作，比如，表访问、连接或排序。

在各操作节点之间，存在父子关系，控制父子关系的规则如下：

? 父有一个或者多个子。

? 子只有一个父

? 唯一没有父的操作是根；

? 子节点都会在父节点缩进的右侧，所有的子节点的缩进都是一致的；

? 父节点的id小于子节点，最靠近子节点是他的父几点；

2) 执行计划的查看

--------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 5 | 13902 (2) | 00:02:47 |

| 1 | SORT AGGREGATE | | 1 | 5 | | |

|* 2 | INDEX FAST FULL SCAN | MSEG~WZQ | 5300K| 25M | 13901 (2)| 00:02:47 |

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("WERKS"='1100')

? 执行顺序：从子节点到父节点；子节点之间从上往下执行；

? *星号代表该执行计划有过滤条件；

? Filter代表把结果查找出来后再进行过滤；

? Access代表对数据查询的时候就进行过滤；（Access的效率会比Filter高）

1) SQL语句EXPLAIN PLAN

相关文章