ORACLE培训SQL性能优化课件

ORACLE培训

--SQL性能优化1........ORACLE培训

--SQL性能优化1...1内容概述课程主要讨论： SQL语句执行的过程、ORACLE优化器，表之间的关联，如何得到SQL执行计划，如何分析执行计划等内容，从而由浅到深的方式了解SQL优化的过程，使大家逐步掌握SQL优化。2........内容概述课程主要讨论：2...2目录优化基础知识性能调整综述

有效的应用设计

SQL语句的处理过程

Oracle的优化器

Oracle的执行计划注意事项3........目录优化基础知识3....3SQL语句优化的过程定位有问题的语句检查执行计划检查执行过程中优化器的统计信息分析相关表的记录数、索引情况改写SQL语句、使用HINT、调整索引、表分析有些SQL语句不具备优化的可能，需要优化处理方式达到最佳执行计划4........SQL语句优化的过程定位有问题的语句4...4什么是好的SQL语句？尽量简单，模块化易读、易维护节省资源内存CPU扫描的数据块要少少排序不造成死锁5........什么是好的SQL语句？尽量简单，模块化5...5为什么要bindvariables?字符级的比较:SELECT*FROMUSER_FILESWHEREUSER_NO=‘10001234’;

与SELECT*FROMUSER_FILESWHEREUSER_NO=:BV1;检查：selectname,executionsfromv$db_object_cachewherenamelike'select*fromuser_files%'6........为什么要bindvariables?字符级的比较:6..6什么叫做重编译问题什么叫做重编译？下面这个语句每执行一次就需要在SHAREPOOL硬解析一次，一百万用户就是一百万次，消耗CPU和内存，如果业务量大，很可能导致宕库……如果绑定变量，则只需要硬解析一次，重复调用即可select*fromdConMsgwherecontract_no=320134840951397........什么叫做重编译问题什么叫做重编译？7...7绑定变量解决重编译问题未使用绑定变量的语句sprintf(sqlstr,"insertintoscott.test1(num1,num2)values(%d,%d)",n_var1,n_var2);EXECSQLEXECUTEIMMEDIATE:sqlstr;EXECSQLCOMMIT;

