Oracle 执行计划分析SQL性能
转载：https://www.cnblogs.com/aaron911/p/10699764.html
执行计划：一条查询语句在 Oracle 中的执行过程或访问路径的描述。即就是对一个查询任务，做出一份怎样去完成任务的详细方案。
如果要分析某条 SQL 的性能问题，通常我们要先看 SQL 的执行计划，看看 SQL 的每一步执行是否存在问题。 看懂执行计划也就成了 SQL 优化的先决条件。 通过执行计划定位性能问题，定位后就通过建立索引、修改 SQL 等解决问题。
用于查询的临时表
--创建 scott 数据库中的 dept 表
CREATE TABLE dept(
-- 部门编号
DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
-- 部门名称
DNAME VARCHAR2(14),
-- 部门所在位置
LOC VARCHAR2(13)
);
--创建 scott 数据库中的 emp 表
CREATE TABLE emp(
-- 雇员编号
EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
-- 雇员姓名
ENAME VARCHAR2(10),
-- 雇员职位
JOB VARCHAR2(9),
-- 雇员对应的领导的编号
MGR NUMBER(4),
-- 雇员的雇佣日期
HIREDATE DATE,
-- 雇员的基本工资
SAL NUMBER(7,2),
-- 奖金
COMM NUMBER(7,2),
-- 所在部门
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
);
--dept表中的数据
INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES (30,'SALES','CHICAGO');
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
--emp表中的数据
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('1980-12-17','yyyy-mm-dd'),800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('1981-2-20','yyyy-mm-dd'),1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('1981-2-22','yyyy-mm-dd'),1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('1981-4-2','yyyy-mm-dd'),2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('1981-9-28','yyyy-mm-dd'),1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1981-5-1','yyyy-mm-dd'),2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('1981-6-9','yyyy-mm-dd'),2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('87-7-13','yyyy-mm-dd'),3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('1981-11-17','yyyy-mm-dd'),5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('1981-9-8','yyyy-mm-dd'),1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('87-7-13','yyyy-mm-dd'),1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('1981-12-3','yyyy-mm-dd'),950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('1981-12-3','yyyy-mm-dd'),3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('1982-1-23','yyyy-mm-dd'),1300,NULL,10);
执行计划的查看
1. 设置autotrace
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 相似，但不显示语句的执行结果 |
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);
3 使用 PL/SQL Developer，Navicat, Toad 等客户端工具
如何读懂执行计划
1 执行顺序的原则
执行顺序的原则是：由上至下，从右向左
由上至下：在执行计划中一般含有多个节点，相同级别(或并列)的节点，靠上的优先执行，靠下的后执行
从右向左：在某个节点下还存在多个子节点，先从最靠右的子节点开始执行
一般按缩进长度来判断，缩进最大的最先执行，如果有2行缩进一样，那么就先执行上面的
以下面的 SQL 为例(数据库中的 emp,dept 连接查询)
select
emp.empno,emp.ename,dept.dname,dept.loc
from
emp
inner join dept on emp.deptno = dept.deptno;
2 执行计划中字段解释
ID: 一个序号，但不是执行的先后顺序。执行的先后根据缩进来判断。
Operation： 当前操作的内容。
Rows： 当前操作的 Cardinality，Oracle 估计当前操作的返回结果集。
Cost（CPU）： Oracle 计算出来的一个数值（代价），用于说明 SQL 执行的代价。
Time：Oracle 估计当前操作的时间。
在看执行计划的时候，除了看执行计划本身，还需要看谓词和统计信息，通过整体信息来判断 SQL 效率
3 谓词说明
Access
通过某种方式定位了需要的数据，然后读取出这些结果集，叫做Access。
表示这个谓词条件的值将会影响数据的访问路劲（表还是索引）。
Filter
把所有的数据都访问了，然后过滤掉不需要的数据，这种方式叫做filter 。
表示谓词条件的值不会影响数据的访问路劲，只起过滤的作用。
在谓词中主要注意access，要考虑谓词的条件，使用的访问路径是否正确
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 | 处理的数据的行数 |
解释
Recursive Calls：Number of recursive calls generated at both the user and system level.
Oracle Database maintains tables used for internal processing. When it needs to change these tables, Oracle Database generates an internal SQL statement, which in turn generates a recursive call. In short, recursive calls are basically SQL performed on behalf of your SQL. So, if you had to parse the query, for example, you might have had to run some other queries to get data dictionary information. These would be recursive calls. Space management, security checks, calling PL/SQL from SQL—all incur recursive SQL calls。
当执行一条SQL语句时，产生的对其他SQL语句的调用，这些额外的语句称之为’’recursive calls’’或’’recursive SQL statements’’. 我们做一条insert 时，没有足够的空间来保存row记录，Oracle 通过Recursive Call 来动态的分配空间。
DB Block Gets：Number of times a CURRENT block was requested.
Current mode blocks are retrieved as they exist right now, not in a consistent read fashion. Normally, blocks retrieved for a query are retrieved as they existed when the query began. Current mode blocks are retrieved as they exist right now, not from a previous point in time. During a SELECT, you might see current mode retrievals due to reading the data dictionary to find the extent information for a table to do a full scan (because you need the "right now" information, not the consistent read). During a modification, you will access the blocks in current mode in order to write to them.
DB Block Gets:请求的数据块在buffer能满足的个数
当前模式块意思就是在操作中正好提取的块数目，而不是在一致性读的情况下而产生的块数。正常的情况下，一个查询提取的块是在查询开始的那个时间点上存在的数据块，当前块是在这个时刻存在的数据块，而不是在这个时间点之前或者之后的数据块数目。
Consistent Gets： Number of times a consistent read was requested for a block.
This is how many blocks you processed in "consistent read" mode. This will include counts of blocks read from the rollback segment in order to roll back a block. This is the mode you read blocks in with a SELECT, for example. Also, when you do a searched UPDATE/DELETE, you read the blocks in consistent read mode and then get the block in current mode to actually do the modification.
(Consistent Gets: 数据请求总数在回滚段Buffer中的数据一致性读所需要的数据块)
这里的概念是在处理你这个操作的时候需要在一致性读状态上处理多少个块，这些块产生的主要原因是因为由于在你查询的过程中，由于其他会话对数据块进行操作，而对所要查询的块有了修改，但是由于我们的查询是在这些修改之前调用的，所以需要对回滚段中的数据块的前映像进行查询，以保证数据的一致性。这样就产 生了一致性读。
Physical Reads：
Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache.
(Physical Reads:实例启动后，从磁盘读到Buffer Cache数据块数量)
就是从磁盘上读取数据块的数量，其产生的主要原因是：
（1） 在数据库高速缓存中不存在这些块
（2） 全表扫描
（3） 磁盘排序
它们三者之间的关系大致可概括为：
逻辑读指的是 Oracle 从内存读到的数据块数量。一般来说是’consistent gets’ + ‘db block gets’。当在内存中找不到所需的数据块的话就需要从磁盘中获取，于是就产生了’physical reads’。
Physical Reads通常是我们最关心的，如果这个值很高，说明要从磁盘请求大量的数据到Buffer Cache里，通常意味着系统里存在大量全表扫描的SQL语句，这会影响到数据库的性能，因此尽量避免语句做全表扫描，对于全表扫描的SQL语句，建议增 加相关的索引，优化SQL语句来解决。
关于 physical reads ，db block gets 和consistent gets这三个参数之间有一个换算公式：
数据缓冲区的使用命中率 = 1 - ( physical reads / (db block gets + consistent gets) )
用以下语句可以查看数据缓冲区的命中率
SQL>SELECT name, value FROM v$sysstat WHERE name IN ('db block gets', 'consistent gets','physical reads');
查询出来的结果 Buffer Cache 的命中率应该在 90％ 以上，否则需要增加数据缓冲区的大小
清空Buffer Cache和数据字典缓存
//请勿随意在生产环境执行此语句
SQL> alter system flush shared_pool;
//请勿随意在生产环境执行此语句
SQL> alter system flush buffer_cache;
bytes sent via SQL * Net to client:
Total number of bytes sent to the client from the foreground processes.
bytes received via SQL * Net from client:
Total number of bytes received from the client over Oracle Net.
SQL Net roundtrips to/from client:*
Total number of Oracle Net messages sent to and received from the client.
Oracle Net是把Oracle网络粘合起来的粘合剂。它负责处理客户到服务器和服务器到客户通信，
sorts (memory): 在内存里排序
Number of sort operations that were performed completely in memory and did not require any disk writes
You cannot do much better than memory sorts, except maybe no sorts at all. Sorting is usually caused by selection criteria specifications within table join SQL operations.
Sorts(disk): 在磁盘上排序
Number of sort operations that required at least one disk write. Sorts that require I/O to disk are quite resource intensive. Try increasing the size of the initialization parameter SORT_AREA_SIZE.
所有的 sort 都是优先在 memory 中做的，当要排序的内容太多，在 sort area 中放不下的时候，会需要临时表空间，产生 sorts(disk)
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）
取决于表大小
取决于连接列是否有索引
取决于连接列是否排序
1 hash join
Hash join 散列连接是 CBO 做大数据集连接时的常用方式，优化器使用两个表中较小的表（通常是小一点的那个表或数据源）利用连接键（JOIN KEY）在内存中建立散列表，将列数据存储到 hash 列表中，然后扫描较大的表，同样对 JOIN KEY 进行 HASH 后探测散列表，找出与散列表匹配的行。需要注意的是：如果 HASH表太大，无法一次构造在内存中，则分成若干个 partition ，写入磁盘的 temporary segment，则会多一个写的代价，会降低效率。
这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成本之和。但是在表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要有较大的临时段从而尽量提高 I/O 的性能。
可以用 USE_HASH(table_name1 table_name2) 提示来强制使用散列连接。
使用情况： Hash join 在两个表的数据量差别很大的时候.
2 merge join
Merge Join 是先将关联表的关联列各自做排序，然后从各自的排序表中抽取数据，到另一个排序表中做匹配。
因为 merge join 需要做更多的排序，所以消耗的资源更多。 通常来讲，能够使用 merge join 的地方，hash join 都可以发挥更好的性能,即散列连接的效果都比排序合并连接要好。然而如果行源已经被排过序，在执行排序合并连接时不需要再排序了，这时排序合并连接的性能会优于散列连接。
可以使用USE_MERGE(table_name1 table_name2)来强制使用排序合并连接.
适用情况：
1.RBO模式
2.不等价关联(>,<,>=,<=,<>)
3.HASH_JOIN_ENABLED=false
4. 用在没有索引，并且数据已经排序的情况
3 nested loop
Nested loops 工作方式是循环从一张表中读取数据(驱动表outer table)，然后访问另一张表（被查找表 inner table,通常有索引）。驱动表中的每一行与inner表中的相应记录 JOIN。类似一个嵌套的循环。
对于被连接的数据子集较小的情况，嵌套循环连接是个较好的选择。在嵌套循环中，内表被外表驱动，外表返回的每一行都要在内表中检索找到与它匹配的行，因此整个查询返回的结果集不能太大（大于1 万不适合），要把返回子集较小表的作为外表（CBO 默认外表是驱动表），而且在内表的连接字段上一定要有索引。当然也可以用ORDERED 提示来改变CBO默认的驱动表。
使用 USE_NL(table_name1 table_name2) 可是强制 CBO 执行嵌套循环连接。
适用情况：
适用于驱动表的记录集比较小（<10000）而且inner表需要有有效的访问方法（Index），并且索引选择性较好的时候.
JOIN的顺序很重要，驱动表的记录集一定要小，返回结果集的响应时间是最快的。
表访问方式
1 表访问方式—->全表扫描（Full Table Scans）
全表扫描是指 Oracle 在访问目标表里的数据时，会从该表所占用的第一个区（EXTENT）的第一个块（BLOCK）开始扫描，一直扫描到该表的高水位线（HWM，High Water Mark），Oracle会对这期间读到的所有数据施加目标 SQL 的 where 条件中指定的过滤条件，最后只返回那些满足过滤条件的数据。
不是说全表扫描不好，事实上 Oracle 在做全表扫描操作时会使用多块读，Oracle 采用一次读入多个数据块 (database block)的方式优化全表扫描，而不是只读取一个数据块，这极大的减少了 I/O 总次数，提高了系统的吞吐量，所以利用多块读的方法可以十分高效地实现全表扫描。这在目标表的数据量不大时执行效率是非常高的，但全表扫描最大的问题就在于走全表扫描的目标 SQL 的执行时间会不稳定、不可控，这个执行时间一定会随着目标表数据量的递增而递增。因为随着目标表数据量的递增，它的高水位线会一直不断往上涨，所以全表扫描该表时所需要读取的数据块的数量也会不断增加，这意味着全表扫描该表时所需要耗费的 I/O 资源会随之不断增加，当然完成对该表的全表扫描操作所需要耗费的时间也会随之增加。
在 Oracle 中，如果对目标表不停地插入数据，当分配给该表的现有空间不足时高水位线就会向上移动，但如果你用 DELETE 语句从该表删除数据， 则高水位线并不会随之往下移动（这在某种程度上契合了”高水位线”的定义，就好比水库的水位，当水库涨水时，水位会往上移，当水库放水后，曾经的最高水位 的痕迹还是会清晰可见）。高水位线的这种特性所带来的副作用是，即使使用 DELETE 语句删光了目标表中的所有数据，高水位线还是会在原来的位置，这意味着全表扫描该表时 Oracle 还是需要扫描该表高水位线下的所有数据块，所以此时对该表的全表扫描操作所耗费的时间与之前相比并不会有明显的改观。
使用 FTS 的前提条件：在较大的表上不建议使用全表扫描，除非取出数据的比较多，超过总量的 5% – 10%，或你想使用并行查询功能时。
2 表访问方式—->通过ROWID访问表（table access by ROWID）
ROWID 是一个伪列，即是一个非用户定义的列，而又实际存储于数据库之中。每一个表都有一个ROWID列，一个ROWID值用于唯一确定数据库表中的的一条记录。因此通过 ROWID 方式来访问数据也是 Oracle 数据库访问数据的实现方式之一。一般情况下，ROWID方式的访问一定以索引访问或用户指定ROWID作为先决条件，因为所有的索引访问方式最终都会转换为通过ROWID来访问数据记录。(注：index full scan 与 index fast full scan 除外)由于 Oracle ROWID 能够直接定位一条记录，因此使用 ROWID 方式来访问数据，极大提高数据的访问效率
ROWID 扫描是指 Oracle 在访问目标表里的数据时，直接通过数据所在的 ROWID 去定位并访问这些数据。
从严格意义上来说，Oracle 中的 ROWID 扫描有两层含义：一种是根据用户在 SQL 语句中输入的ROWID的值直接去访问对应的数据行记录；另外一种是先去访问相关的索引，然后根据访问索引后得到的 ROWID 再回表去访问对应的数据行记录。
对 Oracle 中的堆表而言，我们可以通过 Oracle 内置的 ROWID 伪列得到对应行记录所在的 ROWID 的值（注意，这个 ROWID 只是一个伪 列，在实际的表块中并不存在该列），然后我们还可以通过 DBMS_ROWID 包中的相关方法（dbms_rowid.rowid_object,dbms_rowid.rowid_relative_fno、dbms_rowid.rowid_block_number和 dbms_rowid.rowid_row_number）将上述 ROWID 伪列的值翻译成对应数据行的实际物理存储地址。
3 索引扫描
索引范围扫描(INDEX RANGE SCAN)
索引范围扫描（INDEX RANGE SCAN）适用于所有类型的 B 树索引，当扫描的对象是唯一性索引时，此时目标SQL的where条件一定是范围查询（谓词条件为 BETWEEN、<、>等）；当扫描的对象是非唯一性索引时，对目标 SQL 的 where 条件没有限制（可以是等值查询，也可以是范围查询）。 索引范围扫描的结果可能会返回多条记录，其实这就是索引范围扫描中”范围”二字的本质含义。
一:唯一索引的范围查询
二:非唯一索引的等值查询
三:非唯一索引的范围查询
引唯一性扫描(INDEX UNIQUE SCAN)
索引唯一性扫描（INDEX UNIQUE SCAN）是针对唯一性索引（UNIQUE INDEX）的扫描，它仅仅适用于 where 条件里是等值查询的目标 SQL 。因为扫描的对象是唯一性索引，所以索引唯一性扫描的结果至多只会返回一条记录。
索引全扫描(INDEX FULL SCAN)
所谓的索引全扫描（INDEX FULL SCAN）就是指要扫描目标索引所有叶子块的所有索引行。这里需要注意的是，索引全扫描需要扫描目标索引的所有叶子块，但这并不意味着需要扫描该索引的所有分支块。在默认情况下，Oracle 在做索引全扫描时只需要通过访问必要的分支块定位到位于该索引最左边的叶子块的第一行索引行，就可以利用该索引叶子块之间的双向指针链表，从左至右依次顺序扫描该索引所有叶子块的所有索引行了。
索引快速扫描(index fast full scan)
索引快速全扫描（INDEX FAST FULL SCAN）和索引全扫描（INDEX FULL SCAN）极为类似，它也适用于所有类型的B树索引（包括唯一性索引和非唯一性索引）。和索引全扫描一样，索引快速全扫描也需要扫描目标索引所有叶子块的所有索引行。
索引快速全扫描与索引全扫描相比有如下三点区别
（1）索引快速全扫描只适用于CBO。
（2）索引快速全扫描可以使用多块读，也可以并行执行。
（3）索引快速全扫描的执行结果不一定是有序的。这是因为索引快速全扫描时Oracle是根据索引行在磁盘上的物理存储顺序来扫描，而不是根据索引行的逻辑顺序来扫描的，所以扫描结果才不一定有序（对于单个索引叶子块中的索引行而言，其物理存储顺序和逻辑存储顺序一致；但对于物理存储位置相邻的索引叶子块而言，块与块之间索引行的物理存储顺序则不一定在逻辑上有序）。
索引跳跃式扫描(INDEX SKIP SCAN)
索引跳跃式扫描（INDEX SKIP SCAN）适用于所有类型的复合B树索引（包括唯一性索引和非唯一性索引），它使那些在 where 条件中没有对目标索引的前导列指定查询条件但同时又对该 索引的非前导列指定了查询条件的目标SQL依然可以用上该索引，这就像是在扫描该索引时跳过了它的前导列，直接从该索引的非前导列开始扫描一样（实际的执行过程并非如此），这也是索引跳跃式扫描中”跳跃”（SKIP）一词的含义。
为什么在 where 条件中没有对目标索引的前导列指定查询条件但 Oracle 依然可以用上该索引呢？这是因为 Oracle 帮你对该索引的前导列的所有 distinct 值做了遍历。