用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
　　
/
图表：
　　
http://dolphin-ygj.iyunv.com/upload/attachment/91510/93317ec6-36fd-3f41-a16a-5d07f99a4fcf.jpg
 
　　
　　分析：
从趋势图中我们可以看出，数据库每日读操作较为平稳，数据量大约在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



　　
　　
　　图表：  
http://dolphin-ygj.iyunv.com/upload/attachment/91514/a77691c4-50e5-3416-99ca-58d534b60161.jpg
 
　　分析：
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