Oracle9i 应用系统优化

1、1、优化前提应用系统方案制定准确，对应用系统运行环境分析合理、正确，在数据库服务器性能、存储空间、网络带宽等方面的配置能够达到系统运行要求. 2、优化目标响应时间与吞吐吐量平衡临界资源2.1 响应时时间与吞吐量量平衡根据应用类型的的不同，性能能优化的目标标不同：在线事务处理系系统OLTPP）把吞吐量量定义为性能能指标；决策支持系统（DSS）把响应时间定义为性能指标。响应时间响应时间=服务务时间+等待时间系统吞吐量系统吞吐量指在在给定的时间间内所完成的的工作量。有有以下两种技技术：以相同的资源来来完成更多的的工作（减少少服务时间）；通过减少整个响响应时间来更更快完成工作作。等待时间当竞争增强的时

2、时候，某个任任务的服务时时间也许保持持不变，但它它的等待时间间将增长。我们开发的系统统一般为OLLTP和DSS的复合合系统，侧重重于OLTPP,在硬件允允许的情况下下最好能够将将运行数据库库、分析数据据库分离。2.2 临界资资源诸如 CPU、内内存、I/OO容量、网络络带宽等资源源，都是减少少时间的关键键因素。性能能好坏取决于于以下因素：可用资源的数量量需要该资源的客客户方的数目目客户方等待资源源所消耗的时时间客户保持资源的的时间长短随着请求单元的的增加，服务务时间也增加加。为了处理理这种情形，用用户可以选择择：通过限制请求的的速率，从而而维护可接受受响应时间还可通过增加资资源数目，如如CPU

3、和硬盘盘（增加资源源的前提是应应用系统设计计良好，并且且已经做了充充分的优化）3、优化阶段从实际做的项目目过程来看，除除了系统安装装优化外，系系统优化往往往都是在系统统实施、运行行时才考虑，其其实到这阶段段做系统优化化的局限性比比较大，因为为系统架构设设计都成型、固固化，大幅度度调整设计的的代价非常昂昂贵，一般只只能在局部领领域做优化，只只能通过重新新分配内存或或优化I/OO来或多或少少地提高性能能，实际上优优化应该贯穿穿系统设计、开开发、安装、测测试、运行整整个过程。3.1 设计阶阶段为了达到最佳的的效果，优化化工作应当从从设计阶段进进行，而不是是在系统实施施后进行。在数据库设计阶阶段，个人

4、认认为需要注意意如下几个方方面：业务对象不能建建立在系统表表空间；索引表空间和业业务表空间分分开；将LOB类型的的字段与其它它的类型分开开；根据应用系统功功能确定是否否要采用冗余余字段；正确的主键字段段的选择，建建议采用数字字，不推荐使使用复合主键键；3.2 开发、测测试阶段在开发实现阶段段，个人认为为需要注意如如下几个方面面：执行sql使用用变量绑定的的方式，尽可可能的保留在在共享内存中中，提高sqql命中率；多表关联查询时时采用有效的的连接顺序；尽可能的降低客客户端和服务务器的网络数数据交互，某某个业务功能能点需要频繁繁和数据库交交互的，建议议采用存储过过程、临时表表实现；根据查询条件建建

5、立必要的索索引，查询条条件中使用ooraclee函数建立相相对应的函数数索引,数据值范围围较小的采用用位图索引多张表关联查询询时，有时可可采用先查询询符合条件对对应的表中关关键字，然后后通过关键字字再查询对应应表中相关信信息；频繁访问，较少少更新的数据据量较小的表表信息可采用用缓存的方式式；在实现批量更新新、插入时，要要采用jdbc批量执行行方法，并且且调整对应的的fetchhsize参参数。在测试阶段，应应该模拟实际际运行环境，测测试出相关性性能较差的功功能点。因为在设计、开开发阶段往往往因为并发用用户少、数据据量小，很多多性能问题显显现不出来，如如果软件测试试充分，很多多性能问题都都可以显

6、现出出来，现在有有很多优秀的的软件测试工工具，如LooadRunnner、Roberrt在做压力力测试方面都都比较方便、优优秀。尽量将系统因程程序设计、编编码不当导致致的性能问题题暴露在测试试阶段。3.3 安装阶阶段一般在安装生产产数据库时，我我们根据系统统最早的规划划，集合软、硬硬件环境，需需要调整操作作系统以及数数据库参数，3.3.1操作作系统交换区区交换区是Oraacle的一一项基本的要要求。可以根根据Oraccle的发行行要求来确定定。一般交换换区大小的要要求是该服务务器内存的22倍至4倍之间，建建议是内存的的4倍3.3.2操作作系统内核参参数shmmax 共享内存段，建建议设大点，

