【IT168 技术文档】
检测回滚段争用:
select class,count from v$waitstat
where class in ('undo header','undo block','system undo header','system undo bolck')
CLASS COUNT
------------------ ----------
system undo header 0
undo header 0
undo block 0
select sum(value) from v$sysstat where name in ('consistent gets','db block gets')
SUM(VALUE)
----------
20589
通过以下公式计算等待比率:
system header waits = system undo header / total
reads system block waits = system block / total
readsrollback header waits = undo header / total reads
rollback block waits = undo block / total reads
若任何一个的比率大于1%则建议再创一个回滚段:
create rollback segment rbs21 tablespace rbs storage (inittial 10k optimal 20k next 10k maxextents 8) ;
避免动态分配空间
用以下语句检查回滚段的动态分配:
select name,shrinks from v$rollstat,v$rollname where v$rollstat.usn=v$roll ;
NAME SHRINKS
---------------- ----------
SYSTEM 0
RBS0 100
RBS1 1
若动态分配次数较多可增大回滚段的初始容量。
ALTER ROLLBACK SEGMENT RBS0
STORAGE (inittial 20k optimal 40k next 10k maxextents 8) ;