一、执行计划的打开方式
1.SQLPlus的autotrace开启
SET AUTOTRACE OFF #此为默认值，即关闭Autotrace
SET AUTOTRACE ON EXPLAIN #只显示执行计划
SET AUTOTRACE ON STATISTICS #只显示执行的统计信息
SET AUTOTRACE ON #包含2,3两项内容
SET AUTOTRACE TRACEONLY #与ON相似，但不显示语句的执行结果
2.explain plan for select statements;
select * from table(dbms_xplain.display);
3.PL/SQL Developer F5，手动执行，语句写入explain plan window中
4.通过V$sql_plan视图格式化查询，切实执行计划，autotrace是估算可能的执行计划
select t.HASH_VALUE,t.ADDRESS,t.SQL_TEXT from v$sql t where t.sql_text like '%from t1%'
select '| Operation |Object Name | Rows | Bytes| Cost |'
as "Explain Plan in library cache:" from dual
union all
select rpad('| '||substr(lpad(' ',1*(depth-1))||operation||
decode(options, null,'',' '||options), 1, 35), 36, ' ')||'|'||
rpad(decode(id, 0, '----------------------------',
substr(decode(substr(object_name, 1, 7), 'SYS_LE_', null, object_name)
||' ',1, 30)), 31, ' ')||'|'|| lpad(decode(cardinality,null,' ',
decode(sign(cardinality-1000), -1, cardinality||' ',
decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K',
decode(sign(cardinality-1000000000), -1, trunc(cardinality/1000000)||'M',
trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' ||
lpad(decode(bytes,null,' ',
decode(sign(bytes-1024), -1, bytes||' ',
decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K',
decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M',
trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' ||
lpad(decode(cost,null,' ', decode(sign(cost-10000000), -1, cost||' ',
decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M',
trunc(cost/1000000000)||'G'))), 8, ' ') || '|' as "Explain plan"
from v$sql_plan sp
where sp.hash_value=&hash_value;
SELECT id
, lpad (' ', depth) || operation operation
, options , object_name , optimizer , cost
FROM V$SQL_PLAN
WHERE hash_value = 2245880055
AND address = '000000006946B5D8'
START WITH id = 0
CONNECT BY
( prior id = parent_id
AND prior hash_value = hash_value
AND prior child_number = child_number
)
ORDER SIBLINGS BY id, position;
5.SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('27uhu2q2xuu7r', NULL, 'ADVANCED')); #27uhu2q2xuu7r sql_ID
二、相关名词解释
ROWID：系统自动给所有数据行增加的一个伪列。可以像其它列那样使用，但是不能对该列增删改。一旦一
行数据插入数据库，rowid在该行的生命周期内是唯一的，即使产生行迁移，行的rowid也不会改变。
Recursive SQL：为执行一个sql语句，Oracle必须执行一些额外的语句，称为"recursive calls"或
"recursive SQL statements".如DDL语句发出后，ORACLE总是隐含的发出一些recursive
SQL语句，来修改数据字典信息。用户不比关心这些recursive SQL语句DML语句与SELECT
都可能引起recursive SQL，可以将触发器视为recursive SQL。
recursive calls发生场景：
insert 时，没有足够的空间来保存row记录，Oracle 通过Recursive Call 来动态的分配空间。
DDL语句时，ORACLE总是隐含的发出一些recursive SQL语句，修改数据字典信息。
没有足够的空间存储ORACLE的系统数据字典信息时，会发生Recursive calls，这些Recursive calls会将数据字典信息从硬盘读入内存中。
存储过程、触发器内如果的SQL调用，也会产生recursive SQL。
Row Source：（行源）在查询中，由上一操作返回的符合条件的行的集合。
Predicate：（谓词）一个查询中的WHERE限制条件。
Driving Table：（驱动表）又称为外层表（OUTER TABLE），用于嵌套与HASH连接中。应用查询的限制条件后，
返回较少行源的表作为驱动表。一般将该行源称为连接操作的row source 1。
Probed Table：（被探查表）又称为内层表（INNER TABLE）。从驱动表中得到具体一行的数据后，在该表
中寻找符合连接条件的行。表中的数据一般较大，且相应的列上应该有索 引。一般将该表称
为连接操作的row source 2。
concatenated index：组合索引，由多个列构成的索引。引导列（leading column），限制条件以引导列开始
才会使用索引。10g以后已不再如此限制
selectivity：可选择性，列中唯一键的数量和列的行数比。比值越接近1，则该列的可选择性越高，该列就越
适合创建索引，在可选择性高的列上进行查询时，返回的数据就较少，比较适合使用索引查询。
三、oracle访问数据的存取方法
1、全表扫描（Full Table Scans， FTS）
全表扫描，Oracle需要读取表中所有的行，检查每一行是否满足语句的WHERE限制条件，oracle的多块读机制
通过 db_block_multiblock_read_count 参数设定，可以一次I/O读取多个数据块，减少I/O次数提高系统吞吐量，
只有在FTS下才会多块读操作。FTS会使表使用到 HWM 高水位线，标识表最后写入的数据块。一般尽量避免使用FTS
除非要取数据超过总量的5%-10%。
eg:select * from dual; #不带条件的查询非索引列
2、通过表的ROWID存取（Table Access by ROWID或rowid lookup）
行的ROWID指出了该行所在的数据文件、数据块以及行在该块中的位置，通过ROWID来存取数据可以快速定位
到目标数据，是Oracle存取单行数据的最快方法。不会用到多块读操作，一次I/O只能读取一个数据块。如通过索
引查询数据。
eg:select * from dept where rowid = 'AAAAyGAADAAAAATAAF';
3、索引扫描（Index Scan或index lookup）
索引扫描过程，先通过index查找到数据对应的rowid值（对于非唯一索引可能返回多个rowid值），然后根据
rowid直接从表中得到具体的数据。在索引中，储每个索引的值和此值行对应的ROWID值。索引已经排序。
根据索引的类型与where限制条件的不同，有4种类型的索引扫描：
索引唯一扫描（index unique scan）主键，唯一性索引
索引范围扫描（index range scan）
索引全扫描（index full scan）
索引快速扫描（index fast full scan）
3.1、索引唯一扫描（index unique scan）
通过唯一索引查找一个数值返回单个ROWID.如果存在UNIQUE 或PRIMARY KEY约束（它保证了语句只存取单行）
Oracle经常实现唯一性扫描。
TABLE ACCESS BY ROWID EMP [ANALYZED]
INDEX UNIQUE SCAN EMP_I1
3.2、索引范围扫描（index range scan）
通过索引查找多个索引对应的值，在唯一索引上使用索引范围扫描的典型情况下是在谓词（where限制条件）
中使用了范围操作符（如>、<、<>、>=、<=、between）
SELECT STATEMENT[CHOOSE] Cost=1
TABLE ACCESS BY ROWID EMP [ANALYZED]
INDEX RANGE SCAN EMP_I1 [ANALYZED]
在非唯一索引上，谓词col = 5可能返回多行数据，所以在非唯一索引上都使用索引范围扫描。使用
index rang scan的3种情况：
在唯一索引列上使用了range操作符（> < <> >= <= between）
在组合索引上，只使用部分列进行查询，导致查询出多行
对非唯一索引列上进行的任何查询。
3.3、索引全扫描（index full scan）
与全表扫描对应，全索引扫描。而且此时查询出的数据都必须从索引中可以直接得到。全索引扫描要知道
索引的所有内容
SELECT STATEMENT[CHOOSE] Cost=26
INDEX FULL SCAN BE_IX [ANALYZED]
3.4、索引快速扫描（index fast full scan）
扫描索引中的所有的数据块，与 index full scan很类似，但它不对查询出的数据进行排序，即数据不是
以排序顺序被返回。在这种存取方法中，可以使用多块读功能，也可以使用并行读入，以便获得最大吞吐量与
缩短执行时间。
SELECT STATEMENT[CHOOSE] Cost=1
INDEX FAST FULL SCAN BE_IX [ANALYZED]
3.5、索引跳跃扫描（index skip scan） where条件列是非索引的前导列情况下常发生
四、表之间的连接
Join是将两个表结合在一起的谓词，一次只能连接2个表。Join过程的各个步骤经常是串行操作，即使可以
并行的读取做join连接的两个row source的数据，将数据读入到内存形成row source后，join的其它步骤一般
是串行的。row source（表）之间的连接顺序对于查询的效率有非常大的影响。选取一个较小的row source作
为驱动表连接的效率较高，驱动表带执行限制条件的原因。
根据2个row source的连接条件的中操作符的不同，可以将连接分为等值连接（如WHERE A.COL3 = B.COL4）、
非等值连接（WHERE A.COL3>B.COL4）、外连接（WHEREA.COL3=B.COL4（+））。各个连接的连接原理都基本一
样。
执行计划中典型的连接类型共有3种：
排序-合并连接（Sort/Merge Join （SMJ））
嵌套循环（Nested Loops （NL） ）
哈希连接（Hash Join）
笛卡尔积（Cartesian product），一般情况下，尽量避免使用。
1、排序-合并连接（Sort Merge Join， SMJ） 连接加排序
内部连接过程：
a.首先生成row source1需要的数据，然后对这些数据按照连接操作关联列进行排序。
b.随后生成row source2需要的数据，然后对这些数据按照连接操作关联列进行排序。
c.最后两边已排序的行被放在一起执行合并操作，即将2个row source按照连接条件连接起来。
预先排序的row source包括已经被索引的列或row source已经在前面的步骤中被排序了，可以加快合并效率。
合并两个row source的过程是串行的，但是可以并行访问这两个row source（如并行读入数据，并行排序）。
因为排序消耗时间和资源，SMJ经常不是一个特别有效的连接方法。
SELECT STATEMENT [CHOOSE] Cost=17
MERGE JOIN
SORT JOIN
TABLE ACCESS FULL EMP [ANALYZED]
SORT JOIN
TABLE ACCESS FULL DEPT [ANALYZED]
2、嵌套循环（Nested Loops， NL）应用于连接的两个row source一个有索引，一个无索引情况
该连接过程就是一个2层嵌套循环，所以外层循环的次数越少越好，将小表或返回较小 row source的表作
为驱动表（用于外层循环）。驱动表选择对性能影响较大，但遵循这个理论并不能总保证使语句产生的I/O次
数最少。
内部连接过程：
Row source1的Row 1 —— Probe ->Row source 2
Row source1的Row 2 —— Probe ->Row source 2
Row source1的Row 3 —— Probe ->Row source 2
……。
Row source1的Row n —— Probe ->Row source 2
从内部连接过程来看，用row source1中的每一行，去匹配row source2中的所有行，最少的物理I/O次
数才是我们应该遵从的真正的指导原则。Row source1为驱动表或外部表。Row Source2被称为被探查表或内
部表。
NESTED LOOPS有其它连接方法没有的的一个优点是：可以先返回已经连接的行，而不必等待所有的连
接操作处理完才返回数据，这可以实现快速的响应时间。
SELECT STATEMENT [CHOOSE] Cost=5
NESTED LOOPS
TABLE ACCESS FULL DEPT [ANALYZED]
TABLE ACCESS FULL EMP [ANALYZED]
3、哈希连接（Hash Join， HJ）应用于连接的两个表都没有索引情况
从理论上来说比NL与SMJ更高效，而且只用在CBO优化器中。较小的row source被用来构建hash table
与bitmap，第2个row source被用来被hansed，并与第一个row source生成的hashtable进行匹配，以便进
行进一步的连接。当hash table比较大而能全部容纳在内存中时，这种查找方法效率极高。这种连接方
法中被构建为hash table与bitmap的表为驱动表。使用哈希连接要设置HASH_JOIN_ENABLED=TRUE，同时指
定哈希专用内存hash_area_size提高效率。
SELECT STATEMENT[CHOOSE] Cost=3
HASH JOIN
TABLE ACCESS FULL DEPT
TABLE ACCESS FULL EMP
4、笛卡儿乘积（Cartesian Product）
当做连接的两个row source之间没有关联条件时，就会在两个row source中做笛卡儿乘积，笛卡尔乘积是
一个表的每一行依次与另一个表中的所有行匹配。特殊场合如在星形连接中，除此之外，我们要尽量不使用笛
卡儿乘积。CARTESIAN 关键字指出了在2个表之间做笛卡尔乘积。
SLECT STATEMENT [CHOOSE] Cost=5
MERGE JOIN CARTESIAN
TABLE ACCESS FULL DEPT
SORT JOIN
TABLE ACCESS FULL EMP
连接适用情况总结
排序-合并连接（Sort Merge Join， SMJ）：
a.对于非等值连接，这种连接方式的效率是比较高的。
b.如果在关联的列上都有索引，效果更好。
c.对于将2个较大的row source做连接，该连接方法比NL连接要好一些。
d.但是如果sort merge返回的row source过大，会导致使用过多的rowid在表中查询数据，因过多I/O使
性能下降
嵌套循环（Nested Loops， NL）：
a.如果外部表比较小，并且在内部表上有唯一索引，或有高选择性非唯一索引时可以得到较好的效率。
b.可以先返回已经连接的行，而不必等待所有的连接操作处理完才返回数据，实现快速的响应时间。
哈希连接（Hash Join， HJ）：
a.在oracle7后引入，一般来说，其效率应该好于其它2种连接，但只能用在CBO优化器中，而且需要
设置合适的hash_area_size参数，才能取得较好的性能。
b.只能用于等值连接中。
五、执行计划解析
1、执行计划各列：
ID
操作顺序编号。
Plan hash value:
是这一条语句的hash值，我们知道oracle对每条语句产生的执行计划放在share pool里面，第一次要经
过硬解析，产生hash值。下次再执行该语句时候比较hash值，如果相同就不要执行硬解析。
Plan hash value: 2782876085
Operation（ 操作）
是把sql进行分解为计划的执行步骤。
Name（被操作的对象）
为operation的具体对象。
Row，有的地方也叫Cardinality（用plsqldev里面解释计划窗口）
这里是数据查询的行数
Byte
扫描的数据的字节数
Cost
cost没有单位，是一个相对值，供oracle用来评估cbo成本，选择执行计划用的。公式：
Cost=（Single block I/O cost+ Multiblock I/O cost+ CPU cost）/sreadtim。
Time
每段执行的时间
2、Predicate Information（条件谓词）这里列出的是过滤条件，一共有两种：
非索引（filter）：只起过滤作用
索引（access）：表示这个谓词的条件的值将会影响数据的访问路径（一般针对索引）
3、Statistics（统计信息）
0 recursive calls #递归调用SQL次数
8 db block gets #当前请求的数据块数
6 consistent gets #内存读取的块数，语句执行过程中逻辑访问的块
0 physical reads #物理读—从磁盘读到数据块数量
0 redo size #产生重做日志数，执行SQL的过程中，产生的重做日志的大小
551 bytes sent via SQL*Net to client #发送消耗
430 bytes received via SQL*Net from client #接收消耗
2 SQL*Net roundtrips to/from client #服务端和客户端交互次数
2 sorts (memory) #内存排序
0 sorts (disk) #硬盘排序
6 rows processed #处理行
六、相关内容补充：
执行计划的原则：
最右最上最先执行，在同一级如果某个动作没有子ID就最先执行
执行计划中的运算符：
sort #排序，消耗较多资源
order by,group by,sort merge join
filter #条件限制过滤，not in,in,min,max等
view #视图，由内联视图产生
partition view #分区视图
RBO和CBO
RBO Rule-Based Optimization #基于规则的优化器 oracle6-9 对数据不敏感，根据oracle指定的优先顺序
及规则生成执行计划。
CBO Cost-Based Optimization #基于代价的优化器 oracle10 根据可用的访问路径及访问路径等级选择执行
计划，对统计信息要求较高，会根据访问数据的多少确定是否走索引和全表扫描。
参考资料：http://www.cnblogs.com/kerrycode/p/3842215.html
使用执行计划的目的：
查看表之间连接的顺序(如得知哪个为驱动表，这需要从操作的顺序进行判断)、使用了何种类型的关联及具
体的存取路径(如判断是否利用了索引)，用于判断该表作为驱动表是否合适，如果不合适，对SQL语句进行更改。
组合索引使用原则：
组合索引 #同时包含两个或两个以上列的索引。
RBO优化器要求组合索引的引导列出现在where 字句中时才使用索引；
CBO优化器引入index skip scan,where 字句中没有索引引导列时也会使用索引；
可以人工干预语句是否通过索引查找；
SELECT/**//*+ INDEX (T INDX_T)*/ * FROM T WHERE OBJECT_TYPE='SYNONYM';
select/**//*+ NO_INDEX(T INDX_T)*/ * from t where object_name= 'DEPT';
参考资料：http://www.cnblogs.com/rootq/archive/2008/10/19/1314669.html
扫一扫
3586
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