7、达到最大SSGAshmmin 最小的共享内存存段.shmmni 共享内存标志符符的数量.shmseg 一个进程可分配配的最大内存存段数.shmall 最大可允许的内内存数，比SSGA还要大大.semmns信号量，跟ORRACLE的的PROCEESS数有关关.semmsl 一个信号量中最最大的信号量量数.3.3.3 ooraclee 文件设置置当服务器平台已已完成操作系系统的安装后后，就应该开开始认真的考考虑下面的问问题：是否采用裸设备备实际应用的生产产系统基本都都是采用裸设设备，使用裸裸设备对于读读写频繁的数数据库应用来来说，可以极极大地提高数数据库系统的的性能。安装点的考虑Oracle的的安

8、装点就是是指数据文件件、日志文件件和控制文件件的安置路径径，为了使系系统在以后运运行性能达到到优化，建议议将数据文件件、日志文件件和控制文件件的安置路径径与数据库系系统存放在不不同的路径上上。最好将数数据文件、日日志文件和控控制文件分别别存放在不同同的路径。SYSTEM表表空间对应数数据文件在自定义安装会会话中，建议议你根据需要要设置sysstem表空空间所对应的的数据文件的的大小。一般般要设置比默默认值的2倍。该数据据文件的大小小最好是在3300MB至至500MBB间。因为数数据文件太小小不利于系统统的运行。临时表空间对应应的数据文件件临时表空间对应应的数据文件件可以根据将将来系统存放放的应

9、用的处处理情况来定定。比如系统统将来可能要要经常进程排排序处理，则则需要设置较较大的临时表表空间，也可可能需要再建建立新的临时时表空间。这这里建议临时时表空间的数数据文件在1100MB至至300MBB左右。回滚段表空间对对应的数据文文件9i回滚表空间间都是系统管管理，初始值值也是根据系系统事务量预预估计的值，实实际到运行阶阶段如果系统统常出现orra-015555错误的的时候，可能能就需要增加加回滚表空间间的大小。日志文件的大小小日志文件的大小小对于Oraacle系统统的运行也是是相当重要。默默认值是太小小。实际根据据事务繁忙预预估计日志大大小，没有固固定的具体值值范围，建议议重做日志切切换时

10、间不能能过短也不能能过长，一般般在2040分钟左右右。该参数可可以在系统运运行期间根据据数据库系统统日志切换时时间重新调整整，控制文件件的大小。数据库块的大小小如果你的应用系系统是OLTTP的话，可可以采用较小小的数据库块块。如果是DDSS类型的的应用系统，则则可以设置较较大的数据库库块，目前OOraclee产品所允许许的数据库块块可以是2KKB至64KB之间间。无论你选选择较大的块块或较小的块块，它的值都都必须是2的整数倍，比比如20488,40966,81922等。但需要要注意的是，如如果操作系统统为64位，则可可选择较大的的块。字符集的选择字符集是Oraacle系统统专门支持的的一项技术

11、。详详细请参考另另外的章节。一一般不要与另另外的已经存存放的Oraacle系统统的字符集产产生冲突即可可。但如果你你的环境是一一个新的平台台，不需要与与其它平台进进行数据交换换的话，建议议选择默认的的字符集。这这样可以利于于将来的修改改。3.3.4数据据库启动参数数sga_maxx_sizee例程存活期间所所占用的系统统全局区的最最大大小，一一般为物理内内存的1/22-1/3shared_pool_size指定共享池的大大小，共享池池包含：共享享游标、存储储的过程、控控制结构和并并行执行消息息缓冲区等对对象，较大的的值用于改善善多用户系统统的性能，该该参数调整不不能过大，会会增加管理负负担和l

12、attch 的开开销，一般是是在200MM-500MM左右db_cachhe_sizze该参数指定数据据缓冲区的大大小，原则上上时越大越好好，取代了88i中的dbb_blocck_sizze * ddb_bloock_buuffersslog_bufffer重做日志缓冲区区大小，该参参数设置大没没有意义，OOraclee推荐logg_bufffer最大为为cpu_ccount乘乘以128KKB或5122KB中最大大值processses系统用户进程的的最大数量，该该参数设置为为系统最繁忙忙时估计并发发用户数large_ppool_ssize 如果不设置MTTS，通常在在 RMANN 、OPQQ

