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.
