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 REM 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 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", v$rollstat.GETS, 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", v$rollstat.AVESHRINK, v$rollstat.AVEACTIVE 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 r.name Rollback_Name, p.pid 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 r.name; 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.