性能测试调优策略之数据库性能调优分析

　　和前面提到的SQL_TRACE不同，当我们遇到了数据库性能整体下降的时候，又没有特定的对象可以分析时，做一个Statspack报告是合适的。通过全面的检查，我们可以分析出系统瓶颈在哪儿，如果瓶颈出在sql上面，我们就能获取相应的sql，通过SQL_TRACE来分析。

　　oracle Statspack从Oracle8.1.6被引入，马上成为DBA和Oracle专家用来诊断数据库性能的强有力工具。通过Statspack我们可以很容易的确定Oracle数据库的瓶颈所在，记录数据库性能状态，也可以使远程技术人员迅速了解的的数据库运行状况。所以，了解和使用Statspack 对于DBA来说至关重要。

　　它的原理是

　　1、运行oracle自带脚本，生成一系列的统计表。

　　2、生成快照，采样(运行statspack.snap可生成快照，一般通过自动任务生成快照)

　　3、根据快照生成报告。

　　除了statspack，oracle10g以后都提供一个AWR报告，它是oracle自动采集的，采集周期为1小时，不需要人工干预，收集的信息和statspack非常像，很多时候选择哪种方式都可以。

　　AWR可以通过10g以后的oracle DBconsole去生成。

　　如何安装statspack，这里简单的介绍一下，有兴趣的同事可以通过查阅资料更深入的了解和实践。

　　首先检查oracle系统参数，job_queue_process：为了能够建立自动任务，执行数据收集，此参数必须大于0;timed_statistics，设置为true，使收集的时间信息存储在V$sessstats和V$sysstats等动态性能视图中。

　　如果是9i以后版本可以以oradba的身份登录。sqlplus / as sysdba。首先创建表空间create tablespace perfstat datafile 'D:\**\oradata\orcl\perstat.ora'size 100m extent management local;空间视实际情况而定，如果只是学习使用，不需要那么大，实际生产环境下，可以设置大一些。毕竟Statspack的报表数据还是相当占空间的。然后运行脚本%oracle_home%\rdbms\admin\spcreate.sql，安装statspack，根据提示输入密码，表空间，临时表空间，安装完成，查看lis后缀的日志文件确认是否有错误。

　　select dt.table_name from dba_tables dt where dt.owner='PERFSTAT'可以查看采样数据存储的表格。

　　下一步我们测试statspack，刚才如果create的时候，默认修改了当前的连接用户为perfstat，运行statspack.snap可以产生系统快照，运行两次，产生两次快照。

　　execute statspack.snap;然后执行脚本%oracle_home%\rdbms\admin\spreport.sql就可以生成基于两个时间点的报告。如果一切正常，可以在运行批处理的目录下查看生成的报告文件。

　　生成了statspack报告，我们就可以开始分析了。需要提醒的是，真正看懂这样一份报告，并不需要知道所有指标的含义，最好能够了解oracle内部的运行机制，理解的越深，判断数据库性能也就越准确。这里只能谈谈我的一些理解和思路，供大家思考。

　　我们来看一份报告例子。

　　对于我们现在已有的系统来说，绝大多数都是属于OLTP系统(在线事务处理系统),sql执行非常密集，我们不妨关注以下2个指标，Library Hit,Buffer Hit,前面一个体现了共享池命中率，如果很多SQL不能重用，需要重复解析的话，会大大降低系统的性能。后面一个体现了sql需要的数据块是否能够保留在内存中，这样执行效率要比从磁盘读取数据要高很多。

　　我们来看报告第一部分，主要是数据库和实例的信息，然后是采集周期里面系统的信息。这里有一个数值，DB time: 表示用户操作花费时间，判断一下，在收集周期里面，用户时间占用的比率，然后结合top5来分析。Load Profile描述了数据库资源负载的明细列表。可以通过字面含义来理解它们。这里我们可以关注下，物理读写，逻辑读写，硬分析次数等指标。Redo size是日志的生成量，分为每秒和每事务所产生的，通常在很繁忙的系统中日志生成量可能达到上百k，甚至几百k.逻辑读一般发生在内存中，和物理读是区别对待的。硬分析次数前面已经提到了。

　　Instance Efficiency Indicators表示内存效率的统计信息，对于OLTP来说，尽可能都接近100%，原因前面已经说过了。如果哪项数值过低，就要做相应的分析研究。

　　Top 5 Timed Events一般是我每次重点关注的地方，也是我认为最主要的地方。如果这一部分显示前五位的等待事件，并没有占用很长时间，说明系统状态看起来很好。那么我们可能需要多采集一些时间段的数据来分析了。

　　那么结合我们的top 5的等待事件，我们可以来衡量不同等级的top

　　sql:1.消耗最多CPU的(逻辑IO比较多的)2.导致过多物理I/O的(物理IO比较多的)3.执行次数较频繁的(Execution次数比较多的)4.执行时间较长的(Elapse time比较长的)

　　先看看我的机器上采集的结果。

　　control file sequential read

　　control file single write ：控制文件连续读/控制文件单个写对单个控制文件I/O 存在问题时，这两个事件会出现。如果等待比较明显，检查单个控制文件，看存放位置是否存在I/O 瓶颈。

　　control file parallel write：当server 进程更新所有控制文件时，这个事件可能出现。如果等待很短，可以不用考虑。如果等待时间较长，检查存放控制文件的物理磁盘I/O 是否存在瓶颈。

　　以XXX测试为例，我当时采集的是AWR报告中的一部分，和statspack基本一致，可以看到排名第一位的是顶级 SQL 语句。不得不说oracle的分析报告非常智能，非常明细，能够帮助我们迅速找到问题，配合DBA来调优。

　　前面这些内容是报告中最重要的部分，虽然不同的系统生成的报告都会不一样，但是解决问题的思路是一样的，根据前面的一手信息，我们能够了解到等待时间很长的事件，再去其他的部分查找原因。

　　下面简单说说后面报告的含义，Statistic表示各种操作占用数据库的时间比例，接下来是等待事件的明细，主要用来配合前面的top5事件来分析。等待事件(Wait Events)是Oracle中比较复杂难懂的概念。Oracle 的等待事件是衡量Oracle 运行状况的重要依据及指标。等待事件很多这里不一一赘述。常见的等待事件，一般都有对应的分析手段，大家可以参考oracle的资料学习。这里我们根据XXX测试中的实例来分析。可以看到排名第二位的事件是等待 "日志文件同步" 事件消耗了大量数据库时间。英文翻译就是log file sync: 日志文件同步。当一个用户提交或回滚数据 时，LGWR (Log Writer)将session 会话的重做由redo buffer 写入到重做日志中。log file sync 必须等待这一过程成功完成(Oracle 通过写redo log file 保证commit 成功的数据不丢失)，这个事件说明提交可能过于频繁。为了减少这种等待事件，可以尝试每次提交更多的记录，将重做日志置于较快的磁盘上。

原文转自：http://blog.csdn.net/xuyubotest/article/details/8158500