使用绑定变量的语句strcpy(sqlstr,"insertintotest(num1,num2)values(:v1,:v2)");EXECSQLPREPAREsql_stmtFROM:sqlstr;EXECSQLEXECUTEsql_stmtUSING:n_var1,:n_var2;EXECSQLCOMMIT;8........绑定变量解决重编译问题未使用绑定变量的语句8...8绑定变量的注意事项注意：1、不要使用数据库级的变量绑定参数cursor_sharing来强制绑定，无论其值为force还是similar2、有些带><的语句绑定变量后可能导致优化器无法正确使用索引9........绑定变量的注意事项注意：9...9SQL语句的四个处理阶段SQL语句的处理过程10........SQL语句的四个处理阶段SQL语句的处理过程10...10解析(PARSE)：SQL语句的处理过程在共享池中查找SQL语句检查语法检查语义和相关的权限合并(MERGE)视图定义和子查询确定执行计划11........解析(PARSE)：SQL语句的处理过程在共享池中查找SQL11绑定(BIND)：SQL语句的处理过程在语句中查找绑定变量赋值(或重新赋值)12........绑定(BIND)：SQL语句的处理过程在语句中查找绑定变量12执行(EXECUTE)：SQL语句的处理过程应用执行计划执行必要的I/O和排序操作提取(FETCH)：从查询结果中返回记录必要时进行排序使用ARRAYFETCH机制13........执行(EXECUTE)：SQL语句的处理过程应用执行计划提13共享游标：好处减少解析动态内存调整提高内存使用率14........共享游标：好处减少解析14...14书写可共享的SQL15........书写可共享的SQL15...15绑定变量和共享游标16........绑定变量和共享游标16...16ORACLE优化器模式概述Oracle的优化器共有3种模式：RULE(基于规则)、COST(基于成本)、CHOOSE(基于选择)。 设置缺省的优化器的方法，是在启动参数文件中针对OPTIMIZER_MODE参数的各种声明进行选择，如RULE、COST、CHOOSE、ALL_ROWS、FIRST_ROWS。当然也可以在SQL语句级别或是会话级别对其进行覆盖。 为了使用基于成本的优化器(CBO，Cost—BasedOptimizer)，必须经常运行analyze命令，以增加数据库中的对象统计信息(objectstatistics)的准确性。 如果数据库的优化器模式设置为基于选择，那么实际的优化器模式将和是否运行过analyze命令有关。如果数据表已经被analyze过，优化器模式将自动切换成CBO，反之，数据库将采用RULE形式的优化器。在缺省情况下，Oracle采用CHOOSE优化器。为避免那些不必要的全表扫描，必须尽量避免使用CHOOSE优化器，而直接采用基于规则或者基于成本的优化器。17........ORACLE优化器模式概述Oracle的优化器共有3种模17访问数据表的方式①全表扫描 全表扫描就是顺序地访问表中每条记录。Oracle采用一次读入多个数据块(databaseblock)的方式优化全表扫描。②通过ROWID访问表 ROWID包含了表中记录的物理位置信息。可以采用基于ROWID的访问方式情况提高访问表的效率。Oracle采用索引实现了数据和存放数据的物理位置(ROWID)之间的联系通常索引提供了快速访问ROWID的方法，因此那些基于索引列的查询就可以得到性能的提高。18........访问数据表的方式①全表扫描18...18数据库性能影响数据库系统性能的要素：主机CPU,RAM,存储系统;OS参数配置,ORACLE参数配置;应用方面：数据库设计及SQL编程的质量一个性能优秀的应用系统需要：良好的硬件配置；正确合理的数据库及中间件参数配置；合理的数据库设计；良好的sql编程；运行期的性能优化19........数据库性能影响数据库系统性能的要素：19...19SQLTunning的重点SQL:insert,update,delete,select;主要关注的是select关注的是：如何用最小的硬件资源消耗、最少的响应时间定位数据位置20........SQLTunning的重点SQL:insert,up20SQL优化的一般性原则目标：减少服务器资源消耗（主要是磁盘IO）；设计方面：尽量依赖oracle的优化器，并为其提供条件；合适的索引，索引的双重效应，列的选择性；编码方面：利用索引，避免大表FULLTABLESCAN；合理使用临时表；避免写过于复杂的sql，不一定非要一个sql解决问题；在不影响业务的前提下减小事务的粒度；21........SQL优化的一般性原则目标：21...21优化概括课程Oracle数据库SQL语句优化的总体策略。以这些优化策略为指导，通过经验总结，我们可以不断地丰富优化方案，进而指导我们进行应用系统的数据库性能优化。以下枚举几则被证明行之有效的优化方案：●创建表的时候。应尽量建立主键，尽量根据实际需要调整数据表的PCTFREE和PCTUSED参数；大数据表删除，用truncatetable代替delete。●合理使用索引，在OLTP应用中一张表的索引不要太多。数据重复量大的列不要建立二叉树索引，可以采用位图索引；组合索引的列顺序尽量与查询条件列顺序保持一致；对于数据操作频繁的表，索引需要定期重建，以减少失效的索引和碎片。22........优化概括课程Oracle数据库SQL语句优化的总体策略。以这22优化概括●查询尽量用确定的列名，少用*号。selectcount(key)fromtabwherekey>0性能优于selectcount(*)fromtab；尽量少嵌套子查询，这种查询会消耗大量的CPU资源；对于有比较多or运算的查询，建议分成多个查询，用unionall联结起来.●尽量多用commit语句提交事务，可以及时释放资源、解锁、释放日志空间、减少管理花费；在频繁的、性能要求比较高的数据操作中，尽量避免远程访问，如数据库链等，访问频繁的表可以常驻内存：altertable．．．cache；●在Oracle中动态执行SQL，尽量用execute方式，不用dbms_sql包。23........优化概括●查询尽量用确定的列名，少用*号。selectc23sql语句的编写原则和优化在编写SQL语句时我们应清楚优化器根据何种原则来使用索引，这有助于写出高性能的SQL语句。SQL语句的编写原则和SQL语句的优化，请跟我一起学习以下几方面：24........sql语句的编写原则和优化在编写SQL语句时我们应清楚优24避免复杂的多表关联select…fromuser_filesuf,df_money_filesdm,cw_charge_recordccwhereuf.user_no=dm.user_noanddm.user_no=cc.user_noand……andnotexists(select…)

???很难优化，随着数据量的增加性能的风险很大。25........避免复杂的多表关联select…25...25避免使用耗费资源的操作带有DISTINCT,UNION,MINUS,INTERSECT,ORDERBY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能.DISTINCT需要一次排序操作,而其他的至少需要执行两次排序.例如,一个UNION查询,其中每个查询都带有GROUPBY子句,GROUPBY会触发嵌入排序(NESTEDSORT);这样,每个查询需要执行一次排序,然后在执行UNION时,又一个唯一排序(SORTUNIQUE)操作被执行而且它只能在前面的嵌入排序结束后才能开始执行.嵌入的排序的深度会大大影响查询的效率.通常,带有UNION,MINUS,INTERSECT的SQL语句都可以用其他方式重写.26........避免使用耗费资源的操作带有DISTINCT,UNION,MI26例如:低效:SELECTDISTINCTDEPT_NO,DEPT_NAMEFROMDEPTD,EMPEWHERED.DEPT_NO=E.DEPT_NO高效:SELECTDEPT_NO,DEPT_NAMEFROMDEPTDWHEREEXISTS(SELECT‘X’FROMEMPEWHEREE.DEPT_NO=D.DEPT_NO);用EXISTS替换DISTINCT27........例如:用EXISTS替换DISTINCT27...27用UNION-ALL替换UNION(ifpossible)当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并,然后在输出最终结果前进行排序.举例:

低效：

SELECTACCT_NUM,BALANCE_AMTFROMDEBIT_TRANSACTIONSWHERETRAN_DATE=’31-DEC-95’UNIONSELECTACCT_NUM,BALANCE_AMTFROMDEBIT_TRANSACTIONSWHERETRAN_DATE=’31-DEC-95’高效:SELECTACCT_NUM,BALANCE_AMTFROMDEBIT_TRANSACTIONSWHERETRAN_DATE=’31-DEC-95’UNIONALLSELECTACCT_NUM,BALANCE_AMTFROMDEBIT_TRANSACTIONSWHERETRAN_DATE=’31-DEC-95’28........用UNION-ALL替换UNION(ifpossib282.给优化器更明确的命令29........2.给优化器更明确的命令29...29自动选择索引如果表中有两个以上（包括两个）索引，其中有一个唯一性索引，而其他是非唯一性．在这种情况下，ORACLE将使用唯一性索引而完全忽略非唯一性索引．举例:SELECTENAMEFROMEMPWHEREEMPNO=2326ANDDEPTNO=20;这里，只有EMPNO上的索引是唯一性的，所以EMPNO索引将用来检索记录．TABLEACCESSBYROWIDONEMPINDEXUNIQUESCANONEMP_NO_IDX30........自动选择索引如果表中有两个以上（包括两个）索引，其中有一个唯30至少要包含组合索引的第一列如果索引是建立在多个列上,只有在它的第一个列(leadingcolumn)被where子句引用时,优化器才会选择使用该索引.SQL>createtablemultiindexusage(indanumber,indbnumber,descrvarchar2(10));Tablecreated.SQL>createindexmultindexonmultiindexusage(inda,indb);Indexcreated.SQL>setautotracetraceonlySQL>select*frommultiindexusagewhereinda=1;ExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE10TABLEACCESS(BYINDEXROWID)OF'MULTIINDEXUSAGE'21INDEX(RANGESCAN)OF'MULTINDEX'(NON-UNIQUE)SQL>select*frommultiindexusagewhereindb=1;ExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE10TABLEACCESS(FULL)OF'MULTIINDEXUSAGE'

很明显,当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引31........至少要包含组合索引的第一列如果索引是建立在多个列上,只有在31避免在索引列上使用函数WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描．举例:低效：SELECT…FROMDEPTWHERESAL*12>25000;高效:SELECT…FROMDEPTWHERESAL>25000/12;32........避免在索引列上使用函数WHERE子句中，如果索引列是函数的一32避免使用前置通配符WHERE子句中,如果索引列所对应的值的第一个字符由通配符(WILDCARD)开始,索引将不被采用.

SELECTUSER_NO,USER_NAME,ADDRESS

FROMUSER_FILES

WHEREUSER_NOLIKE'%109204421';

在这种情况下，ORACLE将使用全表扫描.33........避免使用前置通配符WHERE子句中,如果索引列所对应的值的33避免在索引列上使用NOT通常，我们要避免在索引列上使用NOT,NOT会产生在和在索引列上使用函数相同的影响.当ORACLE”遇到”NOT,他就会停止使用索引转而执行全表扫描.举例:

低效:(这里,不使用索引)SELECT…FROMDEPTWHEREDEPT_CODENOT=0;

高效:(这里,使用了索引)SELECT…FROMDEPTWHEREDEPT_CODE>0;34........避免在索引列上使用NOT通常，我们要避免在索引列上使用NOT34避免在索引列上使用ISNULL和ISNOTNULL避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引．对于单列索引，如果列包含空值，索引中将不存在此记录.对于复合索引，如果每个列都为空，索引中同样不存在此记录.如果至少有一个列不为空，则记录存在于索引中．如果唯一性索引建立在表的A列和B列上,并且表中存在一条记录的A,B值为(123,null),ORACLE将不接受下一条具有相同A,B值（123,null）的记录(插入).然而如果所有的索引列都为空，ORACLE将认为整个键值为空而空不等于空.因此你可以插入1000条具有相同键值的记录,当然它们都是空!因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引.任何在where子句中使用isnull或isnotnull的语句优化器是不允许使用索引的。35........避免在索引列上使用ISNULL和ISNOTNULL避35避免出现索引列自动转换当比较不同数据类型的数据时,ORACLE自动对列进行简单的类型转换.假设USER_NO是一个字符类型的索引列.SELECTUSER_NO,USER_NAME,ADDRESSFROMUSER_FILESWHEREUSER_NO=109204421这个语句被ORACLE转换为:SELECTUSER_NO,USER_NAME,ADDRESSFROMUSER_FILESWHERETO_NUMBER(USER_NO)=109204421 因为内部发生的类型转换,这个索引将不会被用到!36........避免出现索引列自动转换当比较不同数据类型的数据时,ORAC36在查询时尽量少用格式转换如用WHEREa.order_no=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)37........在查询时尽量少用格式转换如用WHEREa.order_n373.减少访问次数38........3.减少访问次数38....38减少访问数据库的次数当执行每条SQL语句时,ORACLE在内部执行了许多工作:解析SQL语句,估算索引的利用率,绑定变量,读数据块等等.由此可见,减少访问数据库的次数,就能实际上减少ORACLE的工作量.类比，工程实施39........减少访问数据库的次数当执行每条SQL语句时,ORACLE在39使用DECODE来减少处理时间例如:SELECTCOUNT(*)，SUM(SAL)FROM

EMPWHEREDEPT_NO=0020ANDENAMELIKE‘SMITH%’;SELECTCOUNT(*)，SUM(SAL)FROM

EMPWHEREDEPT_NO=0030ANDENAMELIKE‘SMITH%’;你可以用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_NO,0030,SAL,NULL))D0030_SALFROMEMPWHEREENAMELIKE‘SMITH%’;40........使用DECODE来减少处理时间例如:40...40减少对表的查询在含有子查询的SQL语句中,要特别注意减少对表的查询.例如:

低效

SELECTTAB_NAMEFROMTABLESWHERETAB_NAME=(SELECTTAB_NAMEFROMTAB_COLUMNSWHEREVERSION=604)AND

DB_VER=(SELECTDB_VERFROMTAB_COLUMNSWHEREVERSION=604)

高效

SELECTTAB_NAMEFROMTABLESWHERE(TAB_NAME,DB_VER) =(SELECTTAB_NAME,DB_VER)FROMTAB_COLUMNSWHEREVERSION=604)41........减少对表的查询在含有子查询的SQL语句中,要特别注意减少对表414.细节上的影响42........4.细节上的影响42...42WHERE子句中的连接顺序ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,当在WHERE子句中有多个表联接时，WHERE子句中排在最后的表应当是返回行数可能最少的表，有过滤条件的子句应放在WHERE子句中的最后。如：设从emp表查到的数据比较少或该表的过滤条件比较确定，能大大缩小查询范围，则将最具有选择性部分放在WHERE子句中的最后： select*fromempe,deptd whered.deptno>10ande.deptno=30;如果dept表返回的记录数较多的话，上面的查询语句会比下面的查询语句响应快得多。 select*fromempe,deptd wheree.deptno=30andd.deptno>10;43........WHERE子句中的连接顺序ORACLE采用自下而上的顺序解析43WHERE子句——函数、表达式使用最好不要在WHERE子句中使用函或表达式，如果要使用的话，最好统一使用相同的表达式或函数，这样便于以后使用合理的索引。44........WHERE子句——函数、表达式使用最好不要在WHERE子句44Orderby语句ORDERBY语句决定了Oracle如何将返回的查询结果排序。Orderby语句对要排序的列没有什么特别的限制，也可以将函数加入列中（象联接或者附加等）。任何在Orderby语句的非索引项或者有计算表达式都将降低查询速度。仔细检查orderby语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写orderby语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在orderby子句中使用表达式。45........Orderby语句ORDERBY语句决定了Oracle45联接列对于有联接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的。 select*fromemployss

