Toad World

How does Oracle reuse expired and unexpired undo extents?

Avatar photo
Post author:Written By

A frequent question that I have received from many DBAs is: my undo tablespace is almost full, how can I be sure that the space will be reused?

Well, First you have to know that there are two kind of undo management: Auto and Manual.

Oracle recommends to use Auto Undo Managements so that's where I will focus. 
When you are using Auto Undo Management Oracle Manages all the Undo Segments and its extents. Oracle uses some algorithms in order to allocate space when a transaction is asking for it. Coming back to the question, will the space be reused? I could say that Yes. Most of the times the undo space (Expired and if it is needed Unexpired extents) is reused, the only exception is when Retention Guarantee is enabled, but this is not enabled by default. Also you have to be sure that Oracle will never reuse Active Extents from a transaction.

You can see if the undo tablespace has retention Guarantee enabled with the following query:

select retention from dba_tablespaces where tablespace_name='<tbs_name>';

RETENTION
———–
NOGUARANTEE

So that's the only Exception when the UNEXPIRED Extents will not be reused, instead of this, if there no any other way to get free space, the operation will fail. When you have retention guarantee disabled, the following algorithms are in place.

When we execute an operations that needs to allocate undo space:

  1. Allocate an extent in an undo segment which has no active transaction. Why in other segment? Because Oracle tries to distribute transactions over all undo segments.
  2. If no undo segment was found then oracle tries to online an off-line undo segment and use it to assign the new extent..
  3. If no undo segments was possible to online, then Oracle creates a new undo segment and use it.
  4. If the free space doesn't permit creation of undo segment, then Oracle tries to reuse an expired extent from the current undo segments.
  5. If failed, Oracle tries to reuse an expired extent from another undo segment.
  6. If failed, Oracle tries to autoextend a datafile (if autoextensible=yes)
  7. If failed, Oracle tries to reuse an unexpired extent from the current undo segment.
  8. If failed, Oracle tries to reuse an unexpired extent from another undo segment.
  9. If failed, then the operation will fail.

For a running transaction associated with undo segment/ extent and it needs more undo space:

  1. If the current extent has more free blocks then use the next free block that is all ready allocated to the extent.
  2. If the current extent does not have free blocks and if the next extent of the segment has expired then wrap in the the next extent and return the first block.
  3. If the next extent has not expired then get space from the UNDO tablespace. If a free extent is available then allocate it to the undo segment.
  4. If there is no free extent available then reuse from an offline undo segment. Deallocate the extent from the offline undo segment and add it to the current undo segment.
  5. Steal from online undo segment. Deallocate the extent from the online undo segment and add it to the current undo segment.

Note: The only different with retention guarantee is enabled is that Unexpired Extents will not be reused. 

In the following example, I will show you easily and quickly the behaviour of the first algorithm when we have an undo tablespace with autoextend=on and also with autoextend=off.

Example 1: Autoextend off

For this example I am using an undo tablespace of 5MB. The following datafile is the only one in the tablespace:

SQL> select tablespace_name, file_name,autoextensible, bytes/1024/1024 MB from dba_data_files where tablespace_name='UNDOTBS1';

TABLESPACE_NAME FILE_NAME                                  AUT MB
————— —————————————— — ———-
UNDOTBS1        +DATA/orcl/datafile/undotbs1.264.882411811 NO  5

I was monitoring the Undo extents while I was performing some updates in other session, the idea is to see how the expired and unexpired extents will be reused.

1* select status, count(*) Num_Extents, sum(blocks) Num_Blocks, round((sum(bytes)/1024/1024),2) MB from dba_undo_extents group by status order by status

STATUS    NUM_EXTENTS 
——— ———– 
EXPIRED    7 
UNEXPIRED  41

No Active extents are created.

STATUS    NUM_EXTENTS 
——— ———– 
ACTIVE    9 <–There were free extents to use 
EXPIRED   6 <–1 extent was reused
UNEXPIRED 40 <–1 extent already expired

STATUS     NUM_EXTENTS 
——— ———– 
ACTIVE    11 <–There were free extents to use 
EXPIRED   6 
UNEXPIRED 40

STATUS    NUM_EXTENTS 
——— ———– 
ACTIVE    13 <–There were free extents to use
EXPIRED   6 
UNEXPIRED 40

STATUS    NUM_EXTENTS
——— ———– 
ACTIVE    15 <–There were free extents to use
EXPIRED   6 
UNEXPIRED 40

STATUS    NUM_EXTENTS 
——— ———– 
ACTIVE    17 <–There were free extents to use
EXPIRED   6 
UNEXPIRED 40

STATUS    NUM_EXTENTS 
——— ———– 
ACTIVE    19 
EXPIRED   6 
UNEXPIRED 31 <–unexpired extents were reused

Why Expired extents were not reused? Well, I am performing updates in batches of 10,000 so perhaps the extents that already exists are too small to allocate the undo data that my update generated. Remember that the Undo tablespace extents are autoallocated, so the first extents are small, (64KB at the beginning).

STATUS     NUM_EXTENTS 
——— ———– 
ACTIVE     29 
EXPIRED    6 
UNEXPIRED  29 <–unexpired extents were reused

STATUS    NUM_EXTENTS 
——— ———– 
ACTIVE    31 
EXPIRED   6 
UNEXPIRED 27 <–unexpired extents were reused

STATUS     NUM_EXTENTS
--------- ----------- 
ACTIVE    33 
EXPIRED   6 
UNEXPIRED 25 <--unexpired extents were reused

STATUS     NUM_EXTENTS 
——— ———– 
ACTIVE    35 
EXPIRED   6 
UNEXPIRED 23 <–unexpired extents were reused

STATUS    NUM_EXTENTS
——— ———– 
ACTIVE    37 
EXPIRED   6 
UNEXPIRED 21 <–unexpired extents were reused

STATUS    NUM_EXTENTS 
——— ———–
ACTIVE    39 
EXPIRED   6 
UNEXPIRED 19

STATUS    NUM_EXTENTS 
——— ———– 
ACTIVE    41 
EXPIRED   6 
UNEXPIRED 17 <–unexpired extents were reused

STATUS    NUM_EXTENTS 
——— ———–
ACTIVE    43 
EXPIRED   6 
UNEXPIRED 15 <–unexpired extents were reused

STATUS    NUM_EXTENTS
——— ———– 
ACTIVE    45 
EXPIRED   6 
UNEXPIRED 13 <–unexpired extents were reused

STATUS    NUM_EXTENTS 
——— ———– 
ACTIVE    46 
EXPIRED   6 
UNEXPIRED 12 <–unexpired extents were reused

At this time I got the following error:

ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

This is because both Expired and Unexpired extents were not big enough to allocate my undo data and since the Tablespace has autoextend disabled Oracle raised an error saying that no more free space was found. As you can see Oracle did not resize any datafile:

SQL> select tablespace_name, file_name,autoextensible, bytes/1024/1024 MB from dba_data_files where tablespace_name='UNDOTBS1';

TABLESPACE_NAME FILE_NAME                                   AUT MB
————— —————————————–  —  ———-
UNDOTBS1        +DATA/orcl/datafile/undotbs1.264.882411811 YES  5

Example 2: Autoextend=on

SQL> alter database datafile '+DATA/orcl/datafile/undotbs1.264.882411811' autoextend on next 1M maxsize 1G;

Database altered.

SQL> select tablespace_name, file_name,autoextensible, bytes/1024/1024 MB from dba_data_files where tablespace_name='UNDOTBS1';

TABLESPACE_NAME FILE_NAME                                  AUT  MB
————— —————————————— —- ——
UNDOTBS1        +DATA/orcl/datafile/undotbs1.264.882411811 YES  7

1* select status, count(*) Num_Extents, sum(blocks) Num_Blocks, round((sum(bytes)/1024/1024),2) MB from dba_undo_extents group by status order by status

STATUS    NUM_EXTENTS 
——— ———– 
EXPIRED   54 
UNEXPIRED 29

No Active Extents are created.

SQL> r
1* select status, count(*) Num_Extents, sum(blocks) Num_Blocks, round((sum(bytes)/1024/1024),2) MB from dba_undo_extents group by status order by status

STATUS     NUM_EXTENTS
——— ———– 
ACTIVE     3 
EXPIRED    52 <–Expired extents were reused 
UNEXPIRED  28

STATUS     NUM_EXTENTS 
——— ———– 
ACTIVE     7 
EXPIRED    48 <–Expired extents were reused 
UNEXPIRED  28

STATUS     NUM_EXTENTS
——— ———– 
ACTIVE    11 
EXPIRED   38 <–Expired extents were reused 
UNEXPIRED 28

STATUS     NUM_EXTENTS 
——— ———– 
ACTIVE    19 
EXPIRED   34 <–Expired extents were reused 
UNEXPIRED 28

STATUS    NUM_EXTENTS 
——— ———– 
ACTIVE    23 
EXPIRED   30 <–Expired extents were reused 
UNEXPIRED 28

STATUS    NUM_EXTENTS 
——— ———– 
ACTIVE    27 
EXPIRED   20 <–Expired extents were reused 
UNEXPIRED 28

STATUS    NUM_EXTENTS 
——— ———– 
ACTIVE    36 
EXPIRED   10 <–Expired extents were reused 
UNEXPIRED 28

STATUS    NUM_EXTENTS 
——— ———–
ACTIVE    40 
EXPIRED   11 <–Expired extents were reused 
UNEXPIRED 28

STATUS    NUM_EXTENTS 
——— ———– 
ACTIVE    45 
EXPIRED   9 <–Expired extents were reused 
UNEXPIRED 28

Checking the datafile size:

SQL> r
1* select tablespace_name, file_name,autoextensible, bytes/1024/1024 MB from dba_data_files where tablespace_name='UNDOTBS1'

TABLESPACE_NAME FILE_NAME                                  AUT MB
————— —————————————— — ———-
UNDOTBS1        +DATA/orcl/datafile/undotbs1.264.882411811 YES 6

OK, So far the datafile was resized 1 time only, adding 1 MB more.

SQL> r
1* select status, count(*) Num_Extents, sum(blocks) Num_Blocks, round((sum(bytes)/1024/1024),2) MB from dba_undo_extents group by status order by status

STATUS    NUM_EXTENTS
——— ———– 
ACTIVE    48 <–Datafile was autoextended and more free extents are available 
EXPIRED   8 
UNEXPIRED 28

STATUS    NUM_EXTENTS 
——— ———– 
ACTIVE    48 <–Datafile was autoextended and more free extents are available 


EXPIRED   8 
UNEXPIRED 28

STATUS    NUM_EXTENTS
——— ———– 
ACTIVE    48 <–Datafile was autoextended and more free extents are available 
EXPIRED   8 
UNEXPIRED 28

STATUS    NUM_EXTENTS 
——— ———– 
ACTIVE    48 <–Datafile was autoextended and more free extents are available 
EXPIRED   8 
UNEXPIRED 28

The tablespace will keep increasing its size until all the datafiles are full.

Look at the new datafile size:

SQL> select tablespace_name, file_name,autoextensible, bytes/1024/1024 MB from dba_data_files where tablespace_name='UNDOTBS1';

TABLESPACE_NAME FILE_NAME                                  AUT MB
————— —————————————— — ———-
UNDOTBS1        +DATA/orcl/datafile/undotbs1.264.882411811 YES 11

In order to know how many times the undo tablespace was asking for more free space, how many blocks were reused and so on, you can query the view v$undostat. This view shows statistics every 10 minutes:

SQL> select begin_time, 
UNXPSTEALCNT "#UnexpiredBlksTaken", 
EXPSTEALCNT "#ExpiredBlksTaken",
NOSPACEERRCNT "SpaceRequests"
from v$undostat order by begin_time; 2 3 4 5

BEGIN_TIME       #UnexpiredBlksTaken #ExpiredBlksTaken SpaceRequests
—————- ——————- —————– ————-
15-06-2015 02:24 6                   2                 6
15-06-2015 02:34 1                   0                 1
15-06-2015 02:44 11                  12                2
15-06-2015 02:54 76                  42                38
15-06-2015 03:04 71                  71                1
15-06-2015 03:14 0                   4                 0
15-06-2015 03:24 0                   10                0
15-06-2015 03:34 0                   0                 0
15-06-2015 03:44 0                   0                 0

9 rows selected.

Why we are seeing here a lot of Unexpired and Expired blocks reused before "15-06-2015 03:14"? This was because I was executing Updates which generates Undo data and the undo tablespace had autoextend disabled, so it started to reuse first the Expired Blocks and then it Unexpired blocks.

Why between "15-06-2015 03:14" and "15-06-2015 03:24" there were no Unexpired blocks reused but only Expired blocks? This was because at that time the undo tablespace had autoextend enabled, BUT (remember this) Oracle will not autoextend the tablespace immediately when it needs more free space, first Oracle look at the Expired Blocks and only when there is no any other Expired blocks as free then the tablespace is autoextended (The expired extents are reused at the end, until all the datafiles are full and they can not being autoextended more).

Why after "15-06-2015 03:34" there was no Expired and Unexpired blocks reused? This was because The All the Expired blocks that could be reused was already reused and the tablespace was able to be autoextended so the tablespace just kept getting larger.

References:
Troubleshooting ORA-30036 – Unable To Extend Undo Tablespace (Doc ID 460481.1)
Explaining ORA-1555 Error (Doc ID 467872.1)

 

Tags:

About the Author

Deiby Gomez

Deiby Gómez is the first Oracle ACE Director of Guatemala. He has the highest technical certification in the world: "Oracle Certified Master 11g", "Oracle Certified Master 12c" and "Maximum Availability Architecture Oracle Certified Master 12c", he is the first person ever in Central America with all these certifications. Deiby likes to work with complex scenarios, huge and highly available critical databases where a deep knowledge of Oracle is needed. Deiby also has strong knowledge on Oracle Fusion Middleware and Oracle Cloud (PaaS & IaaS). Deiby was the winner of "IOUG SELECT Journal Editor’s Choice Award 2016" in Las Vegas, USA. He is a frequent speaker in Oracle Events around the World like OTN LAD Tour '13, '14, '15, '16, '17 (Colombia, Guatemala, El Salvador, Ecuador, Uruguay, Argentina, Brazil, Perú, Mexico, Costa Rica); Collaborate in Las Vegas, USA and Oracle Open World '15, '16, '17 (Brazil and USA). He was the first Guatemalan accepted as Beta Tester (12cR2) in San Francisco in 2015. Several articles have been published by him in English, Spanish and Portuguese in Oracle’s website, Toad World, and his own blog. Deiby appeared in the Official "Oracle Magazine" in Nov/Dec 2014 Edition as an outstanding expert. Deiby is the Technical Reviewer of the book “Oracle Database 12cR2 Multitenant - Oracle Press” and he is co-author of the book “Oracle Database 12cR2 Testing Tools and Techniques for Performance and Scalability - Oracle Press”. He loves to share his knowledge, to help people, to solve problems, to make friends and to play Chess.

Start the discussion at forums.toadworld.com