ORACLE-SQL性能优化(内部培训资料).docx

ORAC1.ESQ1.性能优化系列(一)1,选用适合的ORAC1.E优化器ORAC1.E的优化法共有3种:a.RU1.E(基于规则)b.COST(基于本饯)c.CHOOSE(选择性)设置缺省的优化舞.可以通过对init.ore文件中OPTINIZERJioDE参数的各种声明.如RU1.E.COST.Q1.OOSh：.A1.1.RoWS.FIRST.R(WS.你当然也在SQ1.句圾或是会话(session)级对其进行/是.为了使用基于本钱的优化器(CB0,Cost-BasedOpti.izer),你必须经常运行ana1.yze命令.以箫加数据库中的对象统计信息(ObjeCtstatistics)的准确性.如果数据康的优化器模式设置为选择性(Q1.oOSE),那么实际的优化器模式将和是否运行过ana1.yze命令有关.如果tab1.e巳垃械ana1.y次过,优化器模或将自动成为CBO,反之,数据库朽采用RU1.E形式的优化器.在缺省情况下,0RAC1.E采用CHOoSE优化器,为了防止那些不必要的全表扫描(fu1.1.tab1.escan).你必须尽量防止使用C1I00SE优化器,而直接采用基于规则或者基于本钱的优化器.2 .访问Tab1.o的方式ORAC1.E杲用为种访问表中记录的方式：a.全表归播全表扫描就是顺序地访问表中每条记录.ORAC1.E采用一次读入多个数据块(databaseI)IoCk)的方式优化全表扫1) .通过ROW1.D访问表你可以采用梦于R(W1.D的访问方式情况.提高访问表的效率.，ROUID包含了表中记录的物理位置信息.ORAC1.E采用密引(INDEX)实现了数据和存放数据的物理位置(ROinD)之间的联系.通常索引提供了快速访问Ro1.n)的方法,因此那些基于东引列的丧谓就可以得到性能上的荏商.3 .共享SQ1.语句为了不查复解析相同的SQ1.语句.在第一次解析之后,ORAC1.E将SQ1.语句存放在内存中.这块位于系统全局区域SGA(systcg1.oba1.area)的共享池(Sharedbufferpoo1.)中的内存可以被所有的我据库用户共享.因此.当你执行一个SQ1.语句(有时被称为一个势标)时.如果它和之前的执行过的语句完全相同，ORAC1.E就能很快荻得已经祓解析的语句以及最好的执行路径.ORAC1.E的这个功能大大地提高了SQ1.的执行牲能并节省了内存的使用.可惜的是ORAaE只对简隼的麦提供高速短冲(Caehebuffering).这个功能并不适用于多表连接登询.联票库管理员必须在init.ora中为这个区域设置适克的叁数.当这个内存区域越大,就可以保存更多的语句,当然被共享的可能性也就越大了.当你向ORAC1.E提交一个SQ1.语句.ORAC1.E会首先在这块内存中查找相同的语句.这里常要注明的是.ORAC1.E时的者采取的是一种严格匹配.要达成共享.SQ1.语句必须完全相同（包括空格,换行等）.共享的语句必须满足三个条件：A.字符级的比较：当前被执行的话句和共享池中的语句必须完全相P1.例如：SE1.ECT*FROMEMP;和以下每一个都不同SE1.ECT*fromBMP：Se1.ectFromEmp；SE1.ECT*FROMEMP;B.两个语句所指的对猊必须完全相同：例如：用户对款名如何访问JackSa1.1.imitprivatesynonymWorkcitypub1.icsynonymPIanJdetai1.pub1.icsynonymJi1.1.sa1._1.imitprivatesynonymWork_citypub1.icsynonymP1.antdetai1.tab1.eowner考虑一下以下SQ1.语句能否在这苒个用户之同共享.SQ1.能否排享/原因不能每个用户都有一个PriVatesynonym-Sa1.1.imit,它f1.是不同的对"象se1.ectcount(0fromworkcitywhereSdesC1ike'NEW;能两个用户访问相同的对"象pub1.icsynony-Work_Cityse1.ecta.sdesc,b.1.ocationfromworkcitya.p1.antdetai1.bwherea.cityid=b.cityid不能用户jack通过PriVateSynOnyW访同PIanJdetai1.而ji1.1.是表的所有者.龙条不同.C.两个SQ1.语句中必须隹用相同的名字的绑定变量(bindvariab1.es)例如：第一缎的两个SQ1.语句是相同的（可以共享）,而第二道中的两个语句是不同的（即使在运行时.戒于不同的绑定变量相同的值）a.se1.ectpin.namefrompeop1.ewherepin=：b1.k1.pin：se1.ectpin,namefrompeop1.ewherepin=：b1.k1.pin：se1.ectpin.namefrompeop1.ewherepin=：b1.k1.ot-ind：se1.ectpin.namefrompeop1.ewherepin=：b1.k1.ovind：4 .选择最有效率的表名顺序（只在基于规需的优化器中有效）ORAC1.E的解析器按照从右到左的联序处理FROM子句中的表名,因此FROM子句中写在最后的赛（基础表drivingU1.b1.e）将被最先处理.在FRoM子句中包含多个表的情况下,你必须迷择记录条败最少的表作为基布,表.当ORAC1.E处理多个表时,会运用排序及合并的方式连接它们.苜先.扫描第一个表（FROM子句中款后的那个表）并附记录进行派序,然后扫描第二个表（FROM于旬中最后第二个表），最后将所有从第二个表中检索出的记录与第一个表中适宜记录送行合并.例如:表TAB1.16.384条记录表TAB21条记录选挣TAB2作为梦死我（最好的方法）选择TAB2作为基犯表(不侏的方法)se1.ectcount()fromtab2,tub1.执行时间26.09秒如果有3个以上的表连接衣酒.那就济要选隹交叉表(intersectiontab1.e)作为基地表.交叉表是指那个被其他表所引用的表.例如：网P表描述了1.OCATION表和CATEGORY表的交集.SE1.ECT*FROM1.OCATION1.CATEGORYC.EMPEWHEREEEMP_NoBETreEN100OAND2000ANDE.CAT_N0=C.CAT_NOANDE.1.oCN=1.1.OCN将比以下SQ1.更有效率SE1.ECTFROMEMPE.1.OCATIONI.CATEGORYCWHEREE.CAT_NO=C.CAT_NOAM)E.1.OCN=1.1.OCNANDE.EM1.'NOBETWEEN1000AND20005 .WHERE子句中的连接通序.ORAe1.E采用自下而上的顺序解析ImERE子句,根据这个原理,表之间的连接必须写在其他ImERE条件之前.那些可以过逑掉最大数量记录的条件必须写在U1.1.ERE子句的末尾.例如：(低效.执行时间156.3秒)SE1.ECTFROMEMPEWHERESA1.>50000ANDJOB=NGER'AND25<(SE1.ECTCOVNT()1.ROMEMpWHEREMGR=EEMPNO)；(高效.执行时间106秒)SE1.ECTFROMEMPEWHERE25<(SE1.ECTCOUNT()FROMEMPWHEREMGR=E.EMPW)ANDSA1.>50000ANDJOB=,MANAGER'6 .SE1.ECT子句中防止使用'*,当你想在SEUieT十句中列出所有的Co1.wN时.笠用动态SQ1.列引用是一个方便的方法.不幸的是,这是一个非常低效的方法.实际上,0RAC1.E在鳏析的过程中.会将',依次转换成所有的列名，这个工作是通过查询数密字典完成的,这意味着挎消耗更多的时间.7 .减少访问数据库的次数当执行每至SQ1.语句时，0RAC1.E在内部执行了许多工作：解析SQ1.语句，估算索引的利用率.绑定变量,读数据块等等.由此可见.减少访问数据库的次数.就能实际上减少ORAC1.E的工作I1.例如,以下有三种方法可以检索出居员号等于0342或0291的职员.方法1(最低效)SE1.ECTEMi1.NAME.SA1.ARY,GRADEFROMEMPWHEREEMP_NO=342;SE1.ECTEMP_NAME.SA1.ARY.GRADEFROMEMI,WHEREEMPNO=291；方法2(次低效)DEC1.ARECURSORC1.(E-NONVMBER)ISSE1.ECTEMPNAME.SA1.ARY.GRADEFROMEMPWHEREEMPNO=E_NO；BEGINOPENC1.(342)；FETCHC1.INrO：OPENC1.(291):FETCHC1.IWO-：C1.OSEC1.：END；方法3(高效)SE1.ECT.BIPKAME.A.SA1.ARY.A.GRADE.B.EMP.NAME.B.SA1.ARY.B.GRADE!ROMEMP.EMPBWHEREA.EMPJiO=342ANDB.EMP_NO=291;注意：SQ1.P1.us.SQ1.*For三s和ProC中更新设置ARRAYSIZE参数,可以增如每次数据乐访问的检索数据量，空议值为200ORAC1.ESQ1.性能优化系列(三)8 .使用DECot)E函数来减少处理时间使用DECoDE函数可以防止更复扫描相同记录或重复挂接相间的表.例如：SE1.ECTCOUW(»).SUM(SA1.)FROMEMPWHEREDEPT-NO=0020ANDENAME1.IKE,SMITH%'：SE1.ECTCO1.WT().SUM(SA1.)FROMEMPWHEREDEPT-No=0030ANDENAME1.IKE'SMIT1I,；你可以用DECoDE函数高效地得到相同结果SE1.ECTCO1.NT(DECODE(DEPTNO.0020.'X,.NU1.1.)D0020COUNT.C(X)NTECoDE(DEP1.M0030.'X,.NU1.1.)1.)0030_COUNT.SUM(DECOt)E(DEPTKO.0020.SA1.NU1.1.)IX>020S1.SUM(DECOf)E(DBPT_NO,0030.SA1.NU1.1.)1.)0030.S1.FROMEMPIHEREENAME1.IKE4SMIBft':类似的.DECODE函数也可以运用于GR(XJPBY和ORDERBY子句中.9 .整台简单.无关联的数据库访问如果你有几个简单的数据库交询语句，你可以把它们整合到一个查询中(即使它们之间没有关系)例如：SE1.ECTNAMEFROMEMPWHEREEMPNO=1234；SE1.ECTNAMEFROMDPTWHEREDBTNO=10:SE1.ECTNAMEFROMCATWHERECAT11PE=,RD'；上面的3个查询可以被合并成一个：SE1.ECTE.NMIE.D.NAME.C.NAMEFROMCATC.DPTD.EMP