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 或 vmstat命令，以及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$SYSSTAT
WHERE NAME IN
('parse time cpu', 'parse time elapsed', 'parse count (hard)');
SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS FROM V$SQLAREA
ORDER 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 中可通过性能监视器查看。
  检查ORACLE的I/O的使用：对于ORACLE ，可以通过下列视图来获得相关的信息：
File Type Where to Find Statistics
Database Files V$FILESTAT
  Log Files V$SYSSTAT, V$SYSTEM_EVENT, V$SESSION_EVENT
  Archive Files V$SYSTEM_EVENT, V$SESSION_EVENT
  Control Files V$SYSTEM_EVENT, V$SESSION_EVENT
可以通过如下的方法来解决I/O问题：
  减少磁盘竞争：
  磁盘竞争：当多个进程同时访问同一个磁盘时就会产生磁盘竞争。要减少高负荷磁盘的访问，可以将高访问量的文件移到低负荷的磁盘上。
  分离Redo日志文件和数据文件：ORACLE 总是经常的访问Redo日志文件和数据文件，将二者放在一起，可能会增加磁盘冲突。
  条带化表数据：条带化，就是将一个大表的数据分布到不同磁盘的不同数据文件中，这样也可以减少磁盘冲突。
  分离表和索引：这并不是必须的，由于索引和表的读取是串行的，也可以做到将表和索引放在一起而不发生磁盘冲突。
  磁盘条带化：就是将一个大表的数据分布到不同磁盘的不同数据文件中，条带化允许不同的进程同时访问一个表的不同部分。这尤其对随机访问一个表的多行很有帮助。条带化可以是磁盘的I/O负载平衡。有两种条带化方法。
  手动方法：利用表空间以及分区表的方式。
  利用操作系统：RAID
  避免动态空间管理：当对象创建后，ORACLE会给对象分配对象，当后续的数据库操作需要额外的空间时，ORACLE会扩展该段。动态扩展对性能有损害。
  侦测动态扩展：动态扩展可引起SQL语句的递归调用（ORACLE自己要发出一些SQL语句），可以通过V$SYSSTAT视图来获得有关递归调用的信息。如果发现在应用程序运行时有大量的递归调用，可以使用较大的扩展。
  分配扩展段（Extent）：通过确定最大的对象的大小，选择合适的存储参数值，以时ORACLE分配的段可以满足存储的要求。
（四） （四）优化网络：主要是保证网络的速度和网络上的流量。
  可以通过如下方法检查网络问题：可以通过V$SESSION_EVENT, V$SESSION_WAIT和 V$SESSTAT视图来检查与网络延迟有关的信息。
  V$SESSION_EVENT视图中的 AVERAGE_WAIT列表明了ORACLE等待的时间。
  V$SESSION_WAIT视图中的 EVENT列列出了正在等待的活动会话
  V$SESSION_WAIT视图描述了活动会话在等待什么，也可以看到收发的字节数。
  可通过以下方法解决网络问题：
  使用数组接口：用数组接口取代每次只取一行的做法.在一个网络来回中取多行要比取一行要有效。
  使用预先启动的进程：在没有使用多线程服务器的情况下，如果有预先启动的进程，可以大大缩短连接时间。
       调整会话数据单元缓冲区的大小：当有大量的数据要传输的时候，增加数据单元缓冲区的大小会改善网络的性能。可以通过ORACLE网络管理器来定义数据单元缓冲区的大小。
  增加Listener队列的大小：通过增加Listener队列的大小，可以处理更多的请求。
  使用TCP.NODELAY：
  使用连接管理器：通过连接管理器可以使多个会话共享一个传输层连接，这样可以使进程处理会话的数量增加。
（五） （五）优化操作系统
操作系统性能问题主要是进程管理和内存管理。如果已经优化过ORACLE后，还希望得到更好的性能，则需要优化操作系统。不过不能期望性能会有很大的提高。
  检测操作系统性能问题：从操作系统性能监视器中要获得的数据指标有以下几种：
