Re: Sizing Rollbacks segments

Thank you, Mark!

You actually accepted criticism as the way to deeper understanding! Some of our fellows might have started a vicious flame war. We need more people on this ng who have your open-minded approach.

PS. If you find a difference on older releases, let us know.

mark.powell_at_eds.com wrote:

> Jonathan Lewis took issue with statement that Oracle copies entire block to
> rollback on the first change to the block and vector information for all
> additional changes to the block by a transaction. I looked for the source of
> this belief, but I could not locate it so I thought about it and came up with
> a way to prove Jonathan right.
>
> The theory is this. Build a table with 3 data blocks. Build a rollback
> segment with only 2 blocks. I believe that Oracle will use the first as a
> header block so this leaves one block to hold rollback data. If Oracle
> copies the entire block then changing one approx 45 bytes row per block in
> one transaction should force the rollback segment to extend. It did not so
> it does appear that only vector information is copied. The test was ran on
> ver. 7.3.3.5 I will try to run it on an older system and will post if the
> results are different.
>
> spool output follows
> TRAIN> column segment_name format a30
>
> TRAIN> create table mark_test( fld1 number, fld2 varchar2(40) )
> 2 tablespace usr
> 3 pctfree 10
> 4 pctused 70
> 5 storage (initial 8k next 4k pctincrease 0)
> 6 /
>
> Table created.
>
> TRAIN> insert into mark_test
> 2 values (11111,'abcdefghijklmnopqrstuvwxyz0123456789!@#$')
> 3 /
>
> 1 row created.
>
> TRAIN> insert into mark_test
> 2 values (11112,'abcdefghijklmnopqrstuvwxyz0123456789!@#$')
> 3 /
>
> 1 row created.
>
> .... removed rest of inserts for brevity
>
> TRAIN> insert into mark_test
> 2 values (99999,'abcdefghijklmnopqrstuvwxyz0123456789!@#$')
> 3 /
>
> 1 row created.
>
> TRAIN> commit
> 2 /
>
> Commit complete.
>
> TRAIN> select segment_name, initial_extent, next_extent, bytes, blocks
> 2 from sys.dba_segments
> 3 where segment_name = 'MARK_TEST'
> 4 /
>
> SEGMENT_NAME INITIAL_EXTENT NEXT_EXTENT BYTES BLOCKS
> ------------------------------ -------------- ----------- ----------
> ---------- MARK_TEST 8192 4096 16384 4
>
> TRAIN> create rollback segment roll06
> 2 tablespace rbs
> 3 storage ( initial 4k next 4k minextents 2 )
> 4 /
>
> Rollback segment created.
>
> TRAIN> alter rollback segment roll06 online
> 2 /
>
> Rollback segment altered.
>
> TRAIN> select name, extents, writes, wraps, extends
> 2 from v$rollstat rs, v$rollname rn
> 3 where rs.usn = rn.usn
> 4 and rn.name = 'ROLL06'
> 5 /
>
> NAME EXTENTS WRITES WRAPS EXTENDS
> ------------------------------ ---------- ---------- ---------- ----------
> ROLL06 2 10946 0 0
>
> -- Note I had to run this more than once because I forgot to bring the
> rollback segment online and then ran only the end before deciding I wanted a
> complete run. Oracle remembered the writes from the previous runs.
> Substract the writes from this query from the one at the end to get the size
> of transaction --
>
> TRAIN> select rowid, a.*
> 2 from mark_test a
> 3 where fld1 = 11112
> 4 or fld1 = 44444
> 5 or fld1 = 99999
> 6 /
>
> ROWID FLD1 FLD2 ------------------ ----------
> ---------------------------------------- 00000469.0001.0008 11112
> abcdefghijklmnopqrstuvwxyz0123456789!@#$ 0000046A.0018.0008 44444
> abcdefghijklmnopqrstuvwxyz0123456789!@#$ 0000046B.000D.0008 99999
> abcdefghijklmnopqrstuvwxyz0123456789!@#$ -- I am the only user on the system
> so no one else can interfere with our results TRAIN> select sid, serial#,
> osuser 2 from v$session 3 where username is not null 4 /
>
> SID SERIAL# OSUSER
> ---------- ---------- ---------------
> 8 421 mpowel01
>
> TRAIN> rollback
> 2 /
>
> Rollback complete.
>
> TRAIN> set transaction use rollback segment roll06
> 2 /
>
> Transaction set.
>
> TRAIN> update mark_test
> 2 set fld2 = 'zyxw12345678901234567890123456789'
> 3 where fld1 = 11112
> 4 /
>
> 1 row updated.
>
> TRAIN> update mark_test
> 2 set fld2 = 'zyxw12345678901234567890123456789'
> 3 where fld1 = 44444
> 4 /
>
> 1 row updated.
>
> TRAIN> update mark_test
> 2 set fld2 = 'zyxw12345678901234567890123456789'
> 3 where fld1 = 99999
> 4 /
>
> 1 row updated.
>
> TRAIN> select name, extents, writes, wraps, extends
> 2 from v$rollstat rs, v$rollname rn
> 3 where rs.usn = rn.usn
> 4 and rn.name = 'ROLL06'
> 5 /
>
> NAME EXTENTS WRITES WRAPS EXTENDS
> ------------------------------ ---------- ---------- ---------- ----------
> ROLL06 2 11384 0 0
>
> TRAIN> commit
> 2 /
>
> Commit complete.
>
> TRAIN> alter rollback segment roll06 offline
> 2 /
>
> Rollback segment altered.
>
> TRAIN> drop rollback segment roll06
> 2 /
>
> Rollback segment dropped.
>
> TRAIN> drop table mark_test
> 2 /
>
> Table dropped.
>
> TRAIN> spool off
>
> -- It appears to have taken 438 bytes to hold 3 updates of 45 characters
> worth of data. But a previous run showed a difference of only 162 bytes. I
> am not going to worry about the difference right now. --
>
> Mark D. Powell -- The only advice that counts is the advice that
> you follow so follow your own advice --
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Fri Feb 12 1999 - 19:40:04 CST