oracle数据库应用系统的性能优化

魏亚楠，宋义秋

WEI Ya-nan, SONG Yi-qiu

（唐山职业技术学院，唐山 063000）

0 引言

信息化系统都基于数据库而运行，而数据库系统性能又最大程度的决定着应用系统的性能。大多数数据库系统在运行一段时间后都会存在一定的性能问题，主要涉及数据库硬件、数据库服务器、数据库内存、应用程序、操作系统、数据库参数等方面。因此，基于数据库系统的性能调整与优化对于整个系统的正常运行起着至关重要的作用。

1 oracle数据库及特点

oracle是一个功能极其强大的数据库系统。它起始于七十年代末的关系型数据库技术。这种类型数据库的关键是怎样理解数据间的关系，然后构造反映这些关系的信息库。oracle成功的将关系型数据库转移到桌面计算机上，提供了一个完整的客户/服务器体系结构的商用DBMs。同时它利用SQL*NET软件层，与多种操作系统支持通信协议相配合，为oracle关系型数据库提供分布式环境，可以实现单点更新，多点查询。Oracle数据库已经被用于各种大型信息系统中，特别是诸如银行，保险，烟草，石油等大数据量，对安全性要求较高的企业。其特点主要体现在：1）支持大数据库、多用户的高性能事务处理Oracle支持最大数据库（几百TB），可充分利用硬件设备。支持大量用户同时在同一数据上执行各种应用，并使数据争用最小，保证数据的一致性[1]。2）硬件环境独立。Oracle具有良好的硬件环境独立性，支持各种类型的大型，中型，小型和微机系统。3）遵守数据存取语言、操作系统、用户接口和网络通信协议的工业标准。4）较好的安全性和完整控制。Oracle有用户鉴别、特权)、角色、触发器、日志、后备等功能，有效地保证了数据存取的安全性和完整性以及并发控制和数据的回复。5）具有可移植性、可兼容性与可连接性oracle不仅可以在不同型号的机器上运行，而且可以在同一厂家的不同操作系统支持下运行。具有操作系统的独立性。

2 数据库系统性能评价指标

主要从以下几个方面进行：1）系统吞吐量。吞吐量是指单位时间内数据库完成的SQL语句数目，以每秒钟的事务量(tps)表示。提高系统吞吐量可以通过减少服务时间在同样的资源环境下做更多的工作或通过减少总的响应时间使工作做得更快这两种方法来实现。2）用户响应时间。响应时间是指用户从提交SQL语句开始到获得结果集的第一行所需要的时间，是应用做出反应的时间，以毫秒或秒表示。响应时间可以分为系统服务时间（CPU时间）和用户等待时间两项。也就是说，要获得满意的用户响应时间有两个途径：一是减少系统服务时间，即提高数据库的吞吐量；二是减少用户等待时间，即减少用户访问同一数据库资源的冲突率。3）数据库命中率。Oracle用户进程所需的所有数据都是经过缓冲区高速缓存来存取的。用户对数据的需求能否在内存中得到满足，给出快速的响应，可用缓冲区高速缓存命中率来衡量。该比率等于高速缓存命中总数除以对高速缓存的查找总数。由于从高速缓存中读数据比从磁盘中读数据的开销要小得多，因此一般应使该命中率足够高。4）内存使用情况。内存的使用情况主要体现在可共享内存、永久性内存和运行时内存这三者的分配使用上。内存是否合理使用，一般考虑的主要调整目标有两条：使投资得到最大回报。把时间和精力用于解决可能产生最大利益的问题；使争用减到最小。瓶颈的特点在于延迟和等待，尽可能地消除或减少它。5）磁盘I/O。数据库中发生的每个动作几乎都将产生某种类型的I/O活动，该活动可以是逻辑的（在内存中），也可以是物理的（在磁盘上）。通过降低不必要的I/O开销可以增加用户任务可获得的吞吐量，缩短用户响应时间。其中，磁盘I/O操作是数据库性能最重要的方面，是计算机最大的开销。

3 oracle数据库应用系统性能优化的主要方向

3.1 CPU利用

CPU是服务器的重要资源，服务器良好的工作状态是在工作高峰时CPU的使用率在90%以上。在大型的应用系统中，比较流行的配置是oracle+unix，如IBM的小机，sun公司的red hat等。这些系统中使用sar-u命令查看CPU的使用率，Windows系列的操作系统的服务器，可以使用性能管理器来查看CPU的使用率。Oracle中：v$sysstat数据字典中“CPU used by this session”记录了数据库使用的CPU时间，“OS User level CPU time”统计了操作系统用户态下的CPU时间，“OS System call CPU time”统计了操作系统系统态下的CPU时间，操作系统总的CPU时间就是用户态和系统态时间之和，如果Oracle数据库使用的CPU时间占操作系统总的CPU时间90%以上，说明服务器CPU基本上被Oracle数据库使用着，这是合理，反之，说明服务器CPU被其它程序占用过多，Oracle数据库无法得到更多的CPU时间[2]。出现CPU资源不足的原因可能是SQL语句的重解析、低效率的SQL语句、锁冲突等，oracle提供了初步的分析方法：对于SQL语句的重解析可以执行下述语句来查看SQL语句的解析情况：

SELECT * FROM V$SYSSTAT WHERE NAME IN('parse time cpu','parse time elapsed','parse count(hard)')，其中parse time cpu是系统服务时间，parse time elapsed是响应时间，用户等待时间waite time为两者之差。由此可以得到用户SQL语句平均解析等待时间=waite time/parse count。这个平均等待时间应该接近于0，如果平均解析等待时间过长；对于SQL语句解析效率比较低的问题，可以通过以下方法查询哪些sql需要优化：

SELECT SQLJEXT,PARSE CALLS,EXECUTIONS FROM V$SQLAREA，

SELECT BUFFER_GETS,EXECUTIONS,SQL_TEXT FROM V$SQLAREA。对于冲突，可以通过v$system_event数据字典中的“latch free”统计项查看，如果没有冲突的话，latch free查询出来没有结果。如果冲突太大的话，可以降低spin_count参数值，来消除高的CPU使用率。

3.2 内存分配

内存参数的调整主要是指Oracle数据库的系统全局区SGA（System Global Area）的调整。SGA是Oracle数据库的心脏，是对数据库数据进行快速访问的一个系统区域，可以被服务器和用户共享。SGA主要由三部分构成：共享池（Share Pool）、数据缓冲区（Data Buffers）、日志缓冲区（Redo Log Buffers）和PGA区域。SGA随着不同的环境而不同，没有一种通用的最佳方案，但在设置它之前要先考虑以下的几个方面：物理内存多大：操作系统是哪种以及占多大的内存，数据库系统是文件系统还是存储设备；数据库运行的模式。SGA占有物理内存的比例没有严格的规定，只能遵从一般的规则：SGA占据物理内存的40%～60%左右。如果通过直观的公式化来表达则为：OS使用内存+SGA+并发进程数×（Sort_area_size+Hash_area_size+2M）＜0.7RAM，以这个公式为参考进行自由调整即可。初始化参数文件中的一些参数对SGA的大小有决定性的影响。参数Db_block_Buffers（SGA中存储区高速缓存的缓冲区数目），参数Shared_pool_size（分配给共享SQL区的字节数），是SGA大小的主要影响者。DataBuffers参数是SGA大小和数据库性能的最重要的决定因素。该值较高，可以提高系统的命中率，减少I/O。每个缓冲区的大小等于参数Db_block_size的大小。Oracle数据库块以字节表示大小。Oracle SGA区共享池部分由库高速缓存（Library

Cache）、字典高速缓存（Dictionary Cache）及其他一些用户和服务器会话信息组成，共享池是最大的消耗成分。

4 oracle数据库应用系统性能优化技术

4.1 sql语句优化

SQL语句优化的实质就是在结果正确的前提下，用优化器可以识别的语句，充分利用索引来减少表扫描的I/O次数，尽量避免表搜索的发生。优化的目的就是将性能低下的SQL语句转换成目的相同的、性能优异的SQL语句，使数据查找的路径最简化，并尽量保持处理器时间和I/O时间的平衡。通常分为以下几个步骤：1）查找有问题的SQL语句。优化有问题的sql语句能显著提高数据库性能。寻找的方法有：搜集统计数据，oracle中可以通过DBMSSTATS包或ANALYZE命令，前者可用于搜集有关链接数据行的统计数据，簇的数据情况只能使用ANALYEZ命令获得，其相关语法为ANALYEZE CLUSTER Cluste_name Computer STATISTICS，对于其它情况可以使用DBMS_SATS包，语法为：exec DBMS_SATS.gather_table_stats(‘owname’,’tablename’,’partnma e’)；利用SQLTrace工具分析SQL语句。通过命令ALTER SESSION SET SQL_TRACE=TRUE会话激活SQL_TRACE，oracle就会在udump管理区创建跟踪文件，从而可以了解如解析、执行和返回数据的次数、CPU时间和执行时间、物理读和逻辑读操作次数、库缓冲区命中率等参数；通过oracle Enterprise Manager Console、Oracle Diagnostics Pack等图形性能工具能够很快地获取到数据库缓冲区命中率、CPU利用率、运行时内存等重要的数据库性能指标信息，并将它们以GUI表格形式和曲线图形式显示出来[3]。2）建立合适的SQL语句。参照原则如下：建立“适当”的索引，使用索引的根本目的就是为了提高查询效率，但索引也不是越多越好，使用索引时应遵循相应的原则。此外，为了降低I/O竞争，索引不应与用户表空间建在同一磁盘上；避免使用耗费资源的操作如DSJTNITC、UNION、GROUP BY、ORDER BY等关键字，因为他们会启动SQL引擎执行耗费资源的排序功能，通常这些语句都可以通过其他方式实现；注意WHERE子句中的查询顺序，oracle采用自下而上的顺序解析，因此表之间的连接必须写在其他Where条件之前，那些可以过滤掉最大数量记录的条件必须写在Where子句的末尾，返回记录最少的索引列应写在最前面。

