Oracle9i 应用系统优化

1、优化前提应用系统方案制定准确，对应用系统运行环境分析合理、正确，在数据库服务器性能、存储空间、网络带宽等方面的配置能够达到系统运行要求.2、优化目标响应时间与吞吐量平衡临界资源2.1响应时间与吞吐量平衡根据应用类型的不同，性能优化的目标不同：在线事务处理系统OLTP）把吞吐量定义为性能指标；决策支持系统（DSS）把响应时间定义为性能指标。响应时间响应时间=服务时间+等待时间系统吞吐量系统吞吐量指在给定的时间内所完成的工作量。有以下两种技术：以相同的资源来完成更多的工作（减少服务时间）；通过减少整个响应时间来更快完成工作。等待时间当竞争增强的时候，某个任务的服务时间也许保持不变，但它的等待时间将增长。我们开发的系统一般为OLTP和DSS的复合系统，侧重于OLTP,在硬件允许的情况下最好能够将运行数据库、分析数据库分离。2.2临界资源诸如CPU、内存、I/O容量、网络带宽等资源，都是减少时间的关键因素。性能好坏取决于以下因素：可用资源的数量需要该资源的客户方的数目客户方等待资源所消耗的时间客户保持资源的时间长短随着请求单元的增加，服务时间也增加。为了处理这种情形，用户可以选择：通过限制请求的速率，从而维护可接受响应时间还可通过增加资源数目，如CPU和硬盘（增加资源的前提是应用系统设计良好，并且已经做了充分的优化）3、优化阶段从实际做的项目过程来看，除了系统安装优化外，系统优化往往都是在系统实施、运行时才考虑，其实到这阶段做系统优化的局限性比较大，因为系统架构设计都成型、固化，大幅度调整设计的代价非常昂贵，一般只能在局部领域做优化，只能通过重新分配内存或优化I/O来或多或少地提高性能，实际上优化应该贯穿系统设计、开发、安装、测试、运行整个过程。3.1设计阶段为了达到最佳的效果，优化工作应当从设计阶段进行，而不是在系统实施后进行。在数据库设计阶段，个人认为需要注意如下几个方面：业务对象不能建立在系统表空间；索引表空间和业务表空间分开；将LOB类型的字段与其它的类型分开；根据应用系统功能确定是否要采用冗余字段；正确的主键字段的选择，建议采用数字，不推荐使用复合主键；3.2开发、测试阶段在开发实现阶段，个人认为需要注意如下几个方面：执行sql使用变量绑定的方式，尽可能的保留在共享内存中，提高sql命中率；多表关联查询时采用有效的连接顺序；尽可能的降低客户端和服务器的网络数据交互，某个业务功能点需要频繁和数据库交互的，建议采用存储过程、临时表实现；根据查询条件建立必要的索引，查询条件中使用oracle函数建立相对应的函数索引,数据值范围较小的采用位图索引多张表关联查询时，有时可采用先查询符合条件对应的表中关键字，然后通过关键字再查询对应表中相关信息；频繁访问，较少更新的数据量较小的表信息可采用缓存的方式；在实现批量更新、插入时，要采用jdbc批量执行方法，并且调整对应的fetchsize参数。在测试阶段，应该模拟实际运行环境，测试出相关性能较差的功能点。因为在设计、开发阶段往往因为并发用户少、数据量小，很多性能问题显现不出来，如果软件测试充分，很多性能问题都可以显现出来，现在有很多优秀的软件测试工具，如LoadRunner、Robert在做压力测试方面都比较方便、优秀。尽量将系统因程序设计、编码不当导致的性能问题暴露在测试阶段。3.3安装阶段一般在安装生产数据库时，我们根据系统最早的规划，集合软、硬件环境，需要调整操作系统以及数据库参数，3.3.1操作系统交换区交换区是Oracle的一项基本的要求。可以根据Oracle的发行要求来确定。一般交换区大小的要求是该服务器内存的2倍至4倍之间，建议是内存的4倍3.3.2操作系统内核参数shmmax共享内存段，建议设大点，达到最大SGAshmmin最小的共享内存段.shmmni共享内存标志符的数量.shmseg一个进程可分配的最大内存段数.shmall最大可允许的内存数，比SGA还要大.semmns信号量，跟ORACLE的PROCESS数有关.semmsl一个信号量中最大的信号量数.3.3.3oracle文件设置当服务器平台已完成操作系统的安装后，就应该开始认真的考虑下面的问题：是否采用裸设备实际应用的生产系统基本都是采用裸设备，使用裸设备对于读写频繁的数据库应用来说，可以极大地提高数据库系统的性能。安装点的考虑Oracle的安装点就是指数据文件、日志文件和控制文件的安置路径，为了使系统在以后运行性能达到优化，建议将数据文件、日志文件和控制文件的安置路径与数据库系统存放在不同的路径上。最好将数据文件、日志文件和控制文件分别存放在不同的路径。SYSTEM表空间对应数据文件在自定义安装会话中，建议你根据需要设置system表空间所对应的数据文件的大小。一般要设置比默认值的2倍。该数据文件的大小最好是在300MB至500MB间。因为数据文件太小不利于系统的运行。临时表空间对应的数据文件临时表空间对应的数据文件可以根据将来系统存放的应用的处理情况来定。比如系统将来可能要经常进程排序处理，则需要设置较大的临时表空间，也可能需要再建立新的临时表空间。这里建议临时表空间的数据文件在100MB至300MB左右。回滚段表空间对应的数据文件9i回滚表空间都是系统管理，初始值也是根据系统事务量预估计的值，实际到运行阶段如果系统常出现ora-01555错误的时候，可能就需要增加回滚表空间的大小。日志文件的大小日志文件的大小对于Oracle系统的运行也是相当重要。默认值是太小。实际根据事务繁忙预估计日志大小，没有固定的具体值范围，建议重做日志切换时间不能过短也不能过长，一般在20－40分钟左右。该参数可以在系统运行期间根据数据库系统日志切换时间重新调整，控制文件的大小。数据库块的大小如果你的应用系统是OLTP的话，可以采用较小的数据库块。如果是DSS类型的应用系统，则可以设置较大的数据库块，目前Oracle产品所允许的数据库块可以是2KB至64KB之间。无论你选择较大的块或较小的块，它的值都必须是2的整数倍，比如2048,4096,8192等。但需要注意的是，如果操作系统为64位，则可选择较大的块。字符集的选择字符集是Oracle系统专门支持的一项技术。详细请参考另外的章节。一般不要与另外的已经存放的Oracle系统的字符集产生冲突即可。但如果你的环境是一个新的平台，不需要与其它平台进行数据交换的话，建议选择默认的字符集。这样可以利于将来的修改。3.3.4数据库启动参数sga_max_size例程存活期间所占用的系统全局区的最大大小，一般为物理内存的1/2-1/3shared_pool_size指定共享池的大小，共享池包含：共享游标、存储的过程、控制结构和并行执行消息缓冲区等对象，较大的值用于改善多用户系统的性能，该参数调整不能过大，会增加管理负担和latch的开销，一般是在200M-500M左右db_cache_size该参数指定数据缓冲区的大小，原则上时越大越好，取代了8i中的db_block_size*db_block_bufferslog_buffer重做日志缓冲区大小，该参数设置大没有意义，Oracle推荐log_buffer最大为cpu_count乘以128KB或512KB中最大值processes系统用户进程的最大数量，该参数设置为系统最繁忙时估计并发用户数large_pool_size如果不设置MTS，通常在RMAN、OPQ会使用到，但是在10M--50M应该差不多了。可以考虑为session*(sort_area_size+2M)。Java_pool_size它用于存放java代码，若不使用java，建议设置为30Mpga_aggregate_target程序全局区大小,1.对于OLTP系统PGA_AGGREGATE_TARGET=（TotalPhysicalMemory*80%）*20%2.对于DSS系统PGA_AGGREGATE_TARGET=（TotalPhysicalMemory*80%）*50%timed_statistics建议将timed_statistics设置为true,否则无法查看到准确的统计信息（9i版本后的设置为true对系统性能影响较小，千分之一）上述参数基本是初始估计值，在运行阶段可能会根据实际运行情况再调整。3.4运行阶段这也是实际优化工作最多的阶段，个人认为运行阶段优化的真正工作是解决因为实际运行数据库参数设置不当、表、索引统计信息不准确，执行路径不当等导致的性能问题。优化工作应该作为日常工作的一部分，而不是等到用户反映系统慢，系统宕机时才去优化，那时已经是亡羊补牢，为时有点晚，从实际项目来看，往往都是应用程序编写的sql、表、索引统计信息不准确，执行路径不当而导致的性能问题，个人认为一般的sql调优还是有章可循的,基本三步：查找、分析、优化。3.4.1查找3.4.1.1非实时查找查找工具常用的就是statspack,该工具的安装、使用比较简便。脚本路径${oracle_home}/rdbms/admin目录下，常用脚本如下：spdrop.sql删除脚本，丢弃统计分析的相关包、视图、表、同义词等对象(首次创建无须执行)spcreate.sql创建脚本，生成统计分析的相关包、视图、表、同义词等对象（首次执行前建议创建一个统计用的表空间）spreport.sql生成报告记录sql,生成的报告文件在系统当前路径下，文件名默认为：sp_开始快照号_结束快照号.lstsprepsql.sql分析相关快照中的sql执行计划。sppurge.sql删除在两个快照号之间包括本身的所有统计分析数据。sptrunc.sql截取statspack统计分析的相关数据在统计分析的对应用户perfstat下执行执行时间：统计时生成两次快照，一般在30-40分钟左右执行方法：用sys登陆sqlplus后间隔对应时间执行两次execstatspack.snap;统计结果视图：stats$snapshot快照相关信息；selectsnap_id,snap_timefromstats$snapshot;stats$sqltext快照统计sql信息,查询统计sql(statspack报告中sql过长会被截掉）selectsql_textfromstats$sqltextwherehash_value=查询值andlast_snap_id=begin_snap_idorderbypiece;3.4.1.2实时查找如果需要实时的查找性能隐患的相关sql,通过v$session_wait,v$session,v$sqltext_with_newlines三张动态视图就可以基本查找到相关的sql，脚本如下：selectsql_text,sw.eventfromv$sqltext_with_newlinesst,v$sessionse,v$session_waitswwherest.address=se.sql_addressandst.hash_value=se.sql_hash_valueandse.sid=sw.sidand(sw.event='bufferbusywaits'orsw.event='enqueue'orsw.event='freebufferwaits'orsw.event='globalcachefreelistwait'orsw.event='latchfree'orsw.event='logbufferspace'orsw.event='parallelqueryqreflatch'orsw.event='pipeput'orsw.event='writecompletewaits'orsw.eventlike'librarycache%'orsw.eventlike'logfileswitch%')orderbyst.hash_value,st