oracle undo表空间异常激增排查

1.查看undo表空间使用大于98%

2.查看视图v$undostat，确认MAXQUERYID 占用undo最大的sql，TUNED_UNDORETENTION的值为数据库在提交撤销所属事务后，将会保留撤销数据的时长，单位为秒。

select * from v$undostat;

主要看Unexpiredblks的值，看最上面的两笔数据是否一直在增加，增加说明还没有回收，3小时后可以回收

 

 

 MAXQUERYID ： SQL identifier of the longest running SQL statement in the period
    显示了运行最长时间的sql_id，可以通过这个字段查询出运行时间最长的SQL（连接v$sql视图）

TUNED_UNDORETENTION  :oracle给出不同负荷下的undo_retention智能建议

 

3.将查到耗费时间最长的sql，通过v$sql确认sql语句，并查看执行计划

select * from v$sqlarea where sql_id='50urngxayuzb0'

 

ps一下：show parameter undo 

Undo配置参数含义

UNDO_MANAGEMENT undo的管理模式，分自动和手动

UNDO_TABLESPACE 当前正在被使用的undo表

UNDO_RETENTION 规定多长时间内，数据不能被覆盖。

AUTO 表示undo 为自动管理模式，使用的是undo表空间管理模式。

900 表示在900秒内，undo上的数据不能被覆盖。

UNDOTBS1 是当前正在使用的undo表空间

4. 以下sql查询undo的使用情况

select a.tablespace_name,
round(c.active_undo,2) "ACTIVE_UNDO(MB)",
round(a.UNEXPIRED_undo,2) "UNEXPIRED_UNDO(MB)",
b.total_undo "TOTAL_UNDO(MB)",
trunc(active_undo / total_undo * 100, 2) || '%' active_undo_pct,
trunc(UNEXPIRED_UNDO / total_undo * 100, 2) || '%' UNEXPIRED_UNDO_PCT
from (select nvl(sum(bytes / 1024 / 1024), 0) UNEXPIRED_UNDO, tablespace_name
from dba_undo_extents
where status = 'ACTIVE' or status='UNEXPIRED'
group by tablespace_name) a,
(select tablespace_name, sum(bytes / 1024 / 1024) total_undo
from dba_data_files
group by tablespace_name) b,
(select nvl(sum(bytes / 1024 / 1024), 0) active_undo, tablespace_name
from dba_undo_extents
where status = 'ACTIVE'
group by tablespace_name) c
where a.tablespace_name = b.tablespace_name
and a.tablespace_name=c.tablespace_name(+)
order by tablespace_name ;