The first purpose of using UNDO is for storing all uncommitted data in case we need to undo uncommitted data in rollback or after system failures. As you know, UNDO could be growing very fast during system busy hours. If you ignore undo tablespace usage, some transactions may fail.
In this post, we will discuss how to check overall and separate user usage on UNDO.
UNDO Tablespace Usage
Let's see how we check the overall usage of UNDO by this query as below.
SQL> column tablespace format a20;
SQL> column sum_in_mb format 999999.99;
SQL> select tablespace_name tablespace, status, sum(bytes)/1024/1024 sum_in_mb, count(*) counts from dba_undo_extents group by tablespace_name, status order by 1,2;
TABLESPACE STATUS SUM_IN_MB COUNTS
-------------------- --------- ---------- ----------
UNDOTBS1 ACTIVE 1.00 1
UNDOTBS1 EXPIRED 1063.19 1015
UNDOTBS1 UNEXPIRED 241.94 152
UNDOTBS2 ACTIVE 2.00 2
UNDOTBS2 EXPIRED 12523.14 1456
UNDOTBS2 UNEXPIRED 357.10 265
6 rows selected.
The space is not full yet, and lots of EXPIRED extents in UNDOTBS2 which is an UNDO tablespace dedicated for node 2 instance in a RAC environment. Normally, once System Monitor (SMON) detects space pressure, it either autoextend file size or reclaim EXPIRED extents.
The statement is extremely useful when several failed transactions are rolling back by SMON in the background. You can see how much ACTIVE undo needs to be processed.
You should be aware of ORA-30036 under a very special condition: If your database throws ORA-30036 without reclaiming EXPIRED extents first. You have to patch your database due to an Oracle bug 5442919.
User UNDO Tablespace Usage
Let's drill down a little bit to user-level.
SQL> select u.tablespace_name tablespace, s.username, u.status, sum(u.bytes)/1024/1024 sum_in_mb, count(u.segment_name) seg_cnts from dba_undo_extents u left join v$transaction t on u.segment_name = '_SYSSMU' || t.xidusn || '$' left join v$session s on t.addr = s.taddr group by u.tablespace_name, s.username, u.status order by 1,2,3;
TABLESPACE USERNAME STATUS SUM_IN_MB SEG_CNTS
-------------------- --------------- --------- ---------- ----------
UNDOTBS1 SCOTT ACTIVE 8.00 1
UNDOTBS1 SCOTT EXPIRED 120.12 66
UNDOTBS1 EXPIRED 5476.18 1962
UNDOTBS1 UNEXPIRED 305.40 85
UNDOTBS2 EXPIRED 1743.67 3252
It's pretty easy to identify undo tablespace usage and who is using undo space just now.
In my experience, DML statements for data migration are the first target we should focused on in case UNDO space is full. DDL like SHRINK, MOVE or REBUILD would also take a significant active undo at runtime.
The second purpose of using UNDO is for snapshot query. I think you may like to know why changing UNDO_RETENTION is usually useless to resolve ORA-01555.
col allocated for 999,999.999
col free for 999,999.999
col used for 999,999.999
( select sum(bytes)/1024/1024 from dba_data_files
where tablespace_name like ‘%UND%’ ) allocated_MB,
( select sum(bytes)/1024/1024/1024 from dba_free_space
where tablespace_name like ‘%UND%’) free_GB,
( select sum(bytes)/1024/1024/1024 from dba_undo_extents
where tablespace_name like ‘%UND%’) Used_GB
UNDO BY SESSION LEVE WISE:
sum(ss.value) / 1024 / 1024 as undo_size_mb
from v$sesstat ss
join v$session s on s.sid = ss.sid
join v$statname stat on stat.statistic# = ss.statistic#
where stat.name = ‘undo change vector size’
and s.type ‘BACKGROUND’
and s.username IS NOT NULL
group by s.sid,s.username,s.status;
Thanks for your input.