浅谈ORACLE数据库调优.docVIP

浅谈ORACLE数据库调优 　　【摘要】当今信息化技术在电子政务、电子商务等各个领域的应用日趋广泛深入，数据库作为信息化技术的核心技术之一，其性能正在受到越来越越多的关注。特别是在业务数据量比较大的行业，如物流业、电子商务、电子政务等，数据库的性能直接影响到业务的成败。本文从数据库本身和上层应用两个层面出发，结合笔者的工作经验对目前应用非常广泛的ORACLE数据库的调优问题进行讨论，供大家参考。 　　【关键词】数据库；oracle；调优 　　一、引言 　　随着信息技术的发展，信息技术不仅改变了我们的工作方式，更是我们提高工作效率的重要手段。数据库是信息技术的核心技术之一，其性能更是直接关系到系统的运行效率和服务水平。但是随着业务不断扩展以及业务数据量的长期积累，数据库的速度必然会有所下降。如何在数据量不断增长和有限的资源条件下，保证数据查询和写入速度没有明显下降，使数据库更快的运转从而为更多的业务提供服务成为数据库调优面临的重要挑战。本文首先从数据库的自身优化开始，包括内存、读写相关参数设定等，然后从业务应用程序端展开讨论，包括表和索引的设计、及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