ORACLE培训SQL性能优化

1、ORACLE培训-SQL性能优化内容概述述课程主要要讨论：SQL语句执行行的过程程、ORACLE优化器，表之间间的关联联，如何何得到SQL执行计划划，如何何分析执执行计划等等内容，从而由由浅到深深的方式式了解SQL优化的过过程，使大大家逐步步掌握SQL优化。目录优化基础础知识性能调整整综述有效的应应用设计计SQL语句的处处理过程程Oracle的优化器器Oracle的执行计计划注意事项项SQL语句优化化的过程程定位有问问题的语语句检查执行行计划检查执行行过程中中优化器器的统计计信息分析相关关表的记记录数、索引情情况改写SQL语句、使使用HINT、调整索索引、表表分析有些SQL语句不具具备优化化的

2、可能能，需要要优化处处理方式式达到最佳佳执行计计划什么是好好的SQL语句？尽量简单单，模块块化易读、易易维护节省资源源内存CPU扫描的数数据块要要少少排序不造成死死锁为什么要要bind variables?字符级的的比较:SELECT*FROM USER_FILESWHEREUSER_NO =10001234;与SELECT*FROM USER_FILESWHEREUSER_NO =:BV1;检查：selectname,executionsfrom v$db_object_cachewherename likeselect* fromuser_files%什么叫做做重编译译问题什么叫做做重编译

3、译？下面这个个语句每每执行一一次就需需要在SHAREPOOL硬解析一一次，一百百万用户户就是一一百万次次，消耗耗CPU和内存，如果业业务量大，很很可能导导致宕库库如果绑定定变量，则只需需要硬解解析一次次，重复复调用即即可select*from dConMsgwherecontract_no= 32013484095139绑定变量量解决重重编译问问题未使用绑绑定变量量的语句句sprintf(sqlstr,insertinto scott.test1 (num1,num2)values(%d,%d),n_var1, n_var2);EXEC SQLEXECUTE IMMEDIATE:sqlstr

