Re: Why is rollback so large ?

>1. Does Oracle write index deletion to RBS as well ? I would imagine so.

Yes, deleting an index entry takes up about 80 bytes of overhead as well as the deleted value as an undo record.

>2. How can the rollback size be 3 times of my deleted segment size
> including indexes ? My deleted rows are all adjacent within a block,
> because I am purging based on insertion timestamp.
>
> I understand Oracle copies the whole block even though there is
> only one byte of change. But I believe when you delete the whole
> block, it still copy that block once instead of multiple times.

Since you have two indexes, Oracle will record three separate undo records for each table row deleted. The fact that each block contains lots of rows to be deleted is irrelevant - the undo records each row separately. Consequently you get roughly:

    80 bytes plus total row length in undo record 1     80 bytes plus index entry length in undo record 2     80 bytes plus index entry length in undo record 3

Add to this that every now and again you will get an undo record for the change to the free list - also 80 bytes plus a bit.

If your average row length is somewhere around 75 bytes, then its very likely that your 74 MB table turns into 320 MB of undo when you delete most of the data.

BTW - Oracle does not copy the whole block into the UNDO when changing a single byte in a block - it copies the original byte, information about where the byte came from - and lots of material linking the undo record into the right place in the current transaction chain. (All of which is discussed in some detail around 11:30 on the first day of my seminar ;)

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now running 3-day intensive seminars
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases


Bass Chorng wrote in message ...


>I deleted 90% of the rows of a table which takes 74 MB of space in

>dba_segments. This table has 2 indexes, each's size is no more than

>30% of the table.

>

>But the rollback size of this transaction is 320 MB. ( select USED_UBLK

>from v$transaction ). This size also correspond with my observation

>of the hwmsize in v$rollstat. I made sure no other transaction could

>use this segment, as it was created fresh and as soon as it is created,

>I set my transaction to it, and then I immediately alter it offline so

>no one else could use it.

>

>I have two questions:

>

>1. Does Oracle write index deletion to RBS as well ? I would imagine so.

>

>2. How can the rollback size be 3 times of my deleted segment size

>   including indexes ? My deleted rows are all adjacent within a block,

>   because I am purging based on insertion timestamp.

>

>   I understand Oracle copies the whole block even though there is

>   only one byte of change. But I believe when you delete the whole

>   block, it still copy that block once instead of multiple times.

>

>A side note is, there was no other sessions accessing that table during

>the deletion, so spliting is not an issue.

>

>Thanks for sharing your insight on this.