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 (0×51) in the block, and as you can see from the table of records in the block header, it starts at byte offset 0×6d0 and the record stacked above it (number 0×52) starts at byte offset 0×690 – 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 0×02c000a
- the row identifier within block: tabn: 0 slot: 1(0×01)
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.
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 UTC Nov 9,2006 |
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 appear as if it comes before the start_ record).
Anway, 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 UTC Nov 9,2006 |