用Oracle中的Statspack診斷數(shù)據(jù)庫性能實例
通過Statspack我們可以很容易的確定Oracle數(shù)據(jù)庫的瓶頸所在，記錄數(shù)據(jù)庫性能狀態(tài)，也可以使遠程技術支持人員迅速了解你的數(shù)據(jù)庫運行狀況。因此了解和使用Statspack對于DBA來說至關重要。
整理分析結果
可以通過各種工具建立圖表，使我們收集的數(shù)據(jù)更直觀，更有說服力。
以下是我給一個客戶做的分析報告的實例。
1．物理讀寫IO操作：
觀察物理IO訪問，可以看出數(shù)據(jù)庫日常訪問的峰值及繁忙程度。
腳本：此腳本按時間生成統(tǒng)計數(shù)據(jù)(注:以下示例以8i為基礎,SQL腳本中引用的statistic#在不同版本代表的意義可能不同,對于9i等版本,你應該修改相應參數(shù)值)
SQL代碼
select
substr(to_char(snap_time,'yyyy-mm-ddHH24:MI:SS'),12),
(newreads.value-oldreads.value)reads,
(newwrites.value-oldwrites.value)writes
from
perfstat.stats$sysstatoldreads,
perfstat.stats$sysstatnewreads,
perfstat.stats$sysstatoldwrites,
perfstat.stats$sysstatnewwrites,
perfstat.stats$snapshotsn
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)>0select
substr(to_char(snap_time,'yyyy-mm-ddHH24:MI:SS'),12),
(newreads.value-oldreads.value)reads,
(newwrites.value-oldwrites.value)writes
from
perfstat.stats$sysstatoldreads,
perfstat.stats$sysstatnewreads,
perfstat.stats$sysstatoldwrites,
perfstat.stats$sysstatnewwrites,
perfstat.stats$snapshotsn
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
/
圖表：
分析：
從趨勢圖中我們可以看出，數(shù)據(jù)庫每日讀操作較為平穩(wěn)，數(shù)據(jù)量大約在4000左右。在下午2點到5點期
間比較繁忙。峰值達到150000左右。
數(shù)據(jù)庫寫操作變化也比較平穩(wěn)，數(shù)據(jù)改變量在80000左右，凌晨一點半到早晨8點半左右數(shù)據(jù)庫訪問極少。
這是一個以寫為主的數(shù)據(jù)庫，我們需要更多注意的是寫競爭。
2．Buffer命中率
Sql代碼
select
substr(to_char(snap_time,'yyyy-mm-ddHH24:MI'),12),
round(100*(((a.value-e.value)+(b.value-f.value))-(c.value-g.value))/
((a.value-e.value)+(b.value-f.value)))
"BUFFERHITRATIO"
from
perfstat.stats$sysstata,
perfstat.stats$sysstatb,
perfstat.stats$sysstatc,
perfstat.stats$sysstatd,
perfstat.stats$sysstate,
perfstat.stats$sysstatf,
perfstat.stats$sysstatg,
perfstat.stats$snapshotsn
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#=41select
substr(to_char(snap_time,'yyyy-mm-ddHH24:MI'),12),
round(100*(((a.value-e.value)+(b.value-f.value))-(c.value-g.value))/
((a.value-e.value)+(b.value-f.value)))
"BUFFERHITRATIO"
from
perfstat.stats$sysstata,
perfstat.stats$sysstatb,
perfstat.stats$sysstatc,
perfstat.stats$sysstatd,
perfstat.stats$sysstate,
perfstat.stats$sysstatf,
perfstat.stats$sysstatg,
perfstat.stats$snapshotsn
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（bufferhitratio）命中率是考察Oracle數(shù)據(jù)庫性能的重要指標，它代表在內(nèi)存中找到需要數(shù)據(jù)的比
率,一般來說，如果該值小于90%，則可能說明數(shù)據(jù)庫存在大量代價昂貴的IO操作，數(shù)據(jù)庫需要調(diào)整。
我們數(shù)據(jù)庫的buffer命中率幾乎接近100%,最低值在95%左右，這個比率是比較優(yōu)化的。
安裝statspack
SQL>connect/assysdba |
卸載
SQL>connect/assysdba /* |
收集信息
SQL>connectperfstat/perfstat |
自動收集
SQL>connectperfstat/perfstat |
刪掉自動收集的job.
SQL>select*fromuser_jobs; |
產(chǎn)生報告
SQL>conectperfstat/perfstat |
【編輯推薦】