4、;EXEC SQLCOMMIT;使用绑定定变量的的语句strcpy(sqlstr,insert intotest(num1,num2)values (:v1, :v2);EXEC SQLPREPARE sql_stmt FROM:sqlstr;EXEC SQLEXECUTE sql_stmt USING :n_var1, :n_var2;EXEC SQLCOMMIT;绑定变量量的注意意事项注意：1、不要使使用数据据库级的的变量绑绑定参数数cursor_sharing来强制绑定，无论其其值为force还是similar2、有些带带 0性能优于于selectcount(*)from tab；尽量少

5、嵌嵌套子查查询，这这种查询询会消耗耗大量的的CPU资源；对对于有比比较多or运算的查查询，建建议分成成多个查查询，用用unionall联结起来来. 尽量量多用commit语句提交交事务，可以及及时释放放资源、解锁、释放放日志空空间、减减少管理理花费；在频繁繁的、性性能要求求比较高高的数据操作作中，尽尽量避免免远程访访问，如如数据库库链等，访问频频繁的表表可以常驻内内存：altertablecache； 在Oracle中动态执执行SQL，尽量用用execute方式，不不用dbms_sql包。sql语句的编编写原则则和优化化在编写SQL语句时我我们应清清楚优化化器根据据何种原原则来使使用索引引，这

6、有有助于写写出高性性能的SQL语句。SQL语句的编编写原则则和SQL语句的优优化，请请跟我一一起学习习以下几几方面：避免复杂杂的多表表关联selectfrom user_filesuf,df_money_filesdm,cw_charge_record ccwhereuf.user_no =dm.user_noanddm.user_no= cc.user_noandandnot exists(select)?很难优化化，随着着数据量量的增加加性能的的风险很很大。避免使用用耗费资资源的操操作带有DISTINCT,UNION,MINUS,INTERSECT,ORDERBY的SQL语句会启启动SQL

7、引擎执行行耗费资资源的排排序(SORT)功能.DISTINCT需要一次次排序操操作,而其他的的至少需需要执行行两次排序.例如,一个UNION查询,其中每个个查询都都带有GROUPBY子句, GROUP BY会触发嵌嵌入排序序(NESTED SORT) ;这样,每个查询需要要执行一一次排序序,然后在执执行UNION时,又一个唯唯一排序(SORTUNIQUE)操作被执执行而且且它只能能在前面面的嵌入入排序结束束后才能能开始执执行.嵌入的排排序的深深度会大大大影响响查询的效率率.通常,带有UNION,MINUS,INTERSECT的SQL语句都可可以用其他方方式重写写.例如:低效:SELECTDIS

8、TINCTDEPT_NO,DEPT_NAMEFROM DEPTD,EMPEWHERED.DEPT_NO=E.DEPT_NO高效:SELECTDEPT_NO,DEPT_NAMEFROM DEPTDWHEREEXISTS(SELECTXFROM EMPEWHEREE.DEPT_NO=D.DEPT_NO);用EXISTS替换DISTINCT用UNION-ALL替换UNION( if possible)当SQL语句需要要UNION两个查询询结果集集合时,这两个结结果集合合会以UNION-ALL的方式被被合并,然后在输输出最终终结果前前进行排排序.举例:低效：SELECTACCT_NUM, BALANC

9、E_AMTFROM DEBIT_TRANSACTIONSWHERETRAN_DATE=31-DEC-95UNIONSELECTACCT_NUM, BALANCE_AMTFROM DEBIT_TRANSACTIONSWHERETRAN_DATE=31-DEC-95高效:SELECTACCT_NUM, BALANCE_AMTFROM DEBIT_TRANSACTIONSWHERETRAN_DATE=31-DEC-95UNIONALLSELECTACCT_NUM, BALANCE_AMTFROM DEBIT_TRANSACTIONSWHERETRAN_DATE=31-DEC-952.给优化器器更明确

10、确的命令令自动选择择索引如果表中中有两个个以上（包括两两个）索索引，其其中有一一个唯一一性索引，而而其他是是非唯一一性在这种情情况下，ORACLE将使用唯唯一性索索引而完完全忽略略非唯一性索引引举例:SELECTENAMEFROM EMPWHEREEMPNO= 2326ANDDEPTNO= 20 ;这里，只只有EMPNO上的索引引是唯一一性的，所以EMPNO索引将用来来检索记记录TABLEACCESSBYROWIDONEMPINDEXUNIQUESCANONEMP_NO_IDX至少要包包含组合合索引的的第一列列如果索引引是建立立在多个个列上,只有在它它的第一一个列(leadingcolumn)

11、被where子句引用用时,优化器才才会选择择使用该该索引.SQL createtablemultiindexusage(inda number, indbnumber ,descrvarchar2(10);Tablecreated.SQL createindexmultindexonmultiindexusage(inda,indb);Indexcreated.SQL setautotracetraceonlySQLselect*frommultiindexusagewhereinda= 1;ExecutionPlan-0SELECT STATEMENTOptimizer=CHOOSE10TA

12、BLEACCESS(BY INDEX ROWID)OFMULTIINDEXUSAGE21INDEX (RANGESCAN)OFMULTINDEX(NON-UNIQUE)SQL select* frommultiindexusagewhereindb =1;ExecutionPlan-0SELECT STATEMENTOptimizer=CHOOSE10TABLEACCESS(FULL) OF MULTIINDEXUSAGE 很明显,当仅引用用索引的的第二个个列时,优化器使使用了全全表扫描描而忽略略了索引引避免在索索引列上上使用函函数WHERE子句中，如果索索引列是是函数的的一部分分优化化器将不

13、不使用索引引而使用用全表扫扫描举例:低效：SELECTFROM DEPTWHERESAL*12 25000;高效:SELECTFROM DEPTWHERESAL25000/12;避免使用用前置通通配符WHERE子句中,如果索引引列所对对应的值值的第一一个字符符由通配符(WILDCARD)开始,索引将不不被采用用. SELECTUSER_NO,USER_NAME,ADDRESSFROM USER_FILESWHEREUSER_NO LIKE%109204421;在这种情情况下，ORACLE将使用全全表扫描描.避免在索索引列上上使用NOT通常，我我们要避避免在索索引列上上使用NOT, NOT会产生

14、在在和在索引列上上使用函函数相同同的影响响.当ORACLE”遇到”NOT,他就会停止使使用索引引转而执执行全表表扫描.举例:低效: (这里,不使用索索引)SELECTFROM DEPTWHEREDEPT_CODENOT =0; 高效: (这里,使用了索索引)SELECTFROM DEPTWHEREDEPT_CODE0;避免在索索引列上上使用ISNULL和ISNOTNULL避免在索索引中使使用任何何可以为为空的列列，ORACLE将无法使使用该索引对于单单列索引引，如果果列包含含空值，索引中中将不存存在此记记录.对于复合合索引，如果每每个列都都为空，索引中中同样不不存在此此记录.如果至少少有一个个

15、列不为为空，则则记录存存在于索索引中如果唯一一性索引引建立在在表的A列和B列上,并且表中中存在一一条记录的A,B值为(123,null) ,ORACLE将不接受受下一条条具有相相同A,B值（123,null）的记录录(插入).然而如果果所有的的索引列列都为空，ORACLE将认为整整个键值值为空而而空不等等于空.因此你可可以插入1000条具有相相同键值值的记录录,当然它们们都是空空!因为空值值不存在在于索引引列中,所以WHERE子句中对对索引列列进行空值比较较将使ORACLE停用该索索引.任何在where子句中使使用isnull或isnotnull的语句优优化器是是不允许使使用索引引的。避免出现

16、现索引列列自动转转换当比较不不同数据据类型的的数据时时, ORACLE自动对列列进行简简单的类型转转换.假设USER_NO是一个字字符类型型的索引引列.SELECTUSER_NO,USER_NAME,ADDRESSFROM USER_FILESWHEREUSER_NO =109204421这个语句句被ORACLE转换为:SELECTUSER_NO,USER_NAME,ADDRESSFROM USER_FILESWHERETO_NUMBER(USER_NO)=109204421因为内部部发生的的类型转转换,这个索引引将不会会被用到到!在查询时时尽量少少用格式式转换如用WHEREa.order_n

17、o =b.order_no不用 WHERETO_NUMBER(substr(a.order_no,instr(b.order_no,.) -1)= TO_NUMBER(substr(a.order_no, instr(b.order_no, .)-1)3.减少访问问次数减少访问问数据库库的次数数当执行每每条SQL语句时, ORACLE在内部执执行了许许多工作作:解析SQL语句,估算索引引的利用用率,绑定变量量,读数据块块等等.由此可见见,减少访问问数据库库的次数数,就能实际际上减少少ORACLE的工作量量.类比，工工程实施施使用DECODE来减少处处理时间间例如:SELECTCOUNT(*)，

18、SUM(SAL)FROMEMPWHEREDEPT_NO =0020ANDENAMELIKESMITH%;SELECTCOUNT(*)，SUM(SAL)FROMEMPWHEREDEPT_NO =0030ANDENAMELIKESMITH%;你可以用用DECODE函数高效效地得到到相同结结果SELECTCOUNT(DECODE(DEPT_NO,0020,X,NULL) D0020_COUNT,COUNT(DECODE(DEPT_NO,0030,X,NULL)D0030_COUNT,SUM(DECODE(DEPT_NO,0020,SAL,NULL) D0020_SAL,SUM(DECODE(DEPT

19、_NO,0030,SAL,NULL) D0030_SALFROM EMPWHEREENAMELIKE SMITH%;减少对表表的查询询在含有子子查询的的SQL语句中,要特别注注意减少少对表的的查询.例如:低效SELECTTAB_NAMEFROM TABLESWHERETAB_NAME= (SELECT TAB_NAMEFROM TAB_COLUMNSWHEREVERSION =604)ANDDB_VER= (SELECT DB_VERFROM TAB_COLUMNSWHEREVERSION =604)高效SELECTTAB_NAMEFROM TABLESWHERE(TAB_NAME,DB_VE

20、R)= (SELECT TAB_NAME,DB_VER)FROM TAB_COLUMNSWHEREVERSION =604)4.细节上的的影响WHERE子句中的的连接顺顺序ORACLE采用自下下而上的的顺序解解析WHERE子句,根据这个个原理,当在WHERE子句中有有多个表表联接时时，WHERE子句中排排在最后的的表应当当是返回回行数可可能最少少的表，有过滤滤条件的的子句应放在在WHERE子句中的的最后。如：设从从emp表查到的的数据比比较少或或该表的的过滤条条件比较较确定，能大大大缩小查查询范围围，则将将最具有有选择性性部分放放在WHERE子句中的的最后：select*from empe,d

21、eptdwhered.deptno10ande.deptno=30;如果dept表返回的的记录数数较多的的话，上上面的查查询语句句会比下下面的查查询语句句响应快快得多。select*from empe,deptdwheree.deptno=30andd.deptno10;WHERE子句函数、表表达式使使用最好不要要在WHERE子句中中使用函函或表达达式，如如果要使使用的话话，最好好统一使使用相同同的表达达式或函函数，这这样便于于以后使使用合理理的索引引。Orderby语句ORDERBY语句决定定了Oracle如何将返返回的查查询结果果排序。Orderby语句对要要排序的的列没有有什么特特别的限

22、限制，也也可以将将函数加加入列中中（象联联接或者者附加等等）。任何在Orderby语句的非非索引项项或者有有计算表表达式都都将降低低查询速速度。仔细检查查orderby语句以找找出非索索引项或或者表达达式，它它们会降降低性能能。解决决这个问问题的办办法就是是重写orderby语句以使使用索引引，也可可以为所所使用的的列建立立另外一一个索引引，同时时应绝对对避免在在orderby子句中使使用表达达式。联接列对于有联联接的列列，即使使最后的的联接值值为一个个静态值值，优化化器是不会使使用索引引的。select*from employsswherefirst_name|last_name =Beil

23、lCliton;系统优化化器对基基于last_name创建的索索引没有有使用。当采用下下面这种种SQL语句的编编写，Oracle系统就可可以采用用基于last_name创建的索索引。select*from employeewherefirst_name =Beill andlast_name=Cliton;带通配符符（%）的like语句通配符（%）在搜寻寻词首出出现，Oracle系统不使使用last_name的索引。select*from employee where last_namelike %cliton%;在很多情情况下可可能无法法避免这这种情况况，但是是一定要要心中有有底，通配符符如

24、此使使用会降降低查询询速度。然而当当通配符符出现在在字符串其他他位置时时，优化化器就能能利用索索引。在在下面的的查询中中索引得到了了使用：select*from employee where last_namelike c%;用Where子句替换换HAVING子句避免使用用HAVING子句, HAVING只会在检检索出所所有记录录之后才才对结果果集进行过过滤.这个处理理需要排排序,总计等操操作.如果能通通过WHERE子句限制记录的的数目,那就能减减少这方方面的开开销.例如:低效:SELECTREGION，AVG(LOG_SIZE)FROM LOCATIONGROUPBYREGIONHAVING

25、REGION REGION!=SYDNEYANDREGION != PERTH高效SELECTREGION，AVG(LOG_SIZE)FROM LOCATIONWHEREREGIONREGION != SYDNEYANDREGION != PERTHGROUPBYREGION 顺序WHERE GROUP HAVING用NOTEXISTS替代NOTIN在子查询询中,NOT IN子句将执执行一个个内部的的排序和和合并.无论在哪哪种情况况下,NOT IN都是最低低效的(因为它对对子查询询中的表表执行了了一个全全表遍历历).使用NOTEXISTS子子句可可以有效效地利用用索引。尽可能能使用NOTEXI

26、STS来代替NOTIN，尽管二二者都使使用了NOT（不能使使用索引引而降低低速度），NOTEXISTS要比NOTIN查查询效率率更高。例如:语句1SELECTdname, deptnoFROM deptWHEREdeptnoNOT IN (SELECTdeptno FROMemp);语句2SELECTdname, deptnoFROM deptWHERENOTEXISTS(SELECT deptnoFROM empWHEREdept.deptno= emp.deptno);2要比1的执行性性能好很很多。因为1中对emp进行了full table scan,这是很浪浪费时间间的操作作。而且且1中

27、没有用用到emp的index， 因为为没有where子句。而而2中的语句句对emp进行的是是缩小范范围的查查询。用索引提提高效率率索引是表表的一个个概念部部分,用来提高高检索数数据的效效率，ORACLE使用了一个个复杂的的自平衡衡B-tree结构.通常,通过索引引查询数数据比全全表扫描要快快.当ORACLE找出执行行查询和和Update语句的最最佳路径径时,ORACLE优化器将将使用索索引.同样在联联结多个个表时使使用索引引也可以以提高效率率.另一个使使用索引引的好处处是,它提供了了主键(primarykey)的唯一性性验证。通常,在大型表表中使用用索引特特别有效效.当然,你也会发发现,在扫描

28、小小表时,使用索引引同样能能提高效效率.虽然使用用索引能能得到查查询效率率的提高,但是我们们也必须须注意到到它的代代价.索引需要要空间来来存储,也需要定期维护护,每当有记记录在表表中增减减或索引引列被修修改时,索引本身身也会被修改改.这意味着着每条记记录的INSERT,DELETE,UPDATE将为此多付出出4 ,5次的磁盘盘I/O.因为索引引需要额额外的存存储空间间和处理理,那些不必必要的索索引反而而会使查查询反应应时间变变慢.。定期的的重构索索引是有必要要的。避免在索索引列上上使用计计算WHERE子句中，如果索索引列是是函数的的一部分分优化化器将不不使用索引引而使用用全表扫扫描低效：SEL

29、ECTFROMDEPTWHERESAL*12 25000;高效:SELECTFROM DEPTWHERESAL 25000/12;用=替代如果DEPTNO上有一个个索引。高效:SELECT*FROM EMPWHEREDEPTNO=4 低效:SELECT*FROM EMPWHEREDEPTNO3通过使用用=、=等，避免免使用NOT命令例子：select*from employee wheresalary3000;对这个查查询，可可以改写写为不使使用NOT：select*from employee wheresalary3000;虽然这两两种查询询的结果果一样，但是第第二种查查询方案案会比第第一种

30、查查询方案案更快些些。第二二种查询询允许Oracle对salary列使用索索引，而而第一种种查询则则不能使使用索引引。如果有其其它办法法，不要要使用子子查询。用TRUNCATE替代DELETE当删除表表中的记记录时,在通常情情况下,回滚段(rollbacksegments)用来存放放可以被被恢复的的信息.如果你没没有COMMIT事务,ORACLE会将数据据恢复到到删除之之前的状状态(准确地说是是恢复到到执行删删除命令令之前的的状况)而当运用用TRUNCATE时,回滚段不不再存放放任何可可被恢复复的信息.当命令运运行后,数据不能能被恢复复.因此很少少的资源源被调用用,执行时间间也会很很短.比如有

31、的的表PHONE_NO字段是CHAR型,而且创建建有索引引，但在WHERE条件中忘忘记了加加引号，就不会会用到索索引。WHEREPHONE_NOHEREPHONE_NO符型字字段的引引号优化EXPORT和IMPORT使用较大大的BUFFER(比如10MB ,10,240,000)可以提高高EXPORT和IMPORT的速度;ORACLE将尽可能能地获取取你所指指定的内内存大小小,即使在内内存不满足,也不会报报错.这个值至至少要和和表中最最大的列列相当,否则列值会被被截断;*优化Tools*优化器与与执行计计划Oracle在执行一一个SQL之前,

32、首先要分分析一下下语句的的执行计计划,然后再按按执行计计划去执执行。分分析语句句的执行行计划的的工作是由优化化器(Optimizer)来完成的的Oracle的优化器器共有两两种的优优化方式式,即基于规则则的优化化方式(Rule-BasedOptimization,简称为RBO)和基于代价价的优化方式(Cost-BasedOptimization,简称为CBO)。A、RBO方式：优优化器在在分析SQL语句时,所遵循的的是Oracle内部预定定的一些规规则。比比如我们们常见的的,当一个where子句中的的一列有有索引时时去走索引。B、CBO方式：是是看语句句的代价价(Cost)了,这里的代代价主要

33、要指Cpu和内存。优化器器在判断断是否用用这种方方式时,主要参照照的是表表及索引引的统计计信息，很多的时时侯过期期统计信信息会令令优化器器做出一一个错误误的执行行计划在在Oracle8及以后的的版本,Oracle推荐用CBO的方式。在Oracle10g中,取消了RBO的支持。优化器与与执行计计划Rule:即走基于于规则的的方式Choose:默认的情情况下Oracle用的便是是这种方方式。当当一个表表或或索索引有统计信息息,则走CBO的方式,如果表或或索引没没统计信信息,表又不是是特别的的小,而且相应应的列有有索引时时,那么就走走索引,走RBO的方式FirstRows:它与Choose方式是类类

34、似的,所不同的的是当一一个表有有统计信信息时,它将是以以最快的的方式返返回查询询的最先先的几行行,从总体上上减少了了响应时时间AllRows:all_rows是oracle优化器默默认的模模式,它将选择择一种在在最短时时间内返回回所有数数据的执执行计划划,它将基于于整体成成本的考考虑.first_rows_n:first_rows_n是根据成成本而不不是基于于硬编码码的规则则来选择择执行计划划.n可以是1,10,100,1000或者直接接用first_rows(n) hint指定任意意正数.这里的n是我们想想获取结结果集的的前n条记录,这种需求求在很多多分页语语句的需求求中会碰碰到.Autot

35、race解读Current mode:对于修改改的数据据从数据据段中读读Read-consistentmode:读一致性性模式Physicalblock:物理块（如8192字节）Recursivecalls:嵌套调用用次数在SQLPLUS配置AUTOTRACE在SQLPLUS配置AUTOTRACE1、首先创建建PLUSTRACE角色并且且赋给public：Sql $ORACLE_HOME/sqlplus/admin/plustrce.sql2、 赋权权限给用用户Sql grant plustracetopublic（预赋权权的用户户名）;3、以SYSTEM用户创建建PLAN_TABLE表Sql

36、 $ORACLE_HOME/rdbms/admin/utlxplan.sqlSql createpublicsynonymplan_table forplan_table;Sql grant allonplan_table to public;在每个用用户下设设置AUTOTRACE可显示其其执行计计划。SQL selectename,dnamefrom emp,deptwhere emp.deptno=dept.deptnoanddept.dname in (ACCOUNTING,RESEARCH,SALES,OPERATIONS);ExecutionPlan-0SELECT STATEMEN

37、TOptimizer=CHOOSE10NESTEDLOOPS21TABLE ACCESS(FULL)OFEMP31TABLE ACCESS(BYINDEXROWID) OF DEPT43INDEX(UNIQUESCAN)OFPK_DEPT(UNIQUE)最起码要要解决全全表扫描描问题改变where条件的次次序一般般没有用用优化器与与执行计计划SQL调整的目目标去掉不必必要的大大型表的的全表扫扫描。缓存小型型表的全全表扫描描。校验优化化索引的的使用。检验优化化的连接接技术。以上目标标任务将将占据SQL调整90%以上的工工作。理论上来来说比NL与SMJ更高效，而且只只用在CBO优化器中中。较小的r

38、owsource被用来构构建hash table与bitmap，第2个rowsource被用来被被hansed，并与第第一个rowsource生成的hash table进行匹配配，以便便进行进进一步的的连接。Bitmap被用来作作为一种种比较快快的查找找方法，来检查查在hash table中是否有有匹配的的行。特特别的，当hash table比较大而而不能全全部容纳纳在内存存中时，这种查查找方法法更为有有用。这这种连接接方法也也有NL连接中所所谓的驱驱动表的的概念，被构建建为hash table与bitmap的表为驱驱动表，当被构构建的hash table与bitmap能被容纳纳在内存存中时，

39、这种连连接方式式的效率率极高。要使哈希希连接有有效，需需要设置置HASH_JOIN_ENABLED=TRUE，缺省情情况下该该参数为为TRUE，另外，不要忘忘了还要要设置hash_area_size参数，以以使哈希希连接高高效运行行，因为为哈希连连接会在在该参数数指定大大小的内内存中运运行，过过小的参参数会使使哈希连连接的性性能比其其他连接接方式还还要低。哈希连接接(HashJoin,HJ)HASH连接的的例子：SQL explainplanforselect/*+use_hash(emp) */ empnofrom emp,deptwhereemp.deptno =dept.deptno;Q

40、ueryPlan-SELECTSTATEMENT CHOOSE Cost=3HASH JOINTABLEACCESSFULLDEPTTABLEACCESSFULLEMP在哪种情情况下用用哪种连连接方法法比较好好：排序- -合并连接接(SortMergeJoin,SMJ)：对于非等等值连接接，这种种连接方方式的效效率是比比较高的的。如果在关关联的列列上都有有索引，效果更更好。对将2个较大的的rowsource做连接，该连接接方法比比NL连接要好好些。但是如果果sort merge返回的rowsource过大，则则又会导导致使用用过多的rowid在表中查查询数据据时，数数据库性性能下降降，因为为过

41、多的的I/O。 嵌套循环环(Nested Loops,NL)：如果driving rowsource(外部表)比较小，并且在在innerrowsource(内部表)上有唯一一索引，或有高高选择性性非唯一一索引时时，使用用这种方方法可以以得到较较好的效效率。NESTEDLOOPS有其它连连接方法法没有的的的一个个优点是是：可以以先返回回已经连连接的的行，而而不必等等待所有有的连接接操作处处理完才才返回数数据，这这可以实实现快速速的响应应时间。总 结在哪种情情况下用用哪种连连接方法法比较好好：哈希连接接(HashJoin,HJ)：此方法是是在oracle7后来引入入的，使使用了比比较先进进的连接接

42、理论，其效率应应该好于于其它2种连接，但是这这种连接接只能用用在CBO优化器中中，而且且需要设设置合适适的hash_area_size参数，才才能取得较好好的性能能。在2个较大的的rowsource之间连接接时会取取得相对对较好的的效率，在一个rowsource较小时则则能取得得更好的的效率。只能用于于等值连连接中总 结要为一个个语句生生成执行行计划，可以有有3种方法：1)最简单单的办法法Sql setautotraceonSql select* fromdual;执行完语语句后，会显示示explain plan与 统计计信息。这个语句句的优点点就是它它的缺点点，这样样在用该该方法查查看执行行

43、时间较较长的sql语句时，需要等等待该语语句执行行成功后后，才返返回执行行计划，使优化化的周期期大大增增长。如果不想想执行语语句而只只是想得得到执行行计划可可以采用用：Sql setautotracetraceonly这样，就就只会列列出执行行计划，而不会会真正的的执行语语句，大大大减少少了优化化时间。虽然也也列出了了统计信信息，但但是因为为没有执执行语句句，所以以该统计计信息没没有用处处，如果果执行该该语句时时遇到错错误，解解决方法法为：a.在要分析析的用户户下：Sqlplus ?rdbmsadminutlxplan.sqlb.用sys用户登陆陆Sqlplus ?sqlplusadminpl

44、ustrce.sqlSqlplus grantplustrace to user_name;-user_name是上面所所说的分分析用户户如何产生生执行计计划2)用explain plan命令(1)sqlplus ?rdbmsadminutlxplan.sql(2)sqlplusexplain planset statement_id=?for select注意：此方法时时，并不不执行sql语句，所所以只会会列出执执行计划划，不会会列出统统计信息息，并且且执行计计划只存存在plan_table中。所以以该语句句比起setautotrace traceonly可用性要要差。需需要用下下面的命命令

45、格式式化输出出，所以以这种方方式我用用的不多多：如何产生生执行计计划setlinesize150setpagesize500colPLANLINEfor a120SELECTEXECORDEXEC_ORDER,PLANLINEFROM (SELECTPLANLINE, ROWNUMEXECORD,ID,RIDFROM (SELECTPLANLINE, ID,RID, LEVFROM (SELECTlpad( ,2*(LEVEL),rpad(,80,)|OPERATION| - OperationDECODE(OPTIONS,NULL,(|OPTIONS|)| - OptionsDECODE(O

46、BJECT_OWNER,null,OF | OBJECT_OWNER|.)| - OwnerDECODE(OBJECT_NAME,null,OBJECT_NAME| )| - ObjectNameDECODE(OBJECT_TYPE,null,(|OBJECT_TYPE|)|-ObjectTypeDECODE(ID,0,OPT_MODE:)|-OptimizerDECODE(OPTIMIZER,null,ANALYZED,OPTIMIZER)|DECODE(NVL(COST,0)+NVL(CARDINALITY,0)+NVL(BYTES,0),0,null,(COST=|TO_CHAR(COS

47、T)|,CARD=|TO_CHAR(CARDINALITY)|,BYTES=|TO_CHAR(BYTES)|)PLANLINE,ID, LEVEL LEV,(SELECT MAX(ID)FROM PLAN_TABLEPL2CONNECT BY PRIOR ID =PARENT_IDANDPRIORSTATEMENT_ID =STATEMENT_IDSTARTWITH ID =PL1.IDANDSTATEMENT_ID =PL1.STATEMENT_ID)RIDFROM PLAN_TABLEPL1CONNECT BY PRIOR ID =PARENT_IDANDPRIORSTATEMENT_ID

48、 =STATEMENT_IDSTARTWITH ID =0ANDSTATEMENT_ID =aaa)ORDERBYRID, -LEV)ORDERBYID;2)用explain plan命令以上2种方法只只能为在在本会话话中正在在运行的的语句产产生执行行计划，即我们们需要已已经知道道了哪条条语句运运行的效效率很差差，我们们是有目目的只对对这条SQL语句去优优化。在在很多情情况下，我们只只会听一一个客户户抱怨说说现在系系统运行行很慢，而我们们不知道道是哪个个SQL引起的。此时有有许多现现成的语语句可以以找出耗耗费资源源比较多多的语句句，如：SELECTADDRESS,substr(SQL_TEXT

49、,1,20)Text,buffer_gets,executions,buffer_gets/executions AVGFROM v$sqlareaWHEREexecutions0ANDbuffer_gets100000ORDERBY5;从而对找找出的语语句进行行进一步步优化。还可以以为一个个正在运运行的会会话中运运行的所所有SQL语句生成成执行计计划，这这需要对对该会话话进行跟跟踪，产产生trace文件，然然后对该该文件用用tkprof程序格式式化一下下，这种种得到执执行计划划的方式式很有用用，因为为它包含含其它额额外信息息，如SQL语句执行行的每个个阶段(如Parse、Execute、Fe

50、tch)分别耗费费的各个个资源情情况(如CPU、DISK、elapsed等)。如何产生生执行计计划通过执行行计划如如何判断断驱动表表：在执行计计划中，需要知知道哪个个操作是是先执行行的，哪哪个操作作是后执执行的，这对于于判断哪哪个表为为驱动表表有用处处。判断断之前，如果对对表的访访问是通通过rowid，且该rowid的值是从从索引扫扫描中来来得，则则将该索索引扫描描先从执执行计划划中暂时时去掉。然后在在执行计计划剩下下的部分分中，判判断执行行顺序的的指导原原则就是是：最右、最最上的操操作先执执行。具体解解释如下下：得到去除除妨碍判判断的索索引扫描描后的执执行计划划：ExecutionPlan-0SELECTSTATEMENT Optimizer=CHOOSE10MERGEJO