观察目前谁在使用RBS

王朝other·作者佚名 2008-05-31

SELECT r.name rr,

nvl(s.username,'no transaction') us,

s.osuser os,

s.terminal te

FROM v$lock l, v$session s,v$rollname r

WHERE l.sid = s.sid(+) AND

trunc(l.id1/65536) = r.usn AND

l.type = 'TX' AND

l.lmode = 6

ORDER BY r.name

RR US OS TE

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

_SYSSMU10$ APPLE Oracle pts/2

_SYSSMU4$ SYS oracle pts/0

观察UNDO使用状况

select b.NAME, a.USN seg#, GETS, WAITS, round(((GETS-WAITS)*100)/GETS,2) hit_ratio, XACTS active_transactions, WRITES

from v$rollstat a, v$rollname b where a.USN = b.USN

NAME SEG# GETS WAITS HIT_RATIO ACTIVE_TRANSACTIONS WRITES

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

SYSTEM 0 385 0 100 0 7016

_SYSSMU1$ 1 36196 0 100 0 2397118

_SYSSMU2$ 2 36196 0 100 0 2397218

_SYSSMU3$ 3 32197 0 100 1 2136840

_SYSSMU4$ 4 33015 0 100 0 2183608

_SYSSMU5$ 5 36199 0 100 0 2395922

_SYSSMU6$ 6 36212 0 100 0 2398430

_SYSSMU7$ 7 36210 0 100 0 2398762

_SYSSMU8$ 8 36238 0 100 0 2400166

_SYSSMU9$ 9 36236 1 100 0 2440250

_SYSSMU10$ 10 36209 0 100 0 2396632

11 rows selected.

若HIT_RATIO

不管如何，在一个大量进行交易的batch job里，适当的commit可以大大减轻undo的负担，或避免snapshoot too old的错误发生，又或者你是使用9i以上的版本，利用AUM可能比你自己去设定个别的RBS来的不易出状况吧。