UNDO behavior in Oracle 9i and 10g under microscope

This article is the result of observations of the UNDO tablespace of Oracle 9i and Oracle 10g in various situations. We start with a simple query showing how to monitor the amount of undo generated in a session for a specific time. We investigate the creation, expansion, and resize of UNDO tablespace, and the issues that guide the reuse of UNDO segments. The impact of parameters like UNDO_RETENTION in Oracle 9i and UNDO_RETENTION and the GUARANTEE clause in CREATE UNDO statements is discussed using simple reproducible examples.

Finding the amount of undo generated in the current session

To illustrate the exaplmes in the later sections of this article we need to devise a small transaction (here, it is a single update statement). We also need to know the exact amount of UNDO generated by the statement. Table-1 shows the creation of a table TEMP1, and shows an UPDATE on table TEMP1. It uses a query into the datadictionary dynamic views to find the exact amount of UNDO generated by the UPDATE. We will need this value in subsequent examples. The default block size for the database is 8K.

Table-1: UNDO Blocks and Bytes generated in a transaction/statement

SQL> create table temp1 as
  2  select * from all_objects where rownum < 5001;

Table created.

SQL> update temp1 set owner = 'stage1';

5000 rows updated.

SQL> select USED_UBLK, USED_UREC, START_SCNB
  2  from v$session a, v$transaction b
  3  where rawtohex(a.saddr) = rawtohex(b.ses_addr)
  4  and a.audsid = sys_context('userenv','sessionid');

 USED_UBLK  USED_UREC START_SCNB
---------- ---------- ----------
        59       5001  687483932

SQL> commit;

Commit complete.

FIRST TIME: UNDO segment allocation on creation of UNDO tablespace

The rule of thumb for initial UNDO segment allocation is as follows:

Each undo datafile keeps 64K for internal purposes.

If there is enough space in the UNDO tablespace ten UNDO segments are created. If there is less space, fewer segments are created. A minimum of one segment, and a maximum of ten segments will be created.

Each segment is 128K in size. Each segment has two extents, each 64K in size.

In Oracle 9i one block is unavailable in each undo segment. So if the block size is 8K there will effectively be 120K or 15 undo blocks in each UNDO segment. In Oracle 10g the entire segment is used, so 64K segment gets 16 undo blocks.

Table-2 shows four types of UNDO tablespace in Oracle 9i
The smallest UNDO datafile can be of space 64K header + 1 segment with two extents of 64K = 192K.
The medium UNDO datafile can be of space 64K header + 3 segment with two extents of 64K = 448K.
The just sufficient UNDO datafile is of space 64K header + 10 segment of two extents of 64K = 1344K.
The just sufficient UNDO datafile with autoextend on is of space 64K header + 10 segment of two extents of 64K = 1344K.
The large UNDO datafile of size 20M which has got same segments as of "just sufficient UNDO". So initial usage of UNDO tablespace is maximum of 1344K.

Table-2: UNDO Segments for several sized UNDO Tablespaces

SQL> create undo tablespace UNDO_SMALLEST
  2  datafile '/u02b/oradata/logantst/rbs02a.dbf' size 192K reuse;

Tablespace created.

SQL> select segment_name, sum(blocks), sum(bytes)/1024
  2  from dba_undo_extents
  3  where tablespace_name = 'UNDO_SMALLEST'
  4  group by segment_name;

SEGMENT_NAME                   SUM(BLOCKS) SUM(BYTES)/1024
------------------------------ ----------- ---------------
_SYSSMU11$                              15             120

SQL> create undo tablespace UNDO_MIDSIZE
  2  datafile '/u02b/oradata/logantst/rbs02b.dbf' size 448K reuse;

Tablespace created.

SQL> select segment_name, sum(blocks), sum(bytes)/1024
  2  from dba_undo_extents
  3  where tablespace_name = 'UNDO_MIDSIZE'
  4  group by segment_name;

SEGMENT_NAME                   SUM(BLOCKS) SUM(BYTES)/1024
------------------------------ ----------- ---------------
_SYSSMU12$                              15             120
_SYSSMU13$                              15             120
_SYSSMU14$                              15             120

SQL> create undo tablespace UNDO_FIT
  2  datafile '/u02b/oradata/logantst/rbs02c.dbf' size 1344K reuse;

Tablespace created.

SQL> select segment_name, sum(blocks), sum(bytes)/1024
  2  from dba_undo_extents
  3  where tablespace_name = 'UNDO_FIT'
  4  group by segment_name;

SEGMENT_NAME                   SUM(BLOCKS) SUM(BYTES)/1024
------------------------------ ----------- ---------------
_SYSSMU15$                              15             120
_SYSSMU16$                              15             120
_SYSSMU17$                              15             120
_SYSSMU18$                              15             120
_SYSSMU19$                              15             120
_SYSSMU20$                              15             120
_SYSSMU21$                              15             120
_SYSSMU22$                              15             120
_SYSSMU23$                              15             120
_SYSSMU24$                              15             120

10 rows selected.

SQL> create undo tablespace UNDO_FITEXTEND
  2  datafile '/u02b/oradata/logantst/rbs02d.dbf' size 1344K reuse
  3  autoextend on next 1M;

Tablespace created.

SQL> select segment_name, sum(blocks), sum(bytes)/1024
  2  from dba_undo_extents
  3  where tablespace_name = 'UNDO_FITEXTEND'
  4  group by segment_name;

SEGMENT_NAME                   SUM(BLOCKS) SUM(BYTES)/1024
------------------------------ ----------- ---------------
_SYSSMU25$                              15             120
_SYSSMU26$                              15             120
_SYSSMU27$                              15             120
_SYSSMU28$                              15             120
_SYSSMU29$                              15             120
_SYSSMU30$                              15             120
_SYSSMU31$                              15             120
_SYSSMU32$                              15             120
_SYSSMU33$                              15             120
_SYSSMU34$                              15             120

10 rows selected.

SQL> create undo tablespace UNDO_LARGE
  2  datafile '/u02b/oradata/logantst/rbs02e.dbf' size 10M;

Tablespace created.

SQL> select segment_name, sum(blocks), sum(bytes)/1024
  2  from dba_undo_extents
  3  where tablespace_name = 'UNDO_LARGE'
  4  group by segment_name;

SEGMENT_NAME                   SUM(BLOCKS) SUM(BYTES)/1024
------------------------------ ----------- ---------------
_SYSSMU35$                              15             120
_SYSSMU36$                              15             120
_SYSSMU37$                              15             120
_SYSSMU38$                              15             120
_SYSSMU39$                              15             120
_SYSSMU40$                              15             120
_SYSSMU41$                              15             120
_SYSSMU42$                              15             120
_SYSSMU43$                              15             120
_SYSSMU44$                              15             120

10 rows selected.

RUN TIME: Transactions and UNDO

In the last section we see how at max 10 UNDO segments are allocated when an UNDO tablespace is created for the first time.

UNDO extents can have status ACTIVE, UNEXPIRED and EXPIRED.

The question is how many UNDO extents a transaction (for example from Table-1) will need. The answer is, it will look for 64K more than needed. For example (Table-1) which needs 59 blocks (that is 8 extents 8*8=64 blocks), will alocate 9 extents but will use 8. See Table-3.

Now we move on to the scenario when transactions hit the database. Each transaction will be allocated one UNDO segment. The transaction will look for extents in the UNDO segment to place UNDO data. It will pick up segments as follows -

(1) Pick up an UNDO segment, which has no ACTIVE extent, if none allocate a new segment. If space does not permit new segment creation, return ERROR.
(2) If the UNDO Segment picked up has got autoextend on
(2) Depending on UNDO requirement, try to extend the UNDO segment to create new extents and use them. If it does not have enough space,
(3) Look for EXPIRED extents (which are over and above the initial extents) in other segments attach them to current segment, if none,
(4) Use the UNEXPIRED extents (which are over and above the initial extents) from other segments. Transaction cannot reuse UNEXPIRED extents in its own segment even if it belongs to other transactions.

An UNDO segment is picked in arbitrary way. Oracle does not precalculate the amount of UNDO. So a very large transaction may not reuse a very large UNDO Segment. It will arbitrarily pick existing small UNDO segment, try to extend it if datafile permits. If datafile doe snot permit then it will truncate the other big segment and then reuse extents from them. So it is advisable to have AUTEXTEND OFF or have a suitable MAXSIZE in UNDO datafiles. In Table-3, segment-34 was not reused and segment-27 was extended.

Table-3: Existing Large UNDO Segments may not be reused

SQL> alter system set undo_tablespace=UNDO_FITEXTEND;

System altered.

SQL> alter system set undo_retention=0;

System altered.

SQL> update temp1 set owner = 'stage1';

500 rows updated.

SQL> commit;

Commit complete.

SQL> select segment_name, sum(blocks), sum(bytes)/1024
  2  from dba_undo_extents
  3  where tablespace_name = 'UNDO_FITEXTEND'
  4  group by segment_name;

SEGMENT_NAME                   SUM(BLOCKS) SUM(BYTES)/1024
------------------------------ ----------- ---------------
.........
_SYSSMU34$                              71             568

10 rows selected.

SQL> update temp1 set owner='stage1';

5000 rows updated.

SQL> commit;

Commit complete.

SQL> select segment_name, sum(blocks), sum(bytes)/1024
  2  from dba_undo_extents
  3  where tablespace_name = 'UNDO_FITEXTEND'
  4  group by segment_name;

SEGMENT_NAME                   SUM(BLOCKS) SUM(BYTES)/1024
------------------------------ ----------- ---------------
.....
_SYSSMU27$                              71             568
.....
_SYSSMU34$                              71             568

10 rows selected.

From point(1) above we can say that the number of UNDO segments in a database will give the ceiling number of concurrent transaction load.

REUSE TIME: Rules on reuse of existing UNDO Segements in 9i

UNDO_RETENTION does not guarantee the retention if there is no space (also it does not make sense to throw infinite space to have this work). Existing segements may be truncated by a long running transaction and FLASHBACK if required will fail. This is a common observation.

However the case illustrated in Table-4 brings out something else. The 'UNABLE to extend UNDO' error does not always mean that there is no space in the UNDO tablespace. So every time there is an error like this, it does not make sense to throw more space. The transaction which throws the error, ignores the fact that there is an EXPIRED segment. It picks at random a segment which has got some UNEXPIRED UNDO, cannot extend any more and returns error. This is a common issue when a small transaction picks up the unexpired undo segment of a large transaction which has finished very recently. There is no place to extend, and no space to claim from the other segments (they are already to the minimum).

Let us setup a suitable UNDO which has 2 segments.
It will have (64x2)x2 + 64K header = 320K

Table-4: Transactions cannot evaluate if a segment has enough space for its success

SQL> create table temp2 as select * from temp1 where rownum < 501;

Table created.

SQL> create undo tablespace UNDO_TWO
  2  datafile '/u02b/oradata/logantst/rbs02f.dbf' size 320K reuse;

SQL> alter system set undo_tablespace=UNDO_TWO;
SQL> alter system set undo_retention=180;

Point(a).... wait for 180s ........

SQL> update temp2 set owner='stage1';

500 rows updated.

SQL> commit;

.. donot wait for expiry, that is 180s

SQL> update temp2 set owner='stage1';
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO_TWO'

Point(b).... commit and repeat from point(a) if you donot get the above error..

Once the error is observerd run the following query -

SQL> select segment_name, blocks, bytes/1024, status
  2  from dba_undo_extents
  3  where tablespace_name = 'UNDO_TWO';

SEGMENT_NAME                       BLOCKS BYTES/1024 STATUS
------------------------------ ---------- ---------- ---------
_SYSSMU46$                              7         56 EXPIRED
_SYSSMU46$                              8         64 EXPIRED
_SYSSMU45$                              7         56 UNEXPIRED
_SYSSMU45$                              8         64 UNEXPIRED

The bottomline is transactions cannot reclaim UNEXPIRED extents in its own segment. But they can claim UNEXPIRED or EXPIRED extents in other segments which are over and above 128K, if there is a space constraint.

Creation of UNDO Tablespace with GUARANTEE in Oracle 10g

RETENTION GUARANTEE specifies that Oracle Database should preserve unexpired undo data in all undo segments of tablespace even if doing so forces the failure of ongoing operations that need undo space in those segments. This setting is useful if you need to issue an Oracle Flashback Query or an Oracle Flashback Transaction Query to diagnose and correct a problem with the data.

CREATE UNDO TABLESPACE ... DATAFILE .. RETENTION GUARANTEE;

Resizing the UNDO datafile

The UNDO datafile does not release the EXPIRED extents. It cannot be resized. The only way to shrink an UNDO datafile is to create a new UNDO tablespace. Change the default UNDO_TABLESPACE of the database by ALTER SYSTEM command and drop the inactive one.

Conclusion

UNDO_RETENTION did not ensure retention in Oracle 9i. This is overcome by the GUARANTEE clause in Oracle 10g. However transactions might fail in a guaranteed UNDO tablespace when it cannot ensure retention. However this is a welcome hazard as it ensures consistency of the FLASHBACKs. DBAs can now say for certain that they have the capability of FLASHBACK to UNDO_RETENTION.