ORACLE数据库性能优化(转)

ORACLE数据库性能优化(转)Technorati 标签: oracle字体: 小中大| 打印发表于: 2004-10-27 13:54 作者: tangshan_ph 来源: CNOUG博客首页可以通过调整数据库应用程序、数据库和操作系统来增强ORACLE的性能。

适当的调整ORACLE 可以针对特定的应用程序和硬件配置获得较好的性能。

性能调整要考虑响应时间、吞吐量、限制这三个因素。

依赖于应用的不同特性，对这三个因素的要求也不尽相同。

对于OLTP来说，要求有较高的响应时间，而对于OLAP来说，对吞吐量有较高的要求。

系统的响应时间可分为服务时间和等待时间，对于响应时间性能调整来说，就是缩短服务时间和等待时间。

对于吞吐量性能调整来说，就是利用同样的资源做更多的事情（缩短服务时间）、较快的完成工作（缩短等待时间）。

象CPU、内存、I/O能力、网络带宽等资源对减少服务时间和等待时间有很大影响。

增加资源可能会获得较高的吞吐量和较快的响应时间。

系统性能以来于：λλ可用资源的数量λλ请求资源的客户的数量λλ等待资源的时间λλ占用资源的时间通常，应用程序的开发和设计对性能的影响最大，一旦应用程序被付诸实施，数据库管理员只能在系统设计的限制之下进行基本的调整。

性能调整包含了10个步骤。

在此主要讨论象CPU、内存、I/O能力、网络带宽等资源对性能的影响，并如何配置资源来提高系统的性能。

首先要了解系统的运行情况，有几种工具可以用来获取表示系统性能的数据。

能表示系统性能的数据源有以下几种：λλ数据卷λλ在线数据字典λλ操作系统工具λλ动态性能表λλ SQL跟踪工具λλ报警日志λλ应用程序输出λλ用户λλ初始化参数文件λλ程序文本λλ设计（分析）字典λλ可比较数据而能获得这些数据的可用工具主要有以下几种：λλ动态性能视图：Oracle利用一组动态性能视图来监控数据库。

这些视图名以"V$"开始。

SYS 用户拥有这些视图。

λλ Oracle与SNMP（Simple Network Management Protocol）支持：SNMP使用户可以写自己的工具和应用程序。

λλ解释计划（EXPLAIN PLAN）：EXPLAIN PLAN是一个SQL语句，它能列出查询优化器选择的存取路径。

λλ SQL跟踪工具和TKPROF：SQL跟踪工具以文本方式记录下语句在每个阶段的资源消耗，分析，执行，获取结果，提交或回滚。

λλ TKPROF汇总SQL跟踪工具所产生的文件，有选择的包含在EXPLAIN PLAN的输出里。

λλ脚本：Oracle支持许多PL/SQL包，它们可用于对数据库实例进行调整。

例如：UTLBSTAT.SQL UTLESTAT, SQLUTLCHAIN.SQL, UTLDTREE.SQL, 和UTLLOCKT.SQL。

λλ应用程序注册：可以在数据库中记录下应用程序的名字和它执行的操作，这样管理员就可以根据模块来跟踪性能。

应用程序的名字和活动记录在V$SESSION 和V$SQLAREA视图内。

λλ Oracle企业管理器：它是一个用于管理Oracle环境的工具集。

包括：λλOracle Performance Manager ：它获取、计算和显示数据库的性能数据，通过它可以监控数据库，来从而有效地使用内存，最小化磁盘I/O，避免资源竞争。

λλ Oracle TopSessions ：用于监控每个会话的活动。

λλ Oracle Trace ：在Oracle Performance Manager和Oracle TopSessions采用抽样技术定期的从数据库动态性能视图中收集数据，而Oracle Trace则根据预定义的事件来收集性能数据。

λλ Oracle Tablespace Manager ：如果你怀疑数据库的性能问题来自表空间，可以使用该工具检测和矫正这些问题。

λλOracle Expert：它提供自动的性能调整。

由Oracle Performance Manager, Oracle TopSessions, and Oracle Trace检测出的问题可以在Oracle Expert中进行分析。

λλ Oracle并行服务器管理（OPSM）：OPSM是一个用于Oracle并行服务器的全面的管理方案，它通过开放的client-server体系来管理异构环境中的多个数据库实例。

（一）（一）优化CPU资源确定CPU有关的问题：首先需要确定CPU是否足够大，在此方面主要通过以下三个方面来考虑：λλ在空闲期间的CPU的利用率λλ在高峰期间的CPU的利用率λλ平均的CPU的利用率如果怀疑CPU是系统性能的瓶颈，可以通过如下方法来确定：λλ检查系统的CPU使用率：ORACLE只统计ORACLE会话对CPU的使用情况，而系统中的任何进程都使用CPU，所以调整其他应用程序也有可能改善ORACLE的CPU性能。

在UNIX系统中可以通过sar –u命令来获得有关CPU 使用情况的信息，一般情况下，如果CPU 的空闲时间或I/O等待时间接近于0，说明CPU是瓶颈。

在WINDOWS NT 中，可以通过性能监视器来获得有关CPU 使用情况的信息。

内存管理：Paging and Swapping。

