使用Oracle执行计划分析SQL性能

原文

个人学习记录，不喜勿喷。

使用Oracle执行计划分析SQL性能

转载：https://www.cnblogs.com/aaron911/p/10699764.html

相关好的文章

Dbeaver如何看Oralce执行计划？解决: explain plan FOR 无效? 执行计划的顺序怎么查看?

https://blog.csdn.net/q258523454/article/details/112252826#commentBox

一 执行计划的查看

1.1设置autotrace

相关命令

1.SET AUTOTRACE OFF						此为默认值，即关闭Autotrace
2.SET AUTOTRACE ON EXPLAIN		只显示执行计划
3.SET AUTOTRACE ON STATISTICS 	只显示执行的统计信息
4.SET AUTOTRACE ON		包含2,3两项内容
5.SET AUTOTRACE TRACEONLY	与ON相似，但不显示语句的执行结果

1.2 使用SQL

在执行的sql前面加上EXPLAIN PLAN FOR

SQL> EXPLAIN PLAN FOR SELECT * FROM EMP;

已解释。
SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));

或者：
SQL> select * from table(dbms_xplan.display);

1.3 使用PL/SQL Developer，Navicat, Toad等客户端工具

二、如何读懂执行计划

2.1执行顺序的原则

执行顺序的原则是：由上至下，从右向左
由上至下：在执行计划中一般含有多个节点，相同级别(或并列)的节点，靠上的优先执行，靠下的后执行
从右向左：在某个节点下还存在多个子节点，先从最靠右的子节点开始执行。

一般按缩进长度来判断，缩进最大的最先执行，如果有2行缩进一样，那么就先执行上面的。

执行计划中字段解释

ID: 一个序号，但不是执行的先后顺序。执行的先后根据缩进来判断。

Operation： 当前操作的内容。

Rows： 当前操作的Cardinality，Oracle估计当前操作的返回结果集。

Cost（CPU）：Oracle 计算出来的一个数值（代价），用于说明SQL执行的代价。

Time：Oracle 估计当前操作的时间。

在看执行计划的时候，除了看执行计划本身，还需要看谓词和统计信息。 通过整体信息来判断SQL效率。

2.3 谓词说明

Access :

• 通过某种方式定位了需要的数据，然后读取出这些结果集，叫做Access。
• 表示这个谓词条件的值将会影响数据的访问路劲（表还是索引）。

Filter：

• 把所有的数据都访问了，然后过滤掉不需要的数据，这种方式叫做filter 。
• 表示谓词条件的值不会影响数据的访问路劲，只起过滤的作用。

在谓词中主要注意access，要考虑谓词的条件，使用的访问路径是否正确。

2.4 Statistics(统计信息)说明

recursive calls	产生的递归sql调用的条数。

 Db block gets:	从buffer cache中读取的block的数量

consistent gets	从buffer cache中读取的undo数据的block的数量   

physical reads	从磁盘读取的block的数量  

redo size	DML生成的redo的大小   

bytes sent via SQL*Net to client		数据库服务器通过SQL*Net向查询客户端发送的查询结果字节数

bytes received via SQL*Net from client	通过SQL*Net接受的来自客户端的数据字节数

SQL*Net roundtrips to/from client	服务器和客户端来回往返通信的Oracle Net messages条数

sorts (memory)	在内存执行的排序量 

sorts (disk)	在磁盘上执行的排序量

rows processed	处理的数据的行数

2.5 动态分析

动态统计量收集是Oracle CBO优化器的一种特性。优化器生成执行计划是依据成本cost公式计算出的，如果相关数据表没有收集过统计量，又要使用CBO的机制，就会引起动态采样。

动态采样（dynamic sampling）就是在生成执行计划是，以一个很小的采用率现进行统计量收集。由于采样率低，采样过程快但是不精确，而且采样结果不会进入到数据字典中。

如果在执行计划中有如下提示：

Note
-------------dynamic sampling used for the statement
这提示用户CBO当前使用的技术，需要用户在分析计划时考虑到这些因素。 当出现这个提示，说明当前表使用了动态采样。 我们从而推断这个表可没有做过分析。

这里会出现两种情况：

（1） 如果表没有做过分析，那么CBO可以通过动态采样的方式来获取分析数据，也可以或者正确的执行计划。

（2） 如果表分析过，但是分析信息过旧，这时CBO就不会在使用动态采样，而是使用这些旧的分析数据，从而可能导致错误的执行计划。

三 JOIN方式

在多表联合查询的时候，如果我们查看它的执行计划，就会发现里面有多表之间的连接方式。多表之间的连接有三种方式：Nested Loops，Hash Join 和 Sort Merge Join.具体适用哪种类型的连接取决于

• 当前的优化器模式 （ALL_ROWS 和 RULE）
• 取决于表大小
• 取决于连接列是否有索引
• 取决于连接列是否排序

3.1 hash join

适用情况：Hash join在两个表的数据量差别很大的时候.

3.2 merge join

适用情况：

1.RBO模式

2.不等价关联(>,<,>=,<=,<>)

3.HASH_JOIN_ENABLED=false

4. 用在没有索引，并且数据已经排序的情况.

3.3 nested loop

适用情况：

适用于驱动表的记录集比较小（<10000）而且inner表需要有有效的访问方法（Index），并且索引选择性较好的时候.

JOIN的顺序很重要，驱动表的记录集一定要小，返回结果集的响应时间是最快的。

四、表访问方式

4.1表访问方式---->全表扫描（Full Table Scans）

4.2表访问方式---->通过ROWID访问表（table access by ROWID）

4.3索引扫描
索引范围扫描(INDEX RANGE SCAN)
引唯一性扫描(INDEX UNIQUE SCAN)
索引全扫描（INDEX FULL SCAN）

索引快速扫描(index fast full scan)

索引跳跃式扫描（INDEX SKIP SCAN）

相关问题

注意上面可能因为版本问题出现的坑: 如果执行计划没有任何反应,先删除“；”分号，重新加上然后再执行。

为什么用下面的方法查看不到执行计划呢？

explain plan FOR SELECT * from student;

原因是我们还少了一条查询SQL，上面只是生成执行计划，但是不会主动显示.

explain plan FOR SELECT * from student;
SELECT * FROM TABLE(dbms_xplan.display);

执行计划如何看呢? 执行计划的执行顺序如何判断？

答：树的后续遍历

--------------------------------------------------
| Id  | Operation                     | Name     |
--------------------------------------------------
|   0 | SELECT STATEMENT              |          |
|*  1 |  FILTER                       |          |
|   2 |   NESTED LOOPS                |          |
|   3 |    TABLE ACCESS FULL          | EMP      |
|   4 |    TABLE ACCESS BY INDEX ROWID| DEPT     |
|*  5 |     INDEX UNIQUE SCAN         | PK_DEPT  |
|*  6 |   TABLE ACCESS FULL           | SALGRADE |
--------------------------------------------------

画图:

同理，下面的执行顺序为：