通过分析SQL语句的执行计划优化SQL(总结)由刀豆文库小编整理，希望给你工作、学习、生活带来方便，猜你可能喜欢“50个经典sql语句总结”。
通过分析SQL语句的执行计划优化SQL(总结)
做DBA快7年了，中间感悟很多。在DBA的日常工作中，调整个别性能较差的SQL语句时一项富有挑战性的工
作。其中的关键在于如何得到SQL语句的执行计划和如何从SQL语句的执行计划中发现问题。总是想将日常
经验的点点滴滴总结一下，但是直到最近才下定决心，总共花了3个周末时间，才将其整理成册，便于自
己日常工作。不好意思独享，所以将其贴出来。
修改日志：
2006.02.20：
根据网友反馈已做部分修改，但pdf文件没有做修改，修改部分在“如何产生执行计划”关于set
autotraceonly的介绍部分
第一章、第2章 并不是很重要，是自己的一些想法，关于如何做一个稳定、高效的应用系统的一些想法。
第三章以后都是比较重要的。
附录的内容也是比较重要的。我常用该部分的内容。
前言
本文档主要介绍与SQL调整有关的内容，内容涉及多个方面：SQL语句执行的过程、ORACLE优化器，表之间 的关联，如何得到SQL执行计划，如何分析执行计划等内容，从而由浅到深的方式了解SQL优化的过程，使
大家逐步步入SQL调整之门，然后你将发现„„。
该文档的不当之处，敬请指出，以便进一步改正。请将其发往我的信箱：xu_yu_jin2000@sina.com。
如果引用本文的内容，请著名出处
目录
第1章 性能调整综述 第2章 有效的应用设计
第3章 SQL语句处理的过程 第4章 ORACLE的优化器 第5章 ORACLE的执行计划 访问路径(方法)--acce path 表之间的连接
如何产生执行计划 如何分析执行计划
如何干预执行计划-合并连接(Sort Merge Join(SMJ))嵌套循环(Nested Loops(NL))哈希连接(Hash Join)
排序-合并连接(Sort Merge Join, SMJ)：
a)对于非等值连接，这种连接方式的效率是比较高的。b)如果在关联的列上都有索引，效果更好。c)对于将2个较大的row source做连接，该连接方法比NL连接要好一些。d)但是如果sort merge返回的row source过大，则又会导致使用过多的rowid在表中查询数据时，数据库
性能下降，因为过多的I/O。
嵌套循环(Nested Loops, NL)：
a)如果driving row source(外部表)比较小，并且在inner row source(内部表)上 有唯一索引，或有高选择性非唯一索引时，使用这种方法可以得到较好的效率。b)NESTED LOOPS有其它连接方法没有的的一个优点是：可以先返回已经 连接的行，而不必等待所有的连接操作处理完才返回数据，这可以实现快速的响应时间。哈希连接(Hash Join, HJ)：
a)这种方法是在oracle7后来引入的，使用了比较先进的连接理论，一般来说，其效率应该好于其它2种连接，但是这种连接只能用在 CBO优化器中，而且需要设置合适的hash_area_size参数，才能取得较好的性能。
b)在2个较大的row source之间连接时会取得相对较好的效率，在一个 row source较小时则能取得更好的效率。c)只能用于等值连接中
笛卡儿乘积(Cartesian Product)当两个row source做连接，但是它们之间没有关联条件时，就会在两个row source中做笛卡儿乘积，这通
常由编写代码疏漏造成(即程序员忘了写关联条件)。笛卡尔乘积是一个表的每一行依次与另一个表中的所
有行匹配。在特殊情况下我们可以使用笛卡儿乘积，如在星形连接中，除此之外，我们要尽量使用笛卡儿
乘积，否则，自己想结果是什么吧！
注意在下面的语句中，在2个表之间没有连接。SQL> explain plan for select emp.deptno,dept,deptno from emp,dept
Query Plan------------------------------SLECT STATEMENT [CHOOSE] Cost=5 MERGE JOIN CARTESIAN TABLE ACCESS FULL DEPT SORT JOIN TABLE ACCESS FULL EMP
CARTESIAN关键字指出了在2个表之间做笛卡尔乘积。假如表emp有n行，dept表有m行，笛卡尔乘积的结果
就是得到n * m行结果。
7楼
06-01-12 17:48 [ 大 中 小 ]
SunnyXu 一般会员
注册日期: 2004 Nov 来自:
技术贴数:38 精华贴数:1 论坛积分:267 论坛排名:9743 论坛徽章:0
[center]如何产生执行计划[/center]
要为一个语句生成执行计划，可以有3种方法： 1)．最简单的办法
Sql> set autotrace on Sql> select * from dual;执行完语句后，会显示explain plan 与 统计信息。这个语句的优点就是它的缺点，这样在用该方法查看执行时间较长的sql语句时，需要等待该语句执行成
功后，才返回执行计划，使优化的周期大大增长。
如果想得到执行计划，而不想看到语句产生的数据，可以采用： Sql> set autotrace traceonly 这样还是会执行语句。它比set autotrace on的优点是：不会显示出查询的数据，但是还是会将数据输出
到客户端，这样当语句查询的数据比较多时，语句执行将会花费大量的时间，因为很大部分时间用在将数
据从数据库传到客户端上了。我一般不用这种方法。
Sql> set autotrace traceonly explain 如同用explain plan命令。对于select 语句，不会执行select语句，而只是产生执行计划。但是对于dml
语句，还是会执行语句，不同版本的数据库可能会有小的差别。这样在优化执行时间较长的select语句时，大大减少了优化时间，解决了“set autotrace on”与“set autotrace traceonly”命令优化时执行
时间长的问题，但同时带来的问题是：不会产生Statistics数据，而通过tatistics数据的物理I/O的次数，我们可以简单的判断语句执行效率的优劣。
如果执行该语句时遇到错误，解决方法为：(1）在要分析的用户下：
Sqlplus > @ ?rdbmsadminutlxplan.sql(2)用sys用户登陆
Sqlplus > @ ?sqlplusadminplustrce.sql Sqlplus > grant plustrace to user_name;-> A)--> C。如果数据库是基于代价的优化器，它会利用计
算出的代价来决定合适的驱动表与合适的连接顺序。一般来说，CBO都会选择正确的连接顺序，如果CBO选
择了比较差的连接顺序，我们还可以使用ORACLE提供的hints来让CBO采用正确的连接顺序。如下所示：
select /*+ ordered */ A.col4 from B,A,C where B.col3 = 10 and A.col1 = B.col1 and A.col2 = C.col2 and C.col3 = 5
既然选择正确的驱动表这么重要，那么让我们来看一下执行计划，到底各个表之间是如何关联的，从而得
到执行计划中哪个表应该为驱动表：
在执行计划中，需要知道哪个操作是先执行的，哪个操作是后执行的，这对于判断哪个表为驱动表有用处
。判断之前，如果对表的访问是通过rowid，且该rowid的值是从索引扫描中得来得，则将该索引扫描先从
执行计划中暂时去掉。然后在执行计划剩下的部分中，判断执行顺序的指导原则就是：最右、最上的操作
先执行。具体解释如下：
得到去除妨碍判断的索引扫描后的执行计划： Execution Plan---------------------------0 SELECT STATEMENT Optimizer=CHOOSE 1 0 MERGE JOIN 2 1 SORT(JOIN)3 2 NESTED LOOPS 4 3 TABLE ACCESS(FULL)OF 'B' 5 3 TABLE ACCESS(BY INDEX ROWID)OF 'A' 7 1 SORT(JOIN)8 7 TABLE ACCESS(FULL)OF 'C' 看执行计划的第3列，即字母部分，每列值的左面有空格作为缩进字符。在该列值左边的空格越多，说明
该列值的缩进越多，该列值也越靠右。如上面的执行计划所示：第一列值为6的行的缩进最多，即该行最
靠右；第一列值为4、5的行的缩进一样，其靠右的程度也一样，但是第一列值为4的行比第一列值为5的行
靠上；谈论上下关系时，只对连续的、缩进一致的行有效。
从这个图中我们可以看到，对于NESTED LOOPS部分，最右、最上的操作是TABLE ACCESS(FULL)OF 'B'，所以这一操作先执行，所以该操作对应的B表为第一个驱动表(外部表)，自然，A表就为内部表了。从图中
还可以看出，B与A表做嵌套循环后生成了新的row source，对该row source进行来排序后，与C表对应的
排序了的row source(应用了C.col3 = 5限制条件)进行MSJ连接操作。所以从上面可以得出如下事实：B表
先与A表做嵌套循环，然后将生成的row source与C表做排序—合并连接。
通过分析上面的执行计划，我们不能说C表一定在B、A表之后才被读取，事实上，B表有可能与C表同时被
读入内存，因为将表中的数据读入内存的操作可能为并行的。事实上许多操作可能为交叉进行的，因为
