oracle删除数据回滚段太短,Oracle数据库优化之回滚段

【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) ;