Oracle Scratchpad

November 8, 2006

Undo (rollback) volume

Filed under: Infrastructure,Troubleshooting — Jonathan Lewis @ 9:11 pm BST Nov 8,2006

A recent poster on the comp.databases.oracle.server had a question about the amount of undo generated when inserting into a table. In outline the problem was this:

My table size is 350MB, whereas the transaction involving the insert statement has taken 2,500 MB. There are no indexes, only data.

There are plenty of questions you could ask before producing a sensible answer to this question, but on the face of it the results are a little strange.

When Oracle inserts a row (in a table that has no indexes) the basic undo record is just a few dozen bytes – irrespective of the size of the row. Basically, apart from the standard management overheads, and the information about ITL changes, last change SCN, free space and so on, the undo record only has to carry enough information to say something like: “row entry 5 in block 66 for file 19” should not exist.

Here’s an example of such an undo record – and I’ve included a little of the undo block header so that you can see the recorded size of the record:

UNDO BLK: 
xid: 0x0005.011.000000c8  seq: 0x33  cnt: 0x53  irb: 0x53  icl: 0x0   flg: 0x0000    

 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset 
--------------------------------------------------------------------------- 
0x01 0x1fb4     0x02 0x1f64     0x03 0x1f14     0x04 0x1eac     0x05 0x1e78 
   ... 
0x51 0x06d0     0x52 0x0690     0x53 0x0650         

*----------------------------- 
* Rec #0x51  slt: 0x11  objn: 30147(0x000075c3)  objd: 30147  tblspc: 11(0x0000000b) 
*       Layer:  11 (Row)   opc: 1   rci 0x00 
Undo type:  Regular undo    Begin trans    Last buffer split:  No 
Temp Object:  No 
Tablespace Undo:  No 
rdba: 0x00000000 
*----------------------------- 
uba: 0x00809d6e.0033.50 ctl max scn: 0x0000.00029d99 prv tx scn: 0x0000.00029da0 
KDO undo record: 
KTB Redo 
op: 0x03  ver: 0x01 
op: Z 
KDO Op code: DRP row dependencies Disabled 
  xtype: XA  bdba: 0x02c0000a  hdba: 0x02c00009 
itli: 2  ispac: 0  maxfr: 4863 
tabn: 0 slot: 1(0x1)    

*-----------------------------    

The record is number 81 (0x51) in the block, and as you can see from the table of records in the block header, it starts at byte offset 0x6d0 and the record stacked above it (number 0x52) starts at byte offset 0x690 – leaving our undo record with a total length of 64 bytes. 

The critical bits of this  undo are

  • Opcode:  DRP (delete row piece)
  • the block address: bdba 0x02c000a
  • the row identifier within block: tabn: 0  slot: 1(0x01)

So whatever is going on in the original poster’s system, the volume is not just undo for the rows he is inserting into the table.

We could make lots of guesses of course – maybe there are lots of freelist updates, unexpected bits of delayed block cleanout, ASSM bitmap block updates and other associated effects – maybe the 2,500MB is just 40 undo segments that have each just added (but not used) a 64MB extent. Whatever it is, the extreme difference in the table size and the undo size may be hard to explain without taking a close look at what’s really going on.

2 Comments »

  1. Not that I plan on getting myself in trouble.. but how did you get that dump?

    Comment by Doug C — November 9, 2006 @ 4:23 am BST Nov 9,2006 | Reply

  2. Doug,

    To decide on which blocks to dump, you have to look at v$transaction whilst the transaction is active, this tells you about the possible range of blocks to be dumped:

    select
            ses_addr,
            used_ublk, used_urec,
            start_ubafil, start_ubablk, start_ubarec, start_ubasqn,
            ubafil, ubablk, ubarec, ubasqn
    from
            v$transaction
    /
    
    
    SES_ADDR  USED_UBLK  USED_UREC START_UBAFIL START_UBABLK START_UBAREC
    -------- ---------- ---------- ------------ ------------ ------------
    START_UBASQN     UBAFIL     UBABLK     UBAREC     UBASQN
    ------------ ---------- ---------- ---------- ----------
    27B241B4          1          2            2         4022           24
            2123          2       4022         25       2123
    

    For dumping, you only need the file and block, but this query also shows you the record number used, and the sequence number of the block (sequencing, as undo blocks are re-used cyclically). The start_ values show the first record used by the transaction, the other four show the most recently used record.

    Bear in mind that people may be sharing the undo segment, that you cycle round and round the segment as you use it, and that extents get inserted into and deleted from an undo segment as needed. This means that you shouldn’t jump to any conclusions about how much of the space between the start_ and the current record is yours if the transaction uses more than one undo block. (In fact, it’s perfectly feasible for the current record to look as if it comes before the start_ record in terms of absolute address) .

    Anyway, having got some block addresses, you can dump one, or several blocks to your trace file:

    alter system dump datafile 2 block 4022;
    alter system dump datafile 2 block min 4022 block max 4022;
    

    I’ve included the ses_addr column in the query – this joins to the addr column of v$session

    Comment by Jonathan Lewis — November 9, 2006 @ 8:20 am BST Nov 9,2006 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Blog at WordPress.com.