Skip to Main Content
  • Questions
  • UNDOTBS shrink automatically if used percentage reach to 100%?

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, lawrence.

Asked: February 20, 2008 - 5:18 pm UTC

Last updated: March 12, 2008 - 5:45 pm UTC

Version: 10.2.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,

Thank you for the wonderful website, I have learned a lot from the website.

first of all, environment:
Database: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64 bit
OS: Windows 2003 Server Enterprise x64 Edition

sql>show sga

Total System Global Area 1258291200 bytes
Fixed Size 2054616 bytes
Variable Size 218105384 bytes
Database Buffers 1023410176 bytes
Redo Buffers 14721024 bytes

Tablespace: UNDOTBS
Extent Management: LOCAL
Segment Managment: MANUAL

Just few days ago, the UNDOTBS tablespace is about 99.8% used (UNDOTBS size is 16G in one file with autoextend 'NO', database size is 450G). The database was running huge work. I tried to add one datafile to the UNDOTBS. My colleague told me that we don't need to do that because Oracle will automatically handle it, if add more space to it, the UNDOTBS will continue to grow,so I did not add a file to it. Maybe one hour or maybe two hours later, the UNDBTBS tablespace shrink to about 14G, is less than 76% used.

I am wondering how Oracle handle UNDOTBS tablespace size if 100% used.

Thank you very much.


and Tom said...

The undo tablespace is a series of circular queues, a set of undo segments (made up of extents)

The undo tablespace will have a set of undo segments in it, each of these is a circular queue, the extents point to each other, the last extent in the segment points to the first extent. The size of this queue is controlled by you indirectly via your setting of undo_retention.

So, let's say you set undo_retention to 1 hour. We will ATTEMPT to hold undo in the undo segments for at least one hour.

So, as you are aware, as we are processing transactions we write UNDO to these segments.

Now, as you generate undo, we get to the end of an extent and want to advance into the next extent (the last extent remember considers the FIRST extent to be its NEXT extent - the circular queue concept).

Before we advance into the next extent - it must not contain any active transactions - if it does, we cannot advance and would simply add another new extent into this queue. We would get that extent from a) free space in the undo tablespace OR b) by extending the datafiles for the undo tablespace to get more free space (not in your case, you disabled that) OR c) by stealing an extent from some other undo segment - finding its oldest unused extent and stealing it. If all of that failed, you would receive an error and the statement you were executing would be rolled back.


Now, let's say the next extent is not active - before we advance into it, we'll check the age of the data in it. If the age of the data is more than 1 hour (our retention period) - we'll advance into it. If the age of the data is less than one hour we will try

a) add extent from free space so as to not overwrite the data
b) add extent from free space after autoextending a file
c) to steal an extent from another undo segment that is not active and has data over an hour old.


If none of that works, we'll have to advance into that extent and prematurely "expire" this undo (eg: violate your undo retention request). We will not fail (unless you set the undo retention guarantee)
http://docs.oracle.com/cd/B28359_01/server.111/b28310/undo002.htm#ADMIN11463


You can monitor all of that in v$undostat


Now, after a while, some extents in your undo tablespace might become older than one hour hold (data is no longer needed), we can and do release these extents over time as we online and offline undo segments and such. That is the "shrinking" you saw - we just freed up data we didn't need anymore, putting it back into the free space so that when we need to grow again - it is there.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

excellent answer, need to confirm growing reason

lawrence lau, February 21, 2008 - 1:02 pm UTC

Thanks Tom,

This makes UNDOTBS mechanism clear. I'd like you to confirm the reason of UNDOTBS growing.

From the link you mentioned:
<quote>
After a transaction is committed, undo data is no longer needed for rollback or transaction recovery purposes. However, for consistent read purposes, long-running queries may require this old undo information for producing older images of data blocks. Furthermore, the success of several Oracle Flashback features can also depend upon the availability of older undo information. For these reasons, it is desirable to retain the old undo information for as long as possible.
</quote>

Is this the reason why UNDOTBS always grows?

Thanks so much.
Tom Kyte
February 21, 2008 - 5:01 pm UTC

it does not ALWAYS grow, but that is one of two things that will make it grow.

reason 1: undo retention - you asked us to keep it for an hour. You have 1gb of undo allocated. In one hour you generate 2gb of undo, we will grow undo to 2gb if we can in order to respect your undo retention.

reason 2: a transaction does not commit for a long long time. We cannot advance into that extent in the circular buffer. So, we keep adding and adding extents - we might have to grow in order to add another extent instead of returning an error to you.

lots of expired extents?

A reader, March 12, 2008 - 1:30 pm UTC

Our undo recently hit 100% (10g) so I looked into it and found the following. From looking at this query,
select status, count(1)
from dba_undo_extents
group by status;
I got
STATUS COUNT(1)
--------- ----------
ACTIVE 2
EXPIRED 1616
UNEXPIRED 1911

And from running this -

select
substr(lkd.os_user_name,1,8) "OS User",
substr(lkd.oracle_username,1,8) "DB User",
substr(obj.owner,1,8) "Schema",
substr(obj.object_name,1,20) "Object Name",
substr(obj.object_type,1,10) "Type",
substr(rbs.segment_name,1,5) "RBS",
substr(trn.used_urec,1,12) "# of Records"
from
v$locked_object lkd,
dba_objects obj,
dba_rollback_segs rbs,
v$transaction trn,
v$session ses
where
obj.object_id = lkd.object_id
and rbs.segment_id = lkd.xidusn
and trn.xidusn = lkd.xidusn
and trn.xidslot = lkd.xidslot
and trn.xidsqn = lkd.xidsqn -- added for completeness
and ses.taddr = trn.addr
;

I got
OS User DB User Schema Object Name Type RBS # of Records
-------- -------- -------- -------------------- ---------- ----- ------------
xxxxxx SYSADM SYSADM PS_BAT_TIMINGS_LOG TABLE _SYSS 28
xxxxxx SYSADM SYSADM PS_BAT_TIMINGS_DTL TABLE _SYSS 28

What is the situation here? Stuff about to time out?
The tablespace room shows
total mb used mb free perc free
UNDOTBS1 24576 24536 40 0

What accounts for the contents being so full if so much is not active or expired?



Tom Kyte
March 12, 2008 - 5:45 pm UTC

so what if it is "full", that is a normal condition - just means we've allocated it and we'll keep it for a while and maybe release it later.

Not like anything else is going to use it - it is the undo tablespace after all. It is expensive to allocate, so we hang onto it.

it is really quite empty - just allocated, so when and if we need it - we have it.

All looks well here.