《ORACLE培训SQL性能优化.pptx》由会员分享，可在线阅读，更多相关《ORACLE培训SQL性能优化.pptx（76页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、ORACLEORACLE培训培训-SQL-SQL性能优化性能优化内容概述内容概述课程主要讨论：课程主要讨论：SQL语句执行的过程、语句执行的过程、ORACLE优化器优化器，表之间的关联，如何得到，表之间的关联，如何得到SQL执行执行计划，如何分析执计划，如何分析执行计划等内容，从而由浅到深的方式行计划等内容，从而由浅到深的方式了解了解SQL优化的过优化的过程，使大家逐步掌握程，使大家逐步掌握SQL优化。优化。目录目录1.优化基础知识优化基础知识2.性能调整综述性能调整综述3.有效的应用设计有效的应用设计4.SQL语句的处理过程语句的处理过程5.Oracle的优化器的优化器6.Oracle的执行
2、计划的执行计划7.注意事项注意事项SQL语句优化的过程语句优化的过程n定位有问题的语句定位有问题的语句n检查执行计划检查执行计划n检查执行过程中优化器的统计信息检查执行过程中优化器的统计信息n分析相关表的记录数、索引情况分析相关表的记录数、索引情况n改写改写SQL语句、使用语句、使用HINT、调整索引、调整索引、表分析表分析n有些有些SQL语句不具备优化的可能，需语句不具备优化的可能，需要优化处理方式要优化处理方式n达到最佳执行计划达到最佳执行计划什么是好的什么是好的SQL语句？语句？n尽量简单，模块化尽量简单，模块化n易读、易维护易读、易维护n节省资源节省资源内存内存CPU扫描的数据块要少扫
3、描的数据块要少少排序少排序n不造成死锁不造成死锁为什么要为什么要bindvariables?n字符级的比较字符级的比较:SELECT*FROMUSER_FILESWHEREUSER_NO=10001234;与与SELECT*FROMUSER_FILESWHEREUSER_NO=:BV1;n检查：检查：selectname,executionsfromv$db_object_cachewherenamelikeselect*fromuser_files%什么叫做重编译问题什么叫做重编译问题什么叫做重编译？什么叫做重编译？下面这个语句每执行一次就需要在下面这个语句每执行一次就需要在SHAREPOO
4、L硬解析一硬解析一次，一百万用户就是一百万次，消耗次，一百万用户就是一百万次，消耗CPU和内存，如果业务和内存，如果业务量大，很可能导致宕库量大，很可能导致宕库如果绑定变量，则只需要硬解析一次，重复调用即可如果绑定变量，则只需要硬解析一次，重复调用即可select*fromdConMsgwherecontract_no=32013484095139绑定变量解决重编译问题绑定变量解决重编译问题未使用绑定变量的语句未使用绑定变量的语句sprintf(sqlstr,insertintoscott.test1(num1,num2)values(%d,%d),n_var1,n_var2);EXECSQL
5、EXECUTEIMMEDIATE:sqlstr;EXECSQLCOMMIT;使用绑定变量的语句使用绑定变量的语句strcpy(sqlstr,insertintotest(num1,num2)values(:v1,:v2);EXECSQLPREPAREsql_stmtFROM:sqlstr;EXECSQLEXECUTEsql_stmtUSING:n_var1,:n_var2;EXECSQLCOMMIT;绑定变量的注意事项绑定变量的注意事项注意：注意：1、不要使用数据库级的变量绑定参数、不要使用数据库级的变量绑定参数cursor_sharing来强来强制绑定，无论其值为制绑定，无论其值为force
6、还是还是similar2、有些带、有些带0性能优于性能优于selectcount(*)fromtab；尽量少嵌套子查询，这种查询会消耗大量的尽量少嵌套子查询，这种查询会消耗大量的CPU资源；对于有比较多资源；对于有比较多or运算的查询，建议分成多个查询，用运算的查询，建议分成多个查询，用unionall联结起来联结起来.尽量多用尽量多用commit语句提交事务，可以及时释放资源、解语句提交事务，可以及时释放资源、解锁、释放日志空间、减少管理花费；在频繁的、性能要求比较高的锁、释放日志空间、减少管理花费；在频繁的、性能要求比较高的数据操作中，尽量避免远程访问，如数据库链等，访问频繁的表可数据操作
7、中，尽量避免远程访问，如数据库链等，访问频繁的表可以常驻内存：以常驻内存：altertablecache；在在Oracle中动态执行中动态执行SQL，尽量用，尽量用execute方式，不用方式，不用dbms_sql包。包。sql语句的编写原则和优化语句的编写原则和优化n在编写在编写SQLSQL语句时我们应清楚优化器根语句时我们应清楚优化器根据何种原则来使用索引，这有助于写据何种原则来使用索引，这有助于写出高性能的出高性能的SQLSQL语句。语句。nSQLSQL语句的编写原则和语句的编写原则和SQLSQL语句的优化，语句的优化，请跟我一起学习以下几方面：请跟我一起学习以下几方面：避免复杂的多表关
8、联避免复杂的多表关联selectfromuser_filesuf,df_money_filesdm,cw_charge_recordccwhereuf.user_no=dm.user_noanddm.user_no=cc.user_noandandnotexists(select)?很难优化，随着数据量的增加性能的风很难优化，随着数据量的增加性能的风险很大。险很大。避免使用耗费资源的操作避免使用耗费资源的操作带有带有DISTINCT,UNION,MINUS,INTERSECT,ORDERBY的的SQL语句会启动语句会启动SQL引擎执行耗费资源的排序引擎执行耗费资源的排序(SORT)功能功能.D
9、ISTINCT需要一次排序操作需要一次排序操作,而其他的至少需要执行两次而其他的至少需要执行两次排序排序.例如例如,一个一个UNION查询查询,其中每个查询都带有其中每个查询都带有GROUPBY子句子句,GROUPBY会触发嵌入排序会触发嵌入排序(NESTEDSORT);这样这样,每个每个查询需要执行一次排序查询需要执行一次排序,然后在执行然后在执行UNION时时,又一个唯一又一个唯一排序排序(SORTUNIQUE)操作被执行而且它只能在前面的嵌入操作被执行而且它只能在前面的嵌入排序结束后才能开始执行排序结束后才能开始执行.嵌入的排序的深度会大大影响查嵌入的排序的深度会大大影响查询的效率询的效
10、率.通常通常,带有带有UNION,MINUS,INTERSECT的的SQL语句都可以语句都可以用其他方式重写用其他方式重写.例如例如:低效低效:SELECTDISTINCTDEPT_NO,DEPT_NAMEFROMDEPTD,EMPEWHERED.DEPT_NO=E.DEPT_NO高效高效:SELECTDEPT_NO,DEPT_NAMEFROMDEPTDWHEREEXISTS(SELECTXFROMEMPEWHEREE.DEPT_NO=D.DEPT_NO);用用EXISTS替换替换DISTINCT用用UNION-ALL替换替换UNION(ifpossible)当当SQL语句需要语句需要UNIO
11、N两个查询结果集合时两个查询结果集合时,这两个结果集合会以这两个结果集合会以UNION-ALL的方式被合并的方式被合并,然后在输出最终结果前进行排序然后在输出最终结果前进行排序.举例举例:低效：低效：SELECTACCT_NUM,BALANCE_AMTFROMDEBIT_TRANSACTIONSWHERETRAN_DATE=31-DEC-95UNIONSELECTACCT_NUM,BALANCE_AMTFROMDEBIT_TRANSACTIONSWHERETRAN_DATE=31-DEC-95高效高效:SELECTACCT_NUM,BALANCE_AMTFROMDEBIT_TRANSACTIO
12、NSWHERETRAN_DATE=31-DEC-95UNIONALLSELECTACCT_NUM,BALANCE_AMTFROMDEBIT_TRANSACTIONSWHERETRAN_DATE=31-DEC-952.给优化器更明确的命令给优化器更明确的命令自动选择索引自动选择索引如果表中有两个以上（包括两个）索引，其中有一个唯一性如果表中有两个以上（包括两个）索引，其中有一个唯一性索引，而其他是非唯一性索引，而其他是非唯一性在这种情况下，在这种情况下，ORACLE将使用唯一性索引而完全忽略非唯将使用唯一性索引而完全忽略非唯一性索引一性索引举例举例:SELECTENAMEFROMEMPWHERE
13、EMPNO=2326ANDDEPTNO=20;这里，只有这里，只有EMPNO上的索引是唯一性的，所以上的索引是唯一性的，所以EMPNO索索引将用来检索记录引将用来检索记录TABLEACCESSBYROWIDONEMPINDEXUNIQUESCANONEMP_NO_IDX至少要包含组合索引的第一列至少要包含组合索引的第一列如果索引是建立在多个列上如果索引是建立在多个列上,只有在它的第一个列只有在它的第一个列(leadingcolumn)被被where子句引用时子句引用时,优化器才会选择使用该索引优化器才会选择使用该索引.SQLcreatetablemultiindexusage(indanumb
14、er,indbnumber,descrvarchar2(10);Tablecreated.SQLcreateindexmultindexonmultiindexusage(inda,indb);Indexcreated.SQLsetautotracetraceonlySQLselect*frommultiindexusagewhereinda=1;ExecutionPlan-0SELECTSTATEMENTOptimizer=CHOOSE10TABLEACCESS(BYINDEXROWID)OFMULTIINDEXUSAGE21INDEX(RANGESCAN)OFMULTINDEX(NON-U
15、NIQUE)SQLselect*frommultiindexusagewhereindb=1;ExecutionPlan-0SELECTSTATEMENTOptimizer=CHOOSE10TABLEACCESS(FULL)OFMULTIINDEXUSAGE很明显很明显,当仅引用索引的第二个列时当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引优化器使用了全表扫描而忽略了索引避免在索引列上使用函数避免在索引列上使用函数WHERE子句中，如果索引列是函数的一部分优化器将不子句中，如果索引列是函数的一部分优化器将不使用索引而使用全表扫描使用索引而使用全表扫描举例举例:低效：低效：SELEC
16、TFROMDEPTWHERESAL*1225000;高效高效:SELECTFROMDEPTWHERESAL25000/12;避免使用前置通配符避免使用前置通配符WHERE子句中子句中,如果索引列所对应的值的第一个字符由通如果索引列所对应的值的第一个字符由通配符配符(WILDCARD)开始开始,索引将不被采用索引将不被采用.SELECTUSER_NO,USER_NAME,ADDRESSFROMUSER_FILESWHEREUSER_NOLIKE%109204421;在这种情况下，在这种情况下，ORACLE将使用全表扫描将使用全表扫描.避免在索引列上使用避免在索引列上使用NOT通常，我们要避免在索
17、引列上使用通常，我们要避免在索引列上使用NOT,NOT会产生在和在会产生在和在索引列上使用函数相同的影响索引列上使用函数相同的影响.当当ORACLE”遇到遇到”NOT,他就他就会停止使用索引转而执行全表扫描会停止使用索引转而执行全表扫描.举例举例:低效低效:(这里这里,不使用索引不使用索引)SELECTFROMDEPTWHEREDEPT_CODENOT=0;高效高效:(这里这里,使用了索引使用了索引)SELECTFROMDEPTWHEREDEPT_CODE0;避免在索引列上使用避免在索引列上使用ISNULL和和ISNOTNULL避免在索引中使用任何可以为空的列，避免在索引中使用任何可以为空的列
18、，ORACLE将无法使用该将无法使用该索引索引对于单列索引，如果列包含空值，索引中将不存在此记对于单列索引，如果列包含空值，索引中将不存在此记录录.对于复合索引，如果每个列都为空，索引中同样不存在此对于复合索引，如果每个列都为空，索引中同样不存在此记录记录.如果至少有一个列不为空，则记录存在于索引中如果至少有一个列不为空，则记录存在于索引中如果唯一性索引建立在表的如果唯一性索引建立在表的A列和列和B列上列上,并且表中存在一条记并且表中存在一条记录的录的A,B值为值为(123,null),ORACLE将不接受下一条具有相同将不接受下一条具有相同A,B值（值（123,null）的记录）的记录(插入
19、插入).然而如果所有的索引列都为然而如果所有的索引列都为空，空，ORACLE将认为整个键值为空而空不等于空将认为整个键值为空而空不等于空.因此你可以因此你可以插入插入1000条具有相同键值的记录条具有相同键值的记录,当然它们都是空当然它们都是空!因为空值不存在于索引列中因为空值不存在于索引列中,所以所以WHERE子句中对索引列进行子句中对索引列进行空值比较将使空值比较将使ORACLE停用该索引停用该索引.任何在任何在where子句中使用子句中使用isnull或或isnotnull的语句优化器是的语句优化器是不允许使用索引的。不允许使用索引的。避免出现索引列自动转换避免出现索引列自动转换当比较不
20、同数据类型的数据时当比较不同数据类型的数据时,ORACLE自动对列进行简单自动对列进行简单的类型转换的类型转换.假设假设USER_NO是一个字符类型的索引列是一个字符类型的索引列.SELECTUSER_NO,USER_NAME,ADDRESSFROMUSER_FILESWHEREUSER_NO=109204421这个语句被这个语句被ORACLE转换为转换为:SELECTUSER_NO,USER_NAME,ADDRESSFROMUSER_FILESWHERETO_NUMBER(USER_NO)=109204421因为内部发生的类型转换因为内部发生的类型转换,这个索引将不会被用到这个索引将不会被用
21、到!在查询时尽量少用格式转换在查询时尽量少用格式转换n如用如用WHEREa.order_no=b.order_non不用不用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语句语句,估算索引的利用率估算索引的利用率,绑定绑定变量变量,读数据块等等读数据块等等.
22、由此可见由此可见,减少访问数据库的次数减少访问数据库的次数,就能就能实际上减少实际上减少ORACLE的工作量的工作量.类比，工程实施类比，工程实施使用使用DECODE来减少处理时间来减少处理时间例如例如:SELECTCOUNT(*)，SUM(SAL)FROMEMPWHEREDEPT_NO=0020ANDENAMELIKESMITH%;SELECTCOUNT(*)，SUM(SAL)FROMEMPWHEREDEPT_NO=0030ANDENAMELIKESMITH%;你可以用你可以用DECODE函数高效地得到相同结果函数高效地得到相同结果SELECTCOUNT(DECODE(DEPT_NO,002
23、0,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_SALFROMEMPWHEREENAMELIKESMITH%;减少对表的查询减少对表的查询在含有子查询的在含有子查询的SQL语句中语句中,要特别注意减少对表的查询要特别注意减少对表的查询.例如例如:低效低效SELECTTAB_NAMEFROMTABLESWHERETAB_NAME=(SELECTTAB_
24、NAMEFROMTAB_COLUMNSWHEREVERSION=604)ANDDB_VER=(SELECTDB_VERFROMTAB_COLUMNSWHEREVERSION=604)高效高效SELECTTAB_NAMEFROMTABLESWHERE(TAB_NAME,DB_VER)=(SELECTTAB_NAME,DB_VER)FROMTAB_COLUMNSWHEREVERSION=604)4.细节上的影响细节上的影响WHERE子句中的连接顺序子句中的连接顺序ORACLE采用自下而上的顺序解析采用自下而上的顺序解析WHERE子句子句,根据这个原根据这个原理理,当在当在WHERE子句中有多个表联
25、接时，子句中有多个表联接时，WHERE子句中排子句中排在最后的表应当是返回行数可能最少的表，有过滤条件的子在最后的表应当是返回行数可能最少的表，有过滤条件的子句应放在句应放在WHERE子句中的最后。子句中的最后。n如：设从如：设从emp表查到的数据比较少或该表的过滤条件比较确定，能大表查到的数据比较少或该表的过滤条件比较确定，能大大缩小查询范围，则将最具有选择性部分放在大缩小查询范围，则将最具有选择性部分放在WHERE子句中的最后：子句中的最后：select*fromempe,deptdwhered.deptno10ande.deptno=30;n如果如果dept表返回的记录数较多的话，上面的
26、查询语句会比下面的查询表返回的记录数较多的话，上面的查询语句会比下面的查询语句响应快得多。语句响应快得多。select*fromempe,deptdwheree.deptno=30andd.deptno10;WHERE子句子句函数、表达式使用函数、表达式使用n最好不要在最好不要在WHERE子句中使用函或表子句中使用函或表达式，如果要使用的话，最好统一使达式，如果要使用的话，最好统一使用相同的表达式或函数，这样便于以用相同的表达式或函数，这样便于以后使用合理的索引。后使用合理的索引。Orderby语句语句nORDERBY语句决定了语句决定了Oracle如何将返回的查询如何将返回的查询结果排序。结
27、果排序。Orderby语句对要排序的列没有什么语句对要排序的列没有什么特别的限制，也可以将函数加入列中（象联接或特别的限制，也可以将函数加入列中（象联接或者附加等）。者附加等）。任何在任何在Orderby语句的非索引项或语句的非索引项或者有计算表达式都将降低查询速度。者有计算表达式都将降低查询速度。n仔细检查仔细检查orderby语句以找出非索引项或者表达语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是式，它们会降低性能。解决这个问题的办法就是重写重写orderby语句以使用索引，也可以为所使用语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在的列建立另外
28、一个索引，同时应绝对避免在orderby子句中使用表达式。子句中使用表达式。联接列联接列对于有联接的列，即使最后的联接值为一个静态值，优化器对于有联接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的。是不会使用索引的。select*fromemploysswherefirst_name|last_name=BeillCliton;系统优化器对基于系统优化器对基于last_name创建的索引没有使用。创建的索引没有使用。当采用下面这种当采用下面这种SQL语句的编写，语句的编写，Oracle系统就可以采用基系统就可以采用基于于last_name创建的索引。创建的索引。select*fro
29、memployeewherefirst_name=Beillandlast_name=Cliton;带通配符（带通配符（%）的）的like语句语句通配符（通配符（%）在搜寻词首出现，）在搜寻词首出现，Oracle系统不使用系统不使用last_name的索引。的索引。select*fromemployeewherelast_namelike%cliton%;在很多情况下可能无法避免这种情况，但是一定要心中有底在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而当通配符出现在字，通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引
30、。在下面的查询中索符串其他位置时，优化器就能利用索引。在下面的查询中索引得到了使用：引得到了使用：select*fromemployeewherelast_namelikec%;用用Where子句替换子句替换HAVING子句子句避免使用避免使用HAVING子句子句,HAVING只会在检索出所有记录之后才对结果只会在检索出所有记录之后才对结果集进行过滤集进行过滤.这个处理需要排序这个处理需要排序,总计等操作总计等操作.如果能通过如果能通过WHERE子句限子句限制记录的数目制记录的数目,那就能减少这方面的开销那就能减少这方面的开销.例如例如:低效低效:SELECTREGION，AVG(LOG_SI
31、ZE)FROMLOCATIONGROUPBYREGIONHAVINGREGIONREGION!=SYDNEYANDREGION!=PERTH高效高效SELECTREGION，AVG(LOG_SIZE)FROMLOCATIONWHEREREGIONREGION!=SYDNEYANDREGION!=PERTHGROUPBYREGION顺序顺序WHEREGROUPHAVING用用NOTEXISTS替代替代NOTIN在子查询中在子查询中,NOTIN子句将执行一个内部的排序和合并子句将执行一个内部的排序和合并.无论在哪种情况无论在哪种情况下下,NOTIN都是最低效的都是最低效的(因为它对子查询中的表执行
32、了一个全表遍历因为它对子查询中的表执行了一个全表遍历).使用使用NOTEXISTS子句可以有效地利用索引。尽可能使用子句可以有效地利用索引。尽可能使用NOTEXISTS来代替来代替NOTIN，尽管二者都使用了，尽管二者都使用了NOT（不能使用索引而降低速度），（不能使用索引而降低速度），NOTEXISTS要比要比NOTIN查询效率更高。查询效率更高。例如例如:语句语句1SELECTdname,deptnoFROMdeptWHEREdeptnoNOTIN(SELECTdeptnoFROMemp);语句语句2SELECTdname,deptnoFROMdeptWHERENOTEXISTS(SELE
33、CTdeptnoFROMempWHEREdept.deptno=emp.deptno);2 2要比要比1 1的执行性能好很多。的执行性能好很多。因为因为1 1中对中对empemp进行了进行了full table scan,full table scan,这是很浪费时间的操作。而且这是很浪费时间的操作。而且1 1中没中没有用到有用到empemp的的indexindex，因为没有因为没有wherewhere子句。而子句。而2 2中的语句对中的语句对empemp进行的是缩进行的是缩小范围的查询。小范围的查询。用索引提高效率用索引提高效率索引是表的一个概念部分索引是表的一个概念部分,用来提高检索数据的
34、效率，用来提高检索数据的效率，ORACLE使使用了一个复杂的自平衡用了一个复杂的自平衡B-tree结构结构.通常通常,通过索引查询数据比全表通过索引查询数据比全表扫描要快扫描要快.当当ORACLE找出执行查询和找出执行查询和Update语句的最佳路径时语句的最佳路径时,ORACLE优化器将使用索引优化器将使用索引.同样在联结多个表时使用索引也可以同样在联结多个表时使用索引也可以提高效率提高效率.另一个使用索引的好处是另一个使用索引的好处是,它提供了主键它提供了主键(primarykey)的唯一性验证。的唯一性验证。通常通常,在大型表中使用索引特别有效在大型表中使用索引特别有效.当然当然,你也会
35、发现你也会发现,在扫描小在扫描小表时表时,使用索引同样能提高效率使用索引同样能提高效率.虽然使用索引能得到查询效率的提虽然使用索引能得到查询效率的提高高,但是我们也必须注意到它的代价但是我们也必须注意到它的代价.索引需要空间来存储索引需要空间来存储,也需要也需要定期维护定期维护,每当有记录在表中增减或索引列被修改时每当有记录在表中增减或索引列被修改时,索引本身也索引本身也会被修改会被修改.这意味着每条记录的这意味着每条记录的INSERT,DELETE,UPDATE将为将为此多付出此多付出4,5次的磁盘次的磁盘I/O.因为索引需要额外的存储空间和处理因为索引需要额外的存储空间和处理,那些不必要的
36、索引反而会使查询反应时间变慢那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引。定期的重构索引是有必要的。是有必要的。避免在索引列上使用计算避免在索引列上使用计算WHERE子句中，如果索引列是函数的一部分优子句中，如果索引列是函数的一部分优化器将不化器将不使用索引而使用全表扫描使用索引而使用全表扫描低效：低效：SELECTFROMDEPTWHERESAL*1225000;高效高效:SELECTFROMDEPTWHERESAL25000/12;用用=替代替代如果如果DEPTNO上有一个索引。上有一个索引。高效高效:SELECT*FROMEMPWHEREDEPTNO=4低效低效:SELEC
37、T*FROMEMPWHEREDEPTNO3通过使用通过使用=、=等，避免使用等，避免使用NOT命令命令n例子：例子：select*fromemployeewheresalary3000;n对这个查询，可以改写为不使用对这个查询，可以改写为不使用NOT：select*fromemployeewheresalary3000;虽然这两种查询的结果一样，但是第二种查询方案会比第虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许一种查询方案更快些。第二种查询允许Oracle对对salary列列使用索引，而第一种查询则不能使用索引。使用索引，而第一种查询则不能使用索引。如
38、果有其它办法，不要使用子查如果有其它办法，不要使用子查询。询。用用TRUNCATE替代替代DELETE当删除表中的记录时当删除表中的记录时,在通常情况下在通常情况下,回滚段回滚段(rollbacksegments)用来存放可以被恢复的信息用来存放可以被恢复的信息.如果你没如果你没有有COMMIT事务事务,ORACLE会将数据恢复到删除之前的会将数据恢复到删除之前的状态状态(准准确地说是恢复到执行删除命令之前的状况确地说是恢复到执行删除命令之前的状况)而当运用而当运用TRUNCATE时时,回滚段不再存放任何可回滚段不再存放任何可被恢复的被恢复的信息信息.当命令运行后当命令运行后,数据不能被恢复数
39、据不能被恢复.因此很少的资因此很少的资源被调用源被调用,执行时间也会很短执行时间也会很短.比如有的表比如有的表PHONE_NO字段是字段是CHAR型型,而且创建有索引，而且创建有索引，但在但在WHERE条件中忘记了加引号，就条件中忘记了加引号，就不会用到索引。不会用到索引。WHEREPHONE_NO=13920202022WHEREPHONE_NO=13920202022字符型字段的引号字符型字段的引号优化优化EXPORT和和IMPORT使用较大的使用较大的BUFFER(比如比如10MB,10,240,000)可以提高可以提高EXPORT和和IMPORT的速度的速度;ORACLE将尽可能地获取
40、你所指定的内将尽可能地获取你所指定的内存大小存大小,即使在内存即使在内存不满足不满足,也不会报错也不会报错.这个值至少要和表中这个值至少要和表中最大的列相当最大的列相当,否则否则列值会被截断列值会被截断;*优化优化Tools*优化器与执行计划优化器与执行计划Oracle在执行一个在执行一个SQL之前之前,首先要分析一下语句的执行计首先要分析一下语句的执行计划划,然后再按执行计划去执行。分析语句的执行计划的工作然后再按执行计划去执行。分析语句的执行计划的工作是由优化器是由优化器(Optimizer)来完成的来完成的Oracle的优化器共有两种的优化方式的优化器共有两种的优化方式,即即基于规则的优
41、化方基于规则的优化方式式(Rule-BasedOptimization,简称为简称为RBO)和和基于代价的优基于代价的优化方式化方式(Cost-BasedOptimization,简称为简称为CBO)。A、RBO方式：优化器在分析方式：优化器在分析SQL语句时语句时,所遵循的是所遵循的是Oracle内部预定内部预定的一些规则。比如我们常见的的一些规则。比如我们常见的,当一个当一个where子句中的一列有索引时去走子句中的一列有索引时去走索引。索引。B、CBO方式：是看语句的代价方式：是看语句的代价(Cost)了了,这里的代价主要指这里的代价主要指Cpu和内存和内存。优化器在判断是否用这种方式时
42、。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息，主要参照的是表及索引的统计信息，很多的时侯过期统计信息会令优化器做出一个错误的执行计划在很多的时侯过期统计信息会令优化器做出一个错误的执行计划在Oracle8及以后的版本及以后的版本,Oracle推荐用推荐用CBO的方式。的方式。在在Oracle10g中中,取消了取消了RBO的支持。的支持。优化器与执行计划优化器与执行计划Rule:即走基于规则的方式即走基于规则的方式Choose:默认的情况下默认的情况下Oracle用的便是这种方式。当一个表或或索引有用的便是这种方式。当一个表或或索引有统计信息统计信息,则走则走CBO的方式的方式
43、,如果表或索引没统计信息如果表或索引没统计信息,表又不是特别的小表又不是特别的小,而且相应的列有索引时而且相应的列有索引时,那么就走索引那么就走索引,走走RBO的方式的方式FirstRows:它与它与Choose方式是类似的方式是类似的,所不同的是当一个表有统计信息所不同的是当一个表有统计信息时时,它将是以最快的方式返回查询的最先的几行它将是以最快的方式返回查询的最先的几行,从总体上减少了响应时从总体上减少了响应时间间AllRows:all_rows是是oracle优化器默认的模式优化器默认的模式,它将选择一种在最短时它将选择一种在最短时间内返回所有数据的执行计划间内返回所有数据的执行计划,它
44、将基于整体成本的考虑它将基于整体成本的考虑.first_rows_n:first_rows_n是根据成本而不是基于硬编码的规则来选择是根据成本而不是基于硬编码的规则来选择执行计划执行计划.n可以是可以是1,10,100,1000或者直接用或者直接用first_rows(n)hint指定任意指定任意正数正数.这里的这里的n是我们想获取结果集的前是我们想获取结果集的前n条记录条记录,这种需求在很多分页语这种需求在很多分页语句的需求中会碰到句的需求中会碰到.Autotrace解读解读Currentmode:对于修改的数据从数据段中读对于修改的数据从数据段中读Read-consistentmode:读
45、一致性模式读一致性模式Physicalblock:物理块（如物理块（如8192字节）字节）Recursivecalls:嵌套调用次数嵌套调用次数在在SQLPLUS配置配置AUTOTRACE在在SQLPLUS配置配置AUTOTRACE1、首先创建首先创建PLUSTRACE角色并且赋给角色并且赋给public：Sql$ORACLE_HOME/sqlplus/admin/plustrce.sql2、赋权限给用户赋权限给用户Sqlgrantplustracetopublic（预赋权的用户名）（预赋权的用户名）;3、以、以SYSTEM用户创建用户创建PLAN_TABLE表表Sql$ORACLE_HOME
46、/rdbms/admin/utlxplan.sqlSqlcreatepublicsynonymplan_tableforplan_table;Sqlgrantallonplan_tabletopublic;在每个用户下设置在每个用户下设置AUTOTRACE可显示其执行计划。可显示其执行计划。SQLselectename,dnamefromemp,deptwhereemp.deptno=dept.deptnoanddept.dnamein(ACCOUNTING,RESEARCH,SALES,OPERATIONS);ExecutionPlan-0SELECTSTATEMENTOptimizer=C
47、HOOSE10NESTEDLOOPS21TABLEACCESS(FULL)OFEMP31TABLEACCESS(BYINDEXROWID)OFDEPT43INDEX(UNIQUESCAN)OFPK_DEPT(UNIQUE)最起码要解决全表扫描问题最起码要解决全表扫描问题改变改变where条件的次序一般没有用条件的次序一般没有用优化器与执行计划优化器与执行计划SQL调整的目标调整的目标1.1.去掉不必要的大型表的全表扫描。去掉不必要的大型表的全表扫描。2.2.缓存小型表的全表扫描。缓存小型表的全表扫描。3.3.校验优化索引的使用。校验优化索引的使用。4.4.检验优化的连接技术。检验优化的连接技术
48、。以上目标任务将占据以上目标任务将占据SQLSQL调整调整90%90%以上以上的工作。的工作。n理论上来说比理论上来说比NL与与SMJ更高效，而且只用在更高效，而且只用在CBO优化器中。优化器中。n较小的较小的rowsource被用来构建被用来构建hashtable与与bitmap，第，第2个个rowsource被用来被被用来被hansed，并与第一个，并与第一个rowsource生成的生成的hashtable进行匹配，以便进行进一步的连接。进行匹配，以便进行进一步的连接。Bitmap被用来作为一种比较快的被用来作为一种比较快的查找方法，来检查在查找方法，来检查在hashtable中是否有匹配
49、的行。特别的，当中是否有匹配的行。特别的，当hashtable比较大而不能全部容纳在内存中时，这种查找方法更为有用。这比较大而不能全部容纳在内存中时，这种查找方法更为有用。这种连接方法也有种连接方法也有NL连接中所谓的驱动表的概念，被构建为连接中所谓的驱动表的概念，被构建为hashtable与与bitmap的表为驱动表，当被构建的的表为驱动表，当被构建的hashtable与与bitmap能被容纳在能被容纳在内存中时，这种连接方式的效率极高。内存中时，这种连接方式的效率极高。n要使哈希连接有效，需要设置要使哈希连接有效，需要设置HASH_JOIN_ENABLED=TRUEHASH_JOIN_EN
50、ABLED=TRUE，缺省情况下，缺省情况下该参数为该参数为TRUETRUE，另外，不要忘了还要设置，另外，不要忘了还要设置hash_area_sizehash_area_size参数，以使参数，以使哈希连接高效运行，因为哈希连接会在该参数指定大小的内存中运行，哈希连接高效运行，因为哈希连接会在该参数指定大小的内存中运行，过小的参数会使哈希连接的性能比其他连接方式还要低。过小的参数会使哈希连接的性能比其他连接方式还要低。哈希连接哈希连接 (HashJoin,HJ)HASH连接的例子：连接的例子：SQL explain plan for select /*+use_hash(emp)*/empn