转自: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峰值,那么它是可用的。
越写知识点越多,就到这吧,感觉都迷糊了。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库