oracleops-support: How does Oracle reuse the Expired and Unexpired undo extents?

How does Oracle reuse the Expired and Unexpired undo extents?

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:

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.
If no undo segment was found then oracle tries to online an off-line undo segment and use it to assign the new extent..
If no undo segments was possible to online, then Oracle creates a new undo segment and use it.
If the free space doesn't permit creation of undo segment, then Oracle tries to reuse an expired extent from the current undo segments.
If failed, Oracle tries to reuse an expired extent from another undo segment.
If failed, Oracle tries to autoextend a datafile (if autoextensible=yes)
If failed, Oracle tries to reuse an unexpired extent from the current undo segment.
If failed, Oracle tries to reuse an unexpired extent from another undo segment.
If failed, then the operation will fail.

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

If the current extent has more free blocks then use the next free block that is all ready allocated to the extent.
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.
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.
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.
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

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 becuase 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)

oracleops-support

Wikipedia

How does Oracle reuse the Expired and Unexpired undo extents?

No comments:

Post a Comment