详细解读 STATSPACK 报告

bytes received via SQL*Net from

d 123,793 146.9 1.6 bytes sent via SQL*Net to

client 25,187,619 29,878.6 333.1 bytes sent via SQL*Net to

dblink 76,754 91.1 1.0 calls to get snapshot scn:

kcmgss 1,533,555 1,819.2 20.3

calls to

kcmgas 149,646 177.5 2.0

calls to

kcmgcs 10,190 12.1 0.1

change write

time 762 0.9 0.0 cleanout - number of ktugct

calls 13,095 15.5 0.2 cluster key scan block

gets 424 0.5 0.0

cluster key

scans 202 0.2 0.0 commit cleanout failures: block

l 1 0.0 0.0 commit cleanout failures:

buffer 18 0.0 0.0 commit cleanout failures:

callbac 63 0.1 0.0 commit cleanout failures:

cannot 2,087 2.5 0.0

commit

cleanouts 643,505 763.4 8.5

commit cleanouts successfully

com 641,336 760.8 8.5 commit txn count during

cleanout 35,188 41.7 0.5

consistent

changes 63,943 75.9 0.9 consistent

gets 16,616,758 19,711.5 219.8

由consistent gets，db block gets和physical reads这三个值，我们也可以计算得到buffer hit ratio，计算的公式如下： buffer hit ratio = 100*(1-physical reads /(consistent gets+ db block gets))，例如在这里，我们可以计算得到：buffer hit ratio =100*(1-26524/(16616758+2941398))= 99.86

consistent gets -

examination 1,168,584 1,386.2 15.5 current blocks converted for

CR 0 0.0 0.0

cursor

authentications 2 0.0 0.0

data blocks consistent reads -

un 63,873 75.8 0.8

db block

changes 2,596,938 3,080.6 34.4

db block

gets 2,941,398 3,489.2 38.9

deferred (CURRENT) block

cleanout 130,783 155.1 1.7

dirty buffers

inspected 166 0.2 0.0

脏数据从LRU列表中老化，A value here indicates that the DBWR is not keeping up。如果这个值大于0，就需要考虑增加DBWRs。

dirty buffers inspected: This is the number of dirty (modified) data buffers that were aged out on the LRU list. You may benefit by adding more DBWRs.If it is greater than 0, consider increasing the database writes.

enqueue

conversions 485 0.6 0.0

enqueue

deadlocks 0 0.0 0.0

enqueue

releases 318,825 378.2 4.2

enqueue

requests 318,825 378.2 4.2

enqueue

timeouts 0 0.0 0.0

Instance Activity Stats for DB: ORA92 Instance: ora92 Snaps: 13 -14 Statistic Total per Second per Trans

--------------------------------- ------------------ -------------- ------------

enqueue

waits 728 0.9 0.0

exchange

deadlocks 30 0.0 0.0

execute

count 1,004,280 1,191.3 13.3

free buffer

inspected 188 0.2 0.0

这个值包含dirty，pinned，busy的buffer区域，如果free buffer inspected - dirty buffers inspected - buffer is pinned count的值还是比较大，表明不能被重用的内存块比较多，这将导致latch争用，需要增大buffer cache。

free buffer

requested 116,422 138.1 1.5 hot buffers moved to head of

LRU 17,750 21.1 0.2 immediate (CR) block cleanout

app 1,916 2.3 0.0 immediate (CURRENT) block

cleanou 81,385 96.5 1.1 index fast full scans

(full) 0 0.0 0.0 index fetch by

key 335,907 398.5 4.4 index scans

kdiixs1 692,053 820.9 9.2 leaf node 90-10

splits 418 0.5 0.0 leaf node

splits 1,941 2.3 0.0

logons

cumulative 716 0.9 0.0

messages

received 67,830 80.5 0.9