ORACLE培训SQL性能优化

ORACLE培训

--SQL性能优化内容概述课程主要讨论： SQL语句执行的过程、ORACLE优化器，表之间的关联，如何得到SQL执行计划，如何分析执行计划等内容，从而由浅到深的方式了解SQL优化的过程，使大家逐步掌握SQL优化。目录优化基础知识性能调整综述有效的应用设计SQL语句的处理过程Oracle的优化器Oracle的执行计划注意事项SQL语句优化的过程定位有问题的语句检查执行计划检查执行过程中优化器的统计信息分析相关表的记录数、索引情况改写SQL语句、使用HINT、调整索引、表分析有些SQL语句不具备优化的可能，需要优化处理方式达到最佳执行计划什么是好的SQL语句？尽量简单，模块化易读、易维护节省资源内存CPU扫描的数据块要少少排序不造成死锁为什么要bindvariables?字符级的比较:SELECT*FROMUSER_FILESWHEREUSER_NO=‘10001234’;

与SELECT*FROMUSER_FILESWHEREUSER_NO=:BV1;检查：selectname,executionsfromv$db_object_cachewherenamelike'select*fromuser_files%'什么叫做重编译问题什么叫做重编译？下面这个语句每执行一次就需要在SHAREPOOL硬解析一次，一百万用户就是一百万次，消耗CPU和内存，如果业务量大，很可能导致宕库……如果绑定变量，则只需要硬解析一次，重复调用即可select*fromdConMsg绑定变量解决重编译问题未使用绑定变量的语句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联结结起来来.●尽尽量多多用commit语语句提提交事事务，，可以以及时时释放放资源源、解解锁、释释放日日志空空间、、减少少管理理花费费；在在频繁繁的、、性能能要求求比较较高的的数据操操作中中，尽尽量避避免远远程访访问，，如数数据库库链等等，访访问频频繁的的表可可以常驻驻内存存：altertable．．．．cache；●在在Oracle中动动态执执行SQL，尽尽量用用execute方方式，，不用用dbms_sql包包。sql语语句的的编写写原则则和优优化在编写写SQL语语句时时我们们应清清楚优优化器器根据据何种种原则则来使使用索索引，，这有有助于于写出出高性性能的的SQL语语句。。SQL语句句的编编写原原则和和SQL语语句的的优化化，请请跟我我一起起学习习以下下几方方面：：避免复复杂的的多表表关联联select……fromuser_filesuf,df_money_filesdm,cw_charge_recordccwhereuf.user_no=dm.user_noanddm.user_no=cc.user_noand………andnotexists(select……)???很难优优化，，随着着数据据量的的增加加性能能的风风险很很大。。避免使使用耗耗费资资源的的操作作带有DISTINCT,UNION,MINUS,INTERSECT,ORDERBY的SQL语句句会启启动SQL引擎擎执行行耗费费资源源的排排序(SORT)功功能.DISTINCT需需要一一次排排序操操作,而而其他他的至至少需需要执执行两两次排序.例如,一个个UNION查查询,其中中每个个查询询都带带有GROUPBY子子句,GROUPBY会会触发发嵌入入排序序(NESTEDSORT);这这样样,每每个个查询需需要执执行一一次排排序,然然后在在执行行UNION时时,又又一一个唯唯一排序(SORTUNIQUE)操作作被执执行而而且它它只能能在前前面的的嵌入入排序结结束后后才能能开始始执行行.嵌嵌入入的排排序的的深度度会大大大影影响查查询的效效率.通常,带带有UNION,MINUS,INTERSECT的的SQL语语句都都可以以用其他他方式式重写写.例如:低效:SELECTDISTINCTDEPT_NO,DEPT_NAMEFROMDEPTD,EMPEWHERED.DEPT_NO=E.DEPT_NO高效:SELECTDEPT_NO,DEPT_NAMEFROMDEPTDWHEREEXISTS(SELECT‘‘X’’FROMEMPEWHEREE.DEPT_NO=D.DEPT_NO);用EXISTS替换换DISTINCT用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;ExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE10TABLEACCESS(BYINDEXROWID)OF'MULTIINDEXUSAGE'21INDEX(RANGESCAN)OF'MULTINDEX'(NON-UNIQUE)SQL>select*frommultiindexusagewhereindb=1;ExecutionPlan0SELECTSTATEMENTOptimizer=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自自动动对对列列进进行行简简单单的类类型型转转换换.假设设USER_NO是一一个个字字符符类类型型的的索索引引列列.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列使使用索索引，，而第第一种种查询询则不不能使使用索索引。。如果有有其它它办法法，不不要使使用子子查询询。用TRUNCATE替代代DELETE当删除除表中中的记记录时时,在在通常常情况况下,回回滚段段(rollbacksegments)用用来来存放放可以以被恢恢复的的信息息.如如果果你没没有COMMIT事事务,ORACLE会将将数据据恢复复到删删除之之前的的状态态(准准确地说说是恢恢复到到执行行删除除命令令之前前的状状况)而当运用TRUNCATE时时,回滚滚段不再存存放任何可可被恢复的的信息.当命命令运行后后,数据不不能被恢复复.因此很很少的资源源被调用,执行时间也也会很短.比如有的表表PHONE_NO字段是CHAR型型,而且创创建有索引引，但在WHERE条件件中忘记了了加引号，，就不会用用到索引。。字符型字段段的引号优化EXPORT和和IMPORT使用较大的的BUFFER(比比如10MB,10,240,000)可可以提高EXPORT和IMPORT的速度;ORACLE将尽可可能地获取取你所指定定的内存大大小,即使使在内存不满足,也也不会报错错.这个值值至少要和和表中最大大的列相当当,否则列值会被截截断;**优化Tools**优化器与执执行计划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条记记录,这种种需求在很很多分页语语句的需求中中会碰到.Autotrace解读Currentmode:对于修改的的数据从数数据段中读读Read-consistentmode:读一致性模模式Physicalblock:物理块（如如8192字节）Recursivecalls:嵌套调用次次数在SQLPLUS配配置AUTOTRACE在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调调整的目标标去掉不必要要的大型表表的全表扫扫描。缓存小型表表的全表扫扫描。校验优化索索引的使用用。检验优化的的连接技术术。以上目标任任务将占据据SQL调调整90%以上的工工作。理论上来说说比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;QueryPlanSELECTSTATEMENT[CHOOSE]Cost=3HASHJOINTABLEACCESSFULLDEPTTABLEACCESSFULLEMP在哪种情情况下用用哪种连连接方法法比较好好：排序--合合并连连接(SortMergeJoin,SMJ)：：对于非等等值连接接，这种种连接方方式的效效率是比比较高的的。如果在关关联的列列上都有有索引，，效果更更好。对将2个个较大的的rowsource做连连接，该该连接方方法比NL连接接要好些些。但是如果果sortmerge返回回的rowsource过过大，则则又会导导致使用用过多的rowid在表中中查询数数据时，，数据库库性能下下降，因因为过多多的I/O。嵌套循环环(NestedLoops,NL)：：如果drivingrowsource(外外部表)比较小小，并且且在innerrowsource(内部表表)上有有唯一索索引，或或有高选选择性非非唯一索索引时，，使用这这种方法法可以得得到较好好的效率率。NESTEDLOOPS有有其它连连接方法法没有的的的一个个优点是是：可以以先返回回已经连连接的的行，而而不必等等待所有有的连接接操作处处理完才才返回数数据，这这可以实实现快速速的响应应时间。。总结在哪种情情况下用用哪种连连接方法法比较好好：哈希连接接(HashJoin,HJ)：此方法是是在oracle7后后来引入入的，使使用了比比较先进进的连接接理论，，其效率应应该好于于其它2种连接接，但是是这种连连接只能能用在CBO优优化器中中，而且且需要设设置合适适的hash_area_size参数，，才能取得较好好的性能能。在2个较较大的rowsource之间连连接时会会取得相相对较好好的效率率，在一一个rowsource较较小时则则能取得得更好的的效率。。只能用于于等值连连接中总结要为一个个语句生生成执行行计划，，可以有有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是是上面面所说的的分析用用户如何产生生执行计计划2)．用用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;2)．用用explainplan命令以上2种种方法只只能为在在本会话话中正在在运行的的语句产产生执行行计划，，即我们们需要已已经知道道了哪条条语句运运行的效效率很差差，我们们是有目目的只对对这条SQL语语句去优优化。在在很多情情况下，，我们只只会听一一个客户户抱怨说说现在系系统运行行很慢，，而我们们不知道道是哪个个SQL引起的的。此时时有许多多现成的的语句可可以找出出耗费资资源比较较多的语语句，如如：SELECTADDRESS,substr(SQL_TEXT,1,20)Text,buffer_gets,executions,buffer_gets/executionsAVGFROMv$sqlareaWHEREexecutions>0ANDbuffer_gets>100000ORDERBY5;从而对找出的的语句进行进进一步优化。。还可以为一一个正在运行行的会话中运运行的所有SQL语句生生成执行计划划，这需要对对该会话进行行跟踪，产生生trace文件，然