</p> Oracle 使用undo segment头部块一一个事务表来跟踪使用他的那些事务,其内容凡是被缓存在database buffer cache中以便被搜索。OLTP上很有可能会因为访谒这个头部发生期待。
! i0 T) t. q2 |# P SQL> select event,total_waits,time_waited,average_wait0 ^# r/ C2 Q3 r) `0 q
from v$system_event where event like '%undo%' and event like '%slot%';7 k. N( q% v# n5 ?
average_wait:平均每毫秒期待的次数,等于0或接近于0最好。
5 [5 ]" v- u) b) p; y SQL>select class,count from v$waitstat
, C ?! [$ n1 L$ v2 H+ D" ]- S where class in ('undo header','system undo header');
) x" X9 ? ~0 s) h count:期待访谒undo segment头部的次数。理想情形下,该值等于0或接近于0最好。7 U# j# A' P/ T8 W3 q. r
SQL>select n.name,s.usn,
' G9 t' v# @2 W" E3 ~8 O decode(s.waits,0,1,1-(s.waits/s.gets)) "RBS Header Get Ratio"6 Z6 r! A4 z4 D* h u0 {$ m$ d
from v$rollstat s,v$rollname n2 A) T: @ M# C( g
where s.usn = n.usn( Q5 Z: z$ `( @. u
order by usn;
' w! _2 O5 G9 V/ I, l* ` RBS Header Get Ratio:等于1或接近1最好,至少为95%。
( Q+ Q/ l' z7 T" j( j 5.5.1.2 undo segment规模的争用
4 _- _' ?# j- ~ U9 D' X$ C SQL>select class,count from v$waitstat) g4 v9 ~% V6 ^ V: }( b
where class ='system undo block' ;1 J+ I' D/ |9 {- r3 T2 p4 W
count:系统回滚段典型围的块争用次数9 N/ x- q* I+ L8 {$ s* [, E
SQL>select w.count,w.count/s.value as wait_ratio from v$waitstat w,v$sysstat s% G9 p" O6 | s) Q* n P
where w.class = 'undo block'
0 J2 A/ e" v" h3 X and s.name = 'consistent gets';% q! m3 ?. m/ U5 t
count:非系统回滚段典型围的块争用次数
% s" u" T! }, |# O9 ~. M& I+ H, F wait_ratio:回滚期待率,如不美观跨越1%,则需要调整了。* b: u! i' G. z, y6 B
5.5.1.3 undo segment的环抱
9 m) a4 E7 v0 v% c+ W( {1 d SQL> select n.name,s.usn," P# i* u) c! y+ l7 N
decode(s.waits,0,1,1-(s.waits/s.gets)) "RBS Header Get Ratio",s.wraps
% ~0 n' p; Y; W8 J. P! d V; h from v$rollstat s,v$rollname n
: j- n1 V. O2 O' ~ where s.usn = n.usn
& i, {( j) t0 U" z: } order by usn;
( n' l! a* _) p' R. V s.wraps:该回滚段被环抱到下矣闽规模的次数。次数太多暗示段规模可能太小。
2 \9 y$ z9 u& _2 r. q& K 5.5.1.4 undo segment的动态规模分配3 {# c" q; O0 q) j
事务的像前版本在undo segment中发生环抱,而下矣闽规模已经分配给其他事务的像前版本,此时会在他们之间动态建树一个规模来给该事务的像前版本环抱。应避免以削减I/O。
$ K. D, w/ w# ^1 ?. u4 i8 v SQL> select event,total_waits,time_waited,average_wait
; f) y) w" o0 y5 p' { from v$system_event where event = 'undo segment extension';
) l/ G$ V. B% p) x3 R time_waited:暗示动态分配的期待次数。若值很高或不竭增添,声名undo segment太少或太小。( `' h) G2 _& P, Q
SQL>select n.name,s.usn,s.extends,$ W& e( S- {3 s6 X, ~7 E8 \
decode(s.waits,0,1,1-(s.waits/s.gets)) "RBS Header Get Ratio",s.wraps
! T1 @8 |+ \, l0 @ from v$rollstat s,v$rollname n
( @$ G' b) m3 y' M7 u' w where s.usn = n.usn: U/ L. R' {/ { N1 Y
order by usn;8 }% r+ c- F" s1 E# R0 ?8 J* q
% M+ x% B8 _6 l+ R s.extends:被冻┈添加典型围数。若经常发活跃态添加,则声名undo segment可能太小。 |