Undo Segments – What is Wrong with this Quote?

21 06 2010

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

21 06 2010
Centinul (06:00:18) :

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

Reply
21 06 2010
Charles Hooper (08:59:11) :

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:

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.

Any other comments?

Reply
21 06 2010
Alistair Wall (08:38:10) :

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.

Reply
21 06 2010
Charles Hooper (15:55:38) :

A brief test to determine the accuracy of the first two sentences using Oracle Database 11.1.0.7:

(Session 1):
CREATE TABLE T1 (
  C1 NUMBER,
  C2 VARCHAR2(10));

SELECT
  XIDUSN SEGMENT,
  XIDSLOT SLOT,
  USED_UBLK,
  USED_UREC 
FROM
  V$TRANSACTION;

no rows selected

INSERT INTO T1 VALUES (1,'1');

SELECT
  XIDUSN SEGMENT,
  XIDSLOT SLOT,
  USED_UBLK,
  USED_UREC 
FROM
  V$TRANSACTION;

   SEGMENT       SLOT  USED_UBLK  USED_UREC
---------- ---------- ---------- ----------
         1         28          1          1

We now have a test table and a single active transaction using undo segment 1.

(Session 2):
INSERT INTO T1 VALUES (2,'2');

SELECT
  XIDUSN SEGMENT,
  XIDSLOT SLOT,
  USED_UBLK,
  USED_UREC 
FROM
  V$TRANSACTION;

   SEGMENT       SLOT  USED_UBLK  USED_UREC
---------- ---------- ---------- ----------
         1         28          1          1
         7         10          1          1

The second session’s transaction is using undo segment 7 rather than undo segment 1.

(Session 3):
INSERT INTO T1 VALUES (3,'3');

SELECT
  XIDUSN SEGMENT,
  XIDSLOT SLOT,
  USED_UBLK,
  USED_UREC 
FROM
  V$TRANSACTION;
 
 SEGMENT       SLOT  USED_UBLK  USED_UREC
-------- ---------- ---------- ----------
       1         28          1          1
       7         10          1          1
      10         14          1          1

(Session 4):
INSERT INTO T1 VALUES (4,'4');

SELECT
  XIDUSN SEGMENT,
  XIDSLOT SLOT,
  USED_UBLK,
  USED_UREC 
FROM
  V$TRANSACTION;

   SEGMENT       SLOT  USED_UBLK  USED_UREC
---------- ---------- ---------- ----------
         4          2          1          1
         1         28          1          1
         7         10          1          1
        10         14          1          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?

SELECT
  USN,
  EXTENTS
FROM
  V$ROLLSTAT
ORDER BY
  USN;

USN    EXTENTS
--- ----------
  0          6
  1          4
  2          3
  3          3
  4          3
  5          5
  6          3
  7          3
  8          3
  9          3
 10          4

Just 10 undo segments (plus the one in the SYSTEM tablespace). So, what happens if we continue?

(Session 5):
INSERT INTO T1 VALUES (5,'5');

SELECT
  XIDUSN SEGMENT,
  XIDSLOT SLOT,
  USED_UBLK,
  USED_UREC 
FROM
  V$TRANSACTION;

SEGMENT       SLOT  USED_UBLK  USED_UREC
------- ---------- ---------- ----------
      4          2          1          1
      1         28          1          1
      7         10          1          1
      8          1          1          1
     10         14          1          1

(Session 6):
INSERT INTO T1 VALUES (6,'6');

SELECT
  XIDUSN SEGMENT,
  XIDSLOT SLOT,
  USED_UBLK,
  USED_UREC 
FROM
  V$TRANSACTION;

SEGMENT       SLOT  USED_UBLK  USED_UREC
------- ---------- ---------- ----------
      4          2          1          1
      1         28          1          1
      5          5          1          1
      7         10          1          1
      8          1          1          1
     10         14          1          1

(Session 7):
INSERT INTO T1 VALUES (7,'7');

SELECT
  XIDUSN SEGMENT,
  XIDSLOT SLOT,
  USED_UBLK,
  USED_UREC 
FROM
  V$TRANSACTION;

SEGMENT       SLOT  USED_UBLK  USED_UREC
------- ---------- ---------- ----------
      4          2          1          1
      1         28          1          1
      5          5          1          1
      7         10          1          1
      8          1          1          1
     10         14          1          1
      3         14          1          1

(Session 8):
INSERT INTO T1 VALUES (8,'8');

SELECT
  XIDUSN SEGMENT,
  XIDSLOT SLOT,
  USED_UBLK,
  USED_UREC 
FROM
  V$TRANSACTION;

SEGMENT       SLOT  USED_UBLK  USED_UREC
------- ---------- ---------- ----------
      4          2          1          1
      1         28          1          1
      5          5          1          1
      7         10          1          1
      8          1          1          1
      6          7          1          1
     10         14          1          1
      3         14          1          1

(Session 9):
INSERT INTO T1 VALUES (9,'9');

SELECT
  XIDUSN SEGMENT,
  XIDSLOT SLOT,
  USED_UBLK,
  USED_UREC 
FROM
  V$TRANSACTION;

SEGMENT       SLOT  USED_UBLK  USED_UREC
------- ---------- ---------- ----------
      9         29          1          1
      4          2          1          1
      1         28          1          1
      5          5          1          1
      7         10          1          1
      8          1          1          1
      6          7          1          1
     10         14          1          1
      3         14          1          1

(Session 10):
INSERT INTO T1 VALUES (10,'10');

SELECT
  XIDUSN SEGMENT,
  XIDSLOT SLOT,
  USED_UBLK,
  USED_UREC 
FROM
  V$TRANSACTION;

SEGMENT       SLOT  USED_UBLK  USED_UREC
------- ---------- ---------- ----------
      2          2          1          1
      9         29          1          1
      4          2          1          1
      1         28          1          1
      5          5          1          1
      7         10          1          1
      8          1          1          1
      6          7          1          1
     10         14          1          1
      3         14          1          1

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:

(Session 11):
INSERT INTO T1 VALUES (11,'11');

SELECT
  XIDUSN SEGMENT,
  XIDSLOT SLOT,
  USED_UBLK,
  USED_UREC 
FROM
  V$TRANSACTION;

SEGMENT       SLOT  USED_UBLK  USED_UREC
------- ---------- ---------- ----------
      2          2          1          1
      9         29          1          1
      4          2          1          1
      1         28          1          1
      5          5          1          1
      7         10          1          1
     11          6          1          1
      8          1          1          1
      6          7          1          1
     10         14          1          1
      3         14          1          1

(Session 12):
INSERT INTO T1 VALUES (12,'12');

SELECT
  XIDUSN SEGMENT,
  XIDSLOT SLOT,
  USED_UBLK,
  USED_UREC 
FROM
  V$TRANSACTION;

SEGMENT       SLOT  USED_UBLK  USED_UREC
------- ---------- ---------- ----------
      2          2          1          1
      9         29          1          1
      4          2          1          1
      1         28          1          1
      5          5          1          1
      7         10          1          1
     11          6          1          1
      8          1          1          1
      6          7          1          1
     12         13          1          1
     10         14          1          1
      3         14          1          1

Just to verify:

SELECT
  USN,
  EXTENTS
FROM
  V$ROLLSTAT
ORDER BY
  USN;

USN    EXTENTS
--- ----------
  0          6
  1          4
  2          3
  3          3
  4          3
  5          5
  6          3
  7          3
  8          3
  9          3
 10          4
 11          2
 12          2
 13          2

The above test seems to confirm that the second sentence is correct. Now, the rest of the paragraph?

Reply
21 06 2010
Charles Hooper (16:28:34) :

Now for sentence number 3: “This usually takes care of the buffer busy waits.” Note that this is output from a different database:

SELECT
  *
FROM
  V$WAITSTAT
ORDER BY
  COUNT DESC,
  TIME,
  CLASS;

CLASS                   COUNT       TIME
------------------ ---------- ----------
undo header                79          0
data block                 18        179
file header block           2          2
1st level bmb               0          0
2nd level bmb               0          0
3rd level bmb               0          0
bitmap block                0          0
bitmap index block          0          0
extent map                  0          0
free list                   0          0
save undo block             0          0
save undo header            0          0
segment header              0          0
sort block                  0          0
system undo block           0          0
system undo header          0          0
undo block                  0          0
unused                      0          0
 
SELECT
  USN,
  EXTENTS
FROM
  V$ROLLSTAT
ORDER BY
  USN;
 
USN    EXTENTS
--- ----------
  0          6
  1         10
  2          4
  3          5
  4          6
  5         10
  6          5
  7          8
  8          5
  9          5
 10          6

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.

Reply
22 06 2010
Centinul (06:21:19) :

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:

SQL> -- Session 1
SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> SHOW PARAMETER UNDO_MANAGEMENT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO

SQL> CREATE UNDO TABLESPACE UNDOTBS02 DATAFILE 'D:\oracle\oradata\pmpprod\undotbs02.dbf' SIZE 81K REUSE AUTOEXTEND OFF;

Tablespace created.

SQL> ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS02;

System altered.

SQL> CREATE TABLE T1 (
  2    C1 NUMBER,
  3    C2 VARCHAR2(10));

Table created.

SQL> SELECT
  2    XIDUSN SEGMENT,
  3    XIDSLOT SLOT,
  4    USED_UBLK,
  5    USED_UREC
  6  FROM
  7    V$TRANSACTION;

no rows selected

SQL> INSERT INTO T1 VALUES (1,'1');

1 row created.

SQL> SELECT
  2    XIDUSN SEGMENT,
  3    XIDSLOT SLOT,
  4    USED_UBLK,
  5    USED_UREC
  6  FROM
  7    V$TRANSACTION;

             SEGMENT                 SLOT            USED_UBLK            USED_UREC
-------------------- -------------------- -------------------- --------------------
                   0                   36                    1                    1

Second session:

SQL> -- Session 2
SQL> INSERT INTO T1 VALUES (2,'2');

1 row created.

SQL> SELECT
  2    XIDUSN SEGMENT,
  3    XIDSLOT SLOT,
  4    USED_UBLK,
  5    USED_UREC
  6  FROM
  7    V$TRANSACTION;

             SEGMENT                 SLOT            USED_UBLK            USED_UREC
-------------------- -------------------- -------------------- --------------------
                   0                   36                    1                    1
                   0                   30                    1                    1

Same segment. Third session:

SQL> -- Session 3
SQL> INSERT INTO T1 VALUES (2,'2');

1 row created.

SQL>
SQL> SELECT
  2    XIDUSN SEGMENT,
  3    XIDSLOT SLOT,
  4    USED_UBLK,
  5    USED_UREC
  6  FROM
  7    V$TRANSACTION;

             SEGMENT                 SLOT            USED_UBLK            USED_UREC
-------------------- -------------------- -------------------- --------------------
                   0                   36                    1                    1
                   0                   85                    1                    1
                   0                   30                    1                    1

Again, same segment. Final stats:

SQL> SELECT
  2    USN,
  3    EXTENTS
  4  FROM
  5    V$ROLLSTAT
  6  ORDER BY
  7    USN;

                 USN              EXTENTS
-------------------- --------------------
                   0                    6

SQL> SELECT
  2    *
  3  FROM
  4    V$WAITSTAT
  5  ORDER BY
  6    COUNT DESC,
  7    TIME,
  8    CLASS;

CLASS                             COUNT                 TIME
------------------ -------------------- --------------------
data block                            4                    4
segment header                        2                    0
undo header                           2                    0
system undo header                    1                    0
1st level bmb                         0                    0
2nd level bmb                         0                    0
3rd level bmb                         0                    0
bitmap block                          0                    0
bitmap index block                    0                    0
extent map                            0                    0
file header block                     0                    0
free list                             0                    0
save undo block                       0                    0
save undo header                      0                    0
sort block                            0                    0
system undo block                     0                    0
undo block                            0                    0
unused                                0                    0

18 rows selected.
Reply
22 06 2010
Charles Hooper (06:59:32) :

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:

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$
Reply
22 06 2010
Centinul (08:13:22) :

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.

SQL> CREATE TABLE T1 (
  2    C1 NUMBER,
  3    C2 VARCHAR2(10)) TABLESPACE USERS;
CREATE TABLE T1 (
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'

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.

Reply
21 06 2010
Jonathan Lewis (16:33:45) :

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.

Reply
21 06 2010
Charles Hooper (20:19:18) :

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

Reply
22 06 2010
Jonathan Lewis (11:38:56) :

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.

Reply
23 06 2010
Charles Hooper (07:40:21) :

I appreciate your discussion on this topic. Very helpful information.

Reply

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. ( Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

w
Cancel

Connecting to %s


%d bloggers like this: