用Oracle中的Statspack诊断数据库性能实例

欢迎进入Oracle社区论坛，与200万技术人员互动交流 >>进入 通过Statspack我们可以很容易的确定Oracle数据库的瓶颈所在，记录数据库性能状态，也可以使远程技术支持人员迅速了解你的数据库运行状况。因此了解和使用Statspack对于DBA来说至关重要。 整理分析结

欢迎进入Oracle社区论坛，与200万技术人员互动交流 >>进入

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

　　整理分析结果

　　可以通过各种工具建立图表，使我们收集的数据更直观，更有说服力。
以下是我给一个客户做的分析报告的实例。

　　1．物理读写IO操作：

　　观察物理IO访问，可以看出数据库日常访问的峰值及繁忙程度。

　　脚本：此脚本按时间生成统计数据(注:以下示例以8i为基础,SQL脚本中引用的statistic#在不同版本代表的意义可能不同,对于9i等版本,你应该修改相应参数值)

　　SQL代码

select<br>substr(to_char(snap_time,'yyyy-mm-ddHH24:MI:SS'),12),<br>(newreads.value-oldreads.value)reads,<br>(newwrites.value-oldwrites.value)writes<br>from<br>perfstat.stats$sysstatoldreads,<br>perfstat.stats$sysstatnewreads,<br>perfstat.stats$sysstatoldwrites,<br>perfstat.stats$sysstatnewwrites,<br>perfstat.stats$snapshotsn<br>where<br>newreads.snap_id=sn.snap_id<br>and<br>newwrites.snap_id=sn.snap_id<br>and<br>oldreads.snap_id=sn.snap_id-1<br>and<br>oldwrites.snap_id=sn.snap_id-1<br>and<br>oldreads.statistic#=40<br>and<br>newreads.statistic#=40<br>and<br>oldwrites.statistic#=41<br>and<br>newwrites.statistic#=41<br>and<br>(newreads.value-oldreads.value)>0<br>and<br>(newwrites.value-oldwrites.value)>0<p></p>
<p>select<br>substr(to_char(snap_time,'yyyy-mm-ddHH24:MI:SS'),12),<br>(newreads.value-oldreads.value)reads,<br>(newwrites.value-oldwrites.value)writes<br>from<br>perfstat.stats$sysstatoldreads,<br>perfstat.stats$sysstatnewreads,<br>perfstat.stats$sysstatoldwrites,<br>perfstat.stats$sysstatnewwrites,<br>perfstat.stats$snapshotsn<br>where<br>newreads.snap_id=sn.snap_id<br>and<br>newwrites.snap_id=sn.snap_id<br>and<br>oldreads.snap_id=sn.snap_id-1<br>and<br>oldwrites.snap_id=sn.snap_id-1<br>and<br>oldreads.statistic#=40<br>and<br>newreads.statistic#=40<br>and<br>oldwrites.statistic#=41<br>and<br>newwrites.statistic#=41<br>and<br>(newreads.value-oldreads.value)>0<br>and<br>(newwrites.value-oldwrites.value)>0<br>/</p>

　　图表：

　　分析：

　　从趋势图中我们可以看出，数据库每日读操作较为平稳，数据量大约在4000左右。在下午2点到5点期
间比较繁忙。峰值达到150000左右。

　　数据库写操作变化也比较平稳，数据改变量在80000左右，凌晨一点半到早晨8点半左右数据库访问极少。
这是一个以写为主的数据库，我们需要更多注意的是写竞争。

　　2．Buffer命中率

　　Sql代码

<br>select<br>substr(to_char(snap_time,'yyyy-mm-ddHH24:MI'),12),<br>round(100*(((a.value-e.value)+(b.value-f.value))-(c.value-g.value))/<br>((a.value-e.value)+(b.value-f.value)))<br>"BUFFERHITRATIO"<br>from<br>perfstat.stats$sysstata,<br>perfstat.stats$sysstatb,<br>perfstat.stats$sysstatc,<br>perfstat.stats$sysstatd,<br>perfstat.stats$sysstate,<br>perfstat.stats$sysstatf,<br>perfstat.stats$sysstatg,<br>perfstat.stats$snapshotsn<br>where<br>a.snap_id=sn.snap_id<br>and<br>b.snap_id=sn.snap_id<br>and<br>c.snap_id=sn.snap_id<br>and<br>d.snap_id=sn.snap_id<br>and<br>e.snap_id=sn.snap_id-1<br>and<br>f.snap_id=sn.snap_id-1<br>and<br>g.snap_id=sn.snap_id-1<br>and<br>a.statistic#=39<br>and<br>e.statistic#=39<br>and<br>b.statistic#=38<br>and<br>f.statistic#=38<br>and<br>c.statistic#=40<br>and<br>g.statistic#=40<br>and<br>d.statistic#=41<p></p>
<p>select<br>substr(to_char(snap_time,'yyyy-mm-ddHH24:MI'),12),<br>round(100*(((a.value-e.value)+(b.value-f.value))-(c.value-g.value))/<br>((a.value-e.value)+(b.value-f.value)))<br>"BUFFERHITRATIO"<br>from<br>perfstat.stats$sysstata,<br>perfstat.stats$sysstatb,<br>perfstat.stats$sysstatc,<br>perfstat.stats$sysstatd,<br>perfstat.stats$sysstate,<br>perfstat.stats$sysstatf,<br>perfstat.stats$sysstatg,<br>perfstat.stats$snapshotsn<br>where<br>a.snap_id=sn.snap_id<br>and<br>b.snap_id=sn.snap_id<br>and<br>c.snap_id=sn.snap_id<br>and<br>d.snap_id=sn.snap_id<br>and<br>e.snap_id=sn.snap_id-1<br>and<br>f.snap_id=sn.snap_id-1<br>and<br>g.snap_id=sn.snap_id-1<br>and<br>a.statistic#=39<br>and<br>e.statistic#=39<br>and<br>b.statistic#=38<br>and<br>f.statistic#=38<br>and<br>c.statistic#=40<br>and<br>g.statistic#=40<br>and<br>d.statistic#=41</p>

　　图表：

　　分析：

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

　　安装statspack

SQL>connect/assysdba<p></p>
<p>SQL>@?/rdbms/admin/spcreate</p>

　　卸载

SQL>connect/assysdba<br>SQL>@?/rdbms/admin/spdrop

/*<br>收集信息前把timed_statistics=true;<br>altersystemsettimed_statistics=true;<br>*/

　　收集信息

SQL>connectperfstat/perfstat<br>SQL>executestatspack.snap;

　　自动收集

SQL>connectperfstat/perfstat<br>SQL>@?/rdbms/admin/spauto

　　删掉自动收集的job.

SQL>select*fromuser_jobs;<p></p><p>SQL>execdbms_job.remove(JOB_ID)
</p><p>/*<br>executestatspack.snap(i_snap_level=>10,i_modify_parameter=>'true');<br>Levels=5Additionaldata:SQLStatements<br>Levels=6Thislevelincludesallstatisticsgatheredinthelowerlevel(s).
</p><p>Levels=7segmentslevelstatistics<br>Levels=10Additionalstatistics:Childlatches<br>i_modify_parameter=>'true'/'false'决定是否保存level的值,下一次执行继续使用<br>*/</p>

　　产生报告

SQL>conectperfstat/perfstat<br>SQL>@?/rdbms/admin/spreport