Oracle数据库基本优化思路

一、性能的评价
        一般来讲，对于数据库而言，性能差主要体现在以下方面： 1.本机和客户端远程访问数据库，连接响应不及时，或者出现连接长时间 尝试后连接超时甚至无法连接； 2.在数据库后台上的操作，包括：执行普通的增、删、改、查SQL，结果返回时   间延迟较长，尤其是一些小的数据操作，等待的时间也超过了；存储过程和包的执行缓慢，也可以认为是“差的性能”；     3.前端应用界面上的操作，如生成和查看报表，增加和修改某条数据，系统响应 缓慢，用户体验差，产生失望的情绪，抱怨次数增加等。 相对而言，如果不存在上述情况，我们认为，数据库拥有一个比较好的性能。
二、优化的目标
       随着业务的调整与项目生命周期地进行，数据库的优化也是一个持续而长久的过程，任何调整和优化，只要性能提高，优化就是有成效的。性能的问题，定位好原因后，有针对性地优化，其他与之不相关的因素，不做调整。
三、优化的环节和人员
        良好的性能，是除了安全、稳定之外，数据库最应该重视的一个方面。在数据库设计阶段，应用程序设计阶段，实施建设阶段，以及后期维护阶段，都应当对性能给予 足够的重视，尤其是设计，有一句话说得好：“良好的性能是设计出来的，而不是调整出来的”。同时在数据库维护的优化工作中，一个SQL语句的书写好坏，数据库表现出的效率可以相差千倍以上。所以，数据库的优化，需要程序开发人员、数据库管理人员以及项目维护人员长期不懈地共同努力来实现。
四、如何提出问题
       如果数据库存在性能问题，需要我们掌握和研究以下各方面的信息：
            1.数据库服务器及存储设备的硬件配置（主要是CPU、内存和SWAP空间，存储设备的型号与RAID级别等）；
            2.操作系统平台的版本（对于UNIX，最好提供uname -a的结果）；
            3.数据库软件的版本，具体到小版本，如10.2.0.4或者9.2.0.8；
            4.系统当前的性能统计结果（top、vmstat、prstat、iostat、sar -d等）；
            5.数据库的参数配置，pfile/spfile文件，或者show parameter的结果；
            6.数据文件和表空间的分布情况；
            7.在线重做日志的分布情况；
           8.alert日志（如果日志很大，可以通过tail -f 2000 alert_testdb.log截取最近的2000条记录）；
           9.报错及告警的截图、详细信息（包括前台和后台），问题已经造成了什么影响，影响的范围有多大；
         10.性能低下的具体表现，如某条SQL语句执行速度降低或者应用的某个操作等待太久；
         11.系统及应用近期是否发生变化，如果有，做了哪些改动和调整；
         12.动态性能视图（V$SESSION_WAIT,V$SESSION,V$SYSTEM_EVENT,V$SESSION_EVENT、）
         13.AWR和ADDM的结果 
五、如何优化
     5.1 优化思路和原则
       性能潜在影响现实应用业务。每当试图解决性能问题的时候，需要注意在修改前后系统的变化。我们必须采用一种系统的方法来发现性能问题的源头并实施适当的解决方案。这个方法要求在做任何变动之前必须为资源的利用情况和响应时间建立基线，而且要求在重新考察系统环境改动后的性能之前只能做少量的改动。 数据库服务器可能会遇到由于多个资源的竞争而导致的瓶颈。事实上，计算机系统在设计的时候考虑到一种资源缺乏的时候另一种资源可以尝试着补偿，这样做有时候也会导致补偿的资源出现赤字。如果物理内存被用完了，操作系统会将内存区置换到磁盘（SWAP分区）中去，这就会导致I/O瓶颈。科学中一个普遍的原理：时间与空间相互制约，牺牲时间可以换取空间，或者牺牲空间可以换取时间。因此，我们需要在各种资源中间找到平衡点，避免短板效应。 
         5.2 优化流程

                                                       

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

         根据上图，建议的优化基本流程：
           一、在系统良好阶段收集如操作系统CPU，内存情况等，数据库的平均调用数、事务数等，形成基线数据。
           二、首先检查硬件问题，硬件是操作系统、数据库及应用运行的物理基础，如果硬件发生故障，系统很难正常运行，如：磁盘阵列电池失效时，其写IO性能将马上下降60%以上。
          三、硬件不存在问题，对于一些比较明显的性能问题，可以直接分析报错或者问题现象，然后进行恰当的处理，如：数据库不能插入任何数据，根据提示，能定位到文件系 统目录满或者表空间已经写满；又或者某个应用模块运行正常，但是升级了程序后性能急剧下降，可以要求研发人员检查程序。通常，比较直观的问题是不多见的。
         四、并行考虑参数、物理硬件（CPU、内存、网络和IO）、bug（包括操作系统和数据库本身）和SQL造成的影响。一些比较典型的数据库参数，比如9i中数据高速缓存大小、10g中的sga_target，对于不同的应用业务，结合物理内存的大小，可以较明显地看出设置是否合理；物理硬件CPU、内存和网络重点关注其配置性能是否能满足应用的需要，如果应用正常运行，CPU和内存长时间保持较高的使用率，通常是由于其处理性能跟不上应用的需求，此时可以考虑扩容，将一个本身运行迅速的数据库服务器接入一个100M或者10M交换机时，从客户端发起的访问，性能也会大受影响。IO是非常容易成为性能瓶颈的一个因素，这是由于磁盘设备的物理结构所导致的，通常，对于数据文件的争用容易形成热点磁盘，一旦发现有这种情况，如果不能升级存储，可以考虑将热点文件分散到不同的磁盘和设备，或者从应用的角度在空间和时间上来分散原本可能产生密集IO读写的操作；而有些性能问题是由于操作系统和数据库的bug产生的，这些可以在厂家的网站上查到资料，下载补丁，通过修复系统和数据库的方式获得性能的提升；据统计，在数据库性能的影响因素中，应用的调整对于数据库性能的提升，能占据60%的效果（见下图）。所以，可以从awr信息中摘取一些资源消耗大的SQL进行分析与优化，优先解决主要矛盾。

                                                          

                                                                                                     图 数据库性能因素

          五、当以上常规方法都失效时，需要考虑深入观察数据库的内部运行情况，从而发现导致性能不佳的蛛丝马迹：从性能视图中查看到的重点等待事件；10g开始，ADDM能够自动识别和报告各种资源瓶颈，例如：CPU竞争，锁相关的问题，或者某些SQL语句性能很糟糕等。企业管理器能够提供Oracle服务器资源利用情况的高级视图和详细视图，从而帮助管理员迅速的找到性能问题的缘由。
         六、优化完成，记录最新的性能数据，对照基线数据比较优化成果，观察一段时间，总结经验，为下一次优化积累经验。
  六.常见的数据库性能原因
         1.硬件故障
         2.初始化参数不当
         3.网络连接管理问题
         4.游标使用不当
         5.IO管理不当
         6.在线重做日志太少（小），切换频率太快
         7.多CPU服务器db_writer进程太少
         8.高速缓存问题
         9.回滚段太少、pct_free和pct_used设置不当
         10.大表的全表扫描
         11.索引、统计信息失效等，SQL查询不走索引
         12.磁盘排序

      特别强调，当统计信息不是最新时，可能会导致错误的执行计划，原本可以走索引的，结果却全部扫描了。

      有时候如果出现SQL短时间不能优化的情况，而SQL执行计划在测试库里运行很好，但是在生产库走了另外一个执行计划，可以把测试库的stored outline导出放到正式库中！让正式库的sql语句按照测试库的执行计划来走！

当使用autotrace实现查看执行计划和统计信息时，对应的统计信息字段的含义如下：

recursive calls：  
     Number of recursive calls generated at both the user and system level. Oracle maintains tables used for internal processing.
  When Oracle needs to make a change to these tables, it internally generates an internal SQL statement, which in turn generates a recursive call.
db block gets：
     Number of times a CURRENT block was requested.
consistent gets：
     Number of times a consistent read was requestedfor a block.
physical reads：
     Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all readsinto buffer cache.
redo size：
     Total amount of redo generated in bytes.
bytes sent via SQL*Net to client：
     Total number of bytes sent to the client from the foreground processes.
bytes received via SQL*Net from client：
     Total number of bytes received from the client over Oracle Net.
SQL*Net roundtrips to/from client：
     Total number of Oracle Net messagessent to and received from the client.
sorts (memory)：
     Number of sort operations that were performed completely in memory and did notrequire any disk writes.
sorts (disk)：
     Number of sort operations that required at least one disk write.
rows processed：
     Number of rows processed during the operation.