Introduction
UNDO is controlled by undo_retention parameter and size of UNDO tablespace.
UNDO_RETENTION: low threshold value (in sec) of undo retention. If an active transaction requires undo space and the undo tablespace does not have available space, then the system starts reusing unexpired undo space.
SQL> show parameter %undo%
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
Check undo tablespace total, free and used space
SELECT a.tablespace_name, SIZEMB, USAGEMB, (SIZEMB - USAGEMB) FREEMB
FROM ( SELECT SUM (bytes) / 1024 / 1024 SIZEMB, b.tablespace_name
FROM dba_data_files a, dba_tablespaces b
WHERE a.tablespace_name = b.tablespace_name AND b.contents like 'UNDO'
GROUP BY b.tablespace_name) a,
( SELECT c.tablespace_name, SUM (bytes) / 1024 / 1024 USAGEMB
FROM DBA_UNDO_EXTENTS c
WHERE status <> 'EXPIRED'
GROUP BY c.tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name;
TABLESPACE_NAME SIZEMB USAGEMB FREEMB
------------------------------ ---------- ---------- ----------
UNDOTBS1 915 28.9375 886.0625
Autoextensible or not?
col FILE_NAME for a50
set lines 200
select TABLESPACE_NAME, FILE_NAME, AUTOEXTENSIBLE, round(MAXBYTES/1024/1024) MAXMB from dba_Data_files where TABLESPACE_NAME like '%UNDO%';
TABLESPACE_NAME FILE_NAME AUT MAXMB
------------------------------ -------------------------------------------------- --- ----------
UNDOTBS1 +DATA/ITSDBTST/DATAFILE/undotbs1.268.1114585111 YES 32768
Active, expired and unexpired transaction space usage in Undo Tablespace
ACTIVE: active transaction going in database, utilizing the undo tablespace and cannot be truncated.
EXPIRED: transaction which is completed and complete the undo_retention time and now first candidate for trucated from undo tablespace.
UNEXPIRED: transaction which is completed but not completed the undo retention time. It can be trucated if required.
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 EXPIRED 20.5 58
UNDOTBS1 UNEXPIRED 29 44
Undo usage by user/schema
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, v$transaction t , v$session s
where u.segment_name = '_SYSSMU' || t.xidusn || '$' and t.addr = s.taddr
group by u.tablespace_name, s.username, u.status order by 1,2,3;
UNDO Monitoring
UNDOBLKS: blocks written per 10min interval
UNXPSTEALCNT: unexpired steal, should be 0, otherwise UNDO_RETENTION does not match and extend UNDO space or lower retention should be considered
SELECT TO_CHAR(end_time, 'yyyymmdd hh24:mi:ss') AS end, undoblks, unxpstealcnt FROM v$undostat;
END UNDOBLKS UNXPSTEALCNT
----------------- ---------- ------------
20231019 06:56:52 246 0
20231019 06:46:52 290 0
20231019 06:36:52 266 0
20231019 06:26:52 244 0
20231019 06:16:52 266 0
20231019 06:06:52 545 0
...
Calculate UNDO_RETENTION for 1h:
1h = 6 10min intervals: 6 * 300 blocks (avg) * 8k = 14M
Estimate proper setting of UNDO_RETENTION parameter
(https://www.alex-sanz.com/2022/06/21/high-undo-tablespace-space-usage/)
select maxquerylen,to_char(begin_time,'DD-MON-YYYY HH24:MI:SS'), tuned_undoretention from v$undostat order by begin_time asc;
MAXQUERYLEN TO_CHAR(BEGIN_TIME,'DD-MON-YY TUNED_UNDORETENTION
----------- ----------------------------- -------------------
1646 15-OCT-2023 08:06:52 2487
1047 15-OCT-2023 08:16:52 1829
1052 15-OCT-2023 08:26:52 1833
1656 15-OCT-2023 08:36:52 2438
1059 15-OCT-2023 08:46:52 1840
1663 15-OCT-2023 08:56:52 2444
...
MAXQUERYLEN: length of the longest query (in seconds) executed in the instance during the period
TUNED_UNDORETENTION: time (in seconds) for which undo will not be recycled from the time it was committed
To be tested:
statements that have used an excessive amount of undo:
select sql_text from dba_hist_sqltext
where sql_id in
(select distinct maxquerysqlid from
(select * from
(select
snap_id,
maxquerylen,
undoblks,
maxquerysqlid,
to_char(begin_time, 'yyyy/mm/dd hh24:mi' ) begin,
to_char(end_time, 'yyyy/mm/dd hh24:mi' ) end
from dba_hist_undostat order by undoblks desc, maxquerylen desc
)
where rownum<11
)
);
list (subselect from above):
select snap_id, maxquerylen, undoblks, maxquerysqlid,
to_char(begin_time, 'yyyy/mm/dd hh24:mi' ) begin,
to_char(end_time, 'yyyy/mm/dd hh24:mi' ) end
from dba_hist_undostat order by undoblks desc, maxquerylen desc
select SQL_TEXT from V$SQLTEXT where SQL_ID='MAXQUERYSQLID from above';
SELECT SES.SID, SQL_TEXT FROM V$SESSION SES, V$SQLTEXT SQL WHERE SES.sql_hash_value = SQL.hash_value and SQL.SQL_ID='MAXQUERYSQLID from above' (if session still exists)
