You’re On A Roll(back)

"I know one thing for certain; it is much harder to tell whether you are lost than whether 
you were lost, for, on many occasions, where you are going is exactly where you are. On 
the other hand, if you often find that where you've been is not at all where you should have 
gone, and, since it's much more difficult to find your way back from someplace you've never 
left, I suggest you go there immediately and then decide."
-- Norton Juster, The Phantom Tollbooth

A recent question in an Oracle forum regarding Automatic UNDO Management shows that the topic is still misunderstood by some. The basic idea behind Automatic UNDO Management is that the database essentially does the work for the DBA, freeing him or her from a tedious task of monitoring and adjusting UNDO (or rollback) segments. This mechanism also manages the number of segments created at database startup, which initiated the question and fueled the discussion. How does Oracle decide how many UNDO segments to create at startup, and what is the underlying goal of the process? Let’s investigate.

Automatic UNDO Management attempts to do the following:

	*	Manage the UNDO tablespace
	*	Allocate UNDO segments
	*	Assign transactions to UNDO segments
	*	Release UNDO segments when transactions complete

To do all of this Oracle reqiures an UNDO tablespace that is different from the others. No standard database objects can be created in an UNDO tablespace (users cannot put tables or indexes there) so Oracle can manage the objects unimpeded. This makes the job easier for the DBA, and also restricts what a DBA can do to the tablespace. The list of available tasks a DBA can perform is shown below:

    Adding a data file

    Renaming a data file

    Bringing a data file online or taking it offline

    Beginning or ending an open backup on a data file

    Enabling and disabling undo retention guarantee

Only one UNDO tablespace can be active for a database but that doesn’t mean additional UNDO tablespaces can’t be created to address different processing scenarios. For example UNDOTBS1 could be a ‘standard’ UNDO tablespace not set to autoextend since OLTP transactions are usually small and don’t need the extra segment space, where UNDOTBS2 would have its datafiles set to autoextend, so that large batch processing jobs could complete without issue. Setting which UNDO tablespace is used is a simple task:

ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2;

This parameter is dynamic so no restart of the database is necessary, thus it can be included as a command in a batch processing script; once the command is run any new transactions will use UNDOTBS2; existnig transactions using UNDOTBS1 will continue to completion and when all current transactions are done UNDOTBS1 will go offline. For a RAC environment doing this may not be possible if, for some reason, more than one node is using the same UNDO tablespace or the UNDO tablespace being switched to is the same as that for another node. In the second case the initial switch would occur without error; when an attempt was made to switch back to the original UNDO tablespace for the node in question the switch would fail since the current UNDO tablespace is in use by two nodes, which would be the first case. Granted neither situation is likely to occur but it is wise to be prepared in the event someone has made such a switch.

Although it is not a recommended practice quotas can be established for the UNDO tablespace for a resource group using the Resource Manager. Users in a resource group will only get the total UNDO specified in the UNDO_POOL directive. The UNDO_POOL setting applies to the resource group, not each individual member, so if one user of a resource group consumes all of the assigned UNDO space before an update transaction completes that transaction is terminated. Unti a rollback is issued, releasing the UNDO, no other users of the group can perform updates.

Several views provide information on the UNDO tablespace and its activity:

	V$UNDOSTAT
	V$ROLLSTAT
	V$TRANSACTION
	GV$UNDOSTAT (RAC)
	GV$ROLLSTAT (RAC)
	GV$TRANSACTION (RAC)
	DBA_UNDO_EXTENTS
	DBA_HIST_UNDOSTAT <-- Needs no licensing in versions 11.2 and later

When Automatic UNDO management is used V$ROLLSTAT provides information on the UNDO segments. [The GV$ROLLSTAT view provides cluster-wide UNDO information.] The V$UNDOSTAT view can be very helpful in tracking UNDO usage over a short period of time, such as the last 24 to 48 hours. For longer periods DBA_HIST_UNDOSTAT can provide usage trends over the configured AWR retention window. Looking at V$UNDOSTAT:

SQL> SELECT TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME,
  2      TO_CHAR(END_TIME, 'MM/DD/YYYY HH24:MI:SS') END_TIME,
  3      UNDOTSN, UNDOBLKS, TXNCOUNT, MAXCONCURRENCY AS "MAXCON"
  4      FROM v$UNDOSTAT WHERE rownum <= 144;

BEGIN_TIME          END_TIME                UNDOTSN    UNDOBLKS    TXNCOUNT      MAXCON
------------------- ------------------- ----------- ----------- ----------- -----------
09/25/2015 14:05:13 09/25/2015 14:07:56           5          16          40           4
09/25/2015 13:55:13 09/25/2015 14:05:13           5         211         459           4
09/25/2015 13:45:13 09/25/2015 13:55:13           5          67         200           4
09/25/2015 13:35:13 09/25/2015 13:45:13           5          69         193           4
...
09/24/2015 14:45:13 09/24/2015 14:55:13           5          19         149           2
09/24/2015 14:35:13 09/24/2015 14:45:13           5          11          97           1
09/24/2015 14:25:13 09/24/2015 14:35:13           5          14         127           2
09/24/2015 14:15:13 09/24/2015 14:25:13           5          21         143           3

144 rows selected.

SQL>

This report shows the UNDO tablespace being used (UNDOTSN), the blocks consumed (UNDOBLKS), the total numner of transactions executed during the period (TXNCOUNT) and the maximum number of concurrent transactions in that same period (MAXCON). This same report, for a longer window, is returned from DBA_HIST_UNDOSTAT; it would be a good idea to order the results to give a clearer picture of the usage trends over time.

UNDO retention is set with the undo_retention parameter, which provides the minimum retention period. Oracle does its best to honor that retention if the UNDO datafiles are set to autoextend. Oracle automatically manages the retention setting by setting the retention period to somewhat longer than the longest currently running active query to avoid “Snapshot too old” errors. As UNDO needs increase Oracle extends the datafiles to the limit set by MAXSIZE; when that limit is reached then unexpired UNDO information may be overwritten, oldest first. When UNDO datafiles are set to fixed sizes then this setting is ignored.

UNDO retention can be guaranteed but that can cause DML operations to fail on active systems. When the UNDO tablespace is created RETENTION GUARANTEE can be specified; it can also be set for exising UNDO tablespaces with the ALTER TABLESPACE statement. To reverse that change the ALTER TABLESPACE command can be used with RETENTION NOGUARANTEE specified.

V$UNDOSTAT allows the tuned UNDO retention size to be tracked:

SQL> select to_char(begin_time, 'DD-MON-RR HH24:MI') begin_time,
   2    to_char(end_time, 'DD-MON-RR HH24:MI') end_time, tuned_undoretention
   3    from v$undostat order by end_time;

BEGIN_TIME      END_TIME        TUNED_UNDORETENTION
--------------- --------------- -------------------
22-SEP-15 11:45 22-SEP-15 11:55               36000
22-SEP-15 11:55 22-SEP-15 12:05               36000
22-SEP-15 12:05 22-SEP-15 12:15               36000
22-SEP-15 12:15 22-SEP-15 12:25               36000
22-SEP-15 12:25 22-SEP-15 12:35               36000
...
25-SEP-15 14:45 25-SEP-15 14:55               36000
25-SEP-15 14:55 25-SEP-15 14:57               36000

452 rows selected.

SQL>

Changes to the retention will be shown in the query results, allowing the DBA to monitor the automatic retention setting. For some systems the undo_retention parameter setting won’t be overridden (as in the results shown above) but in other cases it may be significantly larger to accomodate longer transactions.

Oracle sets the initial number of UNDO segments based on the system activity; by default it appears that 10 is the ‘magic’ number Oracle allocates, subject to change as the system activity increases. It may not be unusual to have almost 400 UNDO segments, or more, allocated for extremely busy systems. DBA_SEGMENTS can provide that information; simply specify the UNDO tablespace for tablespace_name in your query.

Automatic UNDO Management isn’t voodoo or black magic, although it can seem that way when it isn’t clearly understood. Hopefully you now have a better understanding of the mechanism and how it behaves, making your job a bit easier.

You can now go back to where you weren’t.