1.问题描述:
2个undo表空间使用严重不均,一直这样,怎么优化下呢
UNDOTBS1是实例1的,UNDOTBS2是实例2的
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL>
2.原因分析:
SQL> SELECT tablespace_name, status, SUM (bytes) / 1024/ 1024 "Bytes(M)" FROM dba_undo_extents GROUP BY tablespace_name, status;
TABLESPACE_NAME STATUS Bytes(M)
------------------------------ --------- ----------
UNDOTBS1 UNEXPIRED 12981.9375
UNDOTBS2 UNEXPIRED 53.3125
UNDOTBS1 EXPIRED .0625
UNDOTBS2 EXPIRED 1.125
SQL>
参考:
DBA_UNDO_EXTENTS
查看二边session是否失衡
TBS1:
SQL> select count(*) from v$session where status='ACTIVE';
COUNT(*)
----------
55
SQL>
TBS2:
SQL> select count(*) from v$session where status='ACTIVE';
COUNT(*)
----------
54
SQL>
查看回滚段的使用情况,哪个用户正在使用回滚段的资源,当要更换undo表空间时最好为no row
SQL> select s.username, u.name
from v$transaction t, v$rollstat r, v$rollname u, v$session s
where s.taddr = t.addr
and t.xidusn = r.usn
and r.usn = u.usn
order by s.username;
2 3 4 5 6
no rows selected
SQL>
这里查到现在没有用户使用undo段,那为啥表空间使用率100%呢?因为AUM下,自动创建回滚段把空间占了
SQL> set pagesize 999
SQL> select usn,
xacts,
rssize / 1024 / 1024 / 1024,
hwmsize / 1024 / 1024 / 1024,
shrinks
from v$rollstat
order by rssize; 2 3 4 5 6 7
USN XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS
---------- ---------- --------------------- ---------------------- ----------
0 0 .000358582 .000358582 0
9 0 .257926941 .714103699 45
8 0 .395011902 .533561707 37
1 0 .428520203 .554367065 84
6 0 .483451843 .49382782 41
5 0 .509757996 .514640808 47
2 0 .534965515 .550590515 23
10 0 .55657196 1.48448944 90
3 0 .563957214 .563957214 44
7 0 .570404053 1.36797333 94
4 0 .979118347 .989738464 153
11 0 1.6091156 3.99649048 500
12 rows selected.
SQL>
这样看来虽然UNDOTBS1是100%,但只是HWMSIZE没有释放,无需调整
参考:v$rollstat