Oracle数据库优化基本思路

当前公司项目中使用oracle数据库的项目占据了相当高的比例，在项目生命周期的各个阶段，都存在着数据库优化的需求，本文档用于指导工程部技术人员快速定位问题，抓住数据库性能的主要瓶颈，从而规范调优工作，提高工作效率。

 

具有一定数据库管理和研发基础的技术人员，同时，本文档只介绍原理和方法，不提供具体的命令，亦不能代替操作手册。

 

PDCA：PDCA循环又叫戴明环，是美国质量管理专家戴明博士首先提出的，它是全面质量管理所应遵循的科学程序。全面质量管理活动的全部过程，就是质量计划的制订和组织实现的过程，这个过程就是按照PDCA循环，不停顿地周而复始地运转的。

Metalink：Oracle官方技术支持网站，该网站需要使用客户服务号进行注册（CSI），网站提供各种与Oracle公司全系列产品技术资料查询、软件补丁和升级包下载、以及24小时工程师支持等服务。目前，该网站正式更名为My Oracle support。

 

“好的性能”是在缺乏的时候定义的，而不是在出现的时候定义的。可以容易的识别出差的性能，而“好的性能”通常简单定义为没有出现“差的性能”。对于数据库而言，工程部的理解，“差的性能”可以体现在如下方面：

         本机和客户端远程访问数据库，连接响应不及时，或者出现连接长时间尝试后连接超时甚至无法连接；

         在数据库后台上的操作，包括：执行普通的增、删、改、查SQL，结果返回时间延迟较长，尤其是一些小的数据操作，等待的时间也超过了；存储过程和包的执行缓慢，也可以认为是“差的性能”；

         前端应用界面上的操作，如生成和查看报表，增加和修改某条数据，系统响应缓慢，用户体验差，产生失望的情绪，抱怨次数增加等。

相对而言，如果不存在上述情况，我们认为，数据库拥有一个“好的性能”。如图4-1所示，我们力求性能保持在用户满意的区间，消耗大量人力物力达成的性能极致并没有明显提升用户体验，得不偿失，性能太差，客户满意度将迅速降低。

                     图4-1 用户满意度与性能关系图

 

数据库优化的终极目标是数据库用户满意，而不是追求性能上的极致，更不是反映在具体的数据指标上。而且，随着业务的调整与项目生命周期地进行，数据库的优化也是一个持续而长久的过程，任何调整和优化，只要性能提高，优化就是有成效的。所以，我们提倡，数据库优化应该按照PDCA的法则循序渐进（见图4-2），而不应该是大刀阔斧的冒进行为。具体来说，性能的问题，定位好原因后，有针对性地优化，其他与之不相关的因素，不做调整。

       图4-2 数据库优化的PDCA法则

 

良好的性能，是除了安全、稳定之外，数据库最应该重视的一个方面。在数据库设计阶段，应用程序设计阶段，实施建设阶段，以及后期维护阶段，都应当对性能给予足够的重视，尤其是设计，有一句话说得好：“良好的性能是设计出来的，而不是调整出来的”。同时，在数据库维护的优化工作中，一个SQL语句的书写好坏，数据库表现出的效率可以相差千倍以上。所以，数据库的优化，需要程序开发人员（研发部门）、数据库管理人员（工程部）以及项目维护人员（项目组）长期不懈地共同努力来实现。

 

工程部负责公司所有第三方软硬件产品的支持工作，每天都要面对各种技术支持需求。如果数据库存在性能问题，我们希望，提出问题的同事，能够提供如下信息，或者服务台能够引导他们回答这些问题：

         数据库服务器及存储设备的硬件配置（主要是CPU、内存和SWAP空间，存储设备的型号与RAID级别等）；

         操作系统平台的版本（对于UNIX，最好提供uname -a的结果）；

         数据库软件的版本，具体到小版本，如10.2.0.1或者9.2.0.8；

         系统当前的性能统计结果（top、vmstat、prstat、iostat等）；

         数据库的参数配置，pfile/spfile文件，或者show parameter的结果；

         数据文件和表空间的分布情况；

         在线重做日志的分布情况；

         alert日志（如果日志很大，可以截取最近的1万条记录）；

         报错及告警的截图、详细信息（包括前台和后台），问题已经造成了什么影响，影响的范围有多大；

         性能低下的具体表现，如某条SQL语句执行速度降低或者应用的某个操作等待太久；

         系统及应用近期是否发生变化，如果有，做了哪些改动和调整；

         动态性能视图（V$SYSTEM_EVENT、V$SESSION_EVENT、V$SESSION_WAIT和V$SESSION）

         AWR和ADDM的结果

         statspack报告

         各个内存区域的命中率

以上非黄底色为必须提供的信息，黄底色部分为可选的信息，当问题需要深入分析时，往往需要用到这些信息。

 

