基于Oracle数据库性能优化技术的研究

2012年03月28日00:05 来源：万方数据 作者：万方数据 编辑：王玉圆 评论：2条

 【IT168 技术】大型关系数据库Oracle已经广泛应用于各行各业，如政府、交通、公安、电信、金融、能源等部门，并已逐渐成为企业信息化建设的重要数据库平台，但随着 Oracle 数据库规模的扩大，数据库用户人数的增加，数据库性能问题越来越突出，因此，有必要对 Oracle 数据库性能进行调整与优化， 使之在满足需求条件下，系统性能达到最佳和系统开销最小。

　　1 性能优化目标

　　1.1 缩短响应时间

　　响应时间是指从用户提交SQL语句到数据库返回结果集的第一行数据所需要的时间，缩短响应时间可以通过减小系统服务时间或用户等待时间来实现，通过使用毫秒ms来表示，通过缩短响应时间，既能减少用户请求的处理时间，又能提高系统资源利用率。

　　1.2 提高系统吞吐量

　　数据库吞吐量指在单位时间内数据库所能完成的SQL 语句事务数量，吞吐量=事务量/时间，通常用TPS(每秒钟的事务量) 来表示，提高数据库的吞吐量可以在同样的时间内处理更多的请求，即在相同的资源环境内做更加多的工作。

　　1.3 提高数据库多个指标的命中率

　　数据库指标包括数据库高速缓冲区命中率、库缓冲区命中率、软解析率等，其中，高速缓冲区命中率是最常用的指标， 高速缓冲区命中率=高速缓存命中总数/数据请求总数，通常使用高速缓冲区命中率来衡量Oracle数据库的性能。

　　1.4 优化磁盘I/O

　　Oracle 数据库将数据储存在磁盘和内存中，想要往Oracle中写入和读取数据基本上都会涉及到 I/O 操作，通过对磁盘合理的进行规划，利用高速缓存技术，可以提高系统吞吐量，缩短用户响应时间，尽可能有效地利用系统物理内存而尽量避免或推迟使用磁盘 I/O 操作。

　　1.5 合理使用内存

　　在大量并发用户数下，若Oracle 内存尺寸不够会降低程序的处理效率，延缓数据库的响应时间，内存是否合理使用，一般可以从使投资得到最大回报和使争用减到最小这两个指标来判断，通过合理使用内存，可以大大提高系统性能。

　　1.6 减小磁盘排序

　　当用户提交的 SQL 语句含有聚合函数或者有排序时，这些排序可能在内存中进行，也可能在物理磁盘上进行。由于物理磁盘自身结构的原因，其读写速度远远慢于内存读写，因此一个优化的原则是尽可能减少物理磁盘排序操作。

　　2 影响Oracle性能的因素

　　影响Oracle数据性能的因素有很多，比如：操作系统，CPU 性能，内存分配不合理， Oracle 配置， I/O 冲突，网络速度低以及SQL使用常见错误等等都会影响数据库的性能。

　　1)操作系统：Oracle 数据库服务器很大程度上依赖于运行服务器的操作系统，操作系统配置不合理会直接降低Oracle性能;

　　2)CPU 占用过高：CPU 是服务器中一个重要的资源 ，CPU 资源被其它应用占用或被某个数据库事务占用，会导致其它数据库事务运行停滞，而使数据库响应迟钝，比如：空闲时， CPU 占用率超过90%，则说明Oracle服务器CPU 资源不足，低效率的 SQL 语句、锁冲突、SQL 语句的重解析等原因都会引起 CPU 资源不足;

　　3)I/O 冲突：由于磁盘在同一时刻只能满足一个进程的需要，当多个进程同时访问同一个磁盘时，会引起读写盘冲突，进而降低整个系统的速度;

　　4)Oracle 配置：每一个 Oracle 实例都是由一组 Oracle 后台进程和系统全局区的一个内存区所组成的， 正确调整 Oracle 配置将会对系统性能产生重大的影响;

　　5)内存分配不合理：内存分配不合理将会减少 Oracle 用于存放最近访问过的数据的缓冲区空间，并导致操作系统频繁进行页面或内存交换，从而导致计算机系统额外的 I/O 开销;

