Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted September 28, 2015

Automatic UNDO in Oracle

By David Fitzjarrell

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 requires 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; existing 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. Until 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="" span="">

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 number 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. I hope you now have a better understanding of the mechanism and how it behaves, making your job a bit easier.

See all articles by David Fitzjarrell



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM