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

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

整理分析结果

可以通过各种工具建立图表使我们收集的数据更直观更有说服力

以下是我给一个客户做的分析报告的实例

．物理读写IO操作

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

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

SQL代码

select

substr(to_char(snap_timeyyyymmddHH:MI:SS))

(newreadsvalueoldreadsvalue)reads

(newwritesvalueoldwritesvalue)writes

from

perfstatstats$sysstatoldreads

perfstatstats$sysstatnewreads

perfstatstats$sysstatoldwrites

perfstatstats$sysstatnewwrites

perfstatstats$snapshotsn

where

newreadssnap_id=snsnap_id

and

newwritessnap_id=snsnap_id

and

oldreadssnap_id=snsnap_id

and

oldwritessnap_id=snsnap_id

and

oldreadsstatistic#=

and

newreadsstatistic#=

and

oldwritesstatistic#=

and

newwritesstatistic#=

and

(newreadsvalueoldreadsvalue)>

and

(newwritesvalueoldwritesvalue)>

select

substr(to_char(snap_timeyyyymmddHH:MI:SS))

(newreadsvalueoldreadsvalue)reads

(newwritesvalueoldwritesvalue)writes

from

perfstatstats$sysstatoldreads

perfstatstats$sysstatnewreads

perfstatstats$sysstatoldwrites

perfstatstats$sysstatnewwrites

perfstatstats$snapshotsn

where

newreadssnap_id=snsnap_id

and

newwritessnap_id=snsnap_id

and

oldreadssnap_id=snsnap_id

and

oldwritessnap_id=snsnap_id

and

oldreadsstatistic#=

and

newreadsstatistic#=

and

oldwritesstatistic#=

and

newwritesstatistic#=

and

(newreadsvalueoldreadsvalue)>

and

(newwritesvalueoldwritesvalue)>

/

图表

)thisstylewidth=; border= twffan=done>

分析

从趋势图中我们可以看出数据库每日读操作较为平稳数据量大约在左右在下午点到点期

间比较繁忙峰值达到左右

数据库写操作变化也比较平稳数据改变量在左右凌晨一点半到早晨点半左右数据库访问极少

这是一个以写为主的数据库我们需要更多注意的是写竞争

．Buffer命中率

Sql代码

select

substr(to_char(snap_timeyyyymmddHH:MI))

round(*(((avalueevalue)+(bvaluefvalue))(cvaluegvalue))/

((avalueevalue)+(bvaluefvalue)))

BUFFERHITRATIO

from

perfstatstats$sysstata

perfstatstats$sysstatb

perfstatstats$sysstatc

perfstatstats$sysstatd

perfstatstats$sysstate

perfstatstats$sysstatf

perfstatstats$sysstatg

perfstatstats$snapshotsn

where

asnap_id=snsnap_id

and

bsnap_id=snsnap_id

and

csnap_id=snsnap_id

and

dsnap_id=snsnap_id

and

esnap_id=snsnap_id

and

fsnap_id=snsnap_id

and

gsnap_id=snsnap_id

and

astatistic#=

and

estatistic#=

and

bstatistic#=

and

fstatistic#=

and

cstatistic#=

and

gstatistic#=

and

dstatistic#=

select

substr(to_char(snap_timeyyyymmddHH:MI))

round(*(((avalueevalue)+(bvaluefvalue))(cvaluegvalue))/

((avalueevalue)+(bvaluefvalue)))

BUFFERHITRATIO

from

perfstatstats$sysstata

perfstatstats$sysstatb

perfstatstats$sysstatc

perfstatstats$sysstatd

perfstatstats$sysstate

perfstatstats$sysstatf

perfstatstats$sysstatg

perfstatstats$snapshotsn

where

asnap_id=snsnap_id

and

bsnap_id=snsnap_id

and

csnap_id=snsnap_id

and

dsnap_id=snsnap_id

and

esnap_id=snsnap_id

and

fsnap_id=snsnap_id

and

gsnap_id=snsnap_id

and

astatistic#=

and

estatistic#=

and

bstatistic#=

and

fstatistic#=

and

cstatistic#=

and

gstatistic#=

and

dstatistic#=

图表

image javascript:return big(this) height= alt= src=http://imgeducitycn/img_///jpg width= javascript:if(thiswidth>)thisstylewidth=; border= twffan=done>

分析

Buffer（bufferhitratio）命中率是考察Oracle数据库性能的重要指标它代表在内存中找到需要数据的比

率一般来说如果该值小于%则可能说明数据库存在大量代价昂贵的IO操作数据库需要调整

我们数据库的buffer命中率几乎接近%最低值在%左右这个比率是比较优化的

安装statspack

borderColorDark=#ffffff cellPadding= width= align=center borderColorLight=# border=>ee>

SQL>connect/assysdba

SQL>@?/rdbms/admin/spcreate

卸载

borderColorDark=#ffffff cellPadding= width= align=center borderColorLight=# border=>ee>

SQL>connect/assysdba

SQL>@?/rdbms/admin/spdrop

/*

收集信息前把timed_statistics=true;

altersystemsettimed_statistics=true;

*/

收集信息

borderColorDark=#ffffff cellPadding= width= align=center borderColorLight=# border=>ee>

SQL>connectperfstat/perfstat

SQL>executestatspacksnap;

自动收集

borderColorDark=#ffffff cellPadding= width= align=center borderColorLight=# border=>ee>

SQL>connectperfstat/perfstat

SQL>@?/rdbms/admin/spauto

删掉自动收集的job

borderColorDark=#ffffff cellPadding= width= align=center borderColorLight=# border=>ee>

SQL>select*fromuser_jobs;

SQL>execdbms_jobremove(JOB_ID)

/*

executestatspacksnap(i_snap_level=>i_modify_parameter=>true);

Levels=Additionaldata:SQLStatements

Levels=Thislevelincludesallstatisticsgatheredinthelowerlevel(s)

Levels=segmentslevelstatistics

Levels=Additionalstatistics:Childlatches

i_modify_parameter=>true/false决定是否保存level的值下一次执行继续使用

*/

产生报告

borderColorDark=#ffffff cellPadding= width= align=center borderColorLight=# border=>ee>

SQL>conectperfstat/perfstat

SQL>@?/rdbms/admin/spreport