Oracle 19c UNDO 使用率高 ACTIVE 占比高

UNDO表空间使用率告警，查看占用情况

active段占比很高

select tablespace_name,status,sum(bytes/1024/1024) mb from dba_undo_extents group by tablespace_name,status;


不同状态的含义：
**ACTIVE **：有活动事务在使用 Undo，这部分空间属于Session正在使用的空间；
**UNEXPIRED **：事务提交并且没到undo_retention设置时间之前，这些Undo Block还没有过期，但是已经没有活动事务在使用了，在超过undo_retention设置时间之后，这部分空间会变成EXPIRED状态；
**EXPIRED **：事务提交并且到undo_retention设置时间之后，这些Undo Block已经过期了，这部分空间是可以重用的，属于未使用空间；

检查正在执行的事务

正在执行的事务与active占用空间对应不上

SELECT s.username,
s.sid,
pr.PID,
s.OSUSER,
s.MACHINE,
s.PROGRAM,
rs.segment_id,
r.usn,
rs.segment_name,
r.rssize/1024/1024,
sq.sql_text
FROM gv$transaction t, gv$session s, gv$rollstat r, dba_rollback_segs rs ,gv$sqltext sq,gv$process pr
WHERE s.saddr = t.ses_addr
AND t.xidusn = r.usn
AND rs.segment_id = t.xidusn
AND s.sql_address=sq.address
AND s.sql_hash_value = sq.hash_value
AND s.PADDR=pr.ADDR
ORDER BY t.used_ublk DESC ,sq.PIECE;


查看是否有DEAD事务回滚占用

select ADDR,KTUXEUSN,KTUXESLT,KTUXESQN,KTUXESIZ from x$ktuxe where KTUXECFL='DEAD';

---
no rows


检查问题时间段内占用undo最高的sql

select count (maxqueryid),maxqueryid from v$undostat where begin_time>to_date('2024-06-30 0:00:00','yyyy-mm-dd hh24:mi:ss') group by maxqueryid;

count (maxqueryid) maxqueryid
-------------------- --------------------
321 f3yfg50ga0r8n
15 4asdffg50gang
3 cc256507a666g
2 256df50gaasdn


查看语句内容

select sql_fulltext,sql_id from v$sql where sql_id='f3yfg50ga0r8n';

语句查询的是对象基表大致判断为内部job，继续排查调用语句的会话信息。

查询语句的历史会话信息

select * from dba_hist_active_sess_history where sample_time>to_date('2024-06-30 00:00:00','yyyy-mm-dd hh24:mi:ss') and sql_id='f3yfg50ga0r8n'

基本可以判断是BUG 从MOS上找到对应bug
UNDO Document 3013880.1.pdf
关于SMCO (Space Management Coordinator)
SMOC Document 743773.1.pdf
关于这个特性还是有不少问题，可以参考平安数据库团队发的文章
oracle秘境探索之11g tablespace prellocation - 墨天轮

目前没有补丁可以修复，临时处理办法

当使用率高时：
--关闭特性 "Tablespace-level space (Extent) pre-allocation.表空间级别预分配。
ALTER SYSTEM SET "_enable_space_preallocation" = 0;
当使用率下降后：
--重新开启特性
ALTER SYSTEM SET "_ENABLE_SPACE_PREALLOCATION" = 3;