Database altered.
SQL> declare 2 v_sql varchar2(200); 3 begin 4 for i in 1..20 loop 5 v_sql:='create rollback segment seg'||lpad(i,2,'0')||' tablespace rbs storage(initial 640k next 640k)'; 6 execute immediate v_sql; 7 v_sql:='alter rollback segment seg'||lpad(i,2,'0')||' online '; 8 execute immediate v_sql; 9 end loop; 10 end; 11 /
PL/SQL procedure successfully completed.
#查询回滚段； SQL> select name from v$rollname;
NAME -------------------- SYSTEM HAHA SEG01 SEG02 SEG03 SEG04 SEG05 SEG06 SEG07 SEG08
NAME -------------------- SEG09 SEG10 SEG11 SEG12 SEG13 SEG14 SEG15 SEG16 SEG17 SEG18 SEG19
NAME -------------------- SEG20
22 rows selected.
#查询回滚段信息，二十个回滚段，回滚段比原来大20倍。 SQL> select segment_name,tablespace_name,owner,file_id,status, initial_extent/1024 init,next_extent/1024 next from dba_rollback_segs;
SEGMENT_NA TABLESPACE OWNER FILE_ID STATUS INIT NEXT ---------- ---------- ------ ---------- ---------- ---------- ---------- SYSTEM SYSTEM SYS 1 ONLINE 112 _SYSSMU1$ UNDOTBS PUBLIC 2 OFFLINE 128 _SYSSMU2$ UNDOTBS PUBLIC 2 OFFLINE 128 _SYSSMU3$ UNDOTBS PUBLIC 2 OFFLINE 128 _SYSSMU4$ UNDOTBS PUBLIC 2 OFFLINE 128 _SYSSMU5$ UNDOTBS PUBLIC 2 OFFLINE 128 _SYSSMU6$ UNDOTBS PUBLIC 2 OFFLINE 128 _SYSSMU7$ UNDOTBS PUBLIC 2 OFFLINE 128 _SYSSMU8$ UNDOTBS PUBLIC 2 OFFLINE 128 _SYSSMU9$ UNDOTBS PUBLIC 2 OFFLINE 128 _SYSSMU10$ UNDOTBS PUBLIC 2 OFFLINE 128
SEGMENT_NA TABLESPACE OWNER FILE_ID STATUS INIT NEXT ---------- ---------- ------ ---------- ---------- ---------- ---------- HAHA SYSTEM SYS 1 ONLINE 2048 SEG01 RBS SYS 5 ONLINE 1280 SEG02 RBS SYS 5 ONLINE 1280 SEG03 RBS SYS 5 ONLINE 1280 SEG04 RBS SYS 5 ONLINE 1280 SEG05 RBS SYS 5 ONLINE 1280 SEG06 RBS SYS 5 ONLINE 1280 SEG07 RBS SYS 5 ONLINE 1280 SEG08 RBS SYS 5 ONLINE 1280 SEG09 RBS SYS 5 ONLINE 1280
SEGMENT_NA TABLESPACE OWNER FILE_ID STATUS INIT NEXT ---------- ---------- ------ ---------- ---------- ---------- ---------- SEG10 RBS SYS 5 ONLINE 1280 SEG11 RBS SYS 5 ONLINE 1280 SEG12 RBS SYS 5 ONLINE 1280 SEG13 RBS SYS 5 ONLINE 1280 SEG14 RBS SYS 5 ONLINE 1280 SEG15 RBS SYS 5 ONLINE 1280 SEG16 RBS SYS 5 ONLINE 1280 SEG17 RBS SYS 5 ONLINE 1280 SEG18 RBS SYS 5 ONLINE 1280 SEG19 RBS SYS 5 ONLINE 1280 SEG20 RBS SYS 5 ONLINE 1280
32 rows selected.
③.将回滚段的名称列到参数文件中，这样中下次启动时就可以自动创建回滚段。
九.使用自动管理undo段原则：
设置undo空间足够大。设置undo retention参数 设置retention guarantee 及时提交事务。
十.回滚段的调优工具：
1.查看段头发生等待和总的请求的百分比。 SQL> SELECT trunc(sum(waits)* 100 /sum(gets),3)||'%' "Ratio",sum(waits) "Waits", sum(gets) "Gets" FROM v$rollstat;
Ratio Waits Gets ----------------------------------------- ---------- ---------- 0% 0 147
2.查询回滚段的信息： SQL> select segment_name,segment_type,bytes/1024 k,extents,blocks ,tablespace_name from dba_segments where segment_name like '_SYSSMU%';
SEGMENT_NA SEGMENT_TY K EXTENTS BLOCKS TABLESPACE ---------- ---------- ---------- ---------- ---------- ---------- _SYSSMU1$ TYPE2 UNDO 2176 4 272 UNDOTBS _SYSSMU2$ TYPE2 UNDO 2176 4 272 UNDOTBS _SYSSMU3$ TYPE2 UNDO 3200 5 400 UNDOTBS _SYSSMU4$ TYPE2 UNDO 2048 17 256 UNDOTBS _SYSSMU5$ TYPE2 UNDO 1152 3 144 UNDOTBS _SYSSMU6$ TYPE2 UNDO 1152 3 144 UNDOTBS _SYSSMU7$ TYPE2 UNDO 2176 4 272 UNDOTBS _SYSSMU8$ TYPE2 UNDO 1152 3 144 UNDOTBS _SYSSMU9$ TYPE2 UNDO 7296 9 912 UNDOTBS _SYSSMU10$ TYPE2 UNDO 5248 7 656 UNDOTBS
10 rows selected.
3. statspacke的advisory
①.Buffer busy wait
不应该大于1%，表空间非自动管理。
表空间段空间自动管理，重建索引。 ASSM
buffer busy wait：当缓冲区以一种非共享方式或者如正在被读入到缓冲时,就会出现该等待。该值不应该大于1%。当出现等待问题时，可以检查缓冲等待统计部分(或V$WAITSTAT)，确定该等待发生在什么位置：
a) 如果等待是否位于段