性能潜在影响现实应用业务。每当试图解决性能问题的时候，必须确保仔细监控我们所要改善的领域，尤其要注意在修改前后系统的变化。我们必须采用一种系统的方法来发现性能问题的源头并实施适当的解决方案。这个方法要求在做任何变动之前必须为资源的利用情况和响应时间建立基线，而且要求在重新考察系统环境改动后的性能之前只能做少量的改动。

数据库服务器可能会遇到由于多个资源的竞争而导致的瓶颈。事实上，计算机系统在设计的时候考虑到一种资源缺乏的时候另一种资源可以尝试着补偿，这样做有时候也会导致补偿的资源出现赤字。如果物理内存被用完了，操作系统会将内存区置换到磁盘（SWAP分区）中去，这就会导致I/O瓶颈。科学中一个普遍的原理：时间与空间相互制约，牺牲时间可以换取空间，或者牺牲空间可以换取时间。因此，我们需要在各种资源中间找到平衡点，避免短板效应。

 

图5-1 数据库基本优化流程

根据上图，建议的优化基本流程：

一、接收到性能问题，按照3.4中列出的信息收集列表，将信息收集、整理好，这同时也是基线数据。

二、首先检查硬件问题，硬件是操作系统、数据库及应用运行的物理基础，如果硬件发生故障，系统很难正常运行，如：磁盘阵列电池失效时，其写IO性能将马上下降60%以上。

三、硬件不存在问题，对于一些比较明显的性能问题，可以直接分析报错或者问题现象，然后进行恰当的处理，如：数据库不能插入任何数据，根据提示，能定位到文件系统目录满或者表空间已经写满；又或者某个应用模块运行正常，但是升级了程序后性能急剧下降，可以要求研发人员检查程序。通常，比较直观的问题是不多见的。

四、并行考虑参数、物理硬件（CPU、内存、网络和IO）、bug（包括操作系统和数据库本身）和SQL造成的影响。一些比较典型的数据库参数，比如9i中数据高速缓存大小、10g中的sga_target，对于不同的应用业务，结合物理内存的大小，可以较明显地看出设置是否合理；物理硬件CPU、内存和网络重点关注其配置性能是否能满足应用的需要，如果应用正常运行，CPU和内存长时间保持较高的使用率，通常是由于其处理性能跟不上应用的需求，此时可以考虑扩容，将一个本身运行迅速的数据库服务器接入一个100M或者10M交换机时，从客户端发起的访问，性能也会大受影响。IO是非常容易成为性能瓶颈的一个因素，这是由于磁盘设备的物理结构所导致的，通常，对于数据文件的争用容易形成热点磁盘，一旦发现有这种情况，如果不能升级存储，可以考虑将热点文件分散到不同的磁盘和设备，或者从应用的角度在空间和时间上来分散原本可能产生密集IO读写的操作；而有些性能问题是由于操作系统和数据库的bug产生的，这些可以在厂家的网站上查到资料，下载补丁，通过修复系统和数据库的方式获得性能的提升；据统计，在数据库性能的影响因素中，应用的调整对于数据库性能的提升，能占据60%的效果（见图5-2）。所以，可以从awr、statspack信息中摘取一些资源消耗大的SQL提交给研发进行分析与优化，优先解决主要矛盾。这部分的优化，给研发人员推荐的书籍是Oracle公司技术副总裁Tomas Kyte编著的《Effective Oracle by Design》和《Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions》。

                           图5-2 数据库性能因素

五、当以上常规方法都失效时，需要考虑深入观察数据库的内部运行情况，从而发现导致性能不佳的蛛丝马迹：从性能视图中查看到的重点等待事件；10g开始，ADDM能够自动识别和报告各种资源瓶颈，例如：CPU竞争，锁相关的问题，或者某些SQL语句性能很糟糕等。企业管理器能够提供Oracle服务器资源利用情况的高级视图和详细视图，从而帮助管理员迅速的找到性能问题的缘由；在11g中，ADDM可以在集群中执行分析任务。ADDM能够向企业管理器的面板发出告警，向管理人员指出当前发生的资源竞争情况。SQL调优顾问组件甚至能够针对发现的性能问题给出建议的解决方案；与此同时，可以与应用的设计人员进行商量，从业务流程考虑优化的可能性，My Oracle support网站（原Metalink）也通过查询知识库和发起服务器请求（SR）的方式可以得到技术支持。

六、优化完成，请客户测试确认，记录最新的性能数据，对照基线数据比较优化成果，观察一段时间，总结经验，为下一次优化积累经验。

 

1.硬件故障

2.初始化参数不当

3.网络连接管理问题

4.游标使用不当

5.IO管理不当

6.在线重做日志太少（小），切换频率太快

7.多CPU服务器db_writer进程太少

8.高速缓存问题

9.回滚段太少、pct_free和pct_used设置不当

10.大表的全表扫描

11.索引、统计信息失效等，SQL查询不走索引

11.磁盘排序

12.SQL书写没有绑定变量

13.递归sql

14.SQL优化模式不当（CBO和RBO）