Oracle9i 应用系统优化.docx

i、优化应用系筑方案制定选确，对应用系统运行环境分析合理.正确，在数超阵效劳落性能、存隹空间.网络带宽等方面的配置能蟠到达系统运行要求.2、优化目标响应时间与吞吐量平金临界安源2.1 响废HR1.与吞吐量平春根据应用类型的不同.性能优化的目标不同：在找事务於理系琉O1.TP）把吞吐量定义为性能指标：决策支持系施（DSS）把响应对属定义为性能指标。响度品同响应时间=效劳时间+等待时间泉籍吞吐量系统吞吐费指在给定的叶门内所完成的工作量.有以下两种技末：以相后的资源来完成更多的工作（减少效劳时间）：通过减少整个响应时何来更快完成工作.0*W出竞争增强的时候,某个任务的效劳时间也许保持不变，但它的等待时间将增长.我们开发的系统一般为O1.TP和DSS的复合票统，恻曳于MTP.在硬件允许的情况下最好能够将运行航据库.分析效据库别解.2.2 临界费源诸如CRR内存.1/0#*.网络带宽等费理，都是慎少时同的关健因素.性能好坏取决于以下因素：可用资源的数量福要该费处的客户力的数目客户方等待充源所消耗的时间客户保挣资源的时间长空随着请求单元的增加，效劳行同也增枷,为了处理这种情形,用户可以边挣：通过展制请求的速率.从而维护可接殳响应时间还可通过埴扣咨源软目，如CpV和硬登（忖和资源的前提是应用系观设计良好.并且已羟破了充分的优化3,优化阶段从实际破的工程过程东价.除了系统安装优化外.系统优化往往都是在系统实德、运行时才考隹.箕实到这阶段做系笠优化的居限性比较大，因为系统架构设计和成型.固化，大幅度调整设计的代价乖常昂贵，一般只能在局部领域做优化，只能蟠过重新分配内存或优化I/O来或多或少地提高性能.实际上优化应该篁穿系玩设计、开发.安装、测试、运行整个过程.3.1 设计阶段为了到达最正瑜的效果，优化工作应当从设计阶段进行，而不是在系统实施后进行.在数据库设计阶段，个人认为需要注意如下几个方面：业务对象不能建立在桑统表空间：除引表空间和业务表空司分开：将1.OB类组的字段与其它的类组分开：泯据应用系统功能确定是否要采用冗余字段：正确的主键字段的选择.建议采用致字,不推花便用复合主键：3.2 开发、涌M阶段在开发实现阶段,个人认力常要注意如下几个方面：执行SqI使用交绑友的方式，尽可能的保存在共享内存中，提高SqI命中率：S大关联查询时杲用有效的连接顺序：尽可能的降低客户珑和效劳器的网络数据交互，某个业务功能点我要i«繁和数据卑交互的，迩议采用存储过程、估时表实现；根据查询条件迂立必更的索射.麦询条件中使用orac1.e西数陇立相对应的函效索引.盘据值范围较小的采用位图求引多张表关联走沟时，有时可采用先查询符合条件对应的表中关健字.线后通过关父字再查询对应表中相关信息；续繁访问.较少更新的疑据Jt较小的表信息可采用圾存的方式；在实现蚣量更新、插入时,要聚国jdbeit查执行方法.并且调整就应的fetchsize参数。在测试阶段.应该模拟实际运行环境.混试出相关性能较差的功能点。因为在设计.开发阶段往往因为并发用户少.故据5M、，很多性能问题显现不出来,如果软件更试充分.笈多性能问题都可以显现出来,现在有很多优秀的就件测试工具，1.oadRunner.Robert在傲区力测试方面和比校方便、化秀.尽量挎系统因程序设计、编礴不当导致的性能间曩基贰在测试阶段.3.3 安裳阶段一般在安装生产敛据库时，我们根据桑观景早的规划，集合软.硬件环境，雷要调整操作系统以及敛据库参数，3.3.1 抵停案就交排区交换区是OraC1.e的一项根本的要求.可以根据OraCie的发行要求来确定。一般交换区大小的要求是该效劳器内存的2倍至4倍之间，然也是内存的4倍3.3.2 黑体系统内横套敷Shn1.U1.X共享内存戌,珑说设大点,到达最大SGAShiHiin最小的共享内存段.Shiinni共享内存标志符的敷盘.shmseg一个进程可分配的最大内存段数.Shna1.1.最大可允许竹内存数.比SGA迂要大.SEnnSt.JRORAC1.EWPROCESS*Seff1.I1.S1.一个信号量中最大的信号量数.3.3.3 orac1.e文件设置当效劳舞平台已完成操作系统的安.装后，就应该开始认真的考虑下面的同题：是否系用摄设各实际应用的生产系筑根本都是采用棵设备，使用裸谀备对于谈号频繁的纹抠库应用来说，可以按大地提而数据库系统的性能，安装点的考由OraC1.e的安装点就是指数据文件，日志文件和控制文件的安置路径.为了使系统在以后运行性能到达优化,建议将数据文件.E)志文件和控制文件的安Jt路径与数据库系统存故在不同的路径上.最好将数据文件.日志文件和控制文件分别存放在不同的路径。SYSTEM表空同时应他据文件在自定义安袋会话中,建议你根据第要设置SyStem表空间所片应的数据文件的大小.一般要设置比既认值的2陆。该数据文件的大小最好是在300WJ至500MB网.必为数S；文件大小不利于系统的运行.临时表空间对应的数据文件临时表空同对应的数第文件可以根据将未系燎存放的应用的处理情况来定.比方系统将来可能要径常进桎排序处理，则雷要设置较大的临时表空危，也可能需要再赛立新的临时表空间，这里珑议啮时表空何的数裾文件在100MB至30OiB左右。回滚段表空间对应的数据文件9i回液表空同箱是系统管理.初始值也是根据系统事务费慎信计的值.实际到运行阶段如果系烧常出曳。H1.-(H555错误的时侯，可能就需要漕如回该表空低的大小.日志文弁的大小日志文件的大小对于OraCIe系统的运行也是相当重要.默认值是大小.实际根据串务繁忙便估计日志大小,没有囹定的具体值范图,建议做日志切换叶问不能过烟也不能过长.一般在2040分钟左右.该泰敛可以在系统运行期何根据奴据库系统日志切换时间更新调整,控制文件的大小。数据库茨的大小如果你的应用袈境是O1.TP的话,可以采用较小的数据库块,如果是DSS类笈的应用系统,则可以设置较大的数据库块，目前OraC1.C产品所允许的数据库块可以是2KB至81KB之何.无论你选择较大的块或较小的块，它的像和必须是2的整数信.比方2048Z096.8192年.但第住注意的是,如果操作系统为64佳，即可选势较大的块。字好袋的选绛字符集是OraC1.e系洗专门支持的一项技术.洋!请靠考另外的章节.一歌不要与另外的已授存就的OraCIe系统的字符集产生冲突卬可.但如果你的环境是一个新的平台，不务要-与其它平台进行敛据交换的话，理议选择取认的字符集。这样可以利于将来的修改。3.3.4敷*鼻启动，敷Sgomaxsize例程存活期何所占用的系统全局区的最大大小，一般为物理内存的I/2-1/3shoredPOO1.size指定共享池的大小.共享池包含：共享卦标、存储的过程,控制结构和并行执行消息矮冲区等对象.段大的值用于改善多跖户系统的性能，谟参新词整不能过大，会增后管理负坦和IatCh的开销,一般是在20QM-500M左右db_cache_size该叁依指定数推场冲区的大小,原则上时越大越好.取代了8i中的dbb1.ocksizedbb1.ockbuffers1.og-buffer更做日志婕冲区大小.该参数设置大没有意义，Orac1.eIog1.buffer最大为CPU_cOUrn束以128KB或512KB中量大1processes系统用户进程的最大数量，该卷敷设置为系统景繁忙时估计并发用户ftIarge_poo1._size如果不设置WS*RMAN,OPQ会使用到.但是在IOM-50V!该差不多TO可以考虑为session(sort-area-size+2M)«Javapoo1.size它用于存放java代码，若不使用java,线仪设置为3(M1.pga_aggregate_targetHriw1.siaiisiics程序全局区大小.1 .对于OiTP系境PGA_aggregate_trrget=(<otaiPhySiCa1.Memory>*80%)*20%2 .对于USS系就PGAAGGREGATETARGET=<<Tota1.Physica1.Menory>»80¾)50建议将Iimed.Statisiics设置为InJe,否则无法查看到准确的统计信息(9i版本后的设置为true对系统性能影晌较小.千分之一)上述参政根本是初始估计值,在运行阶段可能会根据实际运行情况再调整,3.4 运行防段这也是实际优化工作最多的阶段,个人认为运行阶我优化的真正工作是解决因为女毋运行数据库叁数谀置不当.表，索引统计信息不准确，执行路径不当等导致的性能问题。优化工作应该作为日常工作的一局部,而不是等到用户反映条统慢,系统宕机计才去优化，那时巳蛀是亡羊补牢，为时有点晚，从实除工程来看,往往梆是应用程序编写的sq1.表.索引统计信息不准确,执行路径不当而导致的性能问题，个人认为一般的SqI调优迁是有章可循的.根本三步：查找、分析.优化。3.4.14«3.4.1.1 *实时妻找查找工具自用的就是StatSpaCk.该工具的安装、使用比较简便。脚本路径$orac1.ehome"rdbsadmin目录下.常用脚本如下Jspdrop.SqI剑除脚本，丢弃统计分析的相关包.视图、表.同义词等对象（苜次创立无须执行）Spcreate.Sq1.自立脚本.生成统计分析他相关包、视图.表、荒义词等对.象（首次机行前跳软创立一个统计用的表空用）Spreport.Sq1.生成报告记录sq1.生成的报告文件在累统当前路径下，文件名酰认为：S1.开拈快照号结束快照号.Isisprepsq1.Sq1.分析相关快照中的Sq1.执行方案.SPpUrRe.sq1.创除在两个快照号之间包若本身的所有统计分析数据.sptrunc.SQ1.截果Sta1.SPaCk统计分析的相关蚊据在统计分析的对应用户Perfstat下执行执行时间：统讦结果视图：统计时生成两次快照.一般在30-40分钟左右StatsSsnapshot快照相关信息；se1.ectsnap_id.snap.timefromStatsSsnapshot;StatsSsq1.iext快照统计sq1.信息.查询统计sq1.（statspack报告中sq1.Ii长会被截修）se1.ectSq1.ICX1.froStatsJsq1.tcxtwhereh<sh.va1.uo=4j（fiund1.astsnapid=beginsnapidorderbypiece；执行方法：用SyS将林Sq1.p1.us后间肠对应时间机行两次execStatspack.snap；3.4.1.2 实H左执如果常要实时的瓷找桎能隐患的相关sq1.itvSsession«ait.v$session.vjsq1textwithnew1.ines三张动态视图就可以根本查找到相关的Sq1,脚本如下：se1.ectsq1._tx?xt.sw.eventfromv$sq1.text_wi1.h_nev1.inesst.vjsessionse.v$session_vitSWwherest.address=se.sq1.addressandst.hash_va1.ue=se.sq1.hashvaIueandSaSid=Sagidand(sv.event=*bufferbusywaits'orsw.event=*enqueue,orsw.event=,freebufferwaits*orsw.event='g1.oba1.cachefree1.istwait*orsw.event='1.atchfree*orsw.event=*1.ogbufferspace*orsw.event='para1.1.e1.queryqref1.atch*orsw.event=*pipeput*orsw.event=,writecomp1.etewai1.s*orsw.event1.ike,1.ibrarycache%,orsw.event1.ike*1.og')orderbyst.hashva1.ue.st.piece：3.4.2分析分析报告个人一般主要关注1.g5eveni以及相关的读逻辑块、物理块.执行次豌较多的sq1.实际上更多的值宣在sq1.分析上_殷常见的Sp5件知下;dbread等待事件，一般问题出现在试像引上.建议将业务表空间和索引表空姆分开存储在不同的甥理卷下,以提而膻盘的I/O性能.dbread建置程序中尽贵肪止使用金表扫描的语旬，或者可以增大db一的他.根商仝表扫拓一次试取数据块的速度,网少点盘I/O.dbwritei明DBXR进程正等待把缓冲区的内容并行写入数据文件中去.等待将一直挣续到所有的I/O全部完成.健我增大初始化参政中的db.uiter_processes的值.1.og说明任何闿候一个事甥提交时.它将通知1.GUR搂1.OGBurER写入日志文件,如果此局部占用时向校长，应流少COWIT的次勃，健议将宠做日志放到我怏的?龙上进行存储。1.ogwrite等待事件,和上面一样建议将重做日志放到较快的碳密上进行存储.提取出Sq1.以后就可以进行分析,主要采用分析执行方案的方式.个人一般耳戏如下的方式进行分析:生或方案表（初次）以SyS用户执行B本$orac1.eJIOme)rdbBsadmi11Ut1.XPIan.sq1.创立公用同义词,方便在每个用户下生成执行方案（初次）Createpub1.icsynonymp1.antab1.eforp1.antab1.e;Granta1.1onp1.antab1.etopub1.ic：每次分析时设置SqIP1.US环境变量Se1.tiningonSetautocracetraceon1.y查看相关Sq1.执行方案其他客户用软件p1./sq】deve1.oper.toad分析执行方案都比较方便。执行方案珞径解择Fu1.1Tab1.eScans全表扫描.无可用求引IndexUniqueScans索引唯一扫描IndexRangeScans家引箔围扫描IndexRangeScansDescending便引降序范IS扫描IndexSkipScans索引跳跃朽描Fu1.1.Scans全索引扫描FastFu1.1.IndexScans供电全索引扫指IndexJoins东引连接BitmapJoins位图逐捶常见连接解铎:Nested1.ps会循环外表（驱动或），逐个比对和内表的拒接是否符合条件.基于Cost的Orac1.e优化界（CBO）会自动选择较小的表儆外表。优点：在系动匕衣一，制表北校大，而且内外表的连接列行便引的时候比较好，钱去循环连接比其他连接方法有优势,它可以快速地从结果集中提琰第一批记荥,而不用等待整个结果很完全破定下来.耗点：如果内部行源表（读取的第二张表（内表）巳连接的列上不包含索引.或者索引不是高度可选时,架套循环迷接效率是很低的.SORT-mergeJOIN将两表的连接列各自排序然后合并,只能用于连捶列相等的情况，适合两表大小相若的情况化点：在缺乏数据的迭择性或者可用的索引时.或者两个源表都过于庞大（超过记录我的52时，摔序今并连接将比嵌套循环连更加高效.局柔性：排列合并注接只能用于等价连接排列台并连接需要临时的内存块.以用于林序（如果SOR1.AREVSIZE设置得大小的话）。这将导致在将时表空间占用更多的内存和花盘I/OHASHJOIN在其中一表的连接列上作敬列.因此只有另外一个表酸排序合并.只有叁于代价的优化器才可以使用哈希连接。当缺少有用的索引时，哈希连接比嵌套循环连接更如有效。哈希速接可能比排序合并连接更快.哈将连接佗用内存资源，并且当用于扑序内存缺乏时，会增H曲时表空间的I/O(这将使这种连接方法逑度交得极慢3.4.3优化OraCIe运行阶段优化的更多是对Sq1.的优化，个人理解工作主要是：分析性能较差sq1.:调整性能较差的31的实现方式，林助程序员更改相关程序；对相关的交询条件M立合理的索引；根据需要合理的更新表、索引的程序信息；3.4.3.1orac1.e优化器优化器优化方式Orac1.e的优化器共有两种的优化方式，即基于规WJ的优化方式(RUIC-BaSCdOinimization,简称为RBO)和基于代价的优化方式(COS1.BiISedOptimization,简称为CBO).A. RBo方式：优化器在分析SQ1.语句时,所遵循的是OraC1.e内部预定的一些妮则.比方我们常见的.当一个Where十句中的一列有索引片去走索引。B. CBO方式：依词义可知,它是看语句的代价(Cost)1.这里的代价主要指CPU和内存。优化器在判斯是否用这种方式时,主要叁照的是表及索引的统计信息.统计佶息给出表的大小、有少行、等行的长度警信息。这些统计信息起初在库内是没有的,是你在做ana1.yze后才出现的.很多的时侯过期统计信息会令优化界做出一个精误的执行方案.因些我们应及时更新这些信息.在OraC1.e8及以后的板本,Orac1.e推希用CBO的方式。优化器的优化模式(Op1.eEizerMode)RU1.e：走基于境则的方式.ChOoSe：默认的情及下OraCIC用的是这种方式,不跳议修改该参数.指的是当一个表或索引有统计佶息.则走CBo的方式,如果表或索引没统计信息.表又不是特别的小,而且相应的列有索引时,那么就走集弓I.走RBo的方式.FirstRoWS:它与ChoOSe方式建类似的.所不同的是当一个表有统计信息时，它将是以最快的方式返回受询的最先的几行,从总体上戒少了响应时间.AIIRgS:也就是我们所说的Cbst的方式,当一个表有统计信息时，它将以最快的方式返回表的所有的行.从总体上提商查询的吞吐量。没有统计信息则走基于规则的方式。3.4.3.2常见优化问题明明有索引，表的战招型也非常大，执行路径不走索引对应表、索引的统计信息有误，可以递itdba_tab1.es.dbaindexes视图中的num_ro,S查桁对应表，宗引的统计信息,如果有误.！&新统计.Ana1.yzetabictab1.e_namecomputestatisticsFortab1.e统计表/Fora1.1indexedco1.umns*统计有索引的表列*/统计后性能反而变差虽然OraCIC推着呆用CBO方式,但有时首应的执行路径并不是最正确,所以我们在统计信息时只看针对性的统计楣关表、索引佶息.一般有两种处理方法a.删除对应的统计信息Ana1.yzetabictab1.e_namede1.etestatisticsAna1.yzeindexindexnamede1.etestatistics对应的系统包dbms_StatS也可实现生成、删除表.索引的统计信息b,使用hints明确指定对应的执行路径3.4.3.3hints常用的几种hints如下:HintSyntaxDescription优化方式*÷1.1.R(WS/说明对语句块选择基于开铺的优化方法.并我律最正确吞吐量.便先说消耗最小化/*CHoOSE/说明如果数据字典中有访问我的统计信息,将基于开销的优化方法.并变得最正确的吞吐量：说明如果数抠字典中没有访问表的统计信息，将基于规则开销的优化方法:/*-FIRSTRUWS/说明对语句块在择基于开箱的优化方法,弁获得最正由响应时间.使资源济利最小化，在做分页查询时有时可以改善性能*RU1.E/说明对语句块选择基于炮则的优化方法处理方法2AM1.EQvR1.(Iab1.Cindex)/提示明确进行执行规*的选择,将几个单列索引的扫描合起来.SE1.ECT/*HNDEX_FFS(BSEMPMSIN_DPTNo.IN_EMPNo.I工SEX)WFRoMBSEMPMSImEREEMPNO='SCOIT'NDDPTNO=,TDC306'ZCIrSTER(tab1.e)/Exp1.icit1.ychoosesac1.usterscantoaccessthespecifiedtab1.e./*+R1.1.(tab1.e)/说明对我选择全局扫描的方法SE1.J-CT/*+FU1.1.(A>*/EM1.1.No.EMPNMFROMBSEMPMSAWHEREEMP-NO='SCOT,/*+INDEX(tab1.eindex)/说明对表选择便引的扫描方法.SE1.ECT/*+INDEX(RSEMPMSSEXNDEX)USESEXINDEXBECAUSETIOEAREFEM1.EBSEMPMS/FROMBSEMpMSUHERESEX='M'/>INDEX.ASc(tab1.eindex)/说明对表选择像引升序的扫描方法QINDtXCOMBINE(tabIeindex)/为指定表选择位国访问路经，如果INDEXCOMBINE中没有提供作为叁效的索弓I.将选择出位图东引的布尔组合方式.例如:SE1.ECT/+INDEX工OMB1.NE(BSEMP>SAjBM1.HIRIDTEBMD/I-ROMBS1.MIjMSWHERES1.<5000000ANDHIREDATE/*INDEXDESC(tab1.eindex)/说明对表选择索引隹序的扫描方法.*÷1XDEX,F1S(tab1.eindex)*/对指定的表执行快速全索引扫赧.而不是生表扫描的方法一*÷NO.INDEX(tabIeindex)/说明优化器不采用扫描相对应的索引*÷R(WID(tab1.e)/明用说明对指定表根据RoW1.Dii行访问.SE1.ECT+ROWID(BSEMPMS)*/»FROMRSEMP1.MSWHEREROWIDA'AAAAAAAAAA,ANDEMP_NG='SCOTT'；/*+ISE_CONCAT*/对证询勺的*HERE后百的OR条件遂行转换为IMONAU.的组合查询.建及绥件/*USEJIAS1.Ktab1.e)*/件指定的表与其也行源通迂哈希连接方式,遍接起来.SE1.ECT/*+HSE_HASII(BSEMPMS.BSDPTMS)/*FROMUSIiMPMS.BSDFMSWHIiRI-BSI-MIjMS.WTNO=BSDPTMS.DR1.'NO；ZISE-MERGEdab1.c)/件指定的表与箕他行阻通过今并排序更接方式连接起来.SE1.ECT*USEM1.RGE(BSEMPMS.BSDFI-MS)/FROMBSEMPMS.BSDPTMSWHEREBSKM1.5MS.DPTNO=BSDPTMS.DPTNO；/*-1.SE_N1.(tab1.e)*/忤指定表与衽套的连接的行海逆行连接.井杷指定表作为内部表.