《ORACLE_SQL性能优化(内部培训资料)6472.docx》由会员分享，可在线阅读，更多相关《ORACLE_SQL性能优化(内部培训资料)6472.docx（77页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、ORACLE SQL性能优化系列 (一) 1. 选用适合的ORACLE优化器 ORACLEE的优化器共共有3种:a. RULEE (基于规规则) b. COSTT (基于成成本) c. CHOOOSE (选选择性) 设置缺省的优优化器,可以以通过对innit.orra文件中OOPTIMIIZER_MMODE参数数的各种声明明,如RULLE,COSST,CHOOOSE,AALL_ROOWS,FIIRST_RROWS . 你当然也也在SQL句句级或是会话话(sesssion)级级对其进行覆覆盖.为了使用基于成成本的优化器器(CBO, Costt-Baseed Opttimizeer) , 你必须经
2、常常运行anaalyze 命令,以增增加数据库中中的对象统计计信息(obbject statiisticss)的准确性性.如果数据库的优优化器模式设设置为选择性性(CHOOOSE),那那么实际的优优化器模式将将和是否运行行过anallyze命令令有关. 如如果tablle已经被aanalyzze过, 优优化器模式将将自动成为CCBO , 反之,数据据库将采用RRULE形式式的优化器. 在缺省情况下下,ORACCLE采用CCHOOSEE优化器, 为了避免那那些不必要的的全表扫描(full tablee scann) , 你你必须尽量避避免使用CHHOOSE优优化器,而直直接采用基于于规则或者基基
3、于成本的优优化器. 2. 访问TTable的的方式 ORACLEE 采用两种种访问表中记记录的方式: a. 全表扫扫描全表扫描就是顺顺序地访问表表中每条记录录. ORAACLE采用用一次读入多多个数据块(databbase bblock)的方式优化化全表扫描. b. 通过RROWID访访问表你可以采用基于于ROWIDD的访问方式式情况,提高高访问表的效效率, , ROWIDD包含了表中中记录的物理理位置信息.ORACCLE采用索索引(INDDEX)实现现了数据和存存放数据的物物理位置(RROWID)之间的联系系. 通常索索引提供了快快速访问ROOWID的方方法,因此那那些基于索引引列的查询就就
4、可以得到性性能上的提高高. 3. 共享SSQL语句 为了不重复解解析相同的SSQL语句,在第一次解解析之后, ORACLLE将SQLL语句存放在在内存中.这这块位于系统统全局区域SSGA(syystem globaal areea)的共享享池(shaared bbufferr pooll)中的内存存可以被所有有的数据库用用户共享. 因此,当你你执行一个SSQL语句(有时被称为为一个游标)时,如果它它和之前的执执行过的语句句完全相同, ORACCLE就能很很快获得已经经被解析的语语句以及最好好的执行路径径. ORAACLE的这这个功能大大大地提高了SSQL的执行行性能并节省省了内存的使使用.可惜
5、的的是ORACCLE只对简简单的表提供供高速缓冲(cachee buffferingg) ,这个个功能并不适适用于多表连连接查询.数据库管理员必必须在iniit.oraa中为这个区区域设置合适适的参数,当当这个内存区区域越大,就就可以保留更更多的语句,当然被共享享的可能性也也就越大了.当你向ORACCLE 提交交一个SQLL语句,ORRACLE会会首先在这块块内存中查找找相同的语句句.这里需要注明的的是,ORAACLE对两两者采取的是是一种严格匹匹配,要达成成共享,SQQL语句必须须完全相同(包括空格,换行等).共享的语句必须须满足三个条条件: A. 字符级级的比较:当前被执行的语语句和共享池
6、池中的语句必必须完全相同同.例如:SELECT * FROOM EMPP;和下列每一个都都不同SELECT * froom EMPP;Select * Froom Empp;SELECT * FROOM EMPP; B. 两个语语句所指的对对象必须完全全相同:例如:用户 对象名 如何访问Jack saal_limmit prrivatee synoonymWork_ciity puublic synonnymPlant_ddetaill publlic syynonymm Jill ssal_liimit pprivatte synnonymWork_ciity puublic synonny
7、mPlant_ddetaill tablle ownner 考虑一下下列列SQL语句句能否在这两两个用户之间间共享. SQL能否共共享/原因select max(ssal_caap) frrom saal_limmit;不能每个用户都有一一个privvate ssynonyym - ssal_liimit , 它们是不不同的对象select countt(*) fromm workk_cityy wherre sdeesc liike NNEW%;能两个用户访问相相同的对象ppublicc synoonym - workk_cityyselect a.sdeesc,b.locattion ff
8、rom wwork_ccity aa , pllant_ddetaill b whhere aa.cityy_id = b.ciity_idd不能用户jack 通过priivate synonnym访问pplant_detaiil 而jiill 是表表的所有者,对象不同. C. 两个SSQL语句中中必须使用相相同的名字的的绑定变量(bind variaables) 例如： 第一组的两个个SQL语句句是相同的(可以共享),而第二组组中的两个语语句是不同的的(即使在运运行时,赋于于不同的绑定定变量相同的的值)a.select pin , namee fromm peopple whhere ppi
9、n = :blk11.pin;select pin , namee fromm peopple whhere ppin = :blk11.pin; b.select pin , namee fromm peopple whhere ppin = :blk11.ot_iind;select pin , namee fromm peopple whhere ppin = :blk11.ov_iind;4. 选择最有有效率的表名名顺序(只在在基于规则的的优化器中有有效)ORACLE的的解析器按照照从右到左的的顺序处理FFROM子句句中的表名,因此FROOM子句中写写在最后的表表(基础表 drivii
10、ng taable)将将被最先处理理. 在FRROM子句中中包含多个表表的情况下,你必须选择择记录条数最最少的表作为为基础表.当当ORACLLE处理多个个表时, 会会运用排序及及合并的方式式连接它们.首先,扫描描第一个表(FROM子子句中最后的的那个表)并并对记录进行行派序,然后后扫描第二个个表(FROOM子句中最最后第二个表表),最后将将所有从第二二个表中检索索出的记录与与第一个表中中合适记录进进行合并. 例如:表 TAB1 16,3884 条记录录表 TAB2 1 条记录录 选择TAB22作为基础表表 (最好的的方法)select countt(*) ffrom ttab1,ttab2 执
11、执行时间0.96秒 选择TAB11作为基础表表 (不佳的的方法)select countt(*) ffrom ttab2,ttab1 执执行时间266.09秒 如果有3个以以上的表连接接查询, 那那就需要选择择交叉表(iinterssectioon tabble)作为为基础表, 交叉表是指指那个被其他他表所引用的的表. 例如: EMP表描述述了LOCAATION表表和CATEEGORY表表的交集. SELECTT *FROM LLOCATIION L ,CATEGORRY C,EMP EWHERE EE.EMP_NO BEETWEENN 10000 AND 2000AND E.CCAT_NOO
12、 = C.CAT_NNOAND E.LLOCN = L.LOOCN 将比下列SQQL更有效率率 SELECTT *FROM EMMP E ,LOCATIOON L ,CATEGORRY CWHERE EE.CAT_NO = C.CATT_NOAND E.LLOCN = L.LOOCNAND E.EEMP_NOO BETWWEEN 11000 AAND 20000 5. WHEERE子句中中的连接顺序序 ORACLEE采用自下而而上的顺序解解析WHERRE子句,根根据这个原理理,表之间的的连接必须写写在其他WHHERE条件件之前, 那那些可以过滤滤掉最大数量量记录的条件件必须写在WWHERE子子
13、句的末尾. 例如: (低效,执行行时间1566.3秒)SELECT FROM EMMP EWHERE SSAL 500000AND JOBB = MMANAGEERAND 25 (SEELECT COUNTT(*) FFROM EEMPWHERE MMGR=E.EMPNOO); (高效,执行行时间10.6秒)SELECT FROM EMMP EWHERE 225 500000AND JOBB = MMANAGEER; 6. SELLECT子句句中避免使用用 * 当你想在SELLECT子句句中列出所有有的COLUUMN时,使使用动态SQQL列引用 * 是一个方方便的方法.不幸的是,这是一个非非常
14、低效的方方法. 实际际上,ORAACLE在解解析的过程中中, 会将* 依次转换换成所有的列列名, 这个个工作是通过过查询数据字字典完成的, 这意味着着将耗费更多多的时间. 7. 减少访访问数据库的的次数当执行每条SQQL语句时, ORACCLE在内部部执行了许多多工作: 解解析SQL语语句, 估算算索引的利用用率, 绑定定变量 , 读数据块等等等. 由此此可见, 减减少访问数据据库的次数 , 就能实实际上减少OORACLEE的工作量. 例如,以下有三种方法法可以检索出出雇员号等于于0342或或0291的的职员. 方法1 (最最低效)SELECT EMP_NNAME , SALAARY , GR
15、ADEEFROM EMMPWHERE EEMP_NOO = 3442; SELECTT EMP_NAME , SALLARY , GRADDEFROM EMMPWHERE EEMP_NOO = 2991; 方法2 (次次低效) DECLARRECURSOR C1 (EE_NO NNUMBERR) ISSELECT EMP_NNAME,SSALARYY,GRADDEFROM EMMPWHERE EEMP_NOO = E_NO;BEGINOPEN C11(342);FETCH CC1 INTTO ,.,. ;.OPEN C11(291);FETCH CC1 INTTO ,.,. ;CLOSE CC
16、1;END; 方法3 (高高效) SELECTT A.EMMP_NAMME , AA.SALAARY , A.GRAADE,B.EMP_NNAME , B.SAALARY , B.GGRADEFROM EMMP A,EEMP BWHERE AA.EMP_NO = 342AND B.EEMP_NOO = 2991;注意:在SQL*Pllus , SQL*FForms和和Pro*CC中重新设置置ARRAYYSIZE参参数, 可以以增加每次数数据库访问的的检索数据量量 ,建议值值为200ORACLE SQL性能能优化系列 (三) 8. 使用DEECODE函函数来减少处处理时间 使用DECOODE函数
17、可可以避免重复复扫描相同记记录或重复连连接相同的表表. 例如:SELECT COUNTT(*)，SSUM(SAAL)FROMEMMPWHERE DDEPT_NNO = 00020AND ENAAME LIIKESMITHH%; SELECTT COUNNT(*)，SSUM(SAAL)FROMEMMPWHERE DDEPT_NNO = 00030AND ENAAME LIIKESMITHH%; 你可以用DEECODE函函数高效地得得到相同结果果 SELECTT COUNNT(DECCODE(DDEPT_NNO,00220,X,NULLL) D00020_CCOUNT,COUNT(DDECODEE
18、(DEPTT_NO,00030,X,NUULL) D00300_COUNNT,SUM(DECCODE(DDEPT_NNO,00220,SALL,NULLL) D00020_SSAL,SUM(DECCODE(DDEPT_NNO,00330,SALL,NULLL) D00030_SSALFROM EMMP WHEERE ENNAME LLIKE SMITHH%; 类似的,DEECODE函函数也可以运运用于GROOUP BYY 和ORDDER BYY子句中. 9. 整合简简单,无关联联的数据库访访问 如果你有几个个简单的数据据库查询语句句,你可以把把它们整合到到一个查询中中(即使它们们之间没有关关系
19、)例如: SELECTT NAMEEFROM EMMPWHERE EEMP_NOO = 12234; SELECTT NAMEEFROM DPPTWHERE DDPT_NOO = 100 ; SELECTT NAMEEFROM CAATWHERE CCAT_TYYPE = RD; 上面的3个查查询可以被合合并成一个: SELECTT E.NAAME , D.NAMME , CC.NAMEEFROM CAAT C , DPT D , EEMP E,DUAL XWHERE NNVL(XX,X.DDUMMY) = NVVL(X,E.ROOWID(+)AND NVLL(X,X.DUMMMY) = NV
20、L(X,DD.ROWIID(+)AND NVLL(X,X.DUMMMY) = NVL(X,CC.ROWIID(+)AND E.EEMP_NOO(+) = 12344AND D.DDEPT_NNO(+) = 10AND C.CCAT_TYYPE(+) = RRD; (译者按: 虽然采取这这种方法,效效率得到提高高,但是程序序的可读性大大大降低,所所以读者 还还是要权衡之之间的利弊) 10. 删除除重复记录最高效的删除重重复记录方法法 ( 因为为使用了ROOWID) DELETEE FROMM EMP EWHERE EE.ROWIID (SELECCT MINN(X.ROOWID)FROM EMM
21、P XWHERE XX.EMP_NO = E.EMPP_NO); 12. 尽量量多使用COOMMIT 只要有可能,在程序中尽尽量多使用CCOMMITT, 这样程程序的性能得得到提高,需需求也会因为为COMMIIT所释放的的资源而减少少: COMMITT所释放的资资源:a. 回滚段上上用于恢复数数据的信息.b. 被程序语语句获得的锁锁c. redoo log buffeer 中的空空间d. ORACCLE为管理理上述3种资资源中的内部部花费 (译者按: 在使用COOMMIT时时必须要注意意到事务的完完整性,现实实中效率和事事务完整性往往往是鱼和熊熊掌不可得兼兼)ORACLE SQL性能能优化系列
22、 (四) 13. 计算记记录条数和一般的观点相相反, coount(*) 比coount(11)稍快 , 当然如果果可以通过索索引检索,对对索引列的计计数仍旧是最最快的. 例例如 COUUNT(EMMPNO) (译者按: 在CSDNN论坛中,曾曾经对此有过过相当热烈的的讨论, 作作者的观点并并不十分准确确,通过实际际的测试,上上述三种方法法并没有显著著的性能差别别) 14. 用WWhere子子句替换HAAVING子子句 避免使用HAAVING子子句, HAAVING 只会在检索索出所有记录录之后才对结结果集进行过过滤. 这个个处理需要排排序,总计等等操作. 如如果能通过WWHERE子子句限制记
23、录录的数目,那那就能减少这这方面的开销销. 例如:低效:SELECT REGIOON，AVGG(LOG_SIZE)FROM LOOCATIOONGROUP BBY REGGIONHAVING REGIOON REGGION != SYYDNEYAND REGGION != PEERTH 高效SELECT REGIOON，AVGG(LOG_SIZE)FROM LOOCATIOONWHERE RREGIONN REGIION != SYDDNEYAND REGGION != PEERTHGROUP BBY REGGION(译者按: HHAVINGG 中的条件件一般用于对对一些集合函函数的比较,如C
24、OUNNT() 等等等. 除此此而外,一般般的条件应该该写在WHEERE子句中中) 15. 减少少对表的查询询在含有子查询的的SQL语句句中,要特别别注意减少对对表的查询. 例如:低效SELECT TAB_NNAMEFROM TAABLESWHERE TTAB_NAAME = ( SELLECT TTAB_NAAMEFROM TAAB_COLLUMNSWHERE VVERSIOON = 6604)ANDDB_VER= ( SELLECT DDB_VERRFROM TAAB_COLLUMNSWHERE VVERSIOON = 6604) 高效SELECT TAB_NNAMEFROM TAABLE
25、SWHERE (TAB_NNAME,DDB_VERR)= ( SELLECT TTAB_NAAME,DBB_VER)FROM TAAB_COLLUMNSWHERE VVERSIOON = 6604) Updatee 多个Coolumn 例子:低效:UPDATE EMPSET EMPP_CAT = (SEELECT MAX(CCATEGOORY) FFROM EEMP_CAATEGORRIES),SAL_RANNGE = (SELEECT MAAX(SALL_RANGGE) FRROM EMMP_CATTEGORIIES)WHERE EEMP_DEEPT = 0020; 高效:UPDATE EM
26、PSET (EMMP_CATT, SALL_RANGGE)= (SELEECT MAAX(CATTEGORYY) , MMAX(SAAL_RANNGE)FROM EMMP_CATTEGORIIES)WHERE EEMP_DEEPT = 0020; 16. 通过过内部函数提提高SQL效效率. SELECTT H.EMMPNO,EE.ENAMME,H.HHIST_TTYPE,TT.TYPEE_DESCC,COUNNT(*)FROM HIISTORYY_TYPEE T,EMMP E,EEMP_HIISTORYY HWHERE HH.EMPNNO = EE.EMPNNOAND H.HHIST_TTYP
27、E = T.HIIST_TYYPEGROUP BBY H.EEMPNO,E.ENAAME,H.HIST_TYPE,T.TYPPE_DESSC; 通过调用下面面的函数可以以提高效率.FUNCTIOON LOOOKUP_HHIST_TTYPE(TTYP INN NUMBBER) RRETURNN VARCCHAR2ASTDESC VVARCHAAR2(300);CURSOR C1 ISSSELECT TYPE_DESCFROM HIISTORYY_TYPEEWHERE HHIST_TTYPE = TYP;BEGINOPEN C11;FETCH CC1 INTTO TDEESC;CLOSE CC1;
28、RETURN (NVL(TDESCC,?);END; FUNCTIION LOOOKUP_EMP(EEMP INN NUMBBER) RRETURNN VARCCHAR2ASENAME VVARCHAAR2(300);CURSOR C1 ISSSELECT ENAMEEFROM EMMPWHERE EEMPNO=EMP;BEGINOPEN C11;FETCH CC1 INTTO ENAAME;CLOSE CC1;RETURN (NVL(ENAMEE,?);END; SELECTT H.EMMPNO,LLOOKUPP_EMP(H.EMPPNO),H.HIST_TYPE,LOOKUUP_HISST
29、_TYPPE(H.HHIST_TTYPE),COUNTT(*)FROM EMMP_HISSTORY HGROUP BBY H.EEMPNO , H.HHIST_TTYPE; ORACLEE SQL性性能优化系列列 (六) 20. 用表连连接替换EXXISTS通常来说 , 采用表连接接的方式比EEXISTSS更有效率SELECT ENAMEEFROM EMMP EWHERE EEXISTSS (SELLECT XFROM DEEPTWHERE DDEPT_NNO = EE.DEPTT_NOAND DEPPT_CATT = AA); (更高效)SELECT ENAMEEFROM DEEPT D,E
30、MP EEWHERE EE.DEPTT_NO = D.DEEPT_NOOAND DEPPT_CATT = AA ; 21. 用EEXISTSS替换DISSTINCTT当提交一个包含含一对多表信信息(比如部部门表和雇员员表)的查询询时,避免在在SELECCT子句中使使用DISTTINCT. 一般可以以考虑用EXXIST替换换 例如:低效:SELECT DISTIINCT DDEPT_NNO,DEPPT_NAMMEFROM DEEPT D,EMP EEWHERE DD.DEPTT_NO = E.DEEPT_NOO高效:SELECT DEPT_NO,DEEPT_NAAMEFROM DEEPT DWH
31、ERE EEXISTSS ( SEELECT XFROM EMMP EWHERE EE.DEPTT_NO = D.DEEPT_NOO); EXISTSS 使查询更更为迅速,因因为RDBMMS核心模块块将在子查询询的条件一旦旦满足后,立立刻返回结果果. 22. 识别别低效执行的SQL语语句 用下列SQLL工具找出低低效SQL: SELECTT EXECCUTIONNS , DDISK_RREADS, BUFFFER_GEETS,ROUND(BUFFEER_GETTS-DISSK_REAADS)/BBUFFERR_GETSS,2) HHit_raadio,ROUND(DDISK_RREADS/EX
32、ECUUTIONSS,2) RReads_per_rrun,SQL_TEXXTFROM V$SQLARREAWHERE EEXECUTTIONS0AND BUFFFER_GGETS 0AND (BUUFFER_GETS-DISK_READSS)/BUFFFER_GGETS llist1 SELECCT *2 FROM dept, emp3* WHERRE empp.depttno = dept.deptnnoSQL seet auttotracce on exp; /*traaceonlly 可以不不显示执行结结果*/或者SQL set aautotrrace ttraceoonly eexp
33、;SQL /14 rowss seleected.Executiion Pllan-0 SELECCT STAATEMENNT Opttimizeer=CHOOOSE1 0 NESSTED LLOOPS2 1 TABBLE ACCCESS (FULLL) OF EMP3 1 TABBLE ACCCESS (BY IINDEX ROWIDD) OF DEPTT4 3 INDDEX (UUNIQUEE SCANN) OF PK_DDEPT (UNIQQUE) Statisstics-0 recurrsive callss2 db bllock ggets30 conssistennt getts0
34、 physiical rreads0 redo size2598 byytes ssent vvia SQQL*Nett to cclientt503 byttes reeceiveed viaa SQL*Net ffrom cclientt2 SQL*NNet rooundtrrips tto/froom cliient0 sortss (memmory)0 sortss (dissk)14 rowss proccessedd 通过以上分析析,可以得出出实际的执行行步骤是:1. TABLLE ACCCESS (FULL) OF EMP2. INDEEX (UNNIQUE SCAN) OF P
35、K_DEEPT (UNIQUUE)3. TABLLE ACCCESS (BY INNDEX RROWID) OF DEPT4. NESTTED LOOOPS (JOINIING 1 AND 33) ORACLEE SQL性性能优化系列列 (八) 25. 用索引引提高效率 索引是表的一一个概念部分分,用来提高高检索数据的的效率. 实实际上,ORRACLE使使用了一个复复杂的自平衡衡B-treee结构. 通常,通过过索引查询数数据比全表扫扫描要快. 当ORACCLE找出执执行查询和UUpdatee语句的最佳佳路径时, ORACLLE优化器将将使用索引. 同样在联联结多个表时时使用索引也也可以提高效
36、效率. 另一一个使用索引引的好处是,它提供了主主键(priimary key)的的唯一性验证证.除了那些LONNG或LONNG RAWW数据类型, 你可以索索引几乎所有有的列. 通通常, 在大大型表中使用用索引特别有有效. 当然然,你也会发发现, 在扫扫描小表时,使用索引同同样能提高效效率.虽然使用索引能能得到查询效效率的提高,但是我们也也必须注意到到它的代价. 索引需要要空间来存储,也需要定定期维护, 每当有记录录在表中增减减或索引列被被修改时, 索引本身也也会被修改. 这意味着着每条记录的的INSERRT , DDELETEE , UPPDATE将将为此多付出出4 , 55 次的磁盘盘I/
37、O . 因为索引引需要额外的的存储空间和和处理,那些些不必要的索索引反而会使使查询反应时时间变慢.译者按:定期的重构索引引是有必要的的.ALTER IINDEX REBBUILD 26. 索引引的操作ORACLE对对索引有两种种访问模式. 索引唯一扫描描 ( INNDEX UUNIQUEE SCANN) 大多数情况下下, 优化器器通过WHEERE子句访访问INDEEX. 例如:表LODGINNG有两个索索引 : 建建立在LODDGING列列上的唯一性性索引LODDGING_PK和建立立在MANAAGER列上上的非唯一性性索引LODDGING$MANAGGER. SELECTT *FROM LO
38、ODGINGGWHERE LLODGINNG = ROSE HILL; 在内部 , 上述SQLL将被分成两两步执行, 首先 , LODGIING_PKK 索引将通通过索引唯一一扫描的方式式被访问 , 获得相对对应的ROWWID, 通通过ROWIID访问表的的方式执行下下一步检索.如果被检索返回回的列包括在在INDEXX列中,ORRACLE将将不执行第二二步的处理(通过ROWWID访问表表). 因为为检索数据保保存在索引中中, 单单访访问索引就可可以完全满足足查询结果.下面SQL只需需要INDEEX UNIIQUE SSCAN 操操作. SELECTT LODGGINGFROM LOODGING
39、GWHERE LLODGINNG = ROSE HILL; 索引范围查询询(INDEEX RANNGE SCCAN)适用于两种情况况:1. 基于一个个范围的检索索2. 基于非唯唯一性索引的的检索 例1: SELECTT LODGGINGFROM LOODGINGGWHERE LLODGINNG LIKKE M%; WHERE子子句条件包括括一系列值, ORACCLE将通过过索引范围查查询的方式查查询LODGGING_PPK . 由由于索引范围围查询将返回回一组值, 它的效率就就要比索引唯唯一扫描低一一些. 例2: SELECTT LODGGINGFROM LOODGINGGWHERE MMANAGEER = BILL GATESS; 这个SQL的的执行分两步步, LODDGING$MANAGGER的索引引范围查询(得到所有符符合条件记录录的ROWIID) 和下下一步同过RROWID访访问表得到LLODGINNG列的值. 由于LOODGINGG$MANAAGER是一一个非唯一性性的索引,数数据库不能对对它执行索引引唯一扫描