文档课题：UNDO表空间使用率过高的处理方式.
应用场景：UNDO表空间使用率过高，想必很多数据库管理员都遇到过此问题，接下来了解undo表空间的相关知识.
1、undo块使用情况
SQL> select status,sum(bytes/1024/1024) from dba_undo_extents group by status;
STATUS SUM(BYTES/1024/1024)
------------------ --------------------
EXPIRED 21.25
UNEXPIRED 10复制
2、当前undo空闲
SQL> select tablespace_name,sum(bytes/1024/1024) from dba_free_space where tablespace_name='UNDOTBS1' group by tablespace_name
TABLESPACE_NAME SUM(BYTES/1024/1024)
-------------------- --------------------
UNDOTBS1 157.75复制
3、表空间情况
SQL> set line 200
SQL> col GRAPH for a50
SQL> col tablespace for a20
SQL> select total.ts tablespace,
total.mb total_mb,
to_char(trunc(total.mb - nvl(free.mb,0),2),'fm999990.0999') used_MB,
NVL(free.mb,0) free_mb,
DECODE(total.mb,NULL,0,NVL(ROUND((total.mb - free.mb)/(total.mb)*100,2),100)) pct_used,
CASE WHEN (total.mb IS NULL) THEN '['||RPAD(LPAD('OFFLINE',13,'-'),20,'-')||']'
ELSE '['|| DECODE(free.mb,
null,'XXXXXXXXXXXXXXXXXXXX',
NVL(RPAD(LPAD('X',trunc((100-ROUND( (free.mb)/(total.mb) * 100, 2))/5),'X'),20,'-'),
'--------------------'))||']'
END as GRAPH
from
(select tablespace_name ts, sum(bytes)/1024/1024 mb from dba_data_files group by tablespace_name) total,
(select tablespace_name ts, sum(bytes)/1024/1024 mb from dba_free_space group by tablespace_name) free
where total.ts=free.ts(+)
UNION ALL
select t3.tn tablespace,
t3.bytes total_mb,
to_char(trunc(t3.bytes - nvl(t4.bytes,0),2),'fm999990.0999') used_mb,
t4.bytes free_mb,
ROUND(((to_char(trunc(t3.bytes - nvl(t4.bytes,0),2),'fm999990.0999'))/t3.bytes)*100,2) pct_used,
CASE WHEN (t3.bytes IS NULL) THEN '['||RPAD(LPAD('OFFLINE',13,'-'),20,'-')||']'
ELSE '['|| DECODE(t4.bytes,
null,'XXXXXXXXXXXXXXXXXXXX',
NVL(RPAD(LPAD('X',trunc((100-ROUND( (t4.bytes)/(t3.bytes) * 100, 2))/5),'X'),20,'-'),
'--------------------'))||']' end as graph
FROM (select tablespace_name tn,sum(bytes)/1024/1024 bytes from dba_temp_files group by tablespace_name) t3,
(select tablespace_name tn,sum(bytes_cached)/1024/1024 bytes from v$temp_extent_pool group by tablespace_name) t4
where t3.tn = t4.tn(+)
order by 5 Desc;
TABLESPACE TOTAL_MB USED_MB FREE_MB PCT_USED GRAPH
-------------------- ---------- ---------- ---------- ---------- --------------------------------------------------
TEMP 97 97 0 100 [XXXXXXXXXXXXXXXXXXXX]
SYSTEM 1080 1065.625 14.375 98.67 [XXXXXXXXXXXXXXXXXXX-]
SYSAUX 1400 1303 97 93.07 [XXXXXXXXXXXXXXXXXX--]
USERS 18.75 6.4375 12.3125 34.33 [XXXXXX--------------]
UNDOTBS1 190 32.25 157.75 16.97 [XXX-----------------]
TSP_ORDADM1 200 4 196 2 [--------------------]
TEST 150 2.625 147.375 1.75 [--------------------]
TSP_ORDADM 800 1 799 .13 [--------------------]
已选择 8 行.复制
4、undo_retention自动调整
undo表空间使用率跟undo_retention的值有关，保留时间越长unexpired越大，进而undo表空间就越大.从oracle 10g r2开始，oracle默认是开启undo表空间的自动调整功能，目的在于避免频繁的ora-01555告警发生.意思是oracle会忽略undo_retention参数设置的阀值，而是根据undo表空间的大小和使用率来自动调整undo信息的保留时间.
通过视图v$undostat.tuned_undoretention可查询该值是否有被自动调整，以下查询语句：
SQL> select to_char(begin_time,'yyyy-mm-dd hh24:mi:ss'),to_char(end_time,'yyyy-mm-dd hh24:mi:ss'),tuned_undoretention from v$undostat
TO_CHAR(BEGIN_TIME,'YYYY-MM-DDHH24:MI: TO_CHAR(END_TIME,'YYYY-MM-DDHH24:MI:SS TUNED_UNDORETENTION
-------------------------------------- -------------------------------------- -------------------
2022-04-21 10:10:01 2022-04-21 10:20:01 900
2022-04-21 10:00:01 2022-04-21 10:10:01 900
2022-04-21 09:50:01 2022-04-21 10:00:01 2334
2022-04-21 09:40:01 2022-04-21 09:50:01 2330
2022-04-21 09:30:01 2022-04-21 09:40:01 1726
2022-04-21 09:20:01 2022-04-21 09:30:01 1121
2022-04-21 09:10:01 2022-04-21 09:20:01 900
2022-04-21 09:00:01 2022-04-21 09:10:01 900
已选择 33 行.复制
结论：以上可看出，undo_retention值有被自动调整到2334s.
5、相关知识
undo被使用顺序.
事务会优先使用undo空闲空间、过期状态(expired)的undo区，然后才会尝试扩展表空间数据文件.在以上步骤都不能获得undo表空间后，才会去使用未过期(unexpired)的undo区.
oracle主要通过以下方式回收undo extend，使其成为undo表空间的空闲空间：
a、smon进程周期性地回收过期状态的undo extent，对于offline的undo段，会回收所有过期的undo extent.对于online的undo段，回收时会根据undo_retention时间和undo块的使用率来确定回收的数量.undo_retention越大，能回收的undo空间就越小.
b、进程在给事务分配undo块时，会回收多余过期状态的undo extent.因此，如果系统未过期的undo extent越多，这部分undo块就不会返回给undo表空间而成为空闲空间，导致undo表空间已分配空间一直很多，同时使用率也会显示很高.
6、解决方案
为避免undo表空间使用率过高，可采取以下措施：
a、降低undo_retention值
b、增加undo表空间数据文件
c、修改隐藏参数_undo_autotune=false来禁用自动undo保留调整功能
alter system set "_undo_autotune"=false scope=both sid='*';
d、设置隐藏参数_sum_debug_mode
e、若undo_retention参数值在10800以下，建议设置为10800.
alter system set undo_retention=10800;
参考网址：