CPU负载：检查系统运行在用户模式和系统模式的时间
设备队列
网络活动（排队）
内存管理（页交换）
  解决操作系统问题：
  基于UNIX的系统：了解系统的CPU时间的花费比例，在用户模式是60%-75%的时间，在系统模式下是25%-40%的时间。该比率表现了系统的底层问题，如过度的页交换、执行太多的系统调用、进程太多。
  基于NT的系统：同基于UNIX的系统类似。只是监视工具更方便。
（六） （六）优化资源竞争
当多个进程同时访问资源时会发生竞争。可以通过V$SYSTEM_EVENT视图来检查资源竞争。
检测竞争问题：
V$RESOURCE_LIMIT视图，提供了当前和最大资源使用情况。
V$SYSTEM_EVENT视图，检查最高的平均等待时间。
V$LATCH视图，检查latch的使用情况。
V$WAITSTAT视图和V$SESSION_WAIT视图，等待缓冲区的时间。
  解决竞争问题：
  减少回滚段的竞争：可通过考察包含回滚段缓冲区的竞争来确定。可通过V$WAITSTAT视图来确定。可用下列语句获得数据，如果回滚段缓冲区的等待时间占总时间的1%以上，则考虑创建更多的回滚段。
SELECT class, count
    FROM v$waitstat
    WHERE class IN ('system undo header', 'system undo block',
       'undo header', 'undo block');
  减少多线程服务器进程的竞争：包括减少分发进程和共享服务器进程的竞争。
通过V$DISPATCHER视图来获得有关分发进程的信息。
SELECT network             "rotocol",
       SUM(busy) / ( SUM(busy) + SUM(idle) ) "Total Busy Rate"
    FROM v$dispatcher
    GROUP BY network;
V$QUEUE视图分发进程的响应队列活动的有关信息。
SELECT network "rotocol",
    DECODE( SUM(totalq), 0, 'No Responses',
       SUM(wait)/SUM(totalq)' '' hundredths of seconds')
    "Average Wait Time per Response"
    FROM v$queue q, v$dispatcher d
    WHERE q.type = 'DISPATCHER'
       AND q.paddr = d.paddr
    GROUP BY network;
可以使用ALTER SYSTEM命令的MTS_DISPATCHERS参数改变分发进程的数量。使用初始化参数MTS_MAX_DISPATCHERS改变最大分发进程的数量。
可以通过请求队列中的请求的数量来判断共享服务器进程的竞争。V$QUEUE视图包含了请求队列中的活动的信息。
SELECT DECODE( totalq, 0, 'No Requests',
       wait/totalq' '' hundredths of seconds')
   "Average Wait Time Per Requests"
   FROM v$queue
    WHERE type = 'COMMON';
ORACLE在负载大的时候自动增加共享进程的数量，所以不可能手动改变它的值。但可以通过初始化参数MTS_MAX_SERVERS来改变共享进程的数量的最大值。
  减少并行服务器进程的竞争：可通过V$PQ_SYSSTAT视图来确定适当的并行服务器进程的数量。一般情况下，并行服务器的最大数量依赖于系统的处理能力（CPU、I/O），无法改变该值。但如果频繁启动和关闭服务器，可以考虑改变参数PARALLEL_MIN_SERVERS的值。可以周期的检查V$PQ_SYSSTAT的值，以了解并行服务器的数量是否合适：
SELECT * FROM V$PQ_SYSSTAT
WHERE statistic = "Servers Busy";
  减少Redo日志缓冲区闩的竞争：Redo日志缓冲区很少会影响数据库的性能。可以通过V$SYSSTAT视图的REDO BUFFER ALLOCATION RETRIES检查等待缓冲区的时间。
SELECT name, value
    FROM v$sysstat
    WHERE name = 'redo buffer allocation retries';
可通过初始化参数LOG_BUFFER改变缓冲区的大小