ORACLE+SQL性能优化系列

CSDN-文档中心-数据库开发标题ORACLESQL性能优化系列(十四)完结篇black_snail（翻译）关键字ORACLEPERFORMANCETUNINGPL/SQL出处连接多个扫描如果你对一个列和一组有限的值进行比较,优化器可能执行多次扫描并对结果进行合并连接.举例:SELECT*FROMLODGINGWHEREMANAGERIN(‘BILLGATES’,’KENMULLER’);优化器可能将它转换成以下形式SELECT*FROMLODGINGWHEREMANAGER=‘BILLGATES’ORMANAGER=’KENMULLER’;当选择执行路径时,优化器可能对每个条件采用LODGING$MANAGER上的索引范围扫描.返回的ROWID用来访问LODGING表的记录(通过TABLEACCESSBYROWID的方式).最后两组记录以连接(CONCATENATION)的形式被组合成一个单一的集合.ExplainPlan:SELECTSTATEMENTOptimizer=CHOOSECONCATENATIONTABLEACCESS(BYINDEXROWID)OFLODGINGINDEX(RANGESCAN)OFLODGING$MANAGER(NON-UNIQUE)TABLEACCESS(BYINDEXROWID)OFLODGINGINDEX(RANGESCAN)OFLODGING$MANAGER(NON-UNIQUE)译者按:本节和第37节似乎有矛盾之处.47.CBO下使用更具选择性的索引基于成本的优化器(CBO,Cost-BasedOptimizer)对索引的选择性进行判断来决定索引的使用是否能提高效率.如果索引有很高的选择性,那就是说对于每个不重复的索引键值,只对应数量很少的记录.比如,表中共有100条记录而其中有80个不重复的索引键值.这个索引的选择性就是80/100=0.8.选择性越高,通过索引键值检索出的记录就越少.如果索引的选择性很低,检索数据就需要大量的索引范围查询操作和ROWID访问表的操作.也许会比全表扫描的效率更低.译者按:下列经验请参阅:a.如果检索数据量超过30%的表中记录数.使用索引将没有显著的效率提高.b.在特定情况下,使用索引也许会比全表扫描慢,但这是同一个数量级上的区别.而通常情况下,使用索引比全表扫描要块几倍乃至几千倍!48.避免使用耗费资源的操作带有DISTINCT,UNION,MINUS,INTERSECT,ORDERBY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能.DISTINCT需要一次排序操作,而其他的至少需要执行两次排序.例如,一个UNION查询,其中每个查询都带有GROUPBY子句,GROUPBY会触发嵌入排序(NESTEDSORT);这样,每个查询需要执行一次排序,然后在执行UNION时,又一个唯一排序(SORTUNIQUE)操作被执行而且它只能在前面的嵌入排序结束后才能开始执行.嵌入的排序的深度会大大影响查询的效率.通常,带有UNION,MINUS,INTERSECT的SQL语句都可以用其他方式重写.译者按:如果你的数据库的SORT_AREA_SIZE调配得好,使用UNION,MINUS,INTERSECT也是可以考虑的,毕竟它们的可读性很强49.优化GROUPBY提高GROUPBY语句的效率,可以通过将不需要的记录在GROUPBY之前过滤掉.下面两个查询返回相同结果但第二个明显就快了许多.低效:SELECTJOB,AVG(SAL)FROMEMPGROUPJOBHAVINGJOB=‘PRESIDENT’ORJOB=‘MANAGER’高效:SELECTJOB,AVG(SAL)FROMEMPWHEREJOB=‘PRESIDENT’ORJOB=‘MANAGER’GROUPJOB译者按:本节和14节相同.可略过.50.使用日期当使用日期是,需要注意如果有超过5位小数加到日期上,这个日期会进到下一天!例如:1.SELECTTO_DATE(‘01-JAN-93’+.99999)FROMDUAL;Returns:’01-JAN-9323:59:59’2.SELECTTO_DATE(‘01-JAN-93’+.999999)FROMDUAL;Returns:’02-JAN-9300:00:00’译者按:虽然本节和SQL性能优化没有关系,但是作者的功力可见一斑51.使用显式的游标(CURSORs)使用隐式的游标,将会执行两次操作.第一次检索记录,第二次检查TOOMANYROWS这个exception.而显式游标不执行第二次操作.52.优化EXPORT和IMPORT使用较大的BUFFER(比如10MB,10,240,000)可以提高EXPORT和IMPORT的速度.ORACLE将尽可能地获取你所指定的内存大小,即使在内存不满足,也不会报错.这个值至少要和表中最大的列相当,否则列值会被截断.译者按:可以肯定的是,增加BUFFER会大大提高EXPORT,IMPORT的效率.(曾经碰到过一个CASE,增加BUFFER后,IMPORT/EXPORT快了10倍!)作者可能犯了一个错误:“这个值至少要和表中最大的列相当,否则列值会被截断.“其中最大的列也许是指最大的记录大小.关于EXPORT/IMPORT的优化,CSDN论坛中有一些总结性的贴子,比如关于BUFFER参数,COMMIT参数等等,详情请查.53.分离表和索引总是将你的表和索引建立在不同的表空间内(TABLESPACES).决不要将不属于ORACLE内部系统的对象存放到SYSTEM表空间里.同时,确保数据表空间和索引表空间置于不同的硬盘上.译者按:“同时,确保数据表空间和索引表空间置与不同的硬盘上.”可能改为如下更为准确“同时,确保数据表空间和索引表空间置与不同的硬盘控制卡控制的硬盘上.”(全文完)[e-mail]4/Sep/2003个人小结－－－1.{优化器}1)查看ORACLE优化模式以SYSTEM身份进入SQL*PLUS，运行'SHOWPARAMETEROPTIMIZER_MODE'。ORACLEV7以来缺省的设置应是CHOOSE，即如果对已分析的表查询的话选择CBO，否则选择RBO。2)要想使用好CBO(优化器Optimizer推荐的方式)，使SQL语句发挥最大效能，必须保证统计数据的及时性：(1)表的统计信息是否存在--表统计信息SELECT*FROMUSER_TABLESWHERETABLE_NAME='A01'ANDNUM_ROWSISNOTNULL;--索引统计信息SELECT*FROMUSER_INDEXESWHERETABLE_NAME='A01'ANDNUM_ROWSISNOTNULL;(2'')单个表及其索引ANALYZETABLEA01COMPUTESTATISTICS;ANALYZETABLEA01COMPUTESTATISTICSFORALLINDEXES;(2'')用户模式下的全部表及表索引Analyze脚本内容如下：sqlplusscott/tiger<<EOFsetpagesize5000setheadingoffSPOOLANALYTAB.SQLSELECT'ANALYZETABLELDMIS.'¦¦TABLE_NAME¦¦'COMPUTESTATISTICS;'FROMUSER_TABLES;SPOOLOFFSPOOLANALYIND.SQLSELECT'ANALYZETABLELDMIS.'¦¦TABLE_NAME¦¦'COMPUTESTATISTICSFORALLINDEXES;'FROMUSER_TABLES;[e-mail]@ANALYIND.SQLSPOOLOFFEXIT2.{ORACLE9i新语法}自己习惯使用的语法：(1)条件逻辑语法CASE...END使用CASE...END函数来减少扫描表的次数。如运用于GROUPBY和ORDERBY子句中.(2)表连接语法INNERJOIN/LEFTJOIN/RIGHTJOIN3.{COMMIT大写字母}只要有可能,(1)尽量多使用COMMIT,以释放资源、提高性能。同时注意事务完整性。(2)尽量使用大写字母。4.{子查询}1)转换(1)子查询的IN/EXISTS方式------------->内联接方式.(2)子查询的NOTIN方式--------->NOTEXISTS2)支持多列比较和多列括起来一并修改。减少了对子表的访问次数。5.{索引}(1)用索引提高效率,但要注意使用索引是要付出代价的。实际上,不必要的索引反而会使查询反应时间变慢.而且，定期的重构索引是有必要的.ALTERINDEX<INDEXNAME>REBUILD;(2)具选择性的索引：每个不重复的索引键值,只对应数量很少的记录。(键值数/记录数)趋近于1最好。(3)索引的访问模式索引唯一扫描(INDEXUNIQUESCAN)索引范围查询(INDEXRANGESCAN)(4)如果被检索返回的列包括在INDEX列中,ORACLE就不必通过ROWID访问表。效率将极大提高。(5)忽略索引的WHERE子句索引列所对应值的第一个字符由通配符(如%)开始,索引将不被采用。WHERE子句中对索引列进行空值比较,将使ORACLE停用该索引。因为:空值不存在于索引列中,所以:建立唯一性索引的列中可以插入多条整个键值为空的记录。‘!=’将不使用索引。(6)索引的引用顺序[参考EXPLAINPLAN]当WHERE子句中索引列和常量比较时，唯一性索引的等级高于非唯一性索引.如果索引列和其他表的索引类相比较.这种子句在优化器中的等级是非常低的.如果相同表中两个相同等级的索引将被引用,WHERE子句中最前面引用的索引将有最高的优先级.6.{建立在多个列上的索引}(1)前导列一定是使用最频繁的列。(2)只有在它的第一个列(leadingcolumn)被where子句引用时,优化器才会选择使用该索引.7.{让ORDERBY使用索引}让ORDERBY使用索引(包括多列索引)，可大大提高查询效率。ORDERBY子句只有在满足下面的条件下才能使用索引：i)ORDERBY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.ii)ORDERBY中所有的列中必须至少有一列定义为非空NOTNULL.8.耗费资源的操作DISTINCT/UNION/ORDERBY标题ORACLESQL性能优化系列(十三)black_snail（翻译）关键字ORACLEPERFORMANCETUNINGPL/SQL出处子句中所使用的索引不能并列.例如:表DEPT包含以下列:DEPT_CODEPKNOTNULLDEPT_DESCNOTNULLDEPT_TYPENULL非唯一性的索引(DEPT_TYPE)低效:(索引不被使用)SELECTDEPT_CODEFROMDEPTORDERBYDEPT_TYPEEXPLAINPLAN:SORTORDERBYTABLEACCESSFULL高效:(使用索引)SELECTDEPT_CODEFROMDEPTWHEREDEPT_TYPE0EXPLAINPLAN:TABLEACCESSBYROWIDONEMPINDEXRANGESCANONDEPT_IDX译者按:ORDERBY也能使用索引!这的确是个容易被忽视的知识点.我们来验证一下:SQLselect*fromemporderbyempno;E