用oracle statspack 诊断数据库性能实例

 

 

 

Oracle Statspack 从Oracle8.1.6开始被引入Oracle,并马上成为DBA和Oracle专家用来诊断数据库性能的强有力的工具。

通过Statspack我们可以很容易的确定Oracle数据库的瓶颈所在，记录数据库性能状态，也可以使远程技术支持人员迅速了解你的数据库运行状况。
因此了解和使用Statspack对于DBA来说至关重要。

 

 

整理分析结果
可以通过各种工具建立图表，使我们收集的数据更直观，更有说服力。
以下是我给一个客户做的分析报告的实例。
1． 物理读写IO 操作：
观察物理IO 访问，可以看出数据库日常访问的峰值及繁忙程度。
脚本：此脚本按时间生成统计数据(注:以下示例以8i 为基础,SQL 脚本中引用的statistic#在不同
版本代表的意义可能不同,对于9i 等版本,你应该修改相应参数值)

select
substr(to_char(snap_time,'yyyy-mm-dd HH24:MI:SS'),12),
(newreads.value-oldreads.value) reads,
(newwrites.value-oldwrites.value) writes
from
perfstat.stats$sysstat oldreads,
perfstat.stats$sysstat newreads,
perfstat.stats$sysstat oldwrites,
perfstat.stats$sysstat newwrites,
perfstat.stats$snapshot sn
where
newreads.snap_id = sn.snap_id
and
newwrites.snap_id = sn.snap_id
and
oldreads.snap_id = sn.snap_id-1
and
oldwrites.snap_id = sn.snap_id-1
and
oldreads.statistic# = 40
and
newreads.statistic# = 40
and
oldwrites.statistic# = 41
and
newwrites.statistic# = 41
and
(newreads.value-oldreads.value) > 0
and
(newwrites.value-oldwrites.value) > 0

 
/
图表：

 

 

 分析：
从趋势图中我们可以看出，数据库每日读操作较为平稳，数据量大约在4000 左右。在下午2 点到5 点期
间比较繁忙。峰值达到150000 左右。
数据库写操作变化也比较平稳，数据改变量在80000 左右，凌晨一点半到早晨8 点半左右数据库访问极少。
这是一个以写为主的数据库，我们需要更多注意的是写竞争。
2． Buffer 命中率

select
substr(to_char(snap_time,'yyyy-mm-dd HH24:MI'),12),
round(100 * (((a.value-e.value)+(b.value-f.value))-(c.value-g.value)) /
((a.value-e.value)+(b.value-f.value)))
"BUFFER HIT RATIO"
from
perfstat.stats$sysstat a,
perfstat.stats$sysstat b,
perfstat.stats$sysstat c,
perfstat.stats$sysstat d,
perfstat.stats$sysstat e,
perfstat.stats$sysstat f,
perfstat.stats$sysstat g,
perfstat.stats$snapshot sn
where
a.snap_id = sn.snap_id
and
b.snap_id = sn.snap_id
and
c.snap_id = sn.snap_id
and
d.snap_id = sn.snap_id
and
e.snap_id = sn.snap_id-1
and
f.snap_id = sn.snap_id-1
and
g.snap_id = sn.snap_id-1
and
a.statistic# = 39
and
e.statistic# = 39
and
b.statistic# = 38
and
f.statistic# = 38
and
c.statistic# = 40
and
g.statistic# = 40
and
d.statistic# = 41

 

 

 

图表：  

 

分析：
Buffer（buffer hit ratio）命中率是考察Oracle 数据库性能的重要指标，它代表在内存中找到需要数据的比
率,一般来说，如果该值小于90%，则可能说明数据库存在大量代价昂贵的IO 操作，数据库需要调整。
我们数据库的buffer 命中率几乎接近100%,最低值在95%左右，这个比率是比较优化的。

 

安装statspack
 
 SQL>connect / as sysdba

 SQL>@?/rdbms/admin/spcreate

卸载

 SQL>connect / as sysdba
 SQL>@?/rdbms/admin/spdrop

 /*
 收集信息前把timed_statistics = true;
 alter system set timed_statistics = true;
 */

收集信息

 SQL>connect perfstat/perfstat
 SQL>execute statspack.snap;

自动收集
 SQL>connect perfstat/perfstat
 SQL>@?/rdbms/admin/spauto

  删掉自动收集的job.

 SQL>select * from user_jobs;

 SQL>exec dbms_job.remove(JOB_ID)

/*
execute statspack.snap(i_snap_level=>10, i_modify_parameter=>'true');
Levels  = 5  Additional data:  SQL Statements
Levels  = 6  This level includes all statistics gathered in the lower level(s). 

Levels  = 7 segments level statistics
Levels  = 10 Additional statistics:  Child latches
i_modify_parameter=>'true'/'false' 决定是否保存level 的值,下一次执行继续使用
*/ 

产生报告

 SQL>conect perfstat/perfstat
 SQL>@?/rdbms/admin/spreport