转自：https://www.modb.pro/db/1810135249725173760
1、发现问题
某套数据库的环境的undo表空间的使用率一直在超过监控的85%，一直报警一直扩容还在一直报警，甚至超过了业务表空间的大小，需要我们确认他是否真的需要这么大的空间？多大的空间能够保证数据库可以正常使用？undo表空间的使用机制到底是什么样的？
1.1、数据库环境
select a.tablespace_name,
round(a.bytes / 1024 / 1024 / 1024, 0) "sum G",
round((a.bytes - b.bytes) / 1024 / 1024 / 1024, 0) "used G",
round(b.bytes / 1024 / 1024 / 1024, 0) "free G",
round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "used%"
from (select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes, max(bytes) largest
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name and a.tablespace_name in ('UNDOTBS1','UNDOTBS2')
order by ((a.bytes - b.bytes) / a.bytes) desc;
##########################################################################
TABLESPACE_NAME sum G used G free G used%
1 UNDOTBS2 150 132 18 87.8
2 UNDOTBS1 156 130 26 83.57
2、定位问题
2.1、涉及的视图或者参数
SQL> show parameter undo
NAME TYPE VALUE
------------------ ----------- ----------------
_gc_undo_affinity boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
select * from dba_views where view_name like '%UNDO%';
OWNER VIEW_NAME
1 SYS V_$UNDOSTAT
4 SYS GV_$UNDOSTAT
3 SYS DBA_UNDO_EXTENTS
2 SYS DBA_HIST_UNDOSTAT
挑几个重要的来解答：
undo_retention决定了撤销保留的时间，单位是秒。
DBA_UNDO_EXTENTS的status列有三种状态，分别是active（正在使用，无法覆盖）、unexpired（未过期，未超过由undo_retention设置的时间），expired（过期，超过了undo_retention设置的时间）。
undo表空间是可以循环使用的表空间，使用顺序是Free（未分配） - expired（过期） - unexpired（未过期） - active（报错，空间不足）。只要在某个时间段undo提供的表空间大于acive状态下的数据量，那么undo就不会报错。
V$UNDOSTAT十分钟记录一条，只会记录576条，接近四天时间的Undo信息，而DBA_HIST_UNDOSTAT则把超过四天时间的信息记录下来，接近10天左右。所以DBA_HIST_UNDOSTAT其实主要是V$UNDOSTAT的延申。而这两个视图是都可以记录undo在某段时间里的undo空间大小。
3、解决问题
3.1、显示过期时间
NAME TYPE VALUE
---------------- ----------- ----------
undo_retention integer 900
3.2、判断思路
那么在这种情况下，该怎么才能保证Undo够用呢？
第一种情况，保证undo_retention的使用时间，可以在过期时间内及时回退，并且使用率85%以下（70%最好），这个是最优。
第二种情况，保证Undo表空间能正常使用，不必在乎他使用率多少，能正常使用就行。（但是这个不能监控。）
第三种情况，Undo空间不足，导致数据没法对外提供服务。
3.2.1、最高等，保证正常使用的情况下还能及时恢复被误删的数据
select tablespace_name, status, round(sum(bytes/1024/1024/1024),2) "GB"
from dba_undo_extents
group by tablespace_name, status
order by 1, 2;
TABLESPACE_NAME STATUS GB
1 UNDOTBS1 ACTIVE 0.95
2 UNDOTBS1 EXPIRED 15.37
3 UNDOTBS1 UNEXPIRED 108.97
4 UNDOTBS2 ACTIVE 0.28
5 UNDOTBS2 EXPIRED 42.93
6 UNDOTBS2 UNEXPIRED 82.09
保证回滚段状态是 ACTIVE 和 UNEXPIRED的正常使用。就能大概计算出undo表空间的最高使用保证。
3.2.2、满足使用
select ur undo_retention,
dbs db_block_size,
round(((ur * (ups * dbs)) + (dbs * 24)) / 1024 / 1024,2) as "M_bytes"
from (select value as ur from v$parameter where name = 'undo_retention'),
(select (sum(undoblks) / sum(((end_time - begin_time) * 86400))) ups
from v$undostat),
(select value as dbs from v$parameter where name = 'db_block_size');
UNDO_RETENTION DB_BLOCK_SIZE M_bytes
1 900 8192 1055.57ups: 这是撤销块的使用率，计算公式为undoblks（撤销块的数量）除以时间差（end_time - begin_time，单位为秒）乘以86400（转换为天）。
(ur * (ups * dbs)): 这是基于撤销保留时间、撤销块使用率和数据库块大小计算的撤销空间大小（以字节为单位）。
(ur * (ups * dbs)) + (dbs * 24): 加上24小时的数据库块大小，可能是为了考虑额外的撤销空间需求。
这个计算公式是算了七天的平均量，我有个很夸张的想法，我要是真的在生产库，就给他两G的undo表空间，禁止自增，是不是也是够用的？
select ur undo_retention,
dbs db_block_size,
round(((ur * (ups * dbs)) + (dbs * 24)) / 1024 / 1024,2) as "M_bytes"
from (select value as ur from v$parameter where name = 'undo_retention'),
(select (undoblks / ((end_time - begin_time) * 86400)) ups
from v$undostat
where undoblks in (select max(undoblks) from v$undostat)),
(select value as dbs from v$parameter where name = 'db_block_size');
UNDO_RETENTION DB_BLOCK_SIZE M_bytes
1 900 8192 4658.81从v$undostat中计算撤销块的使用率（ups），这是通过将最大撤销块数量除以时间段（以天为单位）来计算的。
最后，计算撤销空间使用情况，公式为((ur * (ups * dbs)) + (dbs * 24)) / 1024 / 1024，其中ur是撤销保留时间，ups是撤销块使用率，dbs是数据库块大小，dbs * 24可能代表一天内额外的块需求。
很显然不是，因为七天中最高的undo使用峰值，超过了2G，虽然也就差不多4G，为了避免不够用，5G是绰绰有余的。
3.2.3、无法满足使用
如果undo表空间的没有超过回滚段峰值，也没有保证在（undo表空间GUARANTEE状态下）active和unexpired的数据量大小总值。那就会报空间不足。
题外话：undo表空间的GUARANTEE状态是保证只有expired的回滚段才会覆盖，active和unpired状态的数据是不会被覆盖的，能够保证undo_retention时间内的DML数据回退。
缺省情况下，undo表空间的状态时NOGUARANTEE，是可以覆盖unpired的数据。
4、验证问题
4.1、undo_retention时间过后是否释放
记录测试库本身回滚段信息
SQL> select owner,round(sum(bytes)/1024/1024,2) as MB,status from DBA_UNDO_EXTENTS group by owner,status;
OWN MB STATUS
--- ---------- ---------
SYS .56 EXPIRED
SYS 118.38 UNEXPIRED
设置撤销保留时间为10秒
SQL> alter system set undo_retention = 10 scope=both;
System altered.
查看是否修改成功
SQL> show parameter undo_retention
NAME TYPE VALUE
-------------- ----------- -------
undo_retention integer 10
压测插入数据
SQL> begin
for i in 1..1000000 loop
insert into t_tx values(i,mod(i,10));
end loop;
commit;
end;
/
过一分钟（其实十秒就行）查看回滚段信息
SQL> select owner,round(sum(bytes)/1024/1024,2) as MB,status from DBA_UNDO_EXTENTS group by owner,status;
OWN MB STATUS
--- ---------- ---------
SYS 50.75 EXPIRED
SYS 68.25 UNEXPIRED随着时间的推移，回滚段的数据的确是释放，但是状态只有EXPIRED和UNEXPIRED，没有Free的说法。也就是说此时再看，Undo表空间的使用率是100%。
4.2、undo是否将状态是EXPIRED、UNEXPIRED都能使用
限制undo表空间大小并关闭自增
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/undotbs01.dbf' resize 120M;
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/undotbs01.dbf' autoextend off;
查看undo表空间的限制信息
SQL> set pages 999 lines 999
SQL> col file_name for a60
SQL> select file_name,tablespace_name,bytes/1024/1024 from dba_data_files where tablespace_name='UNDOTBS1';
FILE_NAME TABLESPACE_NAME BYTES/1024/1024
-------------------------------------------- ----------------- ---------------
/u01/app/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1 120
创建测试用户
SQL> create user test identified by test default tablespace users;
SQL> grant connect,resource,dba to test;
查看现在的undo信息
select owner,round(sum(bytes)/1024/1024,2) as MB,status from DBA_UNDO_EXTENTS group by owner,status;
OWN MB STATUS
--- ---------- ---------
SYS 7.38 EXPIRED
SYS 92.25 UNEXPIRED
开始压测，要找到能一口气压测报错的数据量大小
conn test/test
##创建表
create table t_tx (id number,name varchar2(64));
##插入数据
begin
for i in 1..2000000 loop
insert into t_tx values(i,mod(i,10));
end loop;
commit;
end;
/
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
ORA-06512: at line 3
查看此时的undo表空间信息，我这里undo表空间如果满了就不会显示使用率。
select a.tablespace_name,
round(a.bytes / 1024 / 1024 / 1024, 0) "sum G",
round((a.bytes - b.bytes) / 1024 / 1024 / 1024, 0) "used G",
round(b.bytes / 1024 / 1024 / 1024, 0) "free G",
round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "used%"
from (select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes, max(bytes) largest
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name and a.tablespace_name in ('UNDOTBS1','UNDOTBS2')
order by ((a.bytes - b.bytes) / a.bytes) desc;
开始分批次小量压测，将前一个步骤的数据量分批插入，保证能插入成功而且数据量一致。
begin
for i in 1..1000000 loop
insert into t_tx values(i,mod(i,10));
end loop;
commit;
end;
/
begin
for i in 1..1000000 loop
insert into t_tx values(i,mod(i,10));
end loop;
commit;
end;
/
再次查看回滚段大小，可以看到如下信息。
SQL> SQL> select a.tablespace_name,
round(a.bytes / 1024 / 1024 / 1024, 0) "sum G",
round((a.bytes - b.bytes) / 1024 / 1024 / 1024, 0) "used G",
round(b.bytes / 1024 / 1024 / 1024, 0) "free G",
round(((a.bytes - b.bytes) / a.bytes) * 2 3 4 5 100, 2) "used%"
from (select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes, max(bytes) largest
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name and a.tablespace_name in ('UNDOTBS1','UNDOTBS2')
order by ((a.bytes - b.bytes) / a.bytes) desc; 6 7 8 9 10 11 12 13
TABLESPACE_NAME sum G used G free G used%
--------------------- ---------- ---------- ---------- ----------
UNDOTBS1 0 0 0 99.95
SQL> select owner,round(sum(bytes)/1024/1024,2) as MB,status from DBA_UNDO_EXTENTS group by owner,status;
OWN MB STATUS
--- ---------- ---------
SYS .56 EXPIRED
SYS 118.38 UNEXPIRED得到一个结论，只要在回滚段的峰值这个时间段，active的数值大小没超过undo表空间的大小，那就可以保证undo段的正常使用，不会让数据库异常。
5、总结
undo表空间的大小超过undo段使用的峰值，而且超过undo_retention时间内active和unexpected的数值大小，那这个undo表空间是完美的。
undo表空间超过了使用的undo峰值，那么它是可用的。
越写知识点越多，就到这吧，感觉都迷糊了。
浙公网安备 33010602011771号