Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Tuning the Undo Records (Rollback

Oracle Tips by Burleson

Oracle uses Undo records (formally called rollback segments prior to Oracle) to maintain the “before” images for rows. Whenever a task terminates with an abort or a rollback, Oracle goes to the undo records and reapplies the previous images for the rows. Oracle supports both rollback segments or undo records, but rollback segments for managing undo space is being deprecated in Oracle. Oracle strongly recommends that you use automatic undo management and manage undo space using an undo tablespace.

With rollback segments, the DBA was required to create several static rollback segments of a uniform size with a rollback segment tablespace.  With undo tablespaces, Oracle creates and manages undo records automatically and internally as they are needed, and the DBA is only required to specify the overall tablespace size.

Remember, you can continue to use rollback segments in Oracle, and Oracle provides a parameter called undo_management to control the method.  If the value of undo_management is set to RMU, Oracle will use rollback segments, and setting the value of undo_management to SMU will use undo segments.  The default for a new Oracle database is AUTO, which creates undo segments.

The v$ views and STATSPACK tables for rollback management have changed dramatically in Oracle. Oracle introduced the v$undostat view to replace the v$rollstat view from Oracle8i and earlier releases.

The v$undostat view contains statistics for monitoring the effects of transaction execution on undo space in the current instance. These are available for space usage, transaction concurrency and length of query operations. The v$undostat view contains information that spans over a 24 hour period and each row in v$undostat view contains data for a 10 minute interval.

Inside the database instance, there needs to be enough undo record space to accommodate all concurrent update tasks. In addition, the undo record tablespace must be large enough to hold all of the “before” images between the start and the end (commit or rollback) checkpoints.

There is a relationship between the undo segments and system latches. A transaction writing rollback data has to first access the transaction table stored in the undo segment header and acquire a slot in that table. This requires momentary latching of the table to serialize concurrent update to it. If the database is update-intensive and has a small number of rollback segments, user transactions will wait on the latch to access the transaction table.

In Oracle8i and before, rollback segments should be the same size and created with a large number of small extents. For large transactions, special rollback segments can be created and the task directed to the rollback segment with the set transaction use rollback segment xxx command.

The STATSPACK tables provide a table called stats$undostats (called stats$rollstat prior to Oracle) that keeps statistics on the behavior of each rollback segment. The important metrics include the number of wraps for each rollback segment and any waits that may happen within a rollback segment.

The script below displays rollback segment and undo segment information:

rpt_undo.sql (Oracle Only)

column c1 heading "Start|Time" format a15;
column c2 heading "End|Time"   format a15;
column c3 heading "Total|Undo|Blocks|Used" format 999,999;
column c4 heading "Total|Number of|Transactions|Executed" format 999,999;
column c5 heading "Longest|Query|(sec)" format 9,999;
column c6 heading "Highest|Concurrent|Transaction|Count" format 9,999;

   TO_CHAR(Begin_Time,'DD-MON-YY HH24:MI')      c1,
   TO_CHAR(End_Time,'DD-MON-YY HH24:MI')        c2,
   SUM(Undoblks)                                c3,
   SUM(Txncount)                                c4,
   MAX(Maxquerylen)                             c5,
   MAX(Maxconcurrency)                          c6
group by
   TO_CHAR(Begin_Time,'DD-MON-YY HH24:MI'),
   TO_CHAR(End_Time,'DD-MON-YY HH24:MI')

Here is the output from the rpt_undo.sql script.

                                   Total        Total             Highest      
                                    Undo    Number of Longest  Concurrent      
Start           End               Blocks Transactions   Query Transaction      
Time            Time                Used     Executed   (sec)       Count      
--------------- --------------- -------- ------------ ------- -----------      
08-DEC-01 18:30 08-DEC-01 18:40        0            2       0           1      
08-DEC-01 18:40 08-DEC-01 18:50        0            2       0           1      
08-DEC-01 18:50 08-DEC-01 19:00        0            2       0           1      
08-DEC-01 19:00 08-DEC-01 19:10        1            2       0           1      
08-DEC-01 19:10 08-DEC-01 19:20        0            2       0           1      
08-DEC-01 19:20 08-DEC-01 19:30        0            2       0           1      
08-DEC-01 19:30 08-DEC-01 19:40        0            2       0           1      
08-DEC-01 19:40 08-DEC-01 19:50        1            2       0           1      
08-DEC-01 19:50 08-DEC-01 20:00        0            2       0           1      
08-DEC-01 20:00 08-DEC-01 20:10      183          380       1           3      
08-DEC-01 20:10 08-DEC-01 20:20      161        2,008      97           3      
08-DEC-01 20:20 08-DEC-01 20:30      253        1,786      12           3      
13-DEC-01 11:56 13-DEC-01 12:06        1           12      14           1      
13-DEC-01 12:06 13-DEC-01 12:16        0            2       0           1      
13-DEC-01 12:16 13-DEC-01 12:26        0            2       0           1      
13-DEC-01 12:26 13-DEC-01 12:36        0            2       0           1      
13-DEC-01 12:36 13-DEC-01 12:46        0            2       0           1      
13-DEC-01 12:46 13-DEC-01 12:56        0            2       0           1      
13-DEC-01 12:56 13-DEC-01 13:06        1            9       0           2      
13-DEC-01 13:06 13-DEC-01 13:16      812        2,411     105           4      
13-DEC-01 13:16 13-DEC-01 13:26    9,032        1,172      78           3      
13-DEC-01 13:26 13-DEC-01 13:36    6,966          480      78           3      
13-DEC-01 13:36 13-DEC-01 13:46        0            2       0           1      
13-DEC-01 13:46 13-DEC-01 13:56        0            2       0           1      

Here we see the undo activity segregated by hour of the day and the total number of transactions within the undo segments by the hour.

Below we see the Oracle8i version of this script.

rpt_rbs.sql (Pre-Oracle version)

set pages 9999;

column buffer_busy_wait format 999,999,999
column mydate heading 'Yr.  Mo Dy  Hr.' format a16
column c0 heading "Name"    format 99
column c1 heading "sz now"   format 9,999
column c2 heading "extends" format 9,999
column c3 heading "# trans." format 9,999
column c4 heading "wraps" format 9,999
column c5 heading "High WM" format 999;
column c7 heading "Shrinks" format 999;
column c6 heading "status"
column c8 heading "Waits" format 9,999;

   to_char(snap_time,'yyyy-mm-dd HH24')   mydate,
   new.usn                                c0,
   (new.rssize-old.rssize)/1048576        c1,
   (new.hwmsize-old.hwmsize)/1048576      c5,
   new.extends-old.extends                c2,
   new.waits-old.waits                    c8,
   new.xacts-old.xacts                    c3,
   new.wraps-old.wraps                    c4,
   new.shrinks-old.shrinks                c7
   perfstat.stats$rollstat old,
   perfstat.stats$rollstat new,
   perfstat.stats$snapshot   sn
   (new.rssize-old.rssize) > 0
   new.xacts-old.xacts > 0
   new.snap_id = sn.snap_id
   old.snap_id = sn.snap_id-1
   new.usn = old.usn

Here is the output from the STATSPACK script. Here we see the size of each rollback segment, the high-water mark, the number of extends, the number of waits, the number of transactions within the rollback segment, the number of rollback segment wraps, and the number of shrinks within the rollback segments.

Yr.  Mo Dy  Hr.  Name sz now High WM extends  Waits # trans.  wraps Sh
------------ --- ------ ------- ------- ------ -------- ------ -------
2001-01-01 01  3      6       6       6      1        1     63       0
2001-01-07 17  1      2       2       2      0        1     73       0
2001-01-10 23  2      6       6       6      1        1     79       0
2001-01-10 23  3      5       5       5      3        1    155       0

This is an excerpt from "Oracle High Performance tuning with STATSPACK" by Oracle Press.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter