ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORACLE数据库性能优化(转)

ORACLE数据库性能优化(转)

原创 Linux操作系统 作者:云江 时间:2009-03-26 16:20:08 0 删除 编辑
可以通过调整数据库应用程序、数据库和操作系统来增强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             "Protocol",
       SUM(busy) / ( SUM(busy) + SUM(idle) ) "Total Busy Rate"
    FROM v$dispatcher
    GROUP BY network;
V$QUEUE视图分发进程的响应队列活动的有关信息。
SELECT network "Protocol",
    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改变缓冲区的大小
posted on 2005-12-10 20:31 唐朝 阅读(429) 评论(0)  编辑 收藏 网摘 所属分类: Oracle

转载学习

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/65190/viewspace-580261/,如需转载,请注明出处,否则将追究法律责任。

上一篇: merge使用
请登录后发表评论 登录
全部评论

注册时间:2009-03-24

  • 博文量
    18
  • 访问量
    18363