Transactions are the lifeblood of a relational database, Oracle included. Every insert, update and delete is important, but so is the ability to rollback, or undo, any current transaction should an error or mistake occur. Getting some transactions ‘right’ can involve some trial and error on the part of the developer as queries used to provide the data set to modify are tuned and adjusted. Oracle automatically provides the ability to rollback uncommitted transactions and, in the process, allows the DBA to monitor the undo area for usage. Although automatic undo shouldn’t be an issue, provided the UNDO tablespace is properly sized for the workload, it can be helpful to see which SQL statements are consuming undo, and how much they are consuming. Let’s look into the ways a DBA can find this information.
Automatic undo has been in use for quite a while, utilizing a locally managed tablespace created specifically for this purpose. Undo segments are created automatically to meet the changing demand. As these segments are used the number of extents increases allowing even the largest transactions to generate sufficient undo to rollback the transaction if the need arises. Of course there is the dreaded ORA-01555 error, thrown when changes overwrite undo records being used by a long-running transaction. Manual rollback is also possible, should a transaction update more data than intended and need to be modified to provide correct results, provided the action has not yet been committed. This should be common knowledge to the DBA.
Four views can give the DBA information on the undo operations and usage:
DBA_HIST_UNDOSTAT
DBA_UNDO_EXTENTS
GV_$UNDOSTAT
V_$UNDOSTAT
DBA_HIST_UNDOSTAT is part of the Diagnostic and Tuning Pack views used by AWR and ASH reports. It collects limited historical data about the undo stream and its usage; snapshots are collected from the data in GV_$UNDOSTAT and kept in accordance with the configured retention period for the workload repository.
DBA_UNDO_EXTENTS provides information on the allocated undo extents, including segment name, extent number and the associated size.
GV_$UNDOSTAT, along with V_$UNDOSTAT, allow the DBA to monitor undo activity through statistical information such as the blocks consumed, the transaction count for the reported interval, various “steal” counts, active, unexpired and expired block counts and the tuned undo retention value. Such data helps the DBA track usage by date and time so that periods of peak activity can be determined.
The following views also report on undo, using the older rollback terminology in their naming:
DBA_ROLLBACK_SEGS
GV_$ROLLSTAT
KU$_ROLLBACK_VIEW
V_$ROLLNAME
V_$ROLLSTAT
These do provide different information than the %UNDO% views, adding another dimension to the reporting data. All nine should be considered when reporting on UNDO usage.
Other views outside of those nine can also contribute to UNDO reporting; the following query produces a useful report on undo usage and what SQL, if any, is responsible:
set linesize 240
COLUMN sid FORMAT 99999
COLUMN command_or_action FORMAT A90 HEADING ‘SQL Command or Action’ WRAP
COLUMN current_time FORMAT A19 HEADING ‘Current Time’
COLUMN rollback_mb FORMAT 99,990.90 HEADING ‘Undo|MB Used’
COLUMN rows_per_sec FORMAT 99,999,990.90 HEADING ‘TXN Rows|Per Sec’
COLUMN start_time FORMAT a19 HEADING ‘TXN Start Time’
COLUMN status FORMAT A8 HEADING ‘Status’
COLUMN used_urec FORMAT 9,999,999,990 HEADING ‘Undo Rows|Written’
COLUMN username FORMAT A15 HEADING ‘Username’
COLUMN program format a25
SELECT s.sid
, TO_CHAR(TO_DATE(t.start_time, ‘MM/DD/YY HH24:MI:SS’), ‘DD-MON-YY HH24:MI:SS’) start_time
, TO_CHAR(sysdate, ‘DD-MON-YY HH24:MI:SS’) current_time
, CASE WHEN (sysdate – TO_DATE(t.start_time, ‘MM/DD/YY HH24:MI:SS’)) = 0
THEN t.used_urec
ELSE t.used_urec / NVL(((sysdate – TO_DATE(t.start_time, ‘MM/DD/YY HH24:MI:SS’)) * 86400), 1)
END rows_per_sec
, SUBSTR(s.username, 1, 15) username
, program
, DECODE(BITAND(t.flag,128), 0, NULL, ‘Rollback’) status
, t.used_urec
, ROUND(r.rssize / 1024 / 1024, 2) rollback_mb
, DECODE(sq.sql_text, NULL, DECODE(aa.name, NULL, ‘UNKNOWN’, aa.name), sq.sql_text) command_or_action
FROM v$transaction t
, v$session s
, v$rollstat r
, v$sql sq
, audit_actions aa
WHERE (t.xidusn = r.usn)
AND (t.addr = s.taddr (+))
AND ( s.sql_hash_value = sq.hash_value (+)
AND s.sql_address = sq.address (+))
AND (s.command = aa.action)
ORDER BY t.start_time, s.sid;
Looking at the data the above query returns we find a wealth of information:
SID TXN Start Time Current Time Per Sec Username PROGRAM Status Written MB Used SQL Command or Action
145 01-JUN-22 15:38:06 01-JUN-22 15:38:16 3,755.30 BING sqlplus@a633a772bc3b (TNS 37,553 4.99 delete from t1 where mod(id,100) >= 10
V1-V3)
145 01-JUN-22 15:38:06 01-JUN-22 15:38:16 3,755.30 BING sqlplus@a633a772bc3b (TNS 37,553 4.99 delete from t1 where mod(id,100) >= 10
V1-V3)
145 01-JUN-22 15:38:06 01-JUN-22 15:38:16 3,755.30 BING sqlplus@a633a772bc3b (TNS 37,553 4.99 delete from t1 where mod(id,100) >= 10
V1-V3)
The DBA now has the undo rows written, the megabytes of UNDO space consumed and the SQL statement generating this undo usage. Repeated execution of this query will return the current UNDO usage for all active transactions generating UNDO, including recursive operations executed by the database engine such as data dictionary management processes. Such actions are likely to be listed with a SQL command as UNKNOWN in the query output since no name is assigned in the AUDIT_ACTIONS view. This query allows the DBA to dynamically monitor UNDO usage by transaction so long-running operations, that can consume large quantities of space, can be identified and possibly tuned to reduce the UNDO demand.
how does one go about tuning UNDO? With releases of Oracle starting with 10.2 the DBMS_UNDO_ADV package is available:
FUNCTION BEST_POSSIBLE_RETENTION RETURNS NUMBER
Argument Name Type In/Out Default?
STARTTIME DATE IN
ENDTIME DATE IN
FUNCTION BEST_POSSIBLE_RETENTION RETURNS NUMBER
FUNCTION BEST_POSSIBLE_RETENTION RETURNS NUMBER
Argument Name Type In/Out Default?
S1 NUMBER IN
S2 NUMBER IN
FUNCTION LONGEST_QUERY RETURNS NUMBER
Argument Name Type In/Out Default?
STARTTIME DATE IN
ENDTIME DATE IN
FUNCTION LONGEST_QUERY RETURNS NUMBER
FUNCTION LONGEST_QUERY RETURNS NUMBER
Argument Name Type In/Out Default?
S1 NUMBER IN
S2 NUMBER IN
FUNCTION RBU_MIGRATION RETURNS NUMBER
Argument Name Type In/Out Default?
STARTTIME DATE IN
ENDTIME DATE IN
FUNCTION RBU_MIGRATION RETURNS NUMBER
FUNCTION REQUIRED_RETENTION RETURNS NUMBER
Argument Name Type In/Out Default?
STARTTIME DATE IN
ENDTIME DATE IN
FUNCTION REQUIRED_RETENTION RETURNS NUMBER
FUNCTION REQUIRED_RETENTION RETURNS NUMBER
Argument Name Type In/Out Default?
S1 NUMBER IN
S2 NUMBER IN
FUNCTION REQUIRED_UNDO_SIZE RETURNS NUMBER
Argument Name Type In/Out Default?
RETENTION NUMBER IN
STARTTIME DATE IN
ENDTIME DATE IN
FUNCTION REQUIRED_UNDO_SIZE RETURNS NUMBER
Argument Name Type In/Out Default?
RETENTION NUMBER IN
FUNCTION REQUIRED_UNDO_SIZE RETURNS NUMBER
Argument Name Type In/Out Default?
RETENTION NUMBER IN
S1 NUMBER IN
S2 NUMBER IN
FUNCTION REQUIRED_UNDO_SIZE RETURNS NUMBER
Argument Name Type In/Out Default?
RETENTION DBMS_UADV_ARR IN
UTBSIZE DBMS_UADV_ARR IN/OUT
STARTTIME DATE IN
ENDTIME DATE IN
FUNCTION REQUIRED_UNDO_SIZE RETURNS NUMBER
Argument Name Type In/Out Default?
RETENTION DBMS_UADV_ARR IN
UTBSIZE DBMS_UADV_ARR IN/OUT
FUNCTION REQUIRED_UNDO_SIZE RETURNS NUMBER
Argument Name Type In/Out Default?
RETENTION DBMS_UADV_ARR IN
UTBSIZE DBMS_UADV_ARR IN/OUT
S1 NUMBER IN
S2 NUMBER IN
FUNCTION UNDO_ADVISOR RETURNS VARCHAR2
Argument Name Type In/Out Default?
STARTTIME DATE IN
ENDTIME DATE IN
INSTANCE NUMBER IN
FUNCTION UNDO_ADVISOR RETURNS VARCHAR2
Argument Name Type In/Out Default?
INSTANCE NUMBER IN
FUNCTION UNDO_ADVISOR RETURNS VARCHAR2
Argument Name Type In/Out Default?
S1 NUMBER IN
S2 NUMBER IN
INSTANCE NUMBER IN
FUNCTION UNDO_AUTOTUNE RETURNS BOOLEAN
Argument Name Type In/Out Default?
AUTOTUNE_ENABLED BOOLEAN OUT
FUNCTION UNDO_HEALTH RETURNS NUMBER
Argument Name Type In/Out Default?
PROBLEM VARCHAR2 OUT
RECOMMENDATION VARCHAR2 OUT
RATIONALE VARCHAR2 OUT
RETENTION NUMBER OUT
UTBSIZE NUMBER OUT
FUNCTION UNDO_HEALTH RETURNS NUMBER
Argument Name Type In/Out Default?
STARTTIME DATE IN
ENDTIME DATE IN
PROBLEM VARCHAR2 OUT
RECOMMENDATION VARCHAR2 OUT
RATIONALE VARCHAR2 OUT
RETENTION NUMBER OUT
UTBSIZE NUMBER OUT
FUNCTION UNDO_HEALTH RETURNS NUMBER
Argument Name Type In/Out Default?
S1 NUMBER IN
S2 NUMBER IN
PROBLEM VARCHAR2 OUT
RECOMMENDATION VARCHAR2 OUT
RATIONALE VARCHAR2 OUT
RETENTION NUMBER OUT
UTBSIZE NUMBER OUT
FUNCTION UNDO_INFO RETURNS BOOLEAN
Argument Name Type In/Out Default?
TABLE_SPACE_NAME VARCHAR2 OUT
TABLE_SPACE_SIZE NUMBER OUT
AUTO_EXTEND BOOLEAN OUT
UNDO_RETENTION NUMBER OUT
RETENTION_GUARANTEE BOOLEAN OUT
Two functions appear to be quite useful — undo_advisor and undo_health. These functions perform transactions to test the UNDO tablespace so they cannot be executed using selects. Taking undo_health “for a spin” we have:
1 declare
2 PROBLEM VARCHAR2(4000);
3 RECOMMENDATION VARCHAR2(4000);
4 RATIONALE VARCHAR2(4000);
5 RETENTION NUMBER;
6 UTBSIZE NUMBER;
7 retval number;
8 begin
9 retval :=dbms_undo_adv.undo_health(sysdate-1, sysdate,problem,recommendation,rationale,retention,utbsize);
10 dbms_output.put_line(problem);
11 dbms_output.put_line(recommendation);
12 dbms_output.put_line(rationale);
13 dbms_output.put_line(retention);
14 dbms_output.put_line(utbsize);
15* end;
SQL > /
No problem found
0
0
PL/SQL procedure successfully completed.
The function can return up to five values, depending upon what, if any, problem is discovered. As can be seen from the above example no issues are present in the undo tablespace. In similar fashion the undo_advisor is called, and it returns one value. Taking that function for a test drive we find:
1 declare
2 retval varchar2(4000);
3 begin
4 retval :=dbms_undo_adv.undo_advisor(sysdate-1, sysdate,1);
5 dbms_output.put_line(retval);
6* end;
SQL > /
Finding 1:The undo tablespace is OK.
PL/SQL procedure successfully completed.
The output is fairly sparse when no issues are present, but when the UNDO tablespace is healthy effusive output is unnecessary.
Other functions, like best_possible_retention, can be executed through a query. This one outputs, obviously, the best possible undo_retention setting to use:
SQL> select dbms_undo_adv.best_possible_retention(sysdate-1, sysdate) from dual;
DBMS_UNDO_ADV.BEST_POSSIBLE_RETENTION(SYSDATE-1,SYSDATE)
The value it can generate may seem a bit … large when compared to the default setting of 900. Remember this is a recommended setting, not a value chiseled in stone, so it may be accepted or ignored. As long as such recommendations are not implemented with a guarantee there should be no issues with setting a retention value that large.
Part of the tuning process is monitoring the undo system after changes are made. DBA_UNDO_EXTENTS shows the available extents and their expiration status; the larger the undo_retention the longer extents will remain unexpired. Unexpired extents are not an issue, as Oracle is prepared to “steal” unexpired undo extents if they are needed, unless the retention is guaranteed. Guaranteeing undo retention runs the risk of errors in query execution since Oracle cannot “steal” unexpired extents when they are needed, even if they are not being used.
How do you know if undo is being “stolen”? The v$undostat view reports on that and more:
SQL > desc v$undostat
Name Null? Type
BEGIN_TIME DATE
END_TIME DATE
UNDOTSN NUMBER
UNDOBLKS NUMBER
TXNCOUNT NUMBER
MAXQUERYLEN NUMBER
MAXQUERYID VARCHAR2(13)
MAXCONCURRENCY NUMBER
UNXPSTEALCNT NUMBER
UNXPBLKRELCNT NUMBER
UNXPBLKREUCNT NUMBER
EXPSTEALCNT NUMBER
EXPBLKRELCNT NUMBER
EXPBLKREUCNT NUMBER
SSOLDERRCNT NUMBER
NOSPACEERRCNT NUMBER
ACTIVEBLKS NUMBER
UNEXPIREDBLKS NUMBER
EXPIREDBLKS NUMBER
TUNED_UNDORETENTION NUMBER
CON_ID NUMBER
UNDOBLKS, TXNCOUNT, MAXQUERYLEN, MAXQUERYID and UNXPSTEALCNT are worth monitoring since these reflect the volume of UNDO generated and managed by Oracle. Oracle “stealing” unexpired undo extents often indicates an UNDO tablespace that may not be large enough to handle the transaction load users are generating. Let’s look at some typical output from that view:
BEGIN_TIME END_TIME UNDOTSN UNDOBLKS TXNCOUNT MAXQUERYLEN MAXQUERYID MAXCONCURRENCY UNXPSTEALCNT UNXPBLKRELCNT UNXPBLKREUCNT EXPSTEALCNT EXPBLKRELCNT EXPBLKREUCNT SSOLDERRCNT
NOSPACEERRCNT ACTIVEBLKS UNEXPIREDBLKS EXPIREDBLKS TUNED_UNDORETENTION CON_ID
18-OCT-2022 16:51:20 18-OCT-2022 16:54:42 2 0 41 0 0 0 0 0 0 0 0 0
0 160 221312 16512 444650 0
18-OCT-2022 16:41:20 18-OCT-2022 16:51:20 2 0 12 0 0 0 0 0 0 0 0 0
0 160 221312 16512 437282 0
18-OCT-2022 16:31:20 18-OCT-2022 16:41:20 2 17 150 1580 f3yfg50ga0r8n 2 0 0 0 0 0 0 0
0 160 221312 16512 415260 0
…
The MAXQUERYLEN is in seconds, and the MAXQUERYID is the SQL_ID for that query. Notice that all of the various count variables record 0, an indication that UNDO is properly sized. Should any counts increase it is likely that the UNDO tablespace will need one or more additional data files. When data files are added to an UNDO tablespace ensure they are set to autoextend, to minimize the attention that tablespace may need (it is easier to let Oracle automatically extend the available data files than it is to constantly monitor file size and manually extend one or more files after an error is thrown).
Querying v$undostat along with regular execution of the two DBMS_UNDO_ADV scripts provided can provide a fairly robust suite of checks for UNDO health. The scripts take very little time to run and do not affect currently running transactions so regular executions throughout the work day are not a concern.
A “quick and dirty” check on undo consumption can be generated with this query:
et linesize 230 pagesize 30
col username format a30
col target format a67
select s.username, s.sid, s.serial#, t.ubablk, t.ubarec, lo.sofar, lo.totalwork, lo.target
from v$session s join v$transaction t on s.taddr = t.addr
left outer join v$session_longops lo on lo.sid = s.sid and lo.serial# = s.serial#
/
It reports the username, session identifiers, undo blocks used, undo records written, the table being accessed, the work done so far and the total work for the operation reported. Sample output follows:
USERNAME SID SERIAL# UBABLK UBAREC SOFAR TOTALWORK TARGET
CH33_USER 1710 57816 56446 67 1140540 1140540 CH33.TIMS_DOCUMENT
CH33_USER 15 9561 233585 58
CH33_USER 3134 11670 326184 35
CH33_USER 3416 7942 1929966 33
CH33_USER 4294 40398 295056 17
CH33_USER 635 33966 289612 53
SWEAMS_USER 581 36936 295056 17
SWEAMS_USER 3969 62960 56446 67
CH33_USER 3428 1681 1009409 33
9 rows selected.
This query can be useful when seeing large volumes of undo that are not released in a short period of time, signalling the DBA to monitor the UNDO tablespace allocations and the undo segments in use. Should the UNDO tablespace usage pass 90% many monitoring tools will provide a space warning — of course, depending upon the autoextend state of the datafiles the warning may be ignorable. A query like this:
set linesize 132 tab off trimspool on
set pagesize 105
set pause off
set feedb on
column “TOTAL AVAIL (MB)” format 999,999,999,990.00
column “TOTAL PHYS ALLOC (MB)” format 9,999,990.00
column “USED (MB)” format 9,999,990.00
column “FREE (MB)” format 9,999,990.00
column “% USED OF AVAIL” format 990.00
column “% USED OF ALLOC” format 990.00
select a.tablespace_name,
nvl(b.tot_used,0)/(10241024) “USED (MB)”, a.physical_bytes/(10241024) “TOTAL PHYS ALLOC (MB)”,
a.bytes_alloc/(10241024) “TOTAL AVAIL (MB)”, (nvl(b.tot_used,0)/a.physical_bytes)100 “% USED OF ALLOC”,
(nvl(b.tot_used,0)/a.bytes_alloc)100 “% USED OF AVAIL” from ( select tablespace_name, sum(bytes) physical_bytes, sum(decode(autoextensible,’NO’,bytes,’YES’,maxbytes)) bytes_alloc from dba_data_files group by tablespace_name ) a, ( select tablespace_name, sum(bytes) tot_used from dba_segments group by tablespace_name ) b where a.tablespace_name = b.tablespace_name (+) — and (nvl(b.tot_used,0)/a.bytes_alloc)100 > 10
and a.tablespace_name not in (select distinct tablespace_name from dba_temp_files)
order by 6
/
reports tablespace usage relative to the currently allocated space and also calculates usage based upon the maximum size the autoextend files can attain:
TABLESPACE_NAME USED (MB) TOTAL PHYS ALLOC (MB) TOTAL AVAIL (MB) % USED OF ALLOC % USED OF AVAIL
USERS 6.13 5,521.00 32,767.98 0.11 0.02
SYSTEM 1,994.13 2,010.00 32,767.98 99.21 6.09
SYSAUX 2,150.25 13,400.00 32,767.98 16.05 6.56
UNDOTBS1 42,492.50 118,781.00 131,071.94 35.77 32.42
Monitoring tools usually trigger oh the value in the first percent used column, which can differ significantly from value in the second percent used column. A query like this one can help the DBA distinguish the between an actual alert and “noise” and save him or her from needlessly adding storage. It also keeps management from worrying about essentially nothing, and everyone can appreciate that.
In the event the DBA must add space to the UNDO tablespace note that the datafiles are set to autoextend. This is easily accomplished using the following syntax:
alter tablespace undotbs1 add datafile ‘/my/data/files/undotbs4.dbf’ size 20g autoextend on next 128M maxsize unlimited;
Of course the UNDO tablespace name and the fully qualified file name are unique to each database installation, so before adding space verify the file names and locations already configured for the UNDO tablespace. What may seem odd here is the 128M autoextend allocations; checking what Oracle created will confirm that value. Do not change that value as it may adversely affect UNDO performance.
Even with automatic undo management in the database it still falls on the shoulders of the DBA to regularly monitor UNDO space ans usage. Regular database health checks should include such checks to provide at least a daily glimpse in the UNDO arena. If UNDO fails the database stops processing transactions until the issue is resolved, which would indeed be a business disaster visible to all. Being in the “hot seat” is NEVER comfortable, and anything someone can do to prevent that from occurring is definitely effort well-spent. A few minutes each day monitoring UNDO is cheap insurance that can prevent a grinding halt to data processing at the most inopportune time
UNDO is a critical part of the transaction pathway and should be given a prominent place in any database monitoring script. Not monitoring UNDO is a flaw that needs correction — the queries provided here can give the DBA the “heads up” notice needed to ensure every transaction can complete as expected. And they will give the DBA knowledge of the UNDO usage patterns so that at peak times of activity there is sufficient UNDO space to handle the load. A calm DBA is a useful DBA, and a prepared DBA is, obviously, calm. So monitor UNDO before it can become an issue.
Unless you LIKE being Chicken Little.
Oracle Tips and Tricks -- David Fitzjarrell 