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';


-----------------------------------------  --------------------
select obj# from obj$ where dataobj# = :1   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'

SCHEMANAME TYPE SQL_ID SQL_CHILD_NUMBER  MODULE ACTION ACTION_HASH EVENT
-------------------------------------------------------------------------------
SYS BACKGROUND f3yfg50ga0r8n 0  KTSJ KTSJ Slave 796,006,397 Space Manager: slave idle wait
SYS BACKGROUND f3yfg50ga0r8n 0  KTSJ KTSJ Slave 796,006,397 Space Manager: slave idle wait
SYS BACKGROUND f3yfg50ga0r8n 0  KTSJ KTSJ Slave 796,006,397 Space Manager: slave idle wait

基本可以判断是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;