浅谈ORACLE数据库调优

2014-03-16国家信息中心何国庆

电子世界 2014年9期

国家信息中心何国庆朱虹

一、引言

随着信息技术的发展，信息技术不仅改变了我们的工作方式，更是我们提高工作效率的重要手段。数据库是信息技术的核心技术之一，其性能更是直接关系到系统的运行效率和服务水平。但是随着业务不断扩展以及业务数据量的长期积累，数据库的速度必然会有所下降。如何在数据量不断增长和有限的资源条件下，保证数据查询和写入速度没有明显下降，使数据库更快的运转从而为更多的业务提供服务成为数据库调优面临的重要挑战。本文首先从数据库的自身优化开始，包括内存、读写相关参数设定等，然后从业务应用程序端展开讨论，包括表和索引的设计、及SQL语句的拼写等。

二、ORACLE数据库整体优化

数据库调优的关键是内存与磁盘I/O。内存的读写速度远大于磁盘的读写速度，但是内存的容量有限，价格相对较高，最重要的是内存中的内容断电后会丢失，所以数据库调优的核心在保证数据安全写入磁盘的前提下，让更多的数据库操作在内存中完成，尽量避免磁盘读写。

(一)内存调优

Oracle的内存结构如图1所示：

图1 Oracle的内存结构

分为SGA和PGA两个部分。系统全局区(SGA)是一组共享内存结构，不仅包括数据库的部分数据内容还有控制信息。程序全局区(PGA)是一个内存缓存区，它包含服务器进程的数据和控制信息。对于32位的操作系统而言，二者之和最大不超过1.5G，对于64位操作系统而言几乎没有限制。Oracle在对内存优化方面做了巨大努力，oracle9i实现了通过pga_aggregate_target参数对PGA的自动管理，oracle10g自动共享内存管理(Automatic shared memory management ASMM)的新特性实现了通过sga_target和sga_max_size对SGA的自动管理(实际上是共享池、流池、大型池、java池和数据库缓冲区，其他区域是固定值)，到oracle11g更是实现了通过memory_target和memory_max_size对SGA和PGA的动态管理。这里target参数均为动态参数，可以在不停实例的情况下进行变更，max_size作为保护性参数并非对应内存的实际值，是静态参数。此外为了保证SGA全部在物理内存中，而不被交换至虚拟内存中，我们只要设置lock_sga为“TRUE”即可，pre_page_sga参数设置为“TRUE”，可以保证在启动数据库时把整个SGA读入到物理内存中，以便提高系统的效率。

(二)I/O调优

oracle频繁读写的物理文件包括以下三种文件：控制文件、日志文件、数据文件。当数据库发生任何数据变化时(如业务数据或系统数据变更时，不包括系统参数)这些变化首先被写进联机重做日志，而变更的序列号被保存到控制文件和buffer cathe中，最后在一定的机制下写入数据文件。当联机日志写满时，联机日志发生切换，并将写满的日志文件归档，形成归档日志，此时往往有比较大的I/O波动，甚至会影响数据库性能。

检查点(CKPT)是oracle的一个重要事件，当完全检查点发生时，会将buffer cathe中的所有脏数据写入数据文件中，此时大量的I/O会影响数据库库性能，但完全检查点只在正常关闭数据库和DBA手工执行alter system checkpoint的命令时发生；当脏数据达到一定的阀值或时间，系统也会通知DBWR进程将脏数据写入数据文件，DBWR写数据时每3秒会将写的进度(SCN)写入控制文件(即heartbeat)，这两者(系统通知DBWR工作和heartbeat)统称增量检查点。

优化I/O的首要途径是分散I/O，由于安全的原因，控制文件和重做日志组的成员通常有多个副本，将他们分别存放在不同的物理磁盘上，不仅可以提高安全性，也可以有效地提高数据库I/O带宽。利用raid和条带化技术可以充分利用硬件资源，也是分散I/O的重要手段。其次是调整数据库参数。以下是影响I/O的重要参数：

参数FAST_START_MTTR_TARGET是故障关机后启动时数据库恢复的目标时间，系统根据该参数和硬件性能计算出1个阀值，当脏数据达到该值启动增量检查点，从而影响了检查点的频度；db_file_multiblock_read_count是数据库每次读写的数据块数；此外还有DB_BLOCK_SIZE(该参数在实例建成后就无法更改)、DB_WRITER_PROCESSES等；另外“重做日志文件大小”虽然不是一个数据库运行参数，但直接影响数据库的归档频度和归档时的I/O量。

三、应用优化

应用优化是指除了对数据库整体优化之外，合理调整应用访问数据库的设计及SQL语句。其效果通常比数据库整体优化的效果要好得多。

(一)数据库设计优化。

在数据库设计上我们的总体原则仍然是I/O和内存这两方面的考虑。具体包括：尽量将表空间的数据文件分散在不同磁盘上；将常用的数据存放在I/O性能好的磁盘上；使用分区表；合理创建索引及适度数据冗余等。前两项很好理解，不再赘述。

分区表是当前主流数据库均支持的一项技术，它将一个数据表中的数据按照一定的算法分散至不同的子表中，ORACLE数据库支持范围分区、列表分区、Hash分区(散列分区)、复合分区。将一个大的数据表分割成多个子表可以改善查询性能、增强可用性、维护方便以及均衡I/O。

合理创建索引及适度数据冗余均是以空间换时间的方法。索引可以帮助我们快速查询和排序，但是如果索引过多将会影响数据插入、删除、更新等操作的效率。另一方面，在进行数据查询时如果有过多表关联，SQL语句的执行速度会大大降低，为了避免不必要的关联，适度的数据冗余是很好的解决办法。

(二)SQL语句的优化

SQL语句调优通常发生在试运行阶段，以及数据积累到一定程度导致系统运行速度下降时。oracle强大的统计功能让我们很容易获取数据库中最占用资源最多的SQL语句和Session，从而针对这些最占资源的SQL语句和Session进行分析和优化。SQL调优的主要措施有：参数化SQL语句、优化SQL语句的执行计划、避免全表扫描等。

当一个SQL语句提交到oracle数据库后，oracle会从缓存中查找有没有该语句，如果有该语句，直接绑定变量并执行编译好的SQL语句；如果没有该语句，则编译该语句，并根据统计信息制定执行计划，然后绑定变量并执行。参数化SQL语句可以使大量的SQL语句合并，从而提高SQL语句执行效率。SQL语句的执行计划通常由oracle根据数据表的统计信息自动生成，但是有时SQL并非每次起执行计划均是最有效的，笔者在工作中遇到过相同的SQL语句仅参数不同，结果两次查询，时间相差非常大，经过分析后发现执行慢的那次查询没有走索引。像这样的情况就需要我们干涉执行计划，强制使用索引。

(三)应用优化

数据库优化必然是系统优化的一部分，因此数据库优化不能仅限于数据库本身，要从更高的角度考虑这个问题，甚至是改变应用的设计以使整个应用系统更加合理，效率更高，如给系统添加归档功能、将历史数据从运行库中分离出去等。