DBA学习之路

敬畏数据,谨慎对待每一个问题
随笔 - 44, 文章 - 0, 评论 - 0, 阅读 - 19141

导航

< 2025年2月 >
26 27 28 29 30 31 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 1
2 3 4 5 6 7 8

Undo表空间的设置和监控策略

Posted on   dclogs  阅读(16)  评论(0编辑  收藏  举报

转自: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.57

ups: 这是撤销块的使用率,计算公式为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峰值,那么它是可用的。

  越写知识点越多,就到这吧,感觉都迷糊了。

相关博文:
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
点击右上角即可分享
微信分享提示