Re: Sizing Rollbacks segments

From: <mark.powell_at_eds.com>
Date: Fri, 12 Feb 1999 18:51:55 GMT
Message-ID: <7a1t86$kms$1@nnrp1.dejanews.com>

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 - 12:51:55 CST