Oracle数据库的性能调整与优化方法探析_存储_基础信息化.doc

Oracle数据库的性能调整与优化方法探析_存储_基础信息化 1 引言 Oracle数据库作为一种具有高可靠性、高安全性、高兼容性的大型关系型数据库，在金融、通信、医疗、生产制造等行业得到了广泛的应用，已逐渐成为企业信息化建设的重要基础平台。在航天领域中，Oracle数据库应用在人力资源、型号物资、财务及研制过程管理等管理信息系统的同时，还用来存储和管理大量星上数据。要使Oracle数据库适应应用需求，有效管理和利用信息时代不断产生并急剧膨胀的数据，从而更好地支撑业务应用，对Oracle数据库优化显得尤为重要。 在实际应用中，常常会遇到Oracle数据库性能恶化的问题，目前，性能恶化的主要表现是系统响应时间较长，需要用户长时间的等待。而性能恶化的原因是多种多样的，主要包括数据库运行环境恶化、数据库参数设定不当和数据库应用不够优化三个方面，这就需要数据库管理员有比较全面的计算机知识，能够敏感地察觉到影响数据库性能的主要原因，并及时解决问题。 Oracle的性能优化是指根据不同的实际需求，对影响其性能的多种因素进行适当调整，以得到较好的运行状态，达到最优化的性能。Oracle的性能优化主要从数据库运行环境优化、数据库参数优化和应用优化三个方面人手，优化措施之间是相互联系的。 此外，Oracle数据的性能优化还需要对企业的需求有深入的了解，如：数据的吞吐量、用户对响应时间的要求等，并能预见未来的需求变化，从而适时地进行优化配置。 2 环境优化 21优化内存 Oracle数据库管理系统具有内存敏感性的特点，实例的内存结构是否能获得足够的物理内存，是决定Oracle整体性能的关键因素，数据库系统的效率可以通过调整内存分配得到很大的提升。 211合理配置内存 系统内存的配置应尽可能实现如下目标： 1)减少分页； 2)减少内存交换； 3)尽可能让系统全局区(SGA)能驻留内存。 当系统执行分页时，会将当前没有使用的信息从内存移到硬盘上。这样就可以为当前需要内存的程序分配内存。如果频繁地发生分页，系统性能就会严重降低，从而导致很多程序的执行时间变长。 当系统执行内存交换时，会将活动进程临时地从内存移到硬盘上，这样另一个活动进程就可以得到所需要的内存。内存交换基于系统循环时间。如果内存交换过于频繁，就会产生大量的输入输出(I0)，应用的性能可能会急剧恶化。 SGA是Oracle的工作区，它和Oracle进程结合组成一个Oracle数据库实例，管理数据库数据，应答用户请求，为用户提供服务。SGA对数据库性能有着很大的影响，保证共享池有足够的空间尤其重要。如果SGA被频繁地分页和交换，系统性能将严重恶化。 可以通过以下措施合理配置内存： 1)使用“vmstat”或“sarp”命令监控分页； 2)避免运行不必要的系统进程或应用程序进程； 3)减少Unix文件缓冲区的数量； 4)安装更多的内存； 5)将一些工作移到另一操作系统中； 6)配置操作系统核心使用更少的内存； 7)保持SGA在单个共享内存段中。 212对操作系统参数的优化 SGA对数据库性能有着很大的影响，而SGA的设置又受到操作系统一些参数的制约。 1)对于惠普公司的UNIX操作系统，表1给出了一些内核参数、设定值和相关的设置说明。 2)对于SUN公司的Solaris操作系统，etcsystem文件里的几个参数直接影响着Oracle的内存分配，表2给出了一些内核参数、设定值和参数的设置说明。表2 Solaris操作系统参数配置及说明 22优纯数据库磁盘输入输出 磁盘的输入输磁(IO)速度对熬个系统性能有重瑟影响。解决好磁盘I0问题，可显著提高性能。影响磁盘IO的性能的主要原因有磁盘竞争、IO次数过多穰数据块空闻的分配管理。 1)文件独立原则。为了避免数据库文徉之间的竞争，在物理设计时，文件应该被放到不同的IO通道，跨越驱动器的文件分离，避免磁盘争用成为一个“瓶颈”。主要应该注意以下几点技巧：把下列关键数据文件分布在各个可用的磁盘上，这些文件包括SYSTEM表空间、TEMPORARY表空间、回滚段或UNDO段、联机重做日志文件、操作系统盘、Oracle-Home目录中的关键Oracle文件、经常访问的表的数据文件以及经常访问的索引的数据文件；把数据和索引文件分开放置；对于经常连接的表，把它们的数据和索引表空间分开，这样每个表上的信息就不会放在相同的磁盘上；把控制文件的多个备份存储到不同的磁盘和控制器上。 2)使用磁盘阵列。随着硬件和文件系统的改进以及磁盘阵列价格的降低，RAID在提升Oracle数据库的IO操作方面变得很重要。在RAID环境中，数据存放在多个磁盘上，采用奇偶拆分、奇偶校验实现数据冗余、错误检查、数据分聚和数据缓存，以减少数据库的IO负荷。RAID有许多标准，常用的有RAID-0，RAID-1，RAID-5，RAlD-1+0等。由于磁盘阵列能够很好地解决IO负担、安全、廉价等优点，已被广泛使用。 3)使用分区。分区可能是提高冬大型表有关的性能的最佳方法。通过访问一个表或索引的较小片段，雨不是访问整个表或索引，分区可以很好地提高效率，数据在物理上是分开的，但在逻辑上看是在一起的。使用分区可以很好避提高维护操作、备份、恢复、事务处理和查询的性能。 4)应用裸设备。裸设备就是未经格式化的磁盘分区，Oracle可以对它进行读和写操作，而没有UNIXIO缓i孛开销。裰设备可以旗于写操锋频繁、顺序访闻的数据和重徽精志文件，但不能用在备份程序中。 23优化CPU的使用 CPU是服务器的一项重要资源，使用操作命令可敬看到CPU使用情况，一般UNlX操作系统的服务器可以使用sarU命令查看CPU使用率；Windows操作系统的服务器可以使用性能管理器来查看CPU使用率，服务器良好的工作状态是在工做高峰时CPU的使用率在90以上，如果空闲时间CPU使用率就在90以上，说明服务器缺乏CPU资源；如果工作高峰时CPU使用率仍然很低，说明服务器CPU资源比较富余。 如果发理系统CPU资源紧张，应该逶过增加CPU的数量或主频、减少服务器上负荷和提高数据库进程运行的优先级等方法优化数据库服务器性能。 24优化网络IO 网络带宽会影响系统性能，减少网络受载，可以改善系统的性能。减少网络IO的原则是：将应用逻辑集中在数据库服务器中。可以使用Oracle数据库的完整约束性、数据库触发器、存储过程、存储函数和包等，使在网络上传输的只是调用过程的名字和输出结果，大大减少网络lO负荷。 3 数据库参数优化 Oracle里最关键的initora参数就是SGAMAXSIZE、PGAAGGREGATETARGET、DBCASHESIZE、SHAREDPOOLSIZE。通常，只要将这4个主要参数设置好就会显著改善数据库系统的性能。 1)SGAMAX_SIZE的设置。通常的原则是开始就把SGAMAXSIZE设为物理内存的25左右，如果有大量用户或者可用内存有限，系统都会强制将其设为1520左右，如果只有少量用户或者有大量物理内存，则允许使用30-50的物理内存。同时SGAMAXSIZE的大小决定了很多其他参数的粒度大小。SGAMAXSIZE128Mbyte意味着粒度大小为16Mbyte。 2)PGAAGGREGATETARGET的设置。PGAAGGREGATETARGET是Oracle在所有会话中间分配的会话PGA内存总量。PGA_ AG-GREGATE-TARGET是Oracle新引入的参数，可以替代*_SIZE类型的参数，当设置PGA_ AG-GREGATE_ TARGET时，必须将WORKAREASIZE_ POLICY设为AUTO。使用PGA_AGGRE-GATE TARGET的好处就是:可设定用户会话的总内存使用量来减少分页。PGA_ AGGREGATETARGET参数应该被设定成能维持ESTD_ PGACACH几HITPERCENTAGE大于95%的值，这样可以产生95%甚至更高的缓存命中率。 3)DB_ GASHE_ SIZE的设置。DB_ GASHE_SIZE是Oracle最关键的参数，它设定了用来存储和处理内存中数据的SGA区域的大小。DBCASH几SIZE是可用来调整数据库缓存命中率的重要参数，命中率就是指那些不用从硬盘上物理读操作的数据库块的访问比例。对于那些事务处理系统来说，数据缓存命中率通常都应该在95%以上，如果命中率低于95%，通常就必须增大DB_CASHE_SIZE。 4)SHARED_ POOL_ SIZE的设置。SHARED_POOL_ SIZE类似于DB_ CASHE_ SIZE，它为那些数据字典缓存和共享SQL语句指定了在SGA里所分配的内存。为了使性能得到优化，必须保证缓存重载率尽量接近0%，并使缓存命中率在95%以上。无论缓存重载率大于1%或缓存命中率低于95%，都应该通过增大SHARED_ POOL_ SIZE优化系统。 4 应用优化 41索引优化 索引对数据库I/O的影响十分巨大。如果通过索引来访问数据库，可以大大减少对大型表的全表扫描，从而减少I/O的开销。索引的使用通常能够提高SELECT，UPDA.TE以及DELETE语句的性能，但会降低INSERT语句的性能，因此索引并非是越多越好，使用索引应该遵循以下原则: 1)为不经常更新的静态大表建立索引； 2)为查询语句只选择少量字段进行查询的表建立索引； 3)在经常进行连接，但是没有指定为外键的列上建立索引； 4)在频繁进行排序或分组(即进行order by或group by操作)的列上建立索引； 5)在不同值较多的列上建立检索； 6)在多个待排序的列上建立复合索引(compound index)； 7)在没有空值的列上建索引，在含有空值的列上即使建了索引也用不上。 4. 2 SQL语句优化 SQL语句是对数据库进行操作的惟一途径，对数据库系统的性能起着决定性的作用。SQL语句消耗了70%-90%的数据库资源。SQL语句的优化是将性能低下的SQL语句转换成为目的相同的、性能优异的SQL语句。SQL语句的编写和优化应该遵循以下原则: 1)尽可能地避免扫描整个表； 2)减少查询规则数； 3)用表连接代替子查询； 4)用规范的格式和访问数据库对象的一致顺序书写SQL语句 5)在子查询中用“exists(not exists)”代替“in(not in)” 6)用外连接“outer joins”代替“not in”操作 7)字符串查询中尽量少使用通配符，如果必须使用，尽量不放在搜索词首； 8)尽量少使用“distinct” 9) “where”子句中尽最不使用转换函数； 10)在可能的情况下，尽量使用读操作而不是加锁 11)避免使用逻辑操作符“or”； 12)避免使用“!”或“”这样的操作符，因为这会使系统无法使用索引； 13)对于大数据量的求和，应避免使用单一的sum”命令，可人为将数据分组，利用“group by”语句同“sum”命令一起使用。 14)把最具有限制性的条件放在前面，大值在前，小值在后； 15)确保比较运算中的数据类型相同， 16)减少对“sysdate”的调用 17)减少“mod”函数的使用。 5 数据库优化实验 本文实验的管理信息数据库包含4个数据库实例，支撑人力资源管理系统、型号间接经费系统、科研综合信息系统、物资仓储管理系统、元器件可靠性信息系统和仪器设备及计盆管理等多个系统，总数据量约为12Gbyte左右。 本数据库运行在惠普8420服务器上，使用UNIX操作系统，优化过程中首先依照表1调整操作系统参数，然后调整数据库参数，最后优化调整了部分应用的SQL语句。部分参数设值如下: 5GA_MAX_SIZE:738197504 PGA_AGGREGATE_ TARGET:2097152000 DB_CASHE_ SIZE:738197504 SHARED_POOL SIZE:587202560 本数据库经过优化调整后，大部分应用系统在进行数据库相关操作时，响应时间有所缩短，系统整体性能提高20%左右. 6 结论 根据Oracle数据库产品的特点，本文对Oracle数据库的优化调整技术进行了探讨，从环境优化、数据库参数优化及应用优化兰个层面介绍了Oracle数据库优化的方法，并实际优化了管理信息数据库，优化后的数据库较原数据库性能提高20%左右。 如有任何看法或投稿请联系:MSN:QQ:47512648 1 引言 Oracle数据库作为一种具有高可靠性、高安全性、高兼容性的大型关系型数据库，在金融、通信、医疗、生产制造等行业得到了广泛的应用，已逐渐成为企业信息化建设的重要基础平台。在航天领域中，Oracle数据库应用在人力资源、型号物资、财务及研制过程管理等管理信息系统的同时，还用来存储和管理大量星上数据。要使Oracle数据库适应应用需求，有效管理和利用信息时代不断产生并急剧膨胀的数据，从而更好地支撑业务应用，对Oracle数据库优化显得尤为重要。 在实际应用中，常常会遇到Oracle数据库性能恶化的问题，目前，性能恶化的主要表现是系统响应时间较长，需要用户长时间的等待。而性能恶化的原因是多种多样的，主要包括数据库运行环境恶化、数据库参数设定不当和数据库应用不够优化三个方面，这就需要数据库管理员有比较全面的计算机知识，能够敏感地察觉到影响数据库性能的主要原因，并及时解决问题。 Oracle的性能优化是指根据不同的实际需求，对影响其性能的多种因素进行适当调整，以得到较好的运行状态，达到最优化的性能。Oracle的性能优化主要从数据库运行环境优化、数据库参数优化和应用优化三个方面人手，优化措施之间是相互联系的。 此外，Oracle数据的性能优化还需要对企业的需求有深入的了解，如：数据的吞吐量、用户对响应时间的要求等，并能预见未来的需求变化，从而适时地进行优化配