13、 会使用到到，但是在110M -50M应该该差不多了。可可以考虑为 sessiion * (sortt_areaa_sizee + 2MM)。Java_poool_siize 它用于存放jaava代码，若若不使用jaava，建议议设置为300Mpga_agggregatte_tarrget程序全局区大小小,1.对于于OLTP系系统PGA_AGGREEGATE_TARGEET = （ * 80%） * 20% 2.对于于DSS系统统PGA_AAGGREGGATE_TTARGETT = （ * 80%） * 50%timed_sstatisstics建议将timeed_staatistiics 设

14、置置为truee,否则无法法查看到准确确的统计信息息（9i版本后的的设置为trrue对系统统性能影响较较小，千分之之一）上述参数基本是是初始估计值值，在运行阶阶段可能会根根据实际运行行情况再调整整。3.4 运行阶阶段这也是实际优化化工作最多的的阶段，个人人认为运行阶阶段优化的真真正工作是解解决因为实际际运行数据库库参数设置不不当、表、索索引统计信息息不准确，执执行路径不当当等导致的性性能问题。优化工作应该作作为日常工作作的一部分，而而不是等到用用户反映系统统慢，系统宕宕机时才去优优化，那时已已经是亡羊补补牢，为时有有点晚，从实实际项目来看看，往往都是是应用程序编编写的sqll、表、索引引统计信

15、息不不准确，执行行路径不当而而导致的性能能问题，个人人认为一般的的sql调优优还是有章可可循的,基本本三步： 查查找、分析、优优化。3.4.1查找找3.4.1.11 非实时查查找查找工具常用的的就是staatspacck,该工具具的安装、使使用比较简便便。脚本路径径$oraacle_hhome/rdbmss/admiin目录下，常常用脚本如下下：spdrop.sql 删除脚本，丢弃弃统计分析的的相关包、视视图、表、同同义词等对象象(首次创建建无须执行)spcreatte.sqll创建脚本，生成成统计分析的的相关包、视视图、表、同同义词等对象象（首次执行行前建议创建建一个统计用用的表空间）spr

16、eporrt.sqll 生成报告记录ssql ,生生成的报告文文件在系统当当前路径下，文文件名默认为为：sp_开开始快照号_结束快照号号.lst sprepsqql.sqll 分析相关快照中中的sql执执行计划。sppurgee.sql 删除在两个快照照号之间包括括本身的所有有统计分析数数据。sptruncc.sql 截取statsspack统统计分析的相相关数据 在在统计分析的的对应用户pperfsttat下执行行执行时间：统计时生成两次次快照，一般般在30-440分钟左右右执行方法：用sys登登陆sqlpplus后间间隔对应时间间执行两次 exec statsspack.snap;统计结果

17、视图：stats$ssnapshhot快照相关信息； seleect snnap_idd,snapp_timee fromm statts$snaapshott;stats$ssqltexxt快照统计sqll信息,查询询统计sqll(stattspackk报告中sqql过长会被被截掉）seelect sql_ttext ffrom sstats$sqlteext whhere hhash_vvalue=查询值 aand laast_snnap_idd=begiin_snaap_id orderr by ppiece;3.4.1.22 实时查找找如果需要实时的的查找性能隐隐患的相关ssql,通过

18、过v$sesssion_wait,v$sesssion,v$sqlltext_with_newliines三张张动态视图就就可以基本查查找到相关的的sql，脚脚本如下：select sql_ttext ,sw.evvent from v$sqlltext_with_newliines sst,v$ssessioon se,v$sesssion_wait sw wheree st.aaddresss=se.sql_aaddresss andd st.hhash_vvalue=se.sqql_hassh_vallueandd se.ssid =ssw.sidd and (sw.eevent = b

19、uuffer busy waitss orssw.eveent = enquueue orsw.eventt = ffree bbufferr waitts orrsw.evvent = gloobal ccache freellist wwait orsw.eventt = llatch free orsww.evennt = log bbufferr spacce orrsw.evvent = parrallell querry qreef lattch oorsw.eevent = piipe puut orrsw.evvent = wriite coomplette waiits oo

20、rsw.eevent like librrary ccache% orssw.eveent liike llog fiile swwitch% ) oorder by stt.hashh_valuue,st.piecee; 3.4.2分析析分析报告个人一一般主要关注注top 55 evennt以及相关关的读逻辑块块、物理块、执执行次数较多多的sql,实际上更多多的侧重在ssql分析上上一般常见的toop 5 事事件如下：db filee sequuentiaal reaad等待事件，一般般问题出现在在读索引上，建建议将业务表表空间和索引引表空间分开开存储在不同同的物理卷下下，以提高磁磁盘的I/

21、OO性能。db filee scattteredd readd建议程序中尽量量避免使用全全表扫描的语语句，或者可可以增大dbb_filee_multtiblocck_reaad_couunt的值，提提高全表扫描描一次读取数数据块的速度度，减少磁盘盘I/O。db filee paraallel writee说明DBWR进进程正等待把把缓冲区的内内容并行写入入数据文件中中去,等待将将一直持续到到所有的I/O全部完成成。建议增大大初始化参数数中的db_writeer_proocessees的值。log fille synnc说明任何时候一一个事物提交交时，它将通通知LGWRR将LOG_BUFFEER

22、写入日志志文件，如果果此部分占用用时间较长，应应减少COMMMIT的次次数，建议将将重做日志放放到较快的磁磁盘上进行存存储。log fille parrallell writte等待事件，和上上面一样建议议将重做日志志放到较快的的磁盘上进行行存储。提取出sql以以后就可以进进行分析，主主要采用分析析执行计划的的方式。个人人一般喜欢如如下的方式进进行分析：生成计划表（初初次）以sys用户执执行脚本$oraclle_homme/rddbms/aadmin/utlxpplan.ssql，创建公用同义词词，方便在每每个用户下生生成执行计划划（初次）Create publiic synnonym pla

23、n_tablee for plan_tablee;Grant aall onn plann_tablle to publiic;每次分析时设置置sqlpllus环境变变量Set timming oonSet auttotracce traaceonlly查看相关sqll执行计划其他客户端软件件pl/sqql devvelopeer,toaad 分析执执行计划都比比较方便。执行计划路径解解释Full Taable SScans全表扫描、无可可用索引Index UUniquee Scanns索引唯一扫描IndexRaange SScans索引范围扫描IndexRaange SScans Desce

24、endingg索引降序范围扫扫描Index SSkip SScans索引跳跃扫描Full Sccans全索引扫描Fast Fuull Inndex SScans快速全索引扫描描Index JJoins索引连接Bitmap Joinss位图连接常见连接解释：Nested Loopss会循环外表（驱驱动表），逐逐个比对和内内表的连接是是否符合条件件。基于Coost的Orracle优优化器(CBBO)会自动动选择较小的的表做外表。优点：在驱动表表比较小，内内表比较大，而而且内外表的的连接列有索索引的时候比比较好，嵌套套循环连接比比其他连接方方法有优势,它可以快速速地从结果集集中提取第一一批记录,而而

25、不用等待整整个结果集完完全确定下来来。缺点：如如果内部行源源表(读取的的第二张表（内内表）已连接接的列上不包包含索引,或或者索引不是是高度可选时时, 嵌套循循环连接效率率是很低的。SORT- mmerge JOIN将两表的连接列列各自排序然然后合并，只只能用于连接接列相等的情情况，适合两两表大小相若若的情况 优点：在缺乏数数据的选择性性或者可用的的索引时,或或者两个源表表都过于庞大大(超过记录录数的5%)时,排序合合并连接将比比嵌套循环连连更加高效。局限性：排列合并连接只能用于等价连接排列合并连接需要临时的内存块,以用于排序( 如果SORT_AREA_SIZE设置得太小的话)。这将导致在临时表

26、空间占用更多的内存和磁盘I/OHASH JOOIN在其中一表的连连接列上作散散列，因此只只有另外一个个表做排序合合并，只有基基于代价的优优化器才可以以使用哈希连连接。当缺少有用的索索引时，哈希希连接比嵌套套循环连接更更加有效。哈哈希连接可能能比排序合并并连接更快，哈哈希连接使用用内存资源，并并且当用于排排序内存不足足时，会增加加临时表空间间的I/O（这这将使这种连连接方法速度度变得极慢3.4.3优化化 Orracle运运行阶段优化化的更多是对对sql的优化化，个人理解解工作主要是是： 分析性能较较差sql;调整性能较差的的sql的实现现方式，协助助程序员更改改相关程序;对相关的查询条条件建立合

27、理理的索引;根据需要合理的的更新表、索索引的程序信信息;3.4.3.11 oraccle 优化化器 优化器器优化方式Oracle的的优化器共有有两种的优化化方式,即基基于规则的优优化方式(RRule-BBased Optimmizatiion,简称称为RBO)和基于代价价的优化方式式(Costt-Baseed Opttimizaation,简称为CBBO)。A、RBO方式式：优化器在在分析SQLL语句时,所所遵循的是OOraclee内部预定的的一些规则。比比如我们常见见的,当一个个wheree子句中的一一列有索引时时去走索引。B、CBO方式式：依词义可可知,它是看看语句的代价价(Costt)了

28、,这里里的代价主要要指Cpu和和内存。优化化器在判断是是否用这种方方式时,主要要参照的是表表及索引的统统计信息。统统计信息给出出表的大小、有有少行、每行行的长度等信信息。这些统统计信息起初初在库内是没没有的,是你你在做anaalyze后后才出现的,很多的时侯侯过期统计信信息会令优化化器做出一个个错误的执行行计划,因些些我们应及时时更新这些信信息。在Orracle88及以后的版版本,Oraacle推荐荐用CBO的的方式。 优化器器的优化模式式(Opteermizeer Modde)Rule:走基基于规则的方方式。Choose:默认的情况况下Oraccle用的是是这种方式，不不建议修改该该参数。指

29、的的是当一个表表或索引有统统计信息,则走CBO的方式式,如果表或索索引没统计信信息,表又不是特特别的小,而且相应的的列有索引时时,那么就走索索引,走RBO的方式式。First RRows:它它与Chooose方式是是类似的,所不同的是是当一个表有有统计信息时时,它将是以最最快的方式返返回查询的最最先的几行,从总体上减减少了响应时时间。All Rowws:也就是是我们所说的的Cost的方方式,当一个表有有统计信息时时,它将以最快快的方式返回回表的所有的的行,从总体上提提高查询的吞吞吐量。没有有统计信息则则走基于规则则的方式。3.4.3.22 常见优化化问题 明明有索索引，表的数数据量也非常常大，

30、执行路路径不走索引引 对应表、索索引的统计信信息有误，可可以通过dbba_tabbles,ddba_inndexess视图中的nuum_rowws查看对应应表、索引的的统计信息，如如果有误，重重新统计。 Anallyze ttable tablee_namee comppute sstatisstics For tablee /*统统计表*/ For all iindexeed collumns /*统计计有索引的表表列*/ 统计后性性能反而变差差 虽然oraccle推荐采采用CBO方式，但但有时对应的的执行路径并并不是最佳，所所 以我们在在统计信息时时只有针对性性的统计相关关表、索引信信息。

31、 一般有两两种处理方法法 a. 删删除对应的统统计信息 Anallyze ttable tablee_namee deleete sttatisttics Anallyze iindex indexx_namee deleete sttatisttics 对应的系系统包dbmms_staats也可实实现生成、删删除表、索引引的统计信息息 b. 使使用hintts 明确指指定对应的执执行路径3.4.3.33 hintts常用的几种hiints如下下：Hint SyyntaxDescripption优化方式/*+ ALLL_ROWSS */ 表明对语句块选选择基于开销销的优化方法法,并获得最最佳吞

32、吐量,使资源消耗耗最小化/*+ CHOOOSE */ 表明如果数据字字典中有访问问表的统计信信息,将基于于开销的优化化方法,并获获得最佳的吞吞吐量;表明明如果数据字字典中没有访访问表的统计计信息,将基基于规则开销销的优化方法法;/*+ FIRRST_ROOWS */ 表明对语句块选选择基于开销销的优化方法法,并获得最最佳响应时间间,使资源消消耗最小化，在在做分页查询询时有时可以以改善性能/*+ RULLE */ 表明对语句块选选择基于规则则的优化方法法处理方法/*+ ANDD_EQUAAL(tabble inndex) */ 提示明确进行执执行规划的选选择,将几个个单列索引的的扫描合起来来.SELEECT /*+INDEEX_FFSS(BSEMMPMS IIN_DPTTNO,INN_EMPNNO,IN_SEX)*/ * FFROM BBSEMPMMS WHEERE EMMP_NO=SCOTTT ANND DPTT_NO=TDC3006/*+ CLUUSTER(tablee) */ Expliciitly cchoosees a cc