3.执行计划
3.1 获取执行计划常用方法
看执行计划，就用sqlplus，客户端的版本最好大于等于db的版本。
3.1.1 使用AUTOTRACE查看执行计划
我们利用SQLPLUS中自带的AUTOTRACE工具查看执行计划。AUTOTRACE用法如下。
SQL> set autot
Usage: SET AUTOT[ RACE] {OFF | ON | TRACE[ ONLY]} [EXP[ LAIN]] [STAT[ ISTICS]]方括号内的字符可以省略。
set autot on ：该命令会运行SQL并且显示运行结果，执行计划和统计信息。
set autot trace ：该命令会运行SQL，但不显示运行结果，会显示执行计划和统计信息。
set autot traceexp ：运行该命令查询语句不执行，DML语句会执行，只显示执行计划。
set autot tracestat ：该命令会运行SQL，只显示统计信息。
set autot off ：关闭AUTOTRACE。
利用AUTOTRACE查看执行计划会带来一个额外的好处，当SQL执行完毕之后，会在执行计划的末尾显示SQL在运行过程中耗费的一些统计信息。
37 recursive calls
309 db block gets
40 consistent gets
1 physical reads
0 redo size
526 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
recursive calls 表示递归调用的次数。一个SQL第一次执行就会发生硬解析，在硬解析的时候，优化器会隐含地调用一些内部SQL，
因此当一个SQL第一次执行，recursivecalls会大于0；第二次执行的时候不需要递归调用，recursivecalls会等于0。
如果SQL语句中有自定义函数，recursivecalls永远不会等于0，自定义函数被调用了多少次，recursivecalls就会显示为多少次。
db block gets 表示有多少个块发生变化，一般情况下，只有DML语句才会导致块发生变化，所以查询语句中db block gets一般为0。如果有延迟块清除，或者SQL语句中调用了返回CLOB的函数，dbblockgets也有可能会大于0，不要觉得奇怪。可以根据 309，算出大概读了多少数据：309*8k/1024=2.4 MB
consistent gets 表示逻辑读，单位是块。在进行SQL优化的时候，我们应该想方设法减少逻辑读个数。通常情况下逻辑读越小，性能也就越好。需要注意的是，逻辑读并不是衡量SQL执行快慢的唯一标准，需要结合I/O等其他综合因素共同判断。降低了物理读也就降低了逻辑读；降低了逻辑读也就降低了物理读。SQL优化不用看逻辑读，逻辑读不能说明什么问题。SQL跑得快，逻辑读就少，SQL跑得慢，逻辑读就多。
怎么通过逻辑读判断一个SQL还存在较大优化空间呢？如果SQL的逻辑读远远大于SQL语句中所有表的段大小之和（假设所有表都走全表扫描，表关联方式为HASH JOIN），那么该SQL就存在较大优化空间。动手能力强的读者可以据此编写一个SQL，抓出SQL逻辑读远远大于语句中所有表段大小之和的SQL语句。
physical reads 表示从磁盘读取了多少个数据块，如果表已经被缓存在buffer cache中，没有物理读，physical reads等于0。
redo size 表示跑这个SQL产生了多少字节的重做日志，一般情况下只有DML语句才会产生redo，查询语句一般情况下不会产生redo，所以这里redosize为0。如果有延迟块清除，查询语句也会产生redo。
bytes sent via SQL*Net to client 表示从数据库服务器发送了多少字节到客户端。
bytes received via SQL*Net from client 表示从客户端发送了多少字节到服务端。
SQL*Net roundtrips to/from client 表示客户端与数据库服务端交互次数，我们可以通过设置arraysize减少交互次数。
sorts(memory) 和 sorts(disk) 分别表示内存排序和磁盘排序的次数。sorts这个不用看，没啥用。
rows processed 表示SQL一共返回多少行数据。我们在做SQL优化的时候最关心这部分数据，因为可以根据SQL返回的行数判断整个SQL应该是走HASH连接还是走嵌套循环。如果rows processed很大，一般走HASH连接；如果rows processed很小，一般走嵌套循环。
小结：使用autotrace看执行计划最重要的部分：看rows processed 、看逻辑读。
3.1.2 使用 EXPLAIN PLAN FOR 查看执行计划
使用 explain plan for 查看执行计划，用法如下。
explain plan for SQL语句;
select * from table(dbms_xplan.display);查看高级（ADVANCED）执行计划，用法如下。
explain plan for SQL语句;
select * from table(dbms_xplan.display(NULL, NULL, 'advanced-projection'));高级执行计划比普通执行计划多了Query Block Name/Object Alias和 Outline Data。
当需要控制半连接/反连接执行计划的时候，我们就可能需要查看高级执行计划。
有时候我们需要使用SQL PROFILE固定执行计划，也可能需要查看高级执行计划。
Query Block Name表示查询块名称，Object Alias表示对象别名。Outline Data表示SQL内部的HINT。
一条SQL语句可能会包含多个子查询，每个子查询在执行计划内部就是一个Query Block。
为什么会有Query Block呢？比如一个SQL语句包含有多个子查询，假如每个子查询都要访问同一个表，不给表取别名，这个时候我们怎么区分表属于哪个子查询呢？
所以Oracle会给同一个SQL语句中的子查询取别名，这个名字就是QueryBlockName，以此
3.1.3 查看带有 A-TIME 的执行计划
查看带有 A-TIME 的执行计划的用法如下。
alter session set statistics_level= all; 或者在 SQL 语句中添加 hint:
/*+ gather_plan_statistics */Starts表示这个操作执行的次数。
E-Rows表示优化器估算的行数，就是普通执行计划中的Rows。
A-Rows表示真实的行数。
A-Time表示累加的总时间。与普通执行计划不同的是，普通执行计划中的Time是假的，而A-Time是真实的。
Buffers表示累加的逻辑读。
Reads表示累加的物理读。
使用AUTOTRACE或者EXPLAINPLANFOR获取的执行计划来自于PLAN_TABLE。PLAN_TABLE是一个会话级的临时表，里面的执行计划并不是SQL真实的执行计划，它只是优化器估算出来的。
真实的执行计划不应该是估算的，应该是真正执行过的。SQL执行过的执行计划存在于共享池中，具体存在于数据字典V$SQL_PLAN中，带有A-Time的执行计划来自于V$SQL_PLAN，是真实的执行计划，而通过AUTOTRACE、通过EXPLAIN PLAN FOR获取的执行计划只是优化器估算获得的执行计划。
使用AUTOTRACE查看执行计划，SQL是真正运行过的，怎么得到的执行计划不是真实的呢？原因在于AUTOTRACE获取的执行计划来自于PLAN_TABLE，而非来自于共享池中的V$SQL_PLAN。
3.1.4 查看正在执行的SQL的执行计划
有时需要抓取正在运行的SQL的执行计划，这时我们需要获取SQL的SQL_ID以及SQL的CHILD_NUMEBR，然后将其代入下面SQL，就能获取正在运行的SQL的执行计划。
select * from table(dbms_xplan.display_cursor('sql_id', child_number));3.2 定制执行计划
...
3.3 怎么通过查看执行计划建立索引
执行计划分为两部分，Plan hash value 和Predicate Information之间这部分主要是表的访问路径以及表的连接方式。关于访问路径以及表连接方式会在之后章节详细解释。另外一部分是谓词过滤信息，这部分信息位于Predicate Information下面，谓词过滤信息非常重要。
有些人做SQL优化喜欢用10046 trace或者10053 trace，如果仅仅是优化一个SQL，根本就不需要使用这两个工具，直接分析SQL语句以及执行计划即可。
当然，如果是为了深入研究为什么不走索引，为什么走了嵌套循环而没走HASH连接等，这个时候我们可以用10053 trace；如果想研究访问路径是单块读或者是多块读，可以使用10046 trace。
SQL> explain plan for select e.ename,e.job,d.dname from emp e,dept d where e.deptno = d.deptno and e.sal<2000;SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 136 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 4 | 136 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 4 | 84 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMP | 4 | 84 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")
5 - filter("E"."SAL"<2000)
19 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2137789089
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | 8168 | 16336 | 29 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
41 recursive calls
12 db block gets
197 consistent gets
0 physical reads
0 redo size
1981 bytes sent via SQL*Net to client
535 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
19 rows processed注意观察Id这列，有些Id前面有"*"号，这表示发生了谓词过滤，或者发生了HASH连接，或者是走了索引。
Id=1前面有"*"号，它是HASH连接的"*"号，我们观察对应的谓词过滤信息就能知道是哪两个表进行的HASH连接，
而且能知道是对哪些列进行的HASH连接，这里是e表（emp表的别名）的deptno列与d表（dept的别名）deptno列进行HASH连接的。Id=3前面有"*"号，
这里表示表emp有谓词过滤，它的过滤条件就是Id=3对应的谓词过滤信息，也就是e.sal<2000。Id=2前面没有"*"号，那么说明dept表没有谓词过滤条件。
提问：TABLE ACCESS FULL前面没有"*"号怎么办？
回答：如果表很小，那么不需理会，小表不会产生性能问题。如果表很大，那么我们要询问开发人员是不是忘了写过滤条件。
当然了一般也不会遇到这种情况，如果真的是没过滤条件呢？比如一个表有10GB，但是没有过滤条件，那么它就会成为整个SQL的性能瓶颈。
这个时候我们需要查看SQL语句中该表访问了多少列，如果访问的列不多，就可以把这些列组合起来，建立一个组合索引，索引的大小可能就只有1GB左右。我们利用INDEX FAST FULL SCAN代替TABLE ACCESS FULL。
在访问列不多的情况，索引的大小（Segment Size）肯定比表的大小（Segment Size）小，那么就不需要扫描10GB了，只需要扫描1GB，从而达到优化目的。
如果SQL语句里面要访问表中大部分列，这时就不应该建立组合索引了，因为此时索引大小比表更大，可以通过其他方法优化，
比如开启并行查询，或者更改表连接方式，让大表作为嵌套循环的被驱动表，同时在大表的连接列上建立索引。关于表连接方式，我们会在后面章节详细介绍。
提问：TABLE ACCESS FULL前面有"*"号怎么办？
回答：如果表很小，那么我们不需理会；
如果表很大，可以使用"select count(*) from 表"，查看有多少行数据，然后通过"select count(*) from 表 where*"对应的谓词过滤条件，查看返回多少行数据。
如果返回的行数在表总行数的5%以内，我们可以在过滤列上建立索引。
如果已经存在索引，但是没走索引，这时我们要检查统计信息，特别是直方图信息。
如果统计信息已经收集过了，我们可以用HINT强制走索引。
如果有多个谓词过滤条件，我们需要建立组合索引并且要将选择性高的列放在前面，选择性低的列在后面。
如果返回的行数超过表总行数的5%，这个时候我们要查看SQL语句中该表访问了多少列，
如果访问的列少，同样可以把这些列组合起来，建立组合索引，建立组合索引的时候，谓词过滤列在前面，连接列在中间，select部分的列在最后。
如果访问的列多，这个时候就只能走全表扫描了。
提问：TABLE ACCESS BY INDEX ROWID前面有"*"号怎么办？
TABLE ACCESS BY INDEX ROWID前面有"*"号，表示回表再过滤。回表再过滤说明数据没有在索引中过滤干净。
当TABLE ACCESS BY INDEX ROWID前面有"*"号时，可以将"*"号下面的过滤条件包含在索引中，这样可以减少回表次数，提升查询性能。
如果索引返回的数据本身很少，即使TABLE ACCESS BY INDEX ROWID前面有"*"号，也可以不用理会，因为索引本身返回的数据少，回表也没有多少次，因此可以不用再创建组合索引。
浙公网安备 33010602011771号