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

发表于:2013-04-15来源:Csdn作者:xuyubotest点击数: 标签:调优
性能测试调优策略之数据库性能调优分析。和前面提到的SQL_TRACE不同,当我们遇到了数据库性能整体下降的时候,又没有特定的对象可以分析时,做一个Statspack报告是合适的。通过全面的检查,我们可以分析出系统瓶颈在哪儿,如果瓶颈出在sql上面,我们就能获取相应的sql,

  和前面提到的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