4.2 oracle内存调整

Oracle的内存结构较为复杂，但是对系统具有较大影响的参数一般为共Shared pool，Database Buffer Cache，Redo log buffer，Java Pool，Large Pool，Program Global Area等。1）优化Shared pool。共享池大小是否合适，主要体现在库缓冲区和数据字典高速缓冲区的命中率上。库缓冲区设置过小那么语句将被连续不断地装入影响系统性能，如果设置过大，将导致空间碎片化以及CPU使用率的大幅上升。库缓冲区的命中率可以通过命令select (1-sum(reloads/sum(pins) from v$librarycache，该值若小于0.95，则可以通过增加SHARED_POOLSIZE值来提高命中率。对于数据字典高速缓存，通过以下命令select sum(1-(getmisses)/(sum(gets)+sum(getmisses))) form v$rowcache，如果结果小于0.85，则需增大共享池的大小来提高数据字典高速缓存可用的内存数量。2）优化缓冲区高速缓存。缓冲区高速缓存越大，oracle可装入内存的数据就越多，磁盘的I/O性能就越少，系统性能就越好。通过数据字典v$vsysstat可以了解其活动情况：select name，value from v$sysstat where name in （’dbblokc gets’,’consistent gets’,’Physical reads’)；如果命中率小于0.85，则可增加参数DB_CACH_SIZE的值为数据块缓冲区分配更多的内存。3）调整重做日志缓冲区。重做日志缓冲区如果分配太小，会导致没有足够的空间来放重做条目而等待，LGWR进程会频繁将LOG BUFEFR中的数据写入磁盘增加I/O的次数，影响系统性能。查询语句Select name，value from v$sysstat where name=’redo log space requests’，查询结果中的value值应接近零，否则每次将LOG_BUFFERS增大5%再执行上面的查询，直到value值接近零。

4.3 oracle表空间调整

表空间概念是oracle数据库系统的重要的逻辑概念，高效合理的使用表空间，有利于提高数据库性能。1）合理分布表空间。在数据方面，应分离数据和索引，减少I/O竞争，在修改方面，应分离重演日志和归档日志，在系统开销方面，应分离系统表空间、TOOLS表空间和TEMP表空间。尽量将90%以上的I/O操作集中在系统表空间、数据表空间、回滚表空间、索引表空间这4个表空间上，且这4个表空间应单独存储在不同的磁盘上。2）表空间优化结构OFA。首先要分离系统表空间，尽量避免在系统表空间中存储非系统用户的对象，否则会增加产生数据库维护和空间管理问题的可能性。一般而言，除了数据字典，其它能移出系统表空间的任何数据都应该从中移出；其次分离索引段，索引段不应与相关的数据段存储在同一个表空间中，因为它们在数据管理和查询中存在许多I/O冲突。将索引段和数据段存储在不同的表空间中能够平衡两者之间的负载，减少资源争用，并能减少整理数据表或索引碎片所需的管理代价。再次分离临时表空间，临时段是数据库中动态生成的对象，用来存储巨型排序操作的数据。由于它们的动态特性，临时段不应与其他类型的段一起存储。通常，在建立用户时，将这些用户使用的临时数据段设置到临时表空间。

[1]THOMAS KYTE.苏金国,王小振,等译.ORACLE9I&10G编程艺术：深入数据库体系结构[M].人民邮电出版社，2006：265-269.

[2]孙风栋,闫海珍.Oracle 10g数据库系统性能优化与调整[J].计算机技术与发展.2009,19(2)：83-86.

[3]盖国强,冯春培.Oracle数据库性能优化[M].北京：人民邮电出版社,2006.