通过UNIX的sar 或vm stat命令，以及WINDOWS NT 中的性能监视器来分析发生分页和交换的原因。

I/O管理：Thrashing：确保工作量适宜于内存的大小，避免过多的页交换。

如果CPU花费时间片的很大部分来确保程序可运行，则可能只有50%的时间用于实际工作。

Client/Server Round Trips：一些隐含的消息发送会加重CPU的负担，应用程序经常产生一些消息在网络中来回传递。

进程管理：Scheduling and Switching：操作系统可能会花费很多时间用于切换进程，检查是否启动了大量的进程。

Context Switching：进程的上下文切换同样会耗费大量的CPU时间。

λλ检查ORACLE使用CPU的使用率：有两个动态性能视图可以用于检查ORCALE的进程信息。

V$SYSSTAT：显示所有会话的ORACLE CPU的使用。

V$SESSTAT：显示每个会话的ORACLE CPU的使用。

主要在如下几个方面考察ORACLE CPU的使用：λλ重新分析SQL语句：低效率的SQL共享引起语句的重新分析。

SELECT * FROM V$SYSSTATWHERE NAME IN('parse tim e cpu', 'parse time elapsed', 'parse count (hard)');SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS FROM V$SQLAREAORDER BY PARSE_CALLS;λλ效率低的SQL语句：低效率的SQL语句会消耗大量的CPU时间。

SELECT BUFFER_GETS, EXECUTIONS, SQL_TEXT FROM V$SQLAREA;λλ读一致性：通过回滚段实现读一致性可能会需要大量的CPU时间，可通过增加回滚段的数量来解决。

解决办法：在已经达到CPU的最大限制和无法通过调整CPU来优化性能时，则必须考虑重新设计应用。

可以考虑迁移到不同的应用体系结构上，以获得适宜的CPU使用率。

λλ从单层结构迁移到两层结构：将一台机器上的客户和服务器分离。

λλ多层，使用较小的客户机：可以将一台机器上的多个客户分离到各个较小的客户机上。

λλ两层到三层：使用事务处理器λλ利用ORACLE的并行服务器（二）（二）优化内存分配对于在内存中的数据的访问要快于对磁盘的访问，所以内存优化的目的就是提高内存命中率。

通常在优化完应用和SQL语句后，才开始优化内存分配。

内存的分配决定了磁盘I/O的数量。

如何解决内存分配问题：λλ优化操作系统的内存需求：主要通过以下方法解决，减少内存页交换；确保SGA在内存中；为每个用户分配足够的内存（可能只有一些操作系统支持）。

λλ优化Redo日志缓冲区：LOG_BUFFER参数用于控制Redo日志缓冲区，对于磁盘速度慢，CPU快的计算机，Redo日志缓冲区的使用可能会发生冲突。

可适当增大Redo日志缓冲区的大小。

一般Redo日志缓冲区只占SGA中很小的一部分，适当的增加即可提高很大的吞吐量。

λλ优化私有的SQL 和PL/SQL区：该优化主要有两部分工作，一个是区分不必要的重新分析调用，另一个是减少不必要的重新分析调用。

可以通过SQL跟踪工具、检查V$SQLAREA视图来区分不必要的重新分析调用；可以通过HOLD_CURSOR =yes 、RELEASE_CURSOR =no 、MAXOPENCURSORS = desired value这三个属性来减少不必要的重新分析调用。

λλ优化共享池：SGA的共享池部分由库高速缓存、字典高速缓存和某些用户和服务器会话信息组成。

λλ库高速缓存包含分析和执行SQL语句。

每个SQL语句的SGA共享部分为相应共享池中的内存量，在此共享池中包含语句的分析树和执行计划。

执行SGA优化的一个重要问题就是确保库高速缓存足够大，以使ORACLE能在高速缓存中保持分析和执行计划。

可以通过系统性能监视器或者v$librarycache视图查看库高速缓存的性能。

λλ字典高速缓存包含了有关数据库的数据字典信息、文件空间的可用性和对象权限。

通过命中率可以发现此字典高速缓存是否失效，可以通过系统性能监视器或者V$ROWCACHE视图查看相关数据。

如果需要可通过SHARED_POOL_SIZE参数来增加缓冲池大小。

λλ优化排序缓冲区：如果都大量的排序动作，需要增加排序缓冲区的大小。

可以通过SORT_AREA_SIZE参数实现。

（三）（三）优化I/O操作I/O优化被安排在内存优化之后，通过内存的优化，可以是I/O冲突减少，在此情况下，可以通过一些调整以使I/O性能进一步提高。

对于新系统，应自顶向下分析I/O需求，确定所需要的资源。

而对于已存在的系统应采用自底向上的方法：1．1．了解系统的磁盘数量。

2．2．了解ORACLE使用的磁盘数量。

3．3．了解应用系统的I/O类型。

4．4．了解I/O操作是针对文件系统还是原始设备。

5．5．了解对象在磁盘上的分布。

可以通过如下方法检查I/O问题：λλ检查系统I/O的使用：可以使用操作系统提供的工具来监视整个系统对磁盘文件的访问，可以将大量访问磁盘的应用与ORACLE的相关文件分别存放。

在UNIX系统中可以通过sar –d 来获得有关数据。

在WINDOWS NT 中可通过性能监视器查看。