where

first_name||''||last_name='BeillCliton';系统优化器对基于last_name创建的索引没有使用。当采用下面这种SQL语句的编写，Oracle系统就可以采用基于last_name创建的索引。

select*fromemployee

where

first_name='Beill'andlast_name='Cliton';46........联接列对于有联接的列，即使最后的联接值为一个静态值，优化器46带通配符（%）的like语句通配符（%）在搜寻词首出现，Oracle系统不使用last_name的索引。

select*fromemployeewherelast_namelike'%cliton%';在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。在下面的查询中索引得到了使用：

select*fromemployeewherelast_namelike'c%';

47........带通配符（%）的like语句通配符（%）在搜寻词首出现，O47用Where子句替换HAVING子句避免使用HAVING子句,HAVING只会在检索出所有记录之后才对结果集进行过滤.这个处理需要排序,总计等操作.如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.例如:

低效:

SELECTREGION，AVG(LOG_SIZE)FROMLOCATIONGROUPBYREGIONHAVINGREGIONREGION!=‘SYDNEY’ANDREGION!=‘PERTH’

高效

SELECTREGION，AVG(LOG_SIZE)FROMLOCATIONWHEREREGIONREGION!=‘SYDNEY’ANDREGION!=‘PERTH’GROUPBYREGION

顺序 WHERE>GROUP>HAVING48........用Where子句替换HAVING子句避免使用HAVING子句48用NOTEXISTS替代NOTIN在子查询中,NOTIN子句将执行一个内部的排序和合并.无论在哪种情况下,NOTIN都是最低效的(因为它对子查询中的表执行了一个全表遍历).使用NOTEXISTS子句可以有效地利用索引。尽可能使用NOTEXISTS来代替NOTIN，尽管二者都使用了NOT（不能使用索引而降低速度），NOTEXISTS要比NOTIN查询效率更高。例如:语句1SELECTdname,deptnoFROMdeptWHERE deptnoNOTIN(SELECTdeptnoFROMemp);语句2SELECTdname,deptnoFROMdeptWHERE NOTEXISTS(SELECTdeptnoFROMempWHEREdept.deptno=emp.deptno);

2要比1的执行性能好很多。 因为1中对emp进行了fulltablescan,这是很浪费时间的操作。而且1中没有用到emp的index，因为没有where子句。而2中的语句对emp进行的是缩小范围的查询。49........用NOTEXISTS替代NOTIN在子查询中,NOT49用索引提高效率索引是表的一个概念部分,用来提高检索数据的效率，ORACLE使用了一个复杂的自平衡B-tree结构.通常,通过索引查询数据比全表扫描要快.当ORACLE找出执行查询和Update语句的最佳路径时,ORACLE优化器将使用索引.同样在联结多个表时使用索引也可以提高效率.另一个使用索引的好处是,它提供了主键(primarykey)的唯一性验证。通常,在大型表中使用索引特别有效.当然,你也会发现,在扫描小表时,使用索引同样能提高效率.虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价.索引需要空间来存储,也需要定期维护,每当有记录在表中增减或索引列被修改时,索引本身也会被修改.这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5次的磁盘I/O.因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引是有必要的。50........用索引提高效率索引是表的一个概念部分,用来提高检索数据的效率50避免在索引列上使用计算WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描．低效： SELECT…FROMDEPTWHERESAL*12>25000;高效: SELECT…FROMDEPTWHERESAL>25000/12;51........避免在索引列上使用计算WHERE子句中，如果索引列是函数的一51用>=替代>如果DEPTNO上有一个索引。高效:

SELECT*FROMEMPWHEREDEPTNO>=4

低效:

SELECT*FROMEMPWHEREDEPTNO>352........用>=替代>如果DEPTNO上有一个索引。52...52通过使用>=、<=等，避免使用NOT命令例子：select*fromemployeewheresalary<>3000;对这个查询，可以改写为不使用NOT：select*fromemployeewheresalary<3000orsalary>3000; 虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oracle对salary列使用索引，而第一种查询则不能使用索引。53........通过使用>=、<=等，避免使用NOT命令例子：53...53如果有其它办法，不要使用子查询。54........如果有其它办法，不要使用子查询。54...54用TRUNCATE替代DELETE

当删除表中的记录时,在通常情况下,回滚段(rollbacksegments)用来存放可以被恢复的信息.如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时,回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.55........用TRUNCATE替代DELETE 当删除表中的记录时,在通55比如有的表PHONE_NO字段是CHAR型,而且创建有索引，但在WHERE条件中忘记了加引号，就不会用到索引。WHEREPHONE_NO=WHEREPHONE_NO符型字段的引号56........比如有的表PHONE_NO字段是CHAR型,而且创建有索引，56优化EXPORT和IMPORT使用较大的BUFFER(比如10MB,10,240,000)可以提高EXPORT和IMPORT的速度;ORACLE将尽可能地获取你所指定的内存大小,即使在内存不满足,也不会报错.这个值至少要和表中最大的列相当,否则列值会被截断;57........优化EXPORT和IMPORT使用较大的BUFFER(比如157**

优化Tools**58........**优化Tools**58...58优化器与执行计划Oracle在执行一个SQL之前,首先要分析一下语句的执行计划,然后再按执行计划去执行。分析语句的执行计划的工作是由优化器(Optimizer)来完成的Oracle的优化器共有两种的优化方式,即基于规则的优化方式(Rule-BasedOptimization,简称为RBO)和基于代价的优化方式(Cost-BasedOptimization,简称为CBO)。

A、RBO方式：优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的,当一个where子句中的一列有索引时去走索引。B、CBO方式：是看语句的代价(Cost)了,这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息，很多的时侯过期统计信息会令优化器做出一个错误的执行计划在Oracle8及以后的版本,Oracle推荐用CBO的方式。在Oracle10g中,取消了RBO的支持。59........优化器与执行计划Oracle在执行一个SQL之前,首先要分析59优化器与执行计划Rule:即走基于规则的方式Choose:默认的情况下Oracle用的便是这种方式。当一个表或或索引有统计信息,则走CBO的方式,如果表或索引没统计信息,表又不是特别的小,而且相应的列有索引时,那么就走索引,走RBO的方式FirstRows:它与Choose方式是类似的,所不同的是当一个表有统计信息时,它将是以最快的方式返回查询的最先的几行,从总体上减少了响应时间AllRows:all_rows是oracle优化器默认的模式,它将选择一种在最短时间内返回所有数据的执行计划,它将基于整体成本的考虑.first_rows_n:first_rows_n是根据成本而不是基于硬编码的规则来选择执行计划.n可以是1,10,100,1000或者直接用first_rows(n)hint指定任意正数.这里的n是我们想获取结果集的前n条记录,这种需求在很多分页语句的需求中会碰到.60........优化器与执行计划Rule:即走基于规则的方式60...60Autotrace解读Currentmode: 对于修改的数据从数据段中读Read-consistentmode: 读一致性模式Physicalblock: 物理块（如8192字节）Recursivecalls: 嵌套调用次数61........Autotrace解读Currentmode: 对于修改61在SQLPLUS配置AUTOTRACEAUTOTRACE参数解释SETAUTOTRACEOFF不能获得AUTOTRACE报告.这是默认的.SETAUTOTRACEONEXPLAIN仅仅显示优化器执行计划的AUTOTRACE报告SETAUTOTRACEONSTATISTICS仅仅显示SQL语句执行的统计结果的AUTOTRACE报告SETAUTOTRACEON包括上面两项内容的AUTOTRACE报告SETAUTOTRACETRACEONLY与SETAUTOTRACEON类似,所有的统计和数据都在，但不可以打印62........在SQLPLUS配置AUTOTRACEAUTOTRACE62在SQLPLUS配置AUTOTRACE1、首先创建PLUSTRACE角色并且赋给public：Sql>@$ORACLE_HOME/sqlplus/admin/plustrce.sql2、赋权限给用户Sql>grantplustracetopublic（预赋权的用户名）;3、以SYSTEM用户创建PLAN_TABLE表Sql>@$ORACLE_HOME/rdbms/admin/utlxplan.sqlSql>createpublicsynonymplan_tableforplan_table;Sql>grantallonplan_tabletopublic;在每个用户下设置AUTOTRACE可显示其执行计划。63........在SQLPLUS配置AUTOTRACE1、首先创建PLU63SQL>selectename,dname

fromemp,dept

whereemp.deptno=dept.deptno

anddept.dnamein('ACCOUNTING','RESEARCH','SALES','OPERATIONS');

ExecutionPlan

0

SELECTSTATEMENTOptimizer=CHOOSE

1

0

NESTEDLOOPS

2

1

TABLEACCESS(FULL)OF'EMP'

3

1

TABLEACCESS(BYINDEXROWID)OF'DEPT'

4

3

INDEX(UNIQUESCAN)OF'PK_DEPT'(UNIQUE)最起码要解决全表扫描问题改变where条件的次序一般没有用优化器与执行计划64........SQL>selectename,dname

fro64SQL调整的目标去掉不必要的大型表的全表扫描。缓存小型表的全表扫描。校验优化索引的使用。检验优化的连接技术。以上目标任务将占据SQL调整90%以上的工作。65........SQL调整的目标去掉不必要的大型表的全表扫描。65...65理论上来说比NL与SMJ更高效，而且只用在CBO优化器中。较小的rowsource被用来构建hashtable与bitmap，第2个rowsource被用来被hansed，并与第一个rowsource生成的hashtable进行匹配，以便进行进一步的连接。Bitmap被用来作为一种比较快的查找方法，来检查在hashtable中是否有匹配的行。特别的，当hashtable比较大而不能全部容纳在内存中时，这种查找方法更为有用。这种连接方法也有NL连接中所谓的驱动表的概念，被构建为hashtable与bitmap的表为驱动表，当被构建的hashtable与bitmap能被容纳在内存中时，这种连接方式的效率极高。要使哈希连接有效，需要设置HASH_JOIN_ENABLED=TRUE，缺省情况下该参数为TRUE，另外，不要忘了还要设置hash_area_size参数，以使哈希连接高效运行，因为哈希连接会在该参数指定大小的内存中运行，过小的参数会使哈希连接的性能比其他连接方式还要低。

哈希连接(HashJoin,HJ)

HASH连接的例子：SQL>explainplanforselect/*+use_hash(emp)*/empnofromemp,deptwhereemp.deptno=dept.deptno;QueryPlan

SELECTSTATEMENT[CHOOSE]Cost=3HASHJOINTABLEACCESSFULLDEPTTABLEACCESSFULLEMP

66........理论上来说比NL与SMJ更高效，而且只用在CBO优化器中。66在哪种情况下用哪种连接方法比较好：排序--合并连接(SortMergeJoin,SMJ)：

对于非等值连接，这种连接方式的效率是比较高的。

如果在关联的列上都有索引，效果更好。

对将2个较大的rowsource做连接，该连接方法比NL连接要好些。

但是如果sortmerge返回的rowsource过大，则又会导致使用过多的rowid在表中查询数据时，数据库性能下降，因为过多的I/O。

嵌套循环(NestedLoops,NL)：

如果drivingrowsource(外部表)比较小，并且在innerrowsource(内部表)上有唯一索引，或有高选择性非唯一索引时，使用这种方法可以得到较好的效率。NESTEDLOOPS有其它连接方法没有的的一个优点是：可以先返回已经连接的行，而不必等待所有的连接操作处理完才返回数据，这可以实现快速的响应时间。总结

67........在哪种情况下用哪种连接方法比较好：总结67...67在哪种情况下用哪种连接方法比较好：哈希连接(HashJoin,HJ)：

此方法是在oracle7后来引入的，使用了比较先进的连接理论，其效率应该好于其它2种连接，但是这种连接只能用在CBO优化器中，而且需要设置合适的hash_area_size参数，才能取得较好的性能。

在2个较大的rowsource之间连接时会取得相对较好的效率，在一个rowsource较小时则能取得更好的效率。

只能用于等值连接中总结

68........在哪种情况下用哪种连接方法比较好：总结68...68要为一个语句生成执行计划，可以有3种方法：1)．最简单的办法Sql>setautotraceonSql>select*fromdual; 执行完语句后，会显示explainplan与统计信息。 这个语句的优点就是它的缺点，这样在用该方法查看执行时间较长的sql语句时，需要等待该语句执行成功后，才返回执行计划，使优化的周期大大增长。 如果不想执行语句而只是想得到执行计划可以采用： Sql>setautotracetraceonly 这样，就只会列出执行计划，而不会真正的执行语句，大大减少了优化时间。虽然也列出了统计信息，但是因为没有执行语句，所以该统计信息没有用处，如果执行该语句时遇到错误，解决方法为：a.在要分析的用户下：Sqlplus>@?\rdbms\admin\utlxplan.sqlb.用sys用户登陆Sqlplus>@?\sqlplus\admin\plustrce.sqlSqlplus>grantplustracetouser_name;--user_name是上面所说的分析用户如何产生执行计划

69........要为一个语句生成执行计划，可以有3种方法：如何产生执行计划692)．用explainplan命令(1)sqlplus>@?\rdbms\admin\utlxplan.sql(2)sqlplus>explainplansetstatement_id=’???’forselect………………注意： 此方法时，并不执行sql语句，所以只会列出执行计划，不会列出统计信息，并且执行计划只存在plan_table中。所以该语句比起setautotracetraceonly可用性要差。需要用下面的命令格式化输出，所以这种方式我用的不多：如何产生执行计划

setlinesize150setpagesize500colPLANLINEfora120SELECTEXECORDEXEC_ORDER,PLANLINEFROM(SELECTPLANLINE,ROWNUMEXECORD,ID,RIDFROM(SELECTPLANLINE,ID,RID,LEVFROM(SELECTlpad('',2*(LEVEL),rpad('',80,''))||OPERATION||''||--OperationDECODE(OPTIONS,NULL,'','('||OPTIONS||')')||--OptionsDECODE(OBJECT_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(COST)||',CARD='||TO_CHAR(CARDINALITY)||',BYTES='||TO_CHAR(BYTES)||')')PLANLINE,ID,LEVELLEV,(SELECTMAX(ID)FROMPLAN_TABLEPL2CONNECTBYPRIORID=PARENT_IDANDPRIORSTATEMENT_ID=STATEMENT_IDSTARTWITHID=PL1.IDANDSTATEMENT_ID=PL1.STATEMENT_ID)RIDFROMPLAN_TABLEPL1CONNECTBYPRIORID=PARENT_IDANDPRIORSTATEMENT_ID=STATEMENT_IDSTARTWITHID=0ANDSTATEMENT_ID='aaa')ORDERBYRID,-LEV))ORDERBYID;70........2)．用explainplan命令如何产生执行计划se702)．用explainplan命令以上2种方法只能为在本会话中正在运行的语句产生执行计划，即我们需要已经知道了哪条语句运行的效率很差，我们是有目的只对这条SQL语句去优化。在很多情况下，我们只会听一个客户抱怨说现在系统运行很慢，而我们不知道是哪个SQL引起的。此时有许多现成的语句可以找出耗费资源比较多的语句，如：SELECTADDRESS,substr(SQL_TEXT,1,20)Text,buffer_gets,executions,buffer_gets/executionsAVGFROMv$sqlareaWHEREexecutions>0ANDbuffer_gets>100000ORDERBY5; 从而对找出的语句进行进一步优化。还可以为一个正在运行的会话中运行的所有SQL语句生成执行计划，这需要对该会话进行跟踪，产生trace文件，然后对该文件用tkprof程序格式化一下，这种得到执行计划的方式很有用，因为它包含其它额外信息，如SQL语句执行的每个阶段(如Parse、Execute、Fetch)分别耗费的各个资源情况(如CPU、DISK、elapsed等)。如何产生执行计划

71........2)．用explainplan命令如何产生执行计划7171通过执行计划如何判断驱动表：

在执行计划中，需要知道哪个操作是先执行的，哪个操作是后执行的，这对于判断哪个表为驱动表有用处。判断之前，如果对表的访问是通过rowid，且该rowid的值是从索引扫描中来得，则将该索引扫描先从执行计划中暂时去掉。然后在执行计划剩下的部分中，判断执行顺序的指导原则就是：最右、最上的操作先执行。具体解释如下： 得到去除妨碍判断的索引扫描后的执行计划：ExecutionPlan0 SELECT STATEMENTOptimizer=CHOOSE1 0 MERGEJOIN2 1 SORT(JOIN)3 2 NESTEDLOOPS4 3 TABLEACCESS(FULL)OF'B'5 3 TABLEACCESS(BYINDEXROWID)OF'A'7 1 SORT(JOIN)8 7 TABLEACCESS(FULL)OF'C'如何分析执行计划

72........通过执行计划如何判断驱动表： 如何分析执行计划72...72如何使用hints： Hints只应用在它们所在sql语句块(statementblock，由select、update、delete关键字标识)上，对其它SQL语句或语句的其它部分没有影响。如：对于使用union操作的2个sql语句，如果只在一个sql语句上有hints，则该hints不会影响另一个sql语句。 可以使用注释(comment)来为一个语句添加hints，一个语句块只能有一个注释，而且注释只能放在SELECT,UPDATE,orDELETE关键字的后面。 使用hints的语法：{DELETE|INSERT|SELECT|UPDATE}/*+hint[te