ORACLESQL性能优化这个很全的

ORACLE培训

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

有效的应用设计

SQL语句的处理过程

Oracle的优化器

Oracle的执行计划注意事项一、优化基础知识概述性能管理性能问题调整的方法SQL优化机制应用的调整SQL语句的处理过程共享SQL区域SQL语句处理的阶段共享游标SQL编码标准Oracle优化器介绍SQLTunningTips优化Tools性能管理尽早开始设立合适目标边调整边监控相关人员进行合作及时处理过程中发生的意外和变化80/20定律SQL优化衡量指标随着软件技术的不断发展，系统性能越来越重要。系统性能主要用：系统响应时间和并发性来衡量。造成SQL语句性能不佳大致有两个原因：开发人员只关注查询结果的正确性，忽视查询语句的效率。开发人员只关注SQL语句本身的效率，对SQL语句执行原理、影响SQL执行效率的主要因素不清楚。*前者可以通过深入学习SQL语法及各种SQL调优技巧进行解决。SQL调优是一个系统工程，熟悉SQL语法、掌握各种内嵌函数、分析函数的用法只是编写高效SQL的必要条件。*后者从分析SQL语句执行原理入手，指出SQL调优应在优化SQL解析和优化CBO上。调优领域调优领域：应用程序级调优：*SQL语句调优*管理变化调优2.实例级调优*内存*数据结构*实例配置3.操作系统交互*I/O*SWAP*Parameters本课程内容只讲解讨论应用程序级：

OracleSQL语句调优及管理变化调优调整的方法调整业务功能调整数据设计调整流程设计调整SQL语句调整物理结构调整内存分配调整I/O调整内存竞争调整操作系统不同调整产生相应性能收益调整整的的角角色色SQL语句句优优化化是是提提高高性性能能的的重重要要环环节节开发发人人员员不不能能只只注注重重功功能能的的实实现现，，不不管管性性能能如如何何开发发人人员员不不能能把把Oracle当成成一一个个黑黑盒盒子子，，必必须须了了解解其其结结构构、、处处理理SQL和数数据据的的方方法法必需需遵遵守守既既定定的的开开发发规规范范未经经过过SQL语句句优优化化的的模模块块不不要要上上线线SQL语句句优优化化的的过过程程定位位有有问问题题的的语语句句检查查执执行行计计划划检查查执执行行过过程程中中优优化化器器的的统统计计信信息息分析析相相关关表表的的记记录录数数、、索索引引情情况况改写写SQL语句句、、使使用用HINT、调调整整索索引引、、表表分分析析有些些SQL语句句不不具具备备优优化化的的可可能能，，需需要要优优化化处处理理方方式式达到到最最佳佳执执行行计计划划什么么是是好好的的SQL语句句？？尽量量简简单单，，模模块块化化易读读、、易易维维护护节省省资资源源内存存CPU扫描描的的数数据据块块要要少少少排排序序不造造成成死死锁锁SQL共享享原原理理ORACLE将执执行行过过的的SQL语句句存存放放在在内内存存的的共共享享池池(sharedbufferpool)中，，可可以以被被所所有有的的数数据据库库用用户户共共享享。。当你你执执行行一一个个SQL语句句(有时时被被称称为为一一个个游游标标)时,如果果它它和和之之前前的的执执行行过过的的语语句句完完全全相相同同,ORACLE就能能很很快快获获得得已已经经被被解解析析的的语语句句以以及及最最好好的的执执行行路路径径.这个个功功能能大大大大地地提提高高了了SQL的执执行行性性能能并并节节省省了了内内存存的的使使用用。。SQL共享享原原理理为了了不不重重复复解解析析相相同同的的SQL语句句，，在在第第一一次次解解析析之之后后，，Oracle将SQL语句句存存放放在在内内存存中中。。这这块块位位于于系系统统全全局局区区域域SGA(systemglobalarea)的共共享享池池(sharedbufferpoo1)中的的内存存可可以以被被所所有有的的数数据据库库用用户户共共享享。。因因此此，，当当你你执执行行一一个个SQL语句句(有时时被被称称为为一一个个游游标标)时，，如如果果它它和和之之前前执执行行过过的的语语句句完完全全相同同，，Oracle就能能很很快快获获得得已已经经被被解解析析的的语语句句以以及及最最好好的的执执行行方案案。。Oracle的这这个个功功能能大大大大地地提提高高了了SQL的执执行行性性能能并并节节省省了内内存存的的使使用用。。可惜惜的的是是，，Oracle只对对简简单单的的表表提提供供高高速速缓缓冲冲(cachebufferiIlg)，这这个个功功能能并并不不适适用用于于多多表表连连接接查查询询。。数数据据库管管理理员员必必须须在在启启动动参参数数文文件件中中为为这这个个区区域域设设置置合合适适的的参参数数，，当这个内内存区域域越大，，就可以以保留更更多的语语句，当当然被共共享的可能性也也就越大大了。当当向Oracle提交一个个SQL语句时，，Oracle会首先在在这块内内存中查查找相同同的语句句。SQL共享的三三个条件件当前被执执行的语语句和共共享池中中的语句句必须完完全相同同（包包括大小小写、空空格、换换行等））两个语句句所指的的对象必必须完全全相同（（同义义词与表表是不同同的对象象）两个SQL语句中必必须使用用相同的的名字的的绑定变变量(bindvariables)共享SQL语句注意：Oracle对两者采采取的是是一种严严格匹配配策略，，要达成成共享。。SQL语句必须完全相相同(包括空格格、换行行等)。能够使使用共享享的语句句必须满满足三个个条件：①字符符级的比比较。当前被执执行的语语句和共共享池中中的语句句必须完完全相同同。例如：SELECT*FROMATABLE；和下面面每一个个SQL语句都不不同：SELECT*fromATABLESelect*FromAtable；②语句句所指对对象必须须完全相相同即即两条SQL语句操作作的数据据库对象象必须同同一。③语句中中必须使使用相同同命名的的绑定变变量。如如：第一一组的两两个SQL语句是相相同的，可以以共享；；而第二二组中两两个语句句不同，，即使在在运行时时赋予不不同的绑绑定变量以相同同的值：：●第一一组selectpin，namefrompeoplewherepin=：blk1.pin；selectpin，namefrompeoplewherepin=：blk1.pin；●第二组组selectpin，namefrompeoplewherepin=：blk1.ot_jnd；selectpin，namefrompeoplewherepin=：blk1.ov_jnd；SQL语句的处处理过程程共享SQL区域Sql处理过程程SQLPARSE与共享SQL语句当一个Oracle实实例接收收一条sql后后1、CreateaCursor创建游标标2、ParsetheStatement分析语句句3、DescribeResultsofaQuery描述查询询的结果果集4、DefineOutputofaQuery定义查询询的输出出数据5、BindAnyVariables绑定变量量6、ParallelizetheStatement并行执行行语句7、RuntheStatement运行语句句8、FetchRowsofaQuery取查询出出来的行行9、ClosetheCursor关闭游标标为什么要要bindvariables?字符级的的比较:SELECT*FROMUSER_FILESWHEREUSER_NO=‘‘10001234’;与SELECT*FROMUSER_FILESWHEREUSER_NO=‘‘10004321’;检查：selectname,executionsfromv$db_object_cachewherenamelike'select*fromuser_files%'什么叫做做重编译译问题什么叫做做重编译译？下面这个个语句每每执行一一次就需需要在SHAREPOOL硬解析一一次，一百百万用户户就是一一百万次次，消耗耗CPU和内存，，如果业业务量大，很很可能导导致宕库库……如果绑定定变量，，则只需需要硬解解析一次次，重复复调用即即可select*fromdConMsgwherecontract_no=32013484095139绑定变量量解决重重编译问问题未使用绑绑定变量量的语句句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;绑定变量量的注意意事项注意：1、不要使使用数据据库级的的变量绑绑定参数数cursor_sharing来强制绑定，，无论其其值为force还是similar2、有些带带><的语句绑绑定变量量后可能能导致优优化器无无法正确确使用索引SQL语句的四个处理阶段SQL语句的处理过过程解析(PARSE)：SQL语句的处理过过程在共享池中查查找SQL语语句检查语法检查语义和相相关的权限合并(MERGE)视图图定义和子查查询确定执行计划划绑定(BIND)：SQL语句的处理过过程在语句中查找找绑定变量赋值(或重新新赋值)执行(EXECUTE)：SQL语句的处理过过程应用执行计划划执行必要的I/O和排序操作提取(FETCH)：从查询结果中中返回记录必要时进行排排序使用ARRAYFETCH机制共享游标：好好处减少解析动态内存调整整提高内存使用用率书写可共享的的SQL绑定变量和共共享游标ORACLE优化器模式概概述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优化器，而而直接采用基基于规则或者者基于成本的的优化器。访问数据表的的方式①全表扫描全表扫描就是是顺序地访问问表中每条记记录。Oracle采用一次读入多个数据块块(databaseblock)的方式优化化全表扫描。。②通过ROWID访问表ROWID包包含了表中记记录的物理位位置信息。可可以采用基于于ROWID的访问方式式情况提高访访问表的效率率。Oracle采用索索引实现了数数据和存放数数据的物理位位置(ROWID)之间间的联系通常索引提供供了快速访问ROWID的的方法，因此此那些基于索索引列的查询询就可以得到到性能的提高高。数据库性能影响数据库系系统性能的要要素：主机CPU,RAM,存储系统;OS参数配置,ORACLE参数配置;应用方面：数数据库设计及及SQL编程的质量一个性能优秀秀的应用系统统需要：良好的硬件配配置；正确合理的数数据库及中间间件参数配置置；合理的数据库库设计；良好的sql编程；运行期的性能能优化SQLTunning的重点SQL:insert,update,delete,select;主要关注的是是select关注的是：如如何用最小的的硬件资源消消耗、最少的的响应时间定定位数据位置置SQL优化的一般性性原则目标：减少服务器资资源消耗（主主要是磁盘IO）；设计方面：尽量依赖oracle的优化器，并并为其提供条条件；合适的索引，，索引的双重重效应，列的的选择性；编码方面：利用索引，避避免大表FULLTABLESCAN；合理使用临临时表；避免写过于于复杂的sql，不一定非非要一个sql解决问题；；在不影响业业务的前提提下减小事事务的粒度度；优化概括课程Oracle数据库SQL语句优化的的总体策略略。以这些些优化策略为为指导，通通过经验总总结，我们们可以不断断地丰富优优化方案，进进而指导我我们进行应应用系统的的数据库性性能优化。。以下枚举几几则被证明明行之有效效的优化方方案：●创建表表的时候。。应尽量建建立主键，，尽量根据据实际需要要调整数据据表的PCTFREE和PCTUSED参数；大数数据表删除除，用truncatetable代替delete。●合理使使用索引，，在OLTP应用中一张张表的索引引不要太多多。数据重重复量大的的列不要建建立二叉树树索引，可可以采用位位图索引；；组合索引引的列顺序序尽量与查查询条件列列顺序保持持一致；对对于数据操操作频繁的的表，索引引需要定期期重建，以以减少失效效的索引和和碎片。优化概括●查询尽量用用确定的列列名，少用用*号。selectcount(key)fromtabwherekey>0性能优于selectcount(*)fromtab；尽量少嵌套套子查询，，这种查询询会消耗大大量的CPU资源；对于于有比较多多or运算的查询询，建议分分成多个查查询，用unionall联结起来；；多表查询询的查询语句句中，选择择最有效率率的表名顺顺序。Oracle解析器对表表解析从右到左，所所以记录少少的表放在在右边。●尽量多多用commit语句提交事事务，可以以及时释放放资源、解解锁、释放日日志空间、、减少管理理花费；在在频繁的、、性能要求求比较高的的数据操作中中，尽量避避免远程访访问，如数数据库链等等，访问频频繁的表可可以常驻内存存：altertable．．．cache；●在Oracle中动态执行行SQL，尽量用execute方式，不用用dbms_sql包。**SQLTunningTips**sql语句的编写写原则和优优化随着数据库库中数据的的增加，系系统的响应应速度就成成为目前系系统需要解解决的最主主要的问题题之一。系系统优化中中一个很重重要的方面面就是SQL语句的优化化。对于大大量数据，，劣质SQL语句和优质质SQL语句之间的的速度差别别可以达到到上百倍，，对于一个个系统不是是简单地能能实现其功功能就可，，而是要写写出高质量量的SQL语句，提高高系统的可可用性。在多数情况况下，Oracle使用索引来来更快地遍遍历表，优优化器主要要根据定义义的索引来来提高性能能。如果在在SQL语句的where子句中写的的SQL代码不合理理，就会造造成优化器器删去索引引而使用全全表扫描，，一般就这这种SQL语句就是所所谓的劣质SQL语句。sql语句的编写写原则和优优化在编写SQL语句时我们们应清楚优优化器根据据何种原则则来使用索索引，这有有助于写出出高性能的的SQL语句。SQL语句的编写写原则和SQL语句的优化化，请跟我我一起学习习以下几方方面：TunningTip的各个方面面1.不要让Oracle做得太多；；2.给优化器更更明确的命命令；3.减少访问次次数；4.细节上的影影响；1.不要让Oracle做得太多避免复杂的的多表关联联select…fromuser_filesuf,df_money_filesdm,cw_charge_recordccwhereuf.user_no=dm.user_noanddm.user_no=cc.user_noand………andnotexists(select…)???很难优化，，随着数据据量的增加加性能的风风险很大。。避免使用‘‘*‘当你想在SELECT子句中列出出所有的COLUMN时,使用动态SQL列引用‘‘*’是是一个方便便的方法.不幸的是,这是一个非非常低效的方法.实际上,ORACLE在解析的过过程中,会将’*’’依次转转换成所有的的列名,这个工作是是通过查询询数据字典典完成的,这意味着将耗费费更多的时时间；只提取你所所要使用的的列；使用别名能能够加快解解析速度；；避免使用耗耗费资源的的操作带有DISTINCT,UNION,MINUS,INTERSECT,ORDERBY的SQL语句会启动动SQL引擎执行耗耗费资源的的排序(SORT)功能.DISTINCT需要一次排排序操作,而其他的至至少需要执执行两次排序.例如,一个UNION查询,其中每个查查询都带有有GROUPBY子句,GROUPBY会触发嵌入入排序(NESTEDSORT);这样,每个查询需要执执行一次排排序,然后在执行行UNION时,又一个唯一一排序(SORTUNIQUE)操作被执行行而且它只只能在前面面的嵌入排序结束后后才能开始始执行.嵌入的排序序的深度会会大大影响响查询的效率.通常,带有UNION,MINUS,INTERSECT的SQL语句都可以以用其他方式式重写.用EXISTS替换DISTINCT例如:低效:SELECTDISTINCTDEPT_NO,DEPT_NAMEFROMDEPTD,EMPEWHERED.DEPT_NO=E.DEPT_NO高效:SELECTDEPT_NO,DEPT_NAMEFROMDEPTDWHEREEXISTS(SELECT‘X’’FROMEMPEWHEREE.DEPT_NO=D.DEPT_NO);用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’’2.给优化器更更明确的命命令自动选择索索引如果表中有有两个以上上（包括两两个）索引引，其中有有一个唯一一性索引，而其其他是非唯唯一性．在这种情况况下，ORACLE将使用唯一一性索引而而完全忽略略非唯一性索引．．举例:SELECTENAMEFROMEMPWHEREEMPNO=2326ANDDEPTNO=20;这里，只有有EMPNO上的索引是是唯一性的的，所以EMPNO索引将用来检检索记录．．TABLEACCESSBYROWIDONEMPINDEXUNIQUESCANONEMP_NO_IDX至少要包含含组合索引引的第一列列如果索引是是建立在多多个列上,只有在它的的第一个列列(leadingcolumn)被where子句引用时时,优化器才会会选择使用用该索引.SQL>createtablemultiindexusage(indanumber,indbnumber,descrvarchar2(10));Tablecreated.SQL>createindexmultindexonmultiindexusage(inda,indb);Indexcreated.SQL>setautotracetraceonlySQL>select*frommultiindexusagewhereinda=1;ExecutionPlan----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE10TABLEACCESS(BYINDEXROWID)OF'MULTIINDEXUSAGE'21INDEX(RANGESCAN)OF'MULTINDEX'(NON-UNIQUE)SQL>select*frommultiindexusagewhereindb=1;ExecutionPlan----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE10TABLEACCESS(FULL)OF'MULTIINDEXUSAGE'很明显,当仅引用索索引的第二二个列时,优化器使用用了全表扫扫描而忽略略了索引避免在索引引列上使用用函数WHERE子句中，如如果索引列列是函数的的一部分．．优化器将将不使用索引而而使用全表表扫描．举例:低效：SELECT…FROMDEPTWHERESAL*12>25000;高效:SELECT…FROMDEPTWHERESAL>25000/12;避免使用前前置通配符符WHERE子句中,如果索引列列所对应的的值的第一一个字符由由通配符(WILDCARD)开始,索引将不被被采用.SELECTUSER_NO,USER_NAME,ADDRESSFROMUSER_FILESWHEREUSER_NOLIKE'%109204421';在这种情况况下，ORACLE将使用全表表扫描.避免在索引引列上使用用NOT通常，我们们要避免在在索引列上上使用NOT,NOT会产生在和和在索引列上使使用函数相相同的影响响.当ORACLE””遇到到””NOT,他就就会停停止止使使用用索索引引转转而而执执行行全全表表扫扫描描.举例例:低效效:(这里里,不使使用用索索引引)SELECT……FROMDEPTWHEREDEPT_CODENOT=0;高效:(这里,使用了索索引)SELECT……FROMDEPTWHEREDEPT_CODE>0;避免在索索引列上上使用ISNULL和ISNOTNULL避免在索索引中使使用任何何可以为为空的列列，ORACLE将无法使使用该索引．．对于单单列索引引，如果果列包含含空值，，索引中中将不存存在此记记录.对于复合合索引，，如果每每个列都都为空，，索引中中同样不不存在此此记录.如果至少少有一个个列不为为空，则则记录存存在于索索引中．．如果唯一一性索引引建立在在表的A列和B列上,并且表中中存在一一条记录的A,B值为(123,null),ORACLE将不接受受下一条条具有相相同A,B值（123,null）的记录录(插入).然而如果果所有的的索引列列都为空，ORACLE将认为整整个键值值为空而而空不等等于空.因此你可可以插入1000条具有相相同键值值的记录录,当然它们们都是空空!因为空值值不存在在于索引引列中,所以WHERE子句中对对索引列列进行空值比较较将使ORACLE停用该索索引.任何在where子句中使使用isnull或isnotnull的语句优优化器是是不允许使使用索引引的。避免出现现索引列列自动转转换当比较不不同数据据类型的的数据时时,ORACLE自动对列列进行简简单的类型转转换.假设EMP_TYPE是一个字符符类型的索索引列.SELECTUSER_NO,USER_NAME,ADDRESSFROMUSER_FILESWHEREUSER_NO=109204421这个语句被被ORACLE转换为:SELECTUSER_NO,USER_NAME,ADDRESSFROMUSER_FILESWHERETO_NUMBER(USER_NO)=109204421因为内部发发生的类型型转换,这个索引将将不会被用用到!在查询时尽尽量少用格格式转换如用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)3.减少访问次次数减少访问数数据库的次次数当执行每条条SQL语句时,ORACLE在内部执行行了许多工工作:解析SQL语句,估算索引的的利用率,绑定变量,读数据块等等等.由此可见,减少访问数数据库的次次数,就能实际上上减少ORACLE的工作量.类比，工程程实施使用DECODE来减少处理理时间例如:SELECTCOUNT(*)，SUM(SAL)FROMEMPWHEREDEPT_NO=0020ANDENAMELIKE‘SMITH%’;SELECTCOUNT(*)，SUM(SAL)FROMEMPWHEREDEPT_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%’;减少对表的的查询在含有子查查询的SQL语句中,要特别注意意减少对表表的查询.例如:低效SELECTTAB_NAMEFROMTABLESWHERETAB_NAME=(SELECTTAB_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子句中有多多个表联接接时，WHERE子句中排在最后的表表应当是返返回行数可可能最少的的表，有过过滤条件的的子句应放在WHERE子句中的最最后。如：设从emp表查到的数数据比较少少或该表的的过滤条件件比较确定定，能大大大缩小查询询范围，则则将最具有有选择性部部分放在WHERE子句中的最最后：select*fromempe,deptdwhered.deptno>10ande.deptno=30;如果dept表返回的记记录数较多多的话，上上面的查询询语句会比比下面的查查询语句响响应快得多多。select*fromempe,deptdwheree.deptno=30andd.deptno>10;WHERE子句——函数、表达达式使用最好不要在在WHERE子句中中使用函或或表达式，，如果要使使用的话，，最好统一一使用相同同的表达式式或函数，，这样便于于以后使用用合理的索索引。Orderby语句ORDERBY语句决定了了Oracle如何将返回回的查询结结果排序。。Orderby语句对要排排序的列没没有什么特特别的限制制，也可以以将函数加加入列中（（象联接或或者附加等等）。任何在Orderby语句的非索索引项或者者有计算表表达式都将将降低查询询速度。仔细检查orderby语句以找出出非索引项项或者表达达式，它们们会降低性性能。解决决这个问题题的办法就就是重写orderby语句以使用用索引，也也可以为所所使用的列列建立另外外一个索引引，同时应应绝对避免免在orderby子句中使用用表达式。。联接列对于有联接接的列，即即使最后的的联接值为为一个静态态值，优化化器是不会使用用索引的。。select*fromemploysswherefirst_name||''||last_name='BeillCliton';系统优化器对对基于last_name创建的索引没没有使用。当采用下面这这种SQL语句的编写，，Oracle系统就可以采采用基于last_name创建的索引。。select*fromemployeewherefirst_name='Beill'andlast_name='Cliton';带通配符（%）的like语句通配符（%）在搜寻词首首出现，Oracle系统不使用last_name的索引。select*fromemployeewherelast_namelike'%cliton%';在很多情况下下可能无法避避免这种情况况，但是一定定要心中有底底，通配符如此此使用会降低低查询速度。。然而当通配配符出现在字字符串其他位置置时，优化器器就能利用索索引。在下面面的查询中索索引得到了使用用：select*fromemployeewherelast_namelike'c%';用Where子句替换HAVING子句避免使用HAVING子句,HAVING只会在检索出出所有记录之之后才对结果果集进行过滤.这个处理需要要排序,总计等操作.如果能通过WHERE子句限制记录的数目目,那就能减少这这方面的开销销.例如:低效:SELECTREGION，AVG(LOG_SIZE)FROMLOCATIONGROUPBYREGIONHAVINGREGIONREGION!=‘SYDNEY’ANDREGION!=‘PERTH’’高效SELECTREGION，AVG(LOG_SIZE)FROMLOCATIONWHEREREGIONREGION!=‘SYDNEY’’ANDREGION!=‘PERTH’’GROUPBYREGION顺序WHERE>GROUP>HAVING用NOTEXISTS替代NOTIN在子查询中,NOTIN子句将执行一一个内部的排排序和合并.无论在哪种情情况下,NOTIN都是最低效的的(因为它对子查查询中的表执执行了一个全全表遍历).使用NOTEXISTS子句可可以有效地利利用索引。尽尽可能使用NOTEXISTS来代替NOTIN，尽尽管二者都使使用了NOT（不能使用用索引而降低低速度），NOTEXISTS要要比NOTIN查询效效率更高。例如:语句1SELECTdname,deptnoFROMdeptWHEREdeptnoNOTIN(SELECTdeptnoFROMemp);语句2SELECTdname,deptnoFROMdeptWHERENOTEXISTS(SELECTdeptnoFROMempWHEREdept.deptno=emp.deptno);2要比1的执行性能好好很多。因为1中对emp进行了fulltablescan,这是很浪费时时间的操作。。而且1中没有用到emp的index，因为没有有where子句。而2中的语句对emp进行的是缩小小范围的查询询。用索引提高效效率索引是表的一一个概念部分分,用来提高检索索数据的效率率，ORACLE使用了一个复杂杂的自平衡B-tree结构.通常,通过索引查询询数据比全表表扫描要快.当ORACLE找出执行查询询和Update语句的最佳路路径时,ORACLE优化器将使用用索引.同样在联结多多个表时使用用索引也可以以提高效率.另一个使用索索引的好处是是,它提供了主键键(primarykey)的唯一性验证证。通常,在大型表中使使用索引特别别有效.当然,你也会发现,在扫描小表时,使用索引同样样能提高效率率.虽然使用索引引能得到查询询效率的提高,但是我们也必必须注意到它它的代价.索引需要空间间来存储,也需要定期维护,每当有记录在在表中增减或或索引列被修修改时,索引本身也会被修改.这意味着每条条记录的INSERT,DELETE,UPDATE将为此多付出4,5次的磁盘I/O.因为索引需要要额外的存储储空间和处理理,那些不必要的的索引反而会会使查询反应应时间变慢.。定期的重构构索引是有必要的。。避免在索引列列上使用计算算WHERE子句中，如果果索引列是函函数的一部分分．优化器将将不使用索引而使使用全表扫描描．低效：SELECT…FROMDEPTWHERESAL*12>25000;高效:SELECT…FROMDEPTWHERESAL>25000/12;用>=替代>如果DEPTNO上有一个索引引。高效:SELECT*FROMEMPWHEREDEPTNO>=4低效:SELECT*FROMEMPWHEREDEPTNO>3通过使用>=、<=等，避免使用用NOT命令例子：select*fromemployeewheresalary<>3000;对这个查询，，可以改写为为不使用NOT：select*fromemployeewheresalary<3000orsalary>3000;虽然这两种查查询的结果一一样，但是第第二种查询方方案会比第一一种查询方案案更快些。第第二种查询允允许Oracle对salary列使用索引，，而第一种查查询则不能使使用索引。如果有其它办办法，不要使使用子查询。。外部联接"+"的用法外部联接"+"按其在"="的左边或右边边分左联接和和右联接。若若不带"+"运算符的表中中的一个行不不直接匹配于于带"+"预算符的表中中的任何行，，则前者的行行与后者中的的一个空行相相匹配并被返返回。利用外外部联接"+"，可以替代效效率十分低下下的notin运算，大大提提高运行速度度。例如，下下面这条命令令执行起来很很慢：selecta.empnofromempawherea.empnonotin(selectempnofromemp1wherejob='SALE');利用外部联接接，改写命令令如下:selecta.empnofromempa,emp1bwherea.empno=b.empno(+)andb.empnoisnullandb.job='SALE';这样运行速度度明显提高.尽量多使用COMMIT事务是消耗资资源的，大事事务还容易引引起死锁COMMIT所释放的资源源:回滚段上用于于恢复数据的的信息.被程序语句获获得的锁redologbuffer中的空间ORACLE为管理上述3种资源中的内内部花费用TRUNCATE替代DELETE当删除表中的的记录时,在通常情况下下,回滚段(rollbacksegments)用来存放可以以被恢复的信信息.如果你没有COMMIT事务,ORACLE会将数据恢复复到删除之前前的状态(准确地说是恢复复到执行删除除命令之前的的状况)而当运用TRUNCATE时,回滚段不再存存放任何可被被恢复的信息.当命令运行后后,数据不能被恢恢复.因此很少的资资源被调用,执行时间也会会很短.计算记录条数数和一般的观点点相反,count(*)比count(1)稍快,当然如果可以通过索引检检索,对索引列的计计数仍旧是最最快的.例如COUNT(EMPNO)字符型字段的的引号比如有的表PHONE_NO字段是CHAR型,而且创建有索索引，但在WHERE条件中忘记了了加引号，就就不会用到索索引。WHEREPHONE_NO=‘WHEREPHONE_NO化EXPORT和IMPORT使用较大的BUFFER(比如10MB,10,240,000)可以提高EXPORT和IMPORT的速度;ORACLE将尽可能地获获取你所指定定的内存大小小,即使在内存不满足,也不会报错.这个值至少要要和表中最大大的列相当,否则列值会被截断断;**优化Tools**SQL语句的执行步步骤语法分析，，分析语句的的语法是否符符合规范，衡衡量语句中各各表达式的意意义。语义分析，，检查语句中中涉及的所有有数据库对象象是否存在，，且用户有相相应的权限。。视图转换，将将涉及视图的的查询语句转转换为相应的的对基表查询询语句。表达式转换，，将复杂的的SQL表达式转换为为较简单的等等效连接表达达式。选择优化器，，不同的优化化器一般产生生不同的“执执行计划”选择连接方式式，ORACLE有三种连接方方式，对多表表连接ORACLE可选择适当的的连接方式。。选择连接顺序序，对多表表连接ORACLE选择哪一对表表先连接，选选择这两表中中哪个表做为为源数据表。。选择数据的搜搜索路径，根根据以上条条件选择合适适的数据搜索索路径，如是是选用全表搜搜索还是利用用索引或是其其他的方式。。运行“执行计计划”优化器与执行行计划Oracle在执行一个SQL之前,首先要分析一一下语句的执执行计划,然后再按执行行计划去执行行。分析语句句的执行计划划的工作是由优优化器器(Optimizer)来完成成的Oracle的优化化器共共有两两种的的优化化方式式,即基于规规则的的优化化方式(Rule-BasedOptimization,简称为为RBO)和基于代代价的的优化方式式(Cost-BasedOptimization,简称为为CBO)。A、RBO方式：：优化化器在在分析析SQL语句时时,所遵循循的是是Oracle内部预预定的一些些规则则。比比如我我们常常见的的,当一个个where子句中中的一一列有有索引引时去去走索引。。B、CBO方式：：是看看语句句的代代价(Cost)了,这里的的代价价主要要指Cpu和内存存。优化化器在在判断断是否否用这这种方方式时时,主要参参照的的是表表及索索引的的统计计信息息，很多的的时侯侯过期期统计计信息息会令令优化化器做做出一一个错错误的的执行行计划划在Oracle8及以后后的版版本,Oracle推荐用用CBO的方式式。在Oracle10g中,取消了了RBO的支持持。优化器器与执执行计计划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条记录录,这种需需求在在很多多分页页语句的需需求中中会碰碰到.用EXPLAINPLAN分析SQL语句EXPLAINPLAN是一个个很好好的分分析SQL语句的的工具具,它甚至至可以在在不执执行SQL的情况况下分分析语语句.通过分分析,我们就就可以以知道ORACLE是怎么么样连连接表表,使用什什么方方式扫扫描表表(索引扫扫描或全全表扫扫描)以及使使用到到的索索引名名称.你需要要按照照从里里到外外,从上到到下的的次序序解读读分析析的结结果.EXPLAINPLAN分析的的结果果是用用缩进进的格格式排排列的的,最内部部的操作作将被被最先先解读读,如果两两个操操作处处于同同一层层中,带有最最小操作号号的将将被首首先执执行.NESTEDLOOP是少数数不按按照上上述规规则处处理的的操作作,正确的的执行路路径是是检查查对NESTEDLOOP提供数数据的的操作作,其中操操作号最最小的的将被被最先先处理理.Autotrace解读Currentmode:对于修修改的的数据据从数数据段段中读读Read-consistentmode:读一致致性模模式Physicalblock:物理块块（如如8192字节））Recursivecalls:嵌套调调用次次数使用TKPROF工具SQLtrace工具收收集正正在执执行的的SQL的性能能状态态数据据并记记录到到一个跟跟踪文文件中中.这个跟跟踪文文件提提供了了许多多有用用的信信息,例如解解析次数.执行次数,CPU使用时间等等.这些数据将将可以用来来优化你的系统.设置SQLTRACE在会话级别别:有效ALTERSESSIONSETSQL_TRACETRUE设置SQLTRACE在整个数据据库有效,你必须将SQL_TRACE参数在init.ora中设为TRUE,USER_DUMP_DEST参数说明了了生成跟踪文文件的目录录再使用TKPROF对TRACE文件进行分分析分析结果更更加准确、、清楚在SQLPLUS配置AUTOTRACEAUTOTRACE参数解释SETAUTOTRACEOFF不能获得AUTOTRACE报告.这是默认的.SETAUTOTRACEONEXPLAIN仅仅显示优化器执行计划的AUTOTRACE报告SETAUTOTRACEONSTATISTICS仅仅显示SQL语句执行的统计结果的AUTOTRACE报告SETAUTOTRACEON包括上面两项内容的AUTOTRACE报告SETAUTOTRACETRACEONLY与SETAUTOTRACEON类似,所有的统计和数据都在，但不可以打印在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可显示其执执行计划。。优化器与执执行计划SQL>selectename,dnamefromemp,deptwhereemp.deptno=dept.deptnoanddept.dnamein('ACCOUNTING','RESEARCH','SALES','OPERATIONS');ExecutionPlan

----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE10NESTEDLOOPS21TABLEACCESS(FULL)OF'EMP'31TABLEACCESS(BYINDEXROWID)OF'DEPT'43INDEX(UNIQUESCAN)OF'PK_DEPT'(UNIQUE)最起码要解解决全表扫扫描问题改变where条件的次序序一般没有有用目录优化基础知知识性能调整综综述有效的应用用设计SQL语句的处理理过程Oracle的优化器Oracle的执行计划划注意事项性能调整综综述谁来调整系系统?什么时候调调整?建立有效调调整的目标标在设计和开开发时的调调整谁来调整系系统应用设计人人员必须传达应应用系统的的设计，使使得每个人人都清楚应应用中的数数据流动.应用开发人人员必须传达他他们选择的的实现策略略，使得语语句调整的的过程中能能快速、容容易地识别别有问题的的应用模块块和可疑的的SQL语句.数据库管理理人员必须仔细地地监控系统统活动并提提供它们的的资料，使使得异常的的系统性能能可被快速速得识别和和纠正.硬件/软件管理人人员必须传达系系统的硬件件、软件配配置并提供供它们的资资料，使得得相关人员员能有效地地设计和管管理系统。。谁来调整系系统与系统涉及及的每个人人都在调整整过程中起起某些作用用，当上面面提及的那那些人员传传达了系统统的特性并并提供了它它们的资料料，调整就就能相对的的容易和更更快一些。。事实上的结结果是：数数据库管理理员对调整整负有全部部或主要的的责任。但但是，数据据库管理员员很少有合合适的系统统方面的资资料，而且且，在很多多情况下，，数据库管管理员往往往是在实施施阶段才介介入数据库库，这就给给调整工作作带来许多多负面的影影响，因为为在设计阶阶段的缺陷陷是不能通通过DBA的调整而得得以解决，，而设计阶阶段的缺陷陷往往对数数据库性能能造成极大大的影响。。在真正成熟熟的开发环环境下，开开发人员作作为纯代码码编写人员员时，对性性能的影响响最小，此此时大部分分的工作应应由应用设设计人员完完成，而且且数据库管管理员往往往在前期的的需求管理理阶段就介介入，为设设计人员提提供必要的的技术支持持。调整并不是是数据库管管理员的专专利，相反反大部分应应该是设计计人员和开开发人员的的工作，这这就需要设设计人员和和开发人员员具体必要要的数据库库知识，这这样才能组组成一个高高效的团队队，然而事事实上往往往并非如此此。谁来调整系系统与系统涉及及的每个人人都在调整整过程中起起某些作用用，当上面面提及的那那些人员传传达了系统统的特性并并提供了它它们的资料料，调整就就能相对的的容易和更更快一些。。事实上的结结果是：数数据库管理理员对调整整负有全部部或主要的的责任。但但是，数据据库管理员员很少有合合适的系统统方面的资资料，而且且，在很多多情况下，，数据库管管理员往往往是在实施施阶段才介介入数据库库，这就给给调整工作作带来许多多负面的影影响，因为为在设计阶阶段的缺陷陷是不能通通过DBA的调整而得得以解决，，而设计阶阶段的缺陷陷往往对数数据库性能能造成极大大的影响。。在真正成熟熟的开发环环境下，开开发人员作作为纯代码码编写人员员时，对性性能的影响响最小，此此时大部分分的工作应应由应用设设计人员完完成，而且且数据库管管理员往往往在前期的的需求管理理阶段就介介入，为设设计人员提提供必要的的技术支持持。调整并不是是数据库管管理员的专专利，相反反大部分应应该是设计计人员和开开发人员的的工作，这这就需要设设计人员和和开发人员员具体必要要的数据库库知识，这这样才能组组成一个高高效的团队队，然而事事实上往往往并非如此此。什么时候调调整系统多数人认为为当用户感感觉性能差差时才进行行调整，这这对调整过过程中使用用某些最有有效的调整整策略来说说往往是太太迟了。此此时，如果果你不愿意意重新设计计应用的话话，你只能能通过重新分配内存存(调整SGA)和调整I/O的办法或多或或少地提高性性能。Oracle提供了许多特特性，这些特特性只有应用用到正确地设设计的系统中中时才能够很很大地提高性性能。应用设计人员员需要在设计计阶段设置应应用的性能期期望值。然后后在设计和开开发期间，应应用设计人员员应考虑哪些些Oracle特性可以对系系统有好处，，并使用这些些特性。通过良好的系系统设计，你你就可以在应应用的生命周周期中消除性性能调整的代代价和挫折。。下图说明在在应用的生命命周期中调整整的相对代价价和收益，最最有效的调整整时间是在在设计阶段。。在设计期间间的调整能以以最低的代价价给你最大的的收益。什么时候调整整系统图:在应用生命周周期中调整的的代价什么时候调整整系统图:在应用生命周周期中调整的的收益调整的目标不管正在设计计或维护系统统，应该建立立专门的性能能目标，它使使你知道何时时要作调整。。调整你的系系统的最有效效方法如下：：当设计计系统时考虑虑性能调整操操作系统的硬硬件和软件识别性性能瓶颈确定问题的原原因采取纠纠正的动作当你设计系统统时，制定专专门的目标；；例如，响应应时间小于３３秒。当应用不能满足此此目标时，识识别造成变慢慢的瓶颈（例例如，I/O竞争），确定原因，采取取纠正动作。。在开发期间间，你应测试试应用研究，，确定在采取应用之前是是否满足设计计的性能目标标。调整的目标调整通常是一一系列开销。。一旦确定了了瓶颈，可能