Undo Segments – What is Wrong with this Quote?

21062010

June 21, 2010

I located another interesting section of a paragraph in the June 2010 printing of the “Oracle Performance Firefighting” book. From page 231:

“By default, Oracle tries to assign only one active transaction per undo segment. If each undo segment has an active transaction and if there is space available in the undo tablespace, Oracle will automatically create an additional undo segment. This usually takes care of the buffer busy waits. However, if there is no more room in the undo segment tablespace, multiple transactions will be assigned to each undo segment, and eventually undo segment header contention will result. The solution is to add another database file to your undo segment tablespace, thus enabling Oracle to create additional undo segments. It’s that simple.”

What, if anything, is wrong with the above quote from the book (it is possible that nothing is wrong)? Keep in mind that these blog articles are intended to generate discussion – be sure to check any and all comments attached to the articles in this series. The comment section is where the heart of the blog article material in this series will be found.

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true. It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past). If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.

Actions

Information

12 responses

This post may be stating the obvious but I thought it was valuable to at least mention the items below.

“Oracle tries to assign only one active transaction per undo segment.”

I can’t find any references in the 11.2 documentation that state this as fact. The 11.2 documentation states the following:

“Multiple active transactions can write concurrently to the same undo segment or to different segments.”

So it would seem Oracle has no problem allowing multiple transactions to work within a single undo segment. I suppose the above quote could be true and maybe empirical evidence has shown that Oracle will only try and place a single transaction in an undo segment. Was any presented in the book?

“However, if there is no more room in the undo segment tablespace, multiple transactions will be assigned to each undo segment, and eventually undo segment header contention will result.”

Was there any indication of why head contention would result from multiple transactions being assigned to each undo segment? I’m just curious. Was this a Freelist tablespace or ASSM? Could contention result from trying to write to the transaction table within the segment itself?

Why would this solve the problem? A segment can span multiple data files and I can’t seem to find anything in the documentation that says an undo segment can only write to one data file. Maybe this was another observation that is not officially documented? I would suspect that it is definitely possible for Oracle to allocate new extents from this newly added data file to the same undo segment.

When I read the above quote from the book I searched the documentation and found the web page that you included in your comment. Here is the section in the documentation that caused me to pause, and pose the question of whether or not the book’s paragraph is correct:

At any given time, a transaction writes sequentially to only one extent in an undo segment, known as the current extent for the transaction. Multiple active transactions can write simultaneously to the same current extent or to different current extents. Figure 12-20 shows transactions T1 and T2 writing simultaneously to extent E3. Within an undo extent, a data block contains data for only one transaction.

As the current undo extent fills, the first transaction needing space checks the availability of the next allocated extent in the ring. If the next extent does not contain data from an active transaction, then this extent becomes the current extent. Now all transactions that need space can write to the new current extent. In Figure 12-21, when E4 is full, T1 and T2 continue writing to E1, overwriting the nonactive undo data in E1.

If the next extent does contain data from an active transaction, then the database must allocate a new extent. Figure 12-22 shows a scenario in which T1 and T2 are writing to E4. When E4 fills up, the transactions cannot continue writing to E1 because E1 contains active undo entries. Therefore, the database allocates a new extent (E5) for this undo segment. The transactions continue writing to E5.

The above quote from the documentation seems to describe a different sequence of events than what appears in the book.

Should I not be seeing buffer busy waits for the undo header – with Automatic Undo Management enabled, there are more waits for the undo header blocks than for data blocks. Of course the time is insignificant for the undo header block buffer busy waits.

Thanks for providing the information. I extended your test a little further by creating the smallest undo tablespace I could (81K) to see if Oracle would use the same segment for different transactions. Here are the results:

(To Anyone:) Please correct me if I am wrong, but I believe that undo segment 0 is located in the SYSTEM tablespace. As long as Centinul did not create the table T1 in the SYSTEM tablespace, I think that he just demonstrated that question number 10 in the my other blog article is false, and possibly also demonstrated that question number 11 is false.

I wonder if you need to bounce the database before it will create the additional undo segments in UNDOTBS02?

———
Edit (5 minutes after the comment post):
To verify that undo segment 0 is located in the SYSTEM tablespace:

SELECT
NAME
FROM
V$ROLLNAME
WHERE
USN=0;
NAME
------
SYSTEM

SELECT
NAME
FROM
V$ROLLNAME
WHERE
USN=1;
NAME
---------
_SYSSMU1$

– above is from 10.2.0.4, on 11.1.0.7 –

SELECT
NAME
FROM
V$ROLLNAME
WHERE
USN=1;
NAME
--------------------
_SYSSMU1_1236281137$

This does seem to confirm your assertion that the undo segment is in the SYSTEM tablespace.

If I have a sufficiently sized undo tablespace I can create the table perfectly fine in another tablespace (ex. USERS). It seems to occur only when there isn’t enough space in the tablespace specified by the UNDO_TABLESPACE parameter. I tried creating T1 first with a sufficiently sized undo tablespace and then changed the UNDO_TABLESPACE parameter to the small undo tablespace I created, but when attempting to insert on T1 I received the same error as above.

The “buffer busy waits” the author has in mind are probably waits for undo segment header – which would occur if two processes were trying to acquire, free, or update a transaction table entry at the same time. However, they can probably occur in circumstances where just one process is attempting to modify the block – for example, it’s possible (and I haven’t proved this) that a reader process will pin the block exclusive to clone it for reasons relating to transaction table read consistency.

To a very large extent the comments in the paragraph are correct – but (a) you’d probably have to have several transactions active per undo segment before you saw any real threat from buffer busy waits on the segment header block, there would probably be more important problems elsewhere; and (b) you could always increase the size of the file rather than adding a new data file.

Very helpful comments – thank you. You are correct that the buffer busy waits discussed in this particular paragraph are for the undo segment header blocks (I should have mentioned that). Some of your comments tie directly into those made by Alistair Wall and Centinul.

I wonder if a very fast, continuous commit rate in a single session, for instance more than 100 commits per second, could contribute to the number of buffer busy waits for the undo header blocks. It would seem that the session might need to inspect the various undo header blocks to find an undo segment without an active transaction, and this inspection might lead to buffer busy waits if the session could not quickly access the current version of the undo segment header (this is not a statement, just speculation).

I’m not sure that a fast commit rate would necessarily affect the number of buffer busy waits. Your session has to latch the block then pin the block to modify it to start a transaction. Your session only has to latch a block to check if it has an active transaction, and if your session decides the block has no active transaction it’s possible that there is something in the “pin it exclusive / change it” code path that says – if the block is already pinned (or maybe pinned exclusive) then drop the latch and move on to the next undo header.

The upshot of this, and this is also just speculation: if you have an active transaction in an undo segment header I won’t try to pin the block (which means I won’t force your subsequent attempt to “commit” to wait), if you have pinned a block that appears to have no active transactions I won’t try to pin it because you might be about to add an active transaction to it. So we (almost) never see active BBW on undo headers unless we keep cycling through all of them and then waiting for exclusive pins as we come round a second time.

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:

<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: