Undo Space  

Posted by Mawahid in

Overview

Undo logs. Traditionally transaction undo information was stored in Rollback Segments until a commit or rollback statement was issued. Automatic undo management allows the DBA to specify how long undo information should be retained after commit, preventing “snapshot too old” errors on long running queries.

This is done by setting the UNDO_RETENTION parameter. The default is 900 seconds (5 minutes), and you can set this parameter to guarantee that Oracle keeps undo logs for extended periods of time.

Rather than having to define and manage rollback segments, you can simply define an Undo tablespace and let Oracle take care of the rest. Turning on automatic undo management is easy. All you need to do is create an undo tablespace and set UNDO_MANAGEMENT = AUTO.
With the below information gathered by SQL query, you may have a idea to prevent undo space is running out so quickly.

Undo Segments

In Undo Segments there are three types of extents, they are

Unexpired Extents – Undo data whose age is less than the undo retention period.
Expired Extents – Undo data whose age is greater than the undo retention period.
Active Extents – Undo data that is part of the active transaction.

The sequence for using extents is as follows,

1. A new extent will be allocated from the undo tablespace when the requirement arises. As undo is being written to an undo segment, if the undo reaches the end of the current extent and the next extent contains expired undo then the new undo (generated by the current transaction) will wrap into that expired extent, in preference to grabbing a free extent from the undo tablespace free extent pool.

2. If this fails because of no available free extents and we cannot autoextend the datafile, then Oracle tries to steal an expired extent from another undo segment.

3. If it still fails because there are no extents with expired status then Oracle tries to reuse an unexpired extent from the current undo segment.

4. If even that fails, Oracle tries to steal an unexpired extent from another undo segment.

5. If all the above fails, an Out-Of-Space error will be reported.

Check the overall status for undos:

select tablespace_name, status, sum(blocks) * 8192/1024/1024/1024 GB from dba_undo_extents group by tablespace_name, status;
 
TABLESPACE_NAME                STATUS            GB
------------------------------ --------- ----------
UNDOTBS1                       UNEXPIRED 2.29626465
UNDOTBS2                       UNEXPIRED 11.0892944
UNDOTBS1                       EXPIRED   7.20245361
UNDOTBS2                       EXPIRED   1.80932617
UNDOTBS2                       ACTIVE       .015625
 
 
Undo Blocks per Second :

SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
      "UNDO_BLOCK_PER_SEC"
  FROM v$undostat;
 
Optimal Undo Retention:

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
       ROUND((d.undo_size / (to_number(f.value) *
       g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
  FROM (
       SELECT SUM(a.bytes) undo_size
          FROM v$datafile a,
               v$tablespace b,
               dba_tablespaces c
         WHERE c.contents = 'UNDO'
           AND c.status = 'ONLINE'
           AND b.name = c.tablespace_name
           AND a.ts# = b.ts#
       ) d,
       v$parameter e,
       v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
              undo_block_per_sec
         FROM v$undostat
       ) g
WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size';
 
 
Calculate Needed UNDO Size for given Database Activity :

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
       g.undo_block_per_sec) / (1024*1024)
      "NEEDED UNDO SIZE [MByte]"
  FROM (
       SELECT SUM(a.bytes) undo_size
         FROM v$datafile a,
              v$tablespace b,
              dba_tablespaces c
        WHERE c.contents = 'UNDO'
          AND c.status = 'ONLINE'
          AND b.name = c.tablespace_name
          AND a.ts# = b.ts#
       ) d,
      v$parameter e,
       v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
         undo_block_per_sec
         FROM v$undostat
       ) g
 WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size';