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.
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?
“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”
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.
Source: http://download.oracle.com/docs/cd/E11882_01/server.112/e10713/logical.htm#CHDGJJEJ
Centinul,
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:
The above quote from the documentation seems to describe a different sequence of events than what appears in the book.
Any other comments?
There are other approaches to consider: checking for unusual workloads, reducing the retention guarantee, extending the existing data file, using different block and extent sizes.
If you don’t have a spare disk (who does?), creating a new datafile will have an impact on other workloads using the same disk, and on backup/restore time.
A brief test to determine the accuracy of the first two sentences using Oracle Database 11.1.0.7:
We now have a test table and a single active transaction using undo segment 1.
The second session’s transaction is using undo segment 7 rather than undo segment 1.
We now have 4 active transactions, each using a different undo extent. Must be that there are *many* undo segments – luck of the draw?
Just 10 undo segments (plus the one in the SYSTEM tablespace). So, what happens if we continue?
Each session is currently in its own undo segment, so for this test the first sentence from the book quote seems to be correct.
Now, let’s check the second sentence:
Just to verify:
The above test seems to confirm that the second sentence is correct. Now, the rest of the paragraph?
Now for sentence number 3: “This usually takes care of the buffer busy waits.” Note that this is output from a different database:
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.
Sentence number 3 might not be correct.
Charles —
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:
Setup and first transaction:
Second session:
Same segment. Third session:
Again, same segment. Final stats:
Centinul,
Thank you for performing the test. It seems that Oracle Database is OK with using a single undo segment.
I think that you just answered question number 10 in this article:
https://hoopercharles.wordpress.com/2010/05/09/true-or-false-undo/
“10. The automatically created undo segments in the SYSTEM tablespace will only be used when modifying objects residing in the SYSTEM tablespace.”
(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:
– above is from 10.2.0.4, on 11.1.0.7 –
Charles —
“As long as Centinul did not create the table T1 in the SYSTEM tablespace”
The table T1 was created in the SYSTEM tablespace (this is a test system 🙂 ). I receive an error when I try and create this table in another tablespace.
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.
Jonathan,
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).
Charles,
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.
I appreciate your discussion on this topic. Very helpful information.