诊断回滚段

1、  回滚段统计信息

select name SEGMET_NAME ,optsize OPTIMAL,shrinks,wraps,extends,aveshrink,aveactive from v$rollstat s,v$rollname r where s.usn=r.usn;

ALTER ROLLBACK SEGMENT 'SYSTEM'  STORAGE ( OPTIMAL 300K);

ALTER ROLLBACK SEGMENT 'SYSTEM'  STORAGE ( NEXT 300K);

ALTER ROLLBACK SEGMENT 'SYSTEM'  STORAGE (MAXEXTENTS 300K);

ALTER ROLLBACK SEGMENT 'SYSTEM' ONLINE/OFFLINE;

2、  回滚段块标题争用

select name RBS_NAME,gets,waits,round((waits/gets)*100,2) HEADER_RATIO from v$rollname r,v$rollstat s where s.usn=r.usn;

以上是每个回滚段的题头争用，阀值应该小于1%

select sum(gets),sum(waits),round(sum(waits)/sum(gets)*100,2) HEADER_RATIO from v$rollname r,v$rollstat s where s.usn=r.usn;

以上是所有回滚段的标题比率，阀值是5%

3、  段标题争用

select event,total_waits,time_waited from v$system_event where event like '%undo%';

4、  回滚段缓冲器争用

timed_statistics 必须为True

select ((w1.count+w2.count+w3.count+w4.count)/(st1.value+st2.value))*100 RATIO

from v$waitstat w1, v$waitstat w2, v$waitstat w3, v$waitstat w4,  v$sysstat st1,v$sysstat st2

where w1.class='system undo header' and w2.class='system undo block' and w3.class='undo header'

and w4.class='undo block' and st1.name='db block gets' and st2.name='db block changes';

始终大于1%就意味着应该增加回滚段的数量

5、  回滚段用法

显示所有当前事务和事务所使用的回滚段

select r.name SEGMET_NAME,s.username,s.osuser,s.sid,s.serial#,t.used_ublk TRANS_USED,rs.aveactive,rs.extends,rs.waits,rs.shrinks,rs.wraps

from v$rollstat rs,v$rollname r,v$session s,v$transaction t where rs.usn(+)=r.usn and t.addr=s.taddr(+) and t.xidusn(+)=r.usn order by s.username;

6、  回滚段及相关进程

select r.name SEGMENT,p.pid,p.spid, nvl(p.username,'no transaction') USERNAME,P.TERMINAL

from v$lock l,v$process p,v$rollname r

where l.sid=p.pid(+) and trunc(l.id1(+)/65536)=r.usn and l.type(+) ='TX'and l.lmode(+)=6 order by 1;

7、  回滚段持续时间

select r.name,round(24*((sysdate-startup_time)-trunc(sysdate-startup_time))/(s.writes/s.rssize),1) HOURS

from v$instance i,v$rollname r,v$rollstat s where r.usn=s.usn and s.status='ONLINE';

8、  提交与回滚数量

select a.value COMMITS,b.value ROLLBACKS,a.value+b.value TOTAL from v$sysstat a,v$sysstat b where a.name='user commits' and b.name='user rollbacks';