Oracle’s UNDO Tablespace and Autoextend

The UNDO tablespace is one of the workhorses of an Oracle database (TEMP being the other) yet, unlike TEMP, UNDO requires a special tablespace configuration so that the UNDO mechanism operates properly. Conventional wisdom on configuring tablespaces in general has little or no place when it comes to the UNDO tablespace and following such guidelines with the UNDO tablespace can wreak havoc on storage. Let’s look into that a bit more.

UNDO is a special mechanism that provides a way for Oracle to do several things:

*	Provide information to rollback an uncommitted transaction
*	Provide a read-consistent view of data at the time a query starts
*	Allow recovery to undo uncommitted changes to the database
*	Provide FLASHBACK QUERY functionality
*	Allow recovery from logical corruption using FLASHBACK features

It’s also configured to reuse segment space as it becomes available. This is where conventional tablespace wisdom can bite you if that conventional tablespace/datafile wisdom is to set autoextend on for at least one file in the tablespace. Doing so for other tablespaces can be a good idea. [Monitoring TEMP can help catch runaway statements that consume vast amounts of temporary space and cause the TEMP tablespace to expand to the limits of the available storage; such statements are usually few and far between.] Setting autoextend for any file in the UNDO tablespace is a mistake and possibly a disaster waiting to happen.

Let’s look at what is supposed to happen with the UNDO tablespace:

*	A transaction begins, updating a large number of rows in a table.
*	Oracle tracks the changes via the REDO and UNDO mechanisms, writing UNDO data to the UNDO tablespace.
*	As UNDO records age out the space is reused by existing transactions

That, of course, is the same mechanism that can throw the dastardly ORA-01555 (snapshot too old) error. With automatic UNDO management this usually isn’t an issue but there are some who think that setting autoextend on for the UNDO datafiles will fix that problem. Unfortuately it won’t as autoextend doesn’t increase the size of the undo segments, just the UNDO tablespace, and if autoextend is on this defeats the mechanism of reusing UNDO space from aged-out UNDO segments. As a result the UNDO tablespace can grow seemingly without bound since Oracle will simply allocated additional file space rather than resuse the expired segments.

Oracle does not recommend the use of autoextend on any of the datafiles associated with the UNDO tablespace, which doesn’t mean Oracle doesn’t allow such a configuration. Examples in the documentation clearly set up autoextend on an UNDO tablespace. According to the documentation: “Oracle Database supports automatic extension of the undo tablespace to facilitate capacity planning of the undo tablespace in the production environment.” To me production is not the place to be unsure of the UNDO configuration; that’s what the testing database environment is for. Running UNDO with autoextend on, in a load-testing scenario might be a good idea as it could provide very useful information for the setup of a fixed-size UNDO tablespace in the production environment. Letting UNDO grow essentially unchecked is not wise; let the system generate errors for space and address them as the need arises. It’s very likely that these errors will be few and require far smaller adjustments than autoextend would generate.

UNDO needs to be checked just like other tablespaces and two views are key to this task: V$UNDOSTAT and V$ROLLSTAT. The following queries report on the status of used UNDO segments, who is using them, and their respective sizes:

set linesize 80
set verify off echo off pause off timing off time off
set feedback off
column o format a8 heading 'O/S|User'
column u format a20 heading 'Oracle|Userid'
column s format a42 heading 'R-S|Name'
column txt format a45 heading 'Current Statement' word

select osuser o,
       username u,
       segment_name s,
       sa.sql_text txt
from   v$session s,
       v$transaction t,
       dba_rollback_segs r,
       v$sqlarea sa
where  s.taddr = t.addr
and    t.xidusn = r.segment_id(+)
and    s.sql_address = sa.address(+)

set verify on
column o clear
column u clear
column txt clear

REM Displays who is using what rollback segment

select vs.username, vt.used_ublk
from v$session vs,
     v$transaction vt
where vt.addr = vs.taddr

This script, or something similar, can help keep track of who is using UNDO segments and how large those segments are. It can also help if it’s run during long transactions as any UNDO size errors will report the segment at fault and that can be mapped back to the user, and transaction, causing the problem.

If even more UNDO information is desired the following script will report undo segment size, resizes, extensions, active transactions and a few other bits of information:

 break on today 
 column today noprint new_value xdate 
 select substr(to_char(sysdate,'fmMonth DD, YYYY HH:MI:SS P.M.'),1,35) today 
 from dual; 
 column name noprint new_value dbname 
 column owner format a35
 column object format a35
 column object_name format a35
 column "ID#" format a12
 column "ID" format a12
 column "File Name" format a60
 column "Phy Reads" format a10
 column "Phy Writes" format a10
 column "Blk Reads" format a10
 column "Blk Writes" format a10
 column "Read Time" format a10
 column "Write Time" format a10
 column "File Total" format a10
 column "Table Name (Segment)" format a35
 column "DataFile Name" format a60
 column "Tablespace Name" format a35
 column "TableSpace Name" format a35
 column "Rollback Name" format a35
 column "Rollback_Name" format a35
 column "Rollback_name" format a35
 column "INI_extent" format a10
 column "Next Exts" format a10
 column "MinEx" format a10
 column "MaxEx" format a10
 column "Size (Bytes)" format a10
 column "Extent#" format a10
 column "Status" format a10
 column "%Incr" format a10
 column machine format a25
 column terminal format a25
 column extent format a12
 column extend format a12
 column waits format a12
 column xacts format a12
 column wraps format a12
 column "Seg Type" format a35
 column "DB Username" format a35 
 select name from v$database; 
 set heading on 
 set feedback off 
 set linesize 350 trimspool on

 spool undo_info.lst 
 prompt ********************************************************** 
 prompt *****            Database Information                ***** 
 prompt ********************************************************** 
 ttitle left "DATABASE:  "dbname"    (AS OF:  "xdate")" 
 select name, created, log_mode from v$database; 
 prompt ********************************************************** 
 ttitle off 
 rem ------------------------------------------------------------- 
 rem             Rollback Information 
 rem ------------------------------------------------------------- 
 set pagesize 66 
 set line 350 trimspool on
 TTitle left "*** Database:  "dbname", Rollback Information ( As of:  " xdate "  ) ***" skip 2 
 select  substr(sys.dba_rollback_segs.SEGMENT_ID,1,5) "ID#", 
         substr(sys.dba_segments.OWNER,1,8) "Owner", 
         substr(sys.dba_segments.TABLESPACE_NAME,1,17) "Tablespace Name", 
         substr(sys.dba_segments.SEGMENT_NAME,1,17) "Rollback Name", 
         substr(sys.dba_rollback_segs.INITIAL_EXTENT,1,10) "INI_Extent", 
         substr(sys.dba_rollback_segs.NEXT_EXTENT,1,10) "Next Exts", 
         substr(sys.dba_segments.MIN_EXTENTS,1,5) "MinEx", 
       substr(sys.dba_segments.MAX_EXTENTS,1,5) "MaxEx", 
         substr(sys.dba_segments.PCT_INCREASE,1,5) "%Incr", 
         substr(sys.dba_segments.BYTES,1,15) "Size (Bytes)", 
         substr(sys.dba_segments.EXTENTS,1,6) "Extent#", 
         substr(sys.dba_rollback_segs.STATUS,1,10) "Status" 
 from sys.dba_segments, sys.dba_rollback_segs 
 where sys.dba_segments.segment_name = sys.dba_rollback_segs.segment_name and 
       sys.dba_segments.segment_type = 'ROLLBACK' 
 order by sys.dba_rollback_segs.segment_id; 
 ttitle off 
 TTitle left " " skip 2 - 
        left "*** Database:  "dbname", Rollback Status ( As of:  " xdate " )  ***" skip 2 
 select substr(V$rollname.NAME,1,20) "Rollback_Name", 
         substr(V$rollstat.EXTENTS,1,6) "EXTENT", 
         v$rollstat.RSSIZE, v$rollstat.WRITES, 
         substr(v$rollstat.XACTS,1,6) "XACTS", 
         substr(v$rollstat.WAITS,1,6) "WAITS", 
         v$rollstat.HWMSIZE, v$rollstat.SHRINKS, 
         substr(v$rollstat.WRAPS,1,6) "WRAPS", 
         substr(v$rollstat.EXTENDS,1,6) "EXTEND", 
 from v$rollname, v$rollstat 
 where v$rollname.USN = v$rollstat.USN 
 order by v$rollname.USN; 
 ttitle off 
 TTitle left " " skip 2 - 
        left "*** Database:  "dbname", Rollback Segment Mapping ( As of:  "   xdate " ) ***" skip 2 
 select Rollback_Name, Oracle_PID, 
         p.spid OS_PID, 
         nvl(p.username,'NO TRANSACTION') TXN_OWNER, 
         p.terminal Terminal 
 from v$lock l, v$process p, v$rollname r 
 where   l.addr = p.addr(+) 
         and trunc(l.id1(+)/65536)=r.usn 
       and l.type(+) = 'TX' 
         and l.lmode(+) = 6 
 order by; 
 ttitle off 
 rem ------------------------------------------------------------- 
 rem ------------------------------------------------------------- 
 spool off 
 set feedback on 

Such a report may be very useful during problem investigations.

The UNDO tablespace should not be configured using autoextend as it intereferes with the normal operation of the UNDO mechanism. If there are regularly occurring UNDO errors then it’s very likely the UNDO tablespace is undersized and it should be expanded manually, in known increments, until the errors go away. Of course there will likely be that one transaction that does throw an error on occasion but those should be dealt with on a case-by-case basis; in the case of ORA-01555 errors it may not be the UNDO tablespace at fault so query tuning might be the correct direction to take. Each generated error needs to be evaluated on its own and addressed accordingly; not every error benefits from a generalized ‘solution’, and it’s better to let the space-related errors be thrown so an actual root cause can be determined than to let Oracle ‘run wild’ with space allocations by setting autoextend on.

Monitoring and managing UNDO space is a bit different than for any other tablespace, and once that is known the task becomes a bit easier. And shortcuts, like using autoextend, can create more problems than they were intended to fix.

See all articles by David Fitzjarrell

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Latest Articles