　　6)网络速度低：网络的带宽会在一定程度上影响系统的整体性能，网络速度过低会增加网络 I/O 负荷量，从而降低数据库系统的吞吐量并延长用户响应时间;

　　7)SQL使用常见错误：配置和数据迁移的错误，大量递归 SQL 语句的存在，长时间的全表扫描，一些数据库结构的设置不合理，重做日志文件的不合理设置，I/O 设备的不合理的规划，非标准参数的使用，执行效率很差的 SQL 语句，游标和共享池的错误使用，低效率的数据库连接。

 

　　3 性能优化优化与调整技术

　　3.1 调整优化数据库

　　1)最常见的调优方法是在数据库中增加索引，索引(index)是常见的数据库对象，充分利用索引来减少表扫描的 I/O 次数，使用索引可以避免不必要的大表全表扫描，索引设置的位置要视 where 子句中索引列所应用的查询条件而定，通过索引对表的数据进行检索比起直接全表扫描所引起的I/O操作要小得多，索引可增加查询速度;

　　2)若某种业务的数据量增长非常快，可以使用分区表技术将数据进行分散，将不同表空间分布到不同的磁盘当中，使得硬盘之间 I/O 负载均衡，在一定程度上缓解了数据量过大引起的负面影响，并且会缩短查询时间;

　　3)使用存储过程完成数据库中频繁执行的应用逻辑，使代码编程与数据库的操作分离，可以降低网络传输量，提高数据请求的执行效率，执行存储过程时，用户只需要发出执行命令，而不再进行SQL语句提交，节省了系统的SQL语法分析，充分利用了SQL共享池;

　　4)使用Oracle优化器执行直接提交的查询SQL 语句，可以大大提高语句的执行效率;

　　5)使用触发器自动执行的SQL语句，可以降低网络 I/O及系统资源的消耗，如需要对所有访问数据库的程序自动实施一定规则或检查，则使用触发器可以大大提高效率。

　　3.2调整优化内存

　　Oracle的信息存储在内存和磁盘上，由于访问内存比访问磁盘快得多，在大量并发用户数下，如果Oracle 内存尺寸不够会降低程序的处理效率，延缓数据库的响应时间，提高数据库性能需要设置合适的内存尺寸，Oracle 内存包括系统全局区 (SGA)和程序全局区 (PGA)。

　　3.2.1 调整SGA的大小

　　根据数据库运行状况重新调整SGA的大小，对每个节点修改SGA大小的方法如下：

　　SQL>alter system set sga_target=200m scope =both sid=’RACDB1’;

　　SQL>alter system set sga_target=200m scope =both sid=’RACDB2’;

　　3.2.2 提高共享池性能

　　共享池主要是用来存放最近使用过的 SQL语句，共享池内存分配算法保证了数据字典数据比库缓冲区数据在内存停留时间更长，命中率更高，应优先调整库缓冲区。

　　1)通过调整参数SHARED_POOL_SIZE的值，可以根据实际情况对每个节点共享池的大小进行调整;

　　2)为了提高共享池命中率，可以使用代码重用方法;

　　3)对于比较大的对象，如自定义的过程与包，在载入共享池以及硬解析的过程中需要共享池付出很大的代价， 把重要的大对象保持在内存中，可以大大提高共享池性能。

　　3.2.3 优化数据缓冲区高速缓存性能

　　为减少系统磁盘 I/O 开销，应调整数据缓冲区的尺寸，使服务器进程尽量在缓冲区中找到所需的数据，尽量减少等待数据块或空闲缓冲区的时间。

　　1)加大Buffer Cache的大小

　　可以通过调整DB_CACHE_SIZE参数的值增大Buffer Cache。

　　2)使用多个缓冲池

　　Keep池中数据倾向于一直保存，Recycle池中的数据倾向于即时老化，而Default池则存放未指定存储池的数据，通过使用多个缓冲池提高Buffer Cache的命中率。

　　3.2.4 尽量减少全表扫描

　　通过索引的正确使用可以避免不必要的全表扫描，发生的全表扫描越少，Database Buffer Cache命中率将越高，但对于一些表比较小且需要表中的大多数数据时，这时使用全表扫描响应时间可能就会优于不使用全表扫描，通常，当该表的结果集和表中记录总数的比值大于20%时，就应该要使用全表扫描。

　　3.3 调整优化磁盘I/O

　　1)经常使用的对象产生 I/O 争用的机会较多，应将访问量较大的数据文件放在独立磁盘上，同一个表空间的多个数据文件应尽可能地放在不同的磁盘上，为索引创建单独的表空间，并将表和索引分开在不同的表空间;

　　2)在内存中修改过的数据不是直接写入数据文件，而是先写入重做日志文件中，重做日志文件要足够大，要与数据文件存放在不同的磁盘上，减少对磁盘的竞争，重做日志文件分为几个组，写满一组时切换至下一组，最后一组写完后再返回至第一组，按顺序循环写入;

　　3)Oracle 的文件和操作系统的其他文件应尽可能地放在不同的磁盘上，这样可以减小 I/O争用的概率;

　　4)最好使用目前较流行的廉价磁盘冗余阵列(raid)，它能自动分离不同类型、访问频率的数据库文件，减小I/O进程之间的竞争，优化数据库性能;

　　5)创建回滚段及其专用的表空间，回滚段是为了从系统操作的失败中得到数据的恢复，从而减小I/O进程之间的竞争，防止空间竞争影响事务的完成;

　　6)单独创建用户数据表空间，且要与系统表空间(system)分开磁盘存放，创建临时表空间用于排序操作，尽可能防止数据库碎片存在于多个表空间中。

　　3.4 SQL优化

　　SQL 语句本身的执行效率直接影响Oracle 数据库执行效率，它消耗了数据库系统 70%～90%的资源，对SQL语句进行合理设计可以使其更高效地执行，以提高系统对资源的利用率，好的SQL语句可以加快执行速度，减少网络传输，从而最大限度地发挥数据库的性能。

　　1)尽量减少对数据库的查询次数，对几个表查询时 FROM 子句的顺序，按照由内及外的访问顺序应把可筛选出较少记录的表放在前面，执行时最先查找出这个表的几个记录，再和其他表的记录相连接;

　　2)为了充分利用库缓冲区的 SQL 解析信息，对于经常运行条件子句变量值不同的 SQL 语句，应将这些变量改为统一的绑定变量;

　　3)调整 SQL 的关键是使数据库寻找数据的路径最简化，限制动态SQL的使用，优化操作符，如in或not in，is null 或 is not null，like ，union 等操作符，应尽量少用;

　　4)避免不带任何where条件的SQL语句的执行，使用order by、group by、union 等条件的 SQL 语句会对查询完的数据进行排序，增大了 PGA 或 TEMP 的负担，优化这些语句时可在使用这些条件的列上加上有序索引;

　　5)对SQL 语句的索引进行优化，如：在索引列使用 is null 和 is not null，或进行了显式或隐式的运算时索引不被使用，采用函数处理的字段也不能利用索引等;

　　6)避免相关子查询，查询嵌套层次越多，效率越低，为了加速查询速度，可以使用临时表;

　　7)在系统不繁忙或在大量对象更改后定时或及时统计数据库信息，选择适当的方法进行优化，包括几种常用方法：基于代价的优化(CBO)，基于规则的优化(RBO)， 对于需要经常进行查询的表，可以通过建立索引或嵌入内存区以提高查询效率。

▲图1 数据库优化结果

　　4 数据库优化实验

　　选取约为5Gbyte左右的信贷管理系统作为数据库优化实例，该数据库运行在HP ProLiant BL490c G7(603599-B21)服务器上，使用UNIX操作系统，优化结果如表1所示。

　　从表1可以看出，数据库经过调整优化数据库、调整与优化内存、调整与优化I/O和SOL优化后，响应时间变得越来越短，系统性能得到逐步提高。

　　5 结束语

　　随着Oracle数据库规模的扩大，用户数量的增加，Oracle数据库性能问题越来越突出，Oracle 数据库的性能优化涉及的方面很广，优化与调整是一个需要通过不断摸索、总结的过程，在实践中，必须先了解影响数据库系统性能的因素，针对这些不同的因素选择合理的优化调整策略予以调整，同时也需要采取更加先进的技术来对数据库进行调优，使得数据库系统获得最优性能。