ORACLESQL性能优化(这个很全的)

1、ORACLE培训-SQL性能优化内容概述述课程主要要讨论：SQL语句执行行的过程程、ORACLE优化器，表之间间的关联联，如何何得到SQL执行计划划，如何何分析执执行计划等等内容，从而由由浅到深深的方式式了解SQL优化的过过程，使大大家逐步步掌握SQL优化。目录优化基础础知识性能调整整综述有效的应应用设计计SQL语句的处处理过程程Oracle的优化器器Oracle的执行计计划注意事项项一、优化化基础知知识概述性能管理理性能问题题调整的方方法SQL优化机制制应用的调调整SQL语句的处处理过程程共享SQL区域SQL语句处理理的阶段段共享游标标SQL编码标准准Oracle优化器介介绍SQLTunni

2、ngTips优化Tools性能管理理尽早开始始设立合适适目标边调整边边监控相关人员员进行合合作及时处理理过程中中发生的的意外和和变化80/20定律SQL优化衡量量指标随着软件件技术的的不断发发展，系系统性能能越来越越重要。系统性能能主要用用：系统响应应时间和和并发性性来衡量量。造成SQL语句性能能不佳大大致有两两个原因因：开发人员员只关注注查询结结果的正正确性，忽视查查询语句句的效率率。开发人员员只关注注SQL语句本身身的效率率，对SQL语句执行行原理、影响SQL执行效率率的主要要因素不不清楚。*前者可以以通过深深入学习习SQL语法及各各种SQL调优技巧巧进行解解决。SQL调优是一一个系统统工

3、程，熟悉SQL语法、掌掌握各种种内嵌函函数、分分析函数的的用法只只是编写写高效SQL的必要条条件。*后者从分分析SQL语句执行行原理入入手，指指出SQL调优应在在优化SQL解析和优化化CBO上。调优领域域调优领域域：应用程序序级调优优：* SQL语句调优优* 管理理变化调调优2.实例级调调优* 内存存* 数据据结构* 实例例配置3.操作系统统交互*I/O* SWAP* Parameters本课程内内容只讲讲解讨论论应用程程序级：OracleSQL语句调优优及管理理变化调调优调整的方方法调整业务务功能调整数据据设计调整流程程设计调整SQL语句句调整物理理结构调整内存存分配调整I/O调整内存存竞争

4、调整操作作系统不同调整整产生相相应性能能收益调整的角角色SQL语句优化化是提高高性能的的重要环环节开发人员员不能只只注重功功能的实实现，不不管性能能如何开发人员员不能把把Oracle当成一个个黑盒子子，必须须了解其其结构、处理SQL和数据的的方法必需遵守守既定的的开发规规范未经过SQL语句优化化的模块块不要上上线SQL语句优化化的过程程定位有问问题的语语句检查执行行计划检查执行行过程中中优化器器的统计计信息分析相关关表的记记录数、索引情情况改写SQL语句、使使用HINT、调整索索引、表表分析有些SQL语句不具具备优化化的可能能，需要要优化处处理方式式达到最佳佳执行计计划什么是好好的SQL语句？

5、尽量简单单，模块块化易读、易易维护节省资源源内存CPU扫描的数数据块要要少少排序不造成死死锁SQL共享原理理ORACLE将执行过过的SQL语句存放放在内存存的共享享池(sharedbufferpool)中，可以以被所有有的数据据库用户户共享。当你执行行一个SQL语句(有时被称称为一个个游标)时,如果它和和之前的的执行过过的语句句完全相相同,ORACLE就能很快快获得已已经被解解析的语语句以及及最好的的 执行行路径.这个功能能大大地地提高了了SQL的执行性性能并节节省了内内存的使使用。SQL共享原理理为了不重重复解析析相同的的SQL语句，在在第一次次解析之之后，Oracle将SQL语句存放放在内

6、存存中。这这块位于于系统全全局区域域SGA(systemglobal area)的共享池池(shared bufferpoo1)中的内存可以以被所有有的数据据库用户户共享。因此，当你执执行一个个SQL语句(有时被称称为一个个游标)时，如果果它和之之前执行行过的语语句完全全相同，Oracle就能很快快获得已已经被解解析的语语句以及及最好的的执行方案。Oracle的这个功功能大大大地提高高了SQL的执行性性能并节节省了内存的的使用。可惜的是是，Oracle只对简单单的表提提供高速速缓冲(cachebufferiIlg)，这个功功能并不不适用于于多表连连接查询询。数据据库管理员员必须在在启动参参数文

7、件件中为这这个区域域设置合合适的参参数，当这个内内存区域域越大，就可以以保留更更多的语语句，当当然被共共享的可能性也也就越大大了。当当向Oracle提交一个个SQL语句时，Oracle会首先在在这块内内存中查查找相同同的语句句。SQL共享的三三个条件件当前被执执行的语语句和共共享池中中的语句句必须完完全相同同 （包包括大小小写、空空格、换换行等）两个语句句所指的的对象必必须完全全相同（同义义词与表表是不同同的对象象）两个SQL语句中必必须使用用相同的的名字的的绑定变变量(bindvariables)共享SQL语句注意：Oracle对两者采采取的是是一种严严格匹配配策略，要达成成共享。SQL语句

8、必须完全相相同(包括空格格、换行行等)。能够使使用共享享的语句句必须满满足三个个条件： 字符符级的比比较。当前被执执行的语语句和共共享池中中的语句句必须完完全相同同。例如：SELECT*FROM ATABLE；和下面面每一个个SQL语句都不不同：SELECT*from ATABLESelect*FromAtable； 语句句所指对对象必须须完全相相同即即两条SQL语句操作作的数据据库对象象必须同同一。语句中中必须使使用相同同命名的的绑定变变量。如如：第一一组的两两个SQL语句是相相同的，可以以共享；而第二二组中两两个语句句不同，即使在在运行时时赋予不不同的绑绑定变量以相同同的值： 第一一组se

9、lectpin，name frompeople where pin=：blk1.pin；selectpin，name frompeople where pin=：blk1.pin；第二组组selectpin，name frompeople where pin=：blk1.ot_jnd；selectpin，name frompeople where pin=：blk1.ov_jnd；SQL语句的处处理过程程共享SQL区域Sql处理过程程SQLPARSE与共享SQL语句当一个Oracle实实例接收收一条sql后后1、CreateaCursor创建游标标2、ParsetheStatement分析语句

10、句3、DescribeResults of aQuery描述查询询的结果果集4、DefineOutput of aQuery定义查询询的输出出数据5、Bind AnyVariables绑定变量量6、ParallelizetheStatement并行执行行语句7、Runthe Statement运行语句句8、FetchRows of aQuery取查询出出来的行行9、ClosetheCursor关闭游标标为什么要要bind variables?字符级的的比较:SELECT*FROM USER_FILESWHEREUSER_NO =10001234;与SELECT*FROM USER_FILESW

11、HEREUSER_NO =10004321;检查：selectname,executionsfrom v$db_object_cachewherename likeselect* fromuser_files%什么叫做做重编译译问题什么叫做做重编译译？下面这个个语句每每执行一一次就需需要在SHAREPOOL硬解析一一次，一百百万用户户就是一一百万次次，消耗耗CPU和内存，如果业业务量大，很很可能导导致宕库库如果绑定定变量，则只需需要硬解解析一次次，重复复调用即即可select*from dConMsgwherecontract_no= 32013484095139绑定变量量解决重重编译问问题未

12、使用绑绑定变量量的语句句sprintf(sqlstr,insertinto scott.test1 (num1,num2)values(%d,%d),n_var1, n_var2);EXEC SQLEXECUTE IMMEDIATE:sqlstr ;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 S

13、QLCOMMIT;绑定变量量的注意意事项注意：1、不要使使用数据据库级的的变量绑绑定参数数cursor_sharing来强制绑定，无论其其值为force还是similar2、有些带带 0性能优于于selectcount(*)from tab；尽量少嵌嵌套子查查询，这这种查询询会消耗耗大量的的CPU资源；对对于有比比较多or运算的查查询，建建议分成成多个查查询，用用unionall联结起来来；多表表查询的查询语语句中，选择最最有效率率的表名名顺序。Oracle解析器对对表解析析从右到左，所以记记录少的的表放在在右边。 尽量量多用commit语句提交交事务，可以及及时释放放资源、解锁、释放放日志空

14、空间、减减少管理理花费；在频繁繁的、性性能要求求比较高高的数据操作作中，尽尽量避免免远程访访问，如如数据库库链等，访问频频繁的表表可以常驻内内存：altertablecache； 在Oracle中动态执执行SQL，尽量用用execute方式，不不用dbms_sql包。*SQLTunningTips*sql语句的编编写原则则和优化化随着数据据库中数数据的增增加，系系统的响响应速度度就成为为目前系系统需要要解决的的最主要要的问题题之一。系统优优化中一一个很重重要的方方面就是是SQL语句的优优化。对对于大量量数据，劣质SQL语句和优优质SQL语句之间间的速度度差别可可以达到到上百倍倍，对于于一个系系

15、统不是是简单地地能实现现其功能能就可，而是要要写出高高质量的的SQL语句，提提高系统统的可用用性。在多数情情况下，Oracle使用索引引来更快快地遍历历表，优优化器主主要根据据定义的的索引来来提高性性能。如如果在SQL语句的where子句中写写的SQL代码不合合理，就就会造成成优化器器删去索索引而使使用全表表扫描，一般就就这种SQL语句就是是所谓的的劣质SQL语句。sql语句的编编写原则则和优化化在编写SQL语句时我我们应清清楚优化化器根据据何种原原则来使使用索引引，这有有助于写写出高性性能的SQL语句。SQL语句的编编写原则则和SQL语句的优优化，请请跟我一一起学习习以下几几方面：Tunni

16、ng Tip的各个方方面1.不要让Oracle做得太多多；2.给优化器器更明确确的命令令； 3.减少访问问次数；4.细节上的的影响；1.不要让Oracle做得太多多避免复杂杂的多表表关联selectfrom user_filesuf,df_money_filesdm,cw_charge_record ccwhereuf.user_no =dm.user_noanddm.user_no= cc.user_noandandnot exists(select)?很难优化化，随着着数据量量的增加加性能的的风险很很大。避免使用用 *当你想在在SELECT子句中列列出所有有的COLUMN时,使用动态态SQ

17、L列引用* 是一一个方便便的方法法.不幸的是是,这是一个个非常低低效的方法法.实际上,ORACLE在解析的的过程中中,会将*依依次转换成所有有的列名名,这个工作作是通过过查询数数据字典典完成的的,这意味着将耗耗费更多多的时间间；只提取你你所要使使用的列列；使用别名名能够加加快解析析速度；避免使用用耗费资资源的操操作带有DISTINCT,UNION,MINUS,INTERSECT,ORDERBY的SQL语句会启启动SQL引擎执行行耗费资资源的排排序(SORT)功能.DISTINCT需要一次次排序操操作,而其他的的至少需需要执行行两次排序.例如,一个UNION查询,其中每个个查询都都带有GROUP

18、BY子句, GROUP BY会触发嵌嵌入排序序(NESTED SORT) ;这样,每个查询需要要执行一一次排序序,然后在执执行UNION时,又一个唯唯一排序(SORTUNIQUE)操作被执执行而且且它只能能在前面面的嵌入入排序结束束后才能能开始执执行.嵌入的排排序的深深度会大大大影响响查询的效率率.通常,带有UNION,MINUS,INTERSECT的SQL语句都可可以用其他方方式重写写.用EXISTS替换DISTINCT例如:低效:SELECTDISTINCTDEPT_NO,DEPT_NAMEFROM DEPTD,EMPEWHERED.DEPT_NO=E.DEPT_NO高效:SELECTDE

19、PT_NO,DEPT_NAMEFROM DEPTDWHEREEXISTS(SELECTXFROM EMPEWHEREE.DEPT_NO=D.DEPT_NO);用UNION-ALL替换UNION( if possible)当SQL语句需要要UNION两个查询询结果集集合时,这两个结结果集合合会以UNION-ALL的方式被被合并,然后在输输出最终终结果前前进行排排序.举例:低效：SELECTACCT_NUM, BALANCE_AMTFROM DEBIT_TRANSACTIONSWHERETRAN_DATE=31-DEC-95UNIONSELECTACCT_NUM, BALANCE_AMTFROM

20、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.给优化器器更明确确的命令令自动选择择索引如果表中中有两个个以上（包括两两个）索索引，其其中有一一个唯一一性索引，而而其他是是非唯一一性在这种情情况下，ORACLE将使用唯唯一性索索引而完完全忽略

21、略非唯一性索引引举例:SELECTENAMEFROM EMPWHEREEMPNO= 2326ANDDEPTNO= 20 ;这里，只只有EMPNO上的索引引是唯一一性的，所以EMPNO索引将用来来检索记记录TABLEACCESSBYROWIDONEMPINDEXUNIQUESCANONEMP_NO_IDX至少要包包含组合合索引的的第一列列如果索引引是建立立在多个个列上,只有在它它的第一一个列(leadingcolumn)被where子句引用用时,优化器才才会选择择使用该该索引.SQL createtablemultiindexusage(inda number, indbnumber ,desc

22、rvarchar2(10);Tablecreated.SQL createindexmultindexonmultiindexusage(inda,indb);Indexcreated.SQL setautotracetraceonlySQLselect*frommultiindexusagewhereinda= 1;ExecutionPlan-0SELECT STATEMENTOptimizer=CHOOSE10TABLEACCESS(BY INDEX ROWID)OFMULTIINDEXUSAGE21INDEX (RANGESCAN)OFMULTINDEX(NON-UNIQUE)SQL s

23、elect* frommultiindexusagewhereindb =1;ExecutionPlan-0SELECT STATEMENTOptimizer=CHOOSE10TABLEACCESS(FULL) OF MULTIINDEXUSAGE 很明显,当仅引用用索引的的第二个个列时,优化器使使用了全全表扫描描而忽略略了索引引避免在索索引列上上使用函函数WHERE子句中，如果索索引列是是函数的的一部分分优化化器将不不使用索引引而使用用全表扫扫描举例:低效：SELECTFROM DEPTWHERESAL*12 25000;高效:SELECTFROM DEPTWHERESAL25000/12;

24、避免使用用前置通通配符WHERE子句中,如果索引引列所对对应的值值的第一一个字符符由通配符(WILDCARD)开始,索引将不不被采用用. SELECTUSER_NO,USER_NAME,ADDRESSFROM USER_FILESWHEREUSER_NO LIKE%109204421;在这种情情况下，ORACLE将使用全全表扫描描.避免在索索引列上上使用NOT通常，我我们要避避免在索索引列上上使用NOT, NOT会产生在在和在索引列上上使用函函数相同同的影响响.当ORACLE”遇到”NOT,他就会停止使使用索引引转而执执行全表表扫描.举例:低效: (这里,不使用索索引)SELECTFROM D

25、EPTWHEREDEPT_CODENOT =0; 高效: (这里,使用了索索引)SELECTFROM DEPTWHEREDEPT_CODE0;避免在索索引列上上使用ISNULL和ISNOTNULL避免在索索引中使使用任何何可以为为空的列列，ORACLE将无法使使用该索引对于单单列索引引，如果果列包含含空值，索引中中将不存存在此记记录.对于复合合索引，如果每每个列都都为空，索引中中同样不不存在此此记录.如果至少少有一个个列不为为空，则则记录存存在于索索引中如果唯一一性索引引建立在在表的A列和B列上,并且表中中存在一一条记录的A,B值为(123,null) ,ORACLE将不接受受下一条条具有相相

26、同A,B值（123,null）的记录录(插入).然而如果果所有的的索引列列都为空，ORACLE将认为整整个键值值为空而而空不等等于空.因此你可可以插入1000条具有相相同键值值的记录录,当然它们们都是空空!因为空值值不存在在于索引引列中,所以WHERE子句中对对索引列列进行空值比较较将使ORACLE停用该索索引.任何在where子句中使使用isnull或isnotnull的语句优优化器是是不允许使使用索引引的。避免出现现索引列列自动转转换当比较不不同数据据类型的的数据时时, ORACLE自动对列列进行简简单的类型转转换.假设EMP_TYPE是一个字字符类型型的索引引列.SELECTUSER_N

27、O,USER_NAME,ADDRESSFROM USER_FILESWHEREUSER_NO =109204421这个语句句被ORACLE转换为:SELECTUSER_NO,USER_NAME,ADDRESSFROM USER_FILESWHERETO_NUMBER(USER_NO)=109204421因为内部部发生的的类型转转换,这个索引引将不会会被用到到!在查询时时尽量少少用格式式转换如用WHEREa.order_no =b.order_no不用 WHERETO_NUMBER(substr(a.order_no,instr(b.order_no,.) -1)= TO_NUMBER(subs

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

29、EPT_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_NO,0030,SAL,NULL) D0030_SALFROM EMPWHEREENAMELIKE SMITH%;减少对表表的查询询在含有子子查询的的SQL语句中,要特别注注

30、意减少少对表的的查询.例如:低效SELECTTAB_NAMEFROM TABLESWHERETAB_NAME= (SELECT TAB_NAMEFROM TAB_COLUMNSWHEREVERSION =604)ANDDB_VER= (SELECT DB_VERFROM TAB_COLUMNSWHEREVERSION =604)高效SELECTTAB_NAMEFROM TABLESWHERE(TAB_NAME,DB_VER)= (SELECT TAB_NAME,DB_VER)FROM TAB_COLUMNSWHEREVERSION =604)4.细节上的的影响WHERE子句中的的连接顺顺序OR

31、ACLE采用自下下而上的的顺序解解析WHERE子句,根据这个个原理,当在WHERE子句中有有多个表表联接时时，WHERE子句中排排在最后的的表应当当是返回回行数可可能最少少的表，有过滤滤条件的的子句应放在在WHERE子句中的的最后。如：设从从emp表查到的的数据比比较少或或该表的的过滤条条件比较较确定，能大大大缩小查查询范围围，则将将最具有有选择性性部分放放在WHERE子句中的的最后：select*from empe,deptdwhered.deptno10ande.deptno=30;如果dept表返回的的记录数数较多的的话，上上面的查查询语句句会比下下面的查查询语句句响应快快得多。sele

32、ct*from empe,deptdwheree.deptno=30andd.deptno10;WHERE子句函数、表表达式使使用最好不要要在WHERE子句中中使用函函或表达达式，如如果要使使用的话话，最好好统一使使用相同同的表达达式或函函数，这这样便于于以后使使用合理理的索引引。Orderby语句ORDERBY语句决定定了Oracle如何将返返回的查查询结果果排序。Orderby语句对要要排序的的列没有有什么特特别的限限制，也也可以将将函数加加入列中中（象联联接或者者附加等等）。任何在Orderby语句的非非索引项项或者有有计算表表达式都都将降低低查询速速度。仔细检查查orderby语句以找

33、找出非索索引项或或者表达达式，它它们会降降低性能能。解决决这个问问题的办办法就是是重写orderby语句以使使用索引引，也可可以为所所使用的的列建立立另外一一个索引引，同时时应绝对对避免在在orderby子句中使使用表达达式。联接列对于有联联接的列列，即使使最后的的联接值值为一个个静态值值，优化化器是不会使使用索引引的。select*from employsswherefirst_name|last_name =BeillCliton;系统优化化器对基基于last_name创建的索索引没有有使用。当采用下下面这种种SQL语句的编编写，Oracle系统就可可以采用用基于last_name创建的索

34、索引。select*from employeewherefirst_name =Beill andlast_name=Cliton;带通配符符（%）的like语句通配符（%）在搜寻寻词首出出现，Oracle系统不使使用last_name的索引。select*from employee where last_namelike %cliton%;在很多情情况下可可能无法法避免这这种情况况，但是是一定要要心中有有底，通配符符如此使使用会降降低查询询速度。然而当当通配符符出现在在字符串其他他位置时时，优化化器就能能利用索索引。在在下面的的查询中中索引得到了了使用：select*from employe

35、e where last_namelike c%;用Where子句替换换HAVING子句避免使用用HAVING子句, HAVING只会在检检索出所所有记录录之后才才对结果果集进行过过滤.这个处理理需要排排序,总计等操操作.如果能通通过WHERE子句限制记录的的数目,那就能减减少这方方面的开开销.例如:低效:SELECTREGION，AVG(LOG_SIZE)FROM LOCATIONGROUPBYREGIONHAVINGREGION REGION!=SYDNEYANDREGION != PERTH高效SELECTREGION，AVG(LOG_SIZE)FROM LOCATIONWHEREREG

36、IONREGION != SYDNEYANDREGION != PERTHGROUPBYREGION 顺序WHERE GROUP HAVING用NOTEXISTS替代NOTIN在子查询询中,NOT IN子句将执执行一个个内部的的排序和和合并.无论在哪哪种情况况下,NOT IN都是最低低效的(因为它对对子查询询中的表表执行了了一个全全表遍历历).使用NOTEXISTS子子句可可以有效效地利用用索引。尽可能能使用NOTEXISTS来代替NOTIN，尽管二二者都使使用了NOT（不能使使用索引引而降低低速度），NOTEXISTS要比NOTIN查查询效率率更高。例如:语句1SELECTdname, de

37、ptnoFROM deptWHEREdeptnoNOT IN (SELECTdeptno FROMemp);语句2SELECTdname, deptnoFROM deptWHERENOTEXISTS(SELECT deptnoFROM empWHEREdept.deptno= emp.deptno);2要比1的执行性性能好很很多。因为1中对emp进行了full table scan,这是很浪浪费时间间的操作作。而且且1中没有用用到emp的index， 因为为没有where子句。而而2中的语句句对emp进行的是是缩小范范围的查查询。用索引提提高效率率索引是表表的一个个概念部部分,用来提高高检索数

38、数据的效效率，ORACLE使用了一个个复杂的的自平衡衡B-tree结构.通常,通过索引引查询数数据比全全表扫描要快快.当ORACLE找出执行行查询和和Update语句的最最佳路径径时,ORACLE优化器将将使用索索引.同样在联联结多个个表时使使用索引引也可以以提高效率率.另一个使使用索引引的好处处是,它提供了了主键(primarykey)的唯一性性验证。通常,在大型表表中使用用索引特特别有效效.当然,你也会发发现,在扫描小小表时,使用索引引同样能能提高效效率.虽然使用用索引能能得到查查询效率率的提高,但是我们们也必须须注意到到它的代代价.索引需要要空间来来存储,也需要定期维护护,每当有记记录在

39、表表中增减减或索引引列被修修改时,索引本身身也会被修改改.这意味着着每条记记录的INSERT,DELETE,UPDATE将为此多付出出4 ,5次的磁盘盘I/O.因为索引引需要额额外的存存储空间间和处理理,那些不必必要的索索引反而而会使查查询反应应时间变变慢.。定期的的重构索索引是有必要要的。避免在索索引列上上使用计计算WHERE子句中，如果索索引列是是函数的的一部分分优化化器将不不使用索引引而使用用全表扫扫描低效：SELECTFROMDEPTWHERESAL*12 25000;高效:SELECTFROM DEPTWHERESAL 25000/12;用=替代如果DEPTNO上有一个个索引。高效:

40、SELECT*FROM EMPWHEREDEPTNO=4 低效:SELECT*FROM EMPWHEREDEPTNO3通过使用用=、=等，避免免使用NOT命令例子：select*from employee wheresalary3000;对这个查查询，可可以改写写为不使使用NOT：select*from employee wheresalary3000;虽然这两两种查询询的结果果一样，但是第第二种查查询方案案会比第第一种查查询方案案更快些些。第二二种查询询允许Oracle对salary列使用索索引，而而第一种种查询则则不能使使用索引引。如果有其其它办法法，不要要使用子子查询。外部联接接+的用法

41、外部联接接+按其在=的左边或或右边分分左联接接和右联联接。若若不带+运算符的的表中的的一个行行不直接接匹配于于带+预算符的的表中的的任何行行，则前前者的行行与后者者中的一一个空行行相匹配配并被返返回。利利用外部部联接+，可以替替代效率率十分低低下的notin运算，大大大提高高运行速速度。例例如，下下面这条条命令执执行起来来很慢：selecta.empnofrom empa wherea.empno notin(select empno fromemp1wherejob=SALE);利用外部部联接，改写命命令如下下:selecta.empnofrom empa ,emp1 bwherea.emp

42、no=b.empno(+)andb.empnoisnullandb.job=SALE;这样运行行速度明明显提高高.尽量多使使用COMMIT事务是消消耗资源源的，大大事务还还容易引引起死锁锁 COMMIT所释放的的资源:回滚段上上用于恢恢复数据据的信息息.被程序语语句获得得的锁redo logbuffer中的空间间ORACLE为管理上上述3种资源中中的内部部花费用TRUNCATE替代DELETE当删除表表中的记记录时,在通常情情况下,回滚段(rollbacksegments)用来存放放可以被被恢复的的信息.如果你没没有COMMIT事务,ORACLE会将数据据恢复到到删除之之前的状状态(准确地说是

43、是恢复到到执行删删除命令令之前的的状况)而当运用用TRUNCATE时,回滚段不不再存放放任何可可被恢复复的信息.当命令运运行后,数据不能能被恢复复.因此很少少的资源源被调用用,执行时间间也会很很短.计算记录录条数和一般的的观点相相反, count(*)比count(1)稍快,当然如果果可以通过索索引检索索,对索引列列的计数数仍旧是是最快的的.例如 COUNT(EMPNO)字符型字字段的引引号比如有的的表PHONE_NO字段是CHAR型,而且创建建有索引引，但在WHERE条件中忘忘记了加加引号，就不会会用到索索引。WHEREPHONE_NOHEREPHONE_NO=13

44、920202022优化EXPORT和IMPORT使用较大大的BUFFER(比如10MB ,10,240,000)可以提高高EXPORT和IMPORT的速度;ORACLE将尽可能能地获取取你所指指定的内内存大小小,即使在内内存不满足,也不会报报错.这个值至至少要和和表中最最大的列列相当,否则列值会被被截断;*优化Tools*SQL语句的执执行步骤骤语法分析析 ，分分析语句句的语法法是否符符合规范范，衡量量语句中中各表达达式的意意义。语义分析析 ，检检查语句句中涉及及的所有有数据库库对象是是否存在在，且用用户有相相应的权权限。视图转换换，将涉涉及视图图的查询询语句转转换为相相应的对对基表查查询语句

45、句。表达式转转换，将将复杂杂的SQL表达式转转换为较较简单的的等效连连接表达达式。选择优化化器，不不同的优优化器一一般产生生不同的的“执行行计划”选择连接接方式，ORACLE有三种连连接方式式，对多多表连接接ORACLE可选择适适当的连连接方式式。选择连接接顺序， 对多多表连接接ORACLE选择哪一一对表先先连接，选择这这两表中中哪个表表做为源源数据表表。选择数据据的搜索索路径， 根据据以上条条件选择择合适的的数据搜搜索路径径，如是是选用全全表搜索索还是利利用索引引或是其其他的方方式。运行“执执行计划划”优化器与与执行计计划Oracle在执行一一个SQL之前,首先要分分析一下下语句的的执行计计

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

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

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

49、PLAIN PLAN是一个很很好的分分析SQL语句的工工具,它甚至可以在不不执行SQL的情况下下分析语语句.通过分析析,我们就可可以知道ORACLE是怎么样样连接表表,使用什么么方式扫扫描表(索引扫描或全表表扫描)以及使用用到的索索引名称称.你需要按按照从里里到外,从上到下下的次序序解读分分析的结结果.EXPLAIN PLAN分析的结结果是用用缩进的的格式排排列的,最内部的操作将将被最先先解读,如果两个个操作处处于同一一层中,带有最小小操作号的的将被首首先执行行.NESTEDLOOP是少数不不按照上上述规则则处理的的操作,正确的执行路径径是检查查对NESTEDLOOP提供数据据的操作作,其中操

50、作号最小小的将被被最先处处理.Autotrace解读Current mode:对于修改改的数据据从数据据段中读读Read-consistentmode:读一致性性模式Physicalblock:物理块（如8192字节）Recursivecalls:嵌套调用用次数使用TKPROF工具SQLtrace工具收集集正在执执行的SQL的性能状状态数据据并记录录到一个跟踪踪文件中中.这个跟踪踪文件提提供了许许多有用用的信息息,例如解析次数.执行次数数,CPU使用时间间等.这些数据据将可以以用来优优化你的系统.设置SQLTRACE在会话级级别:有效ALTERSESSION SETSQL_TRACETRUE设

51、置SQLTRACE在整个数数据库有有效,你必须将将SQL_TRACE参数在init.ora中设为TRUE,USER_DUMP_DEST参数说明明了生成跟踪踪文件的的目录再使用TKPROF对TRACE文件进行行分析分析结果果更加准准确、清清楚在SQLPLUS配置AUTOTRACEAUTOTRACE 参数解 释SET AUTOTRACE OFF不能获得AUTOTRACE报告. 这是默认的.SET AUTOTRACE ON EXPLAIN仅仅显示优化器执行计划的AUTOTRACE报告SET AUTOTRACE ON STATISTICS仅仅显示SQL语句执行的统计结果的AUTOTRACE报告SET

52、AUTOTRACE ON包括上面两项内容的AUTOTRACE报告SET AUTOTRACE TRACEONLY与SET AUTOTRACE ON类似,所有的统计和数据都在，但不可以打印在SQLPLUS配置AUTOTRACE1、首先创建建PLUSTRACE角色并且且赋给public：Sql $ORACLE_HOME/sqlplus/admin/plustrce.sql2、 赋权权限给用用户Sql grant plustracetopublic（预赋权权的用户户名）;3、以SYSTEM用户创建建PLAN_TABLE表Sql $ORACLE_HOME/rdbms/admin/utlxplan.sql

53、Sql 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 STATEMENTOptimizer=CHOOSE10NESTEDLOO

54、PS21TABLE ACCESS(FULL)OFEMP31TABLE ACCESS(BYINDEXROWID) OF DEPT43INDEX(UNIQUESCAN)OFPK_DEPT(UNIQUE)最起码要要解决全全表扫描描问题改变where条件的次次序一般般没有用用目录优化基础础知识性能调整整综述有效的应应用设计计SQL语句的处处理过程程Oracle的优化器器Oracle的执行计计划注意事项项性能调整整综述谁来调整整系统?什么时候候调整?建立有效效调整的的目标在设计和和开发时时的调整整谁来调整整系统应用设计计人员必须传达达应用系系统的设设计，使使得每个个人都清清楚应用用中的数数据流动动.应用

55、开发发人员必须传达达他们选选择的实实现策略略，使得得语句调调整的过过程中能能快速、容易地地识别有有问题的的应用模模块和可可疑的SQL语句.数据库管管理人员员必须仔细细地监控控系统活活动并提提供它们们的资料料，使得得异常的的系统性性能可被被快速得得识别和和纠正.硬件/软件管理理人员必须传达达系统的的硬件、软件配配置并提提供它们们的资料料，使得得相关人人员能有有效地设设计和管管理系统统。谁来调整整系统与系统涉涉及的每每个人都都在调整整过程中中起某些些作用，当上面面提及的的那些人人员传达达了系统统的特性性并提供供了它们们的资料料，调整整就能相相对的容容易和更更快一些些。事实上的的结果是是：数据据库管

56、理理员对调调整负有有全部或或主要的的责任。但是，数据库库管理员员很少有有合适的的系统方方面的资资料，而而且，在在很多情情况下，数据库库管理员员往往是是在实施施阶段才才介入数数据库，这就给给调整工工作带来来许多负负面的影影响，因因为在设设计阶段段的缺陷陷是不能能通过DBA的调整而而得以解解决，而而设计阶阶段的缺缺陷往往往对数据据库性能能造成极极大的影影响。在真正成成熟的开开发环境境下，开开发人员员作为纯纯代码编编写人员员时，对对性能的的影响最最小，此此时大部部分的工工作应由由应用设设计人员员完成，而且数数据库管管理员往往往在前前期的需需求管理理阶段就就介入，为设计计人员提提供必要要的技术术支持。

57、调整并不不是数据据库管理理员的专专利，相相反大部部分应该该是设计计人员和和开发人人员的工工作，这这就需要要设计人人员和开开发人员员具体必必要的数数据库知知识，这这样才能能组成一一个高效效的团队队，然而而事实上上往往并并非如此此。谁来调整整系统与系统涉涉及的每每个人都都在调整整过程中中起某些些作用，当上面面提及的的那些人人员传达达了系统统的特性性并提供供了它们们的资料料，调整整就能相相对的容容易和更更快一些些。事实上的的结果是是：数据据库管理理员对调调整负有有全部或或主要的的责任。但是，数据库库管理员员很少有有合适的的系统方方面的资资料，而而且，在在很多情情况下，数据库库管理员员往往是是在实施施

58、阶段才才介入数数据库，这就给给调整工工作带来来许多负负面的影影响，因因为在设设计阶段段的缺陷陷是不能能通过DBA的调整而而得以解解决，而而设计阶阶段的缺缺陷往往往对数据据库性能能造成极极大的影影响。在真正成成熟的开开发环境境下，开开发人员员作为纯纯代码编编写人员员时，对对性能的的影响最最小，此此时大部部分的工工作应由由应用设设计人员员完成，而且数数据库管管理员往往往在前前期的需需求管理理阶段就就介入，为设计计人员提提供必要要的技术术支持。调整并不不是数据据库管理理员的专专利，相相反大部部分应该该是设计计人员和和开发人人员的工工作，这这就需要要设计人人员和开开发人员员具体必必要的数数据库知知识，

59、这这样才能能组成一一个高效效的团队队，然而而事实上上往往并并非如此此。什么时候候调整系系统多数人认认为当用用户感觉觉性能差差时才进进行调整整，这对对调整过过程中使使用某些些最有效效的调整整策略来来说往往往是太迟迟了。此此时，如如果你不不愿意重重新设计计应用的的话，你你只能通通过重新分配配内存(调整SGA)和调整I/O的办法或或多或少少地提高高性能。Oracle提供了许许多特性性，这些些特性只只有应用用到正确确地设计计的系统统中时才才能够很很大地提提高性能能。应用设计计人员需需要在设设计阶段段设置应应用的性性能期望望值。然然后在设设计和开开发期间间，应用用设计人人员应考考虑哪些些Oracle特性

60、可以以对系统统有好处处，并使使用这些些特性。 通过良好好的系统统设计，你就可可以在应应用的生生命周期期中消除除性能调调整的代代价和挫挫折。下下图说明明在应用用的生命命周期中中调整的的相对代代价和收收益，最最有效的的调整时时间是是在设计计阶段。在设计计期间的的调整能能以最低低的代价价给你最最大的收收益。什么时候候调整系系统图:在应用生生命周期期中调整整的代价价什么时候候调整系系统图:在应用生生命周期期中调整整的收益益调整的目目标不管正在在设计或或维护系系统，应应该建立立专门的的性能目目标，它它使你知知道何时时要作调调整。调调整你的的系统的的最有效效方法如如下：当当设计系系统时考考虑性能能调调整操