This practice will demonstrate the  concept of extent, wrap and shrink in rollback segment utilization. You will:

• Use the create rollback segment and alter rollback segment syntax.
• Examine the V$ROLLSTAT view.
• Determine what would be required to force an extent, a wrap and a shrink.

ASSUMPTIONS

• The directory and filenames referenced in the commands in this practice reference the UNIX operating system.  However, simply changing the directory and filename references to match the operating system you are using will allow all the commands to work properly on your operating system.
• The database version must be Oracle8i release 2, or higher.
• The database blocksize is 2048 bytes.
• The output produced in these instructions is from a UNIX operating system.  There may be some variance in your output data.

INSTRUCTIONS:

1.    Create a rollback segment of initial 10k, next 10k and minextents of 2.

Ensure there is only one user rollback segment online so that all the transactions have to use this newly created rollback segment.

SQL> create rollback segment RBS4 2  storage (initial 10K next 10K minextents 2); Rollback segment created.   SQL> alter rollback segment RBS4 online; Rollback segment altered.   Note:  Put all the other user RBSs offline

 

 

2.    Create two user sessions that use the rollback segment RBS4.   In Session 1 create TAB111 and insert a value.  Do not commit.  In Session 2, issue create table TAB112 as select * from sys.obj$ where 1=2;

Examine the statistics in V$ROLLSTAT and select the number of shrinks, wraps and extends.  Check how many extents and blocks belong to this rollback segment. Determine what would be required to force an extent, a wrap and a shrink.

Session 1   SQL> create table TAB111 ( a number); Table created.   SQL> insert into TAB111 values (1); 1 row created.   Note: This session does not commit.  This means that the first extent cannot be reused.   Session 2   SQL> create table TAB112 2  as select * 3     from sys.obj$ 4     where 1 = 2; Table created.     SQL> select hwmsize, shrinks, wraps, extends 2  from v$rollstat 3  where usn = 5; HWMSIZE    SHRINKS      WRAPS    EXTENDS ———- ———- ———- ———- 129024          0          0          0   SQL> insert into TAB112 select * from sys.obj$; 3121 rows created.   SQL> select hwmsize, shrinks, wraps, extends 2  from v$rollstat 3  where usn = 5;   HWMSIZE    SHRINKS      WRAPS    EXTENDS ———- ———- ———- ———- 260096          0          3          2

Note: Session 2 has run a long running transaction.  Initially, the current extent is extent 0 (which is where the other transaction started running).  Every new transaction gets allocated blocks in the current extent as long as they are available.  When extent 0 is full, the transaction moves on to extent 1 (making it now the current extent).  The number of wraps increases by one when moving from one extent to the next.

Again, new blocks are allocated from this extent until none is available. Then, we try to wrap back into extent 0 (remember, initially there are only two extents).  However, this is not allowed as session 1 has an active transaction in extent 0.  Every time the head of the extent list catches up with the tail, a new extent must be added. Extends is now increased and since we are moving to the newly allocated extent, wraps is also increased (now it would have the value 2).

This process is repeated one more time, and we end up with the solution displayed: wraps=3,

extends = 2.

3.        Commit both active transactions and re-examine v$rollstat.   Force RBS4 to shrink and re-examine v$rollstat to see the changes.

Session 1   SQL> commit; Commit complete.   Session 2   SQL> commit; Commit complete.   SQL> select hwmsize, shrinks, wraps, extends 2  from v$rollstat 3  where usn = 5;   HWMSIZE    SHRINKS      WRAPS    EXTENDS ———- ———- ———- ———- 260096          0          3          2   SQL> alter rollback segment rbs4 shrink; Rollback segment altered.   SQL> select hwmsize, shrinks, wraps, extends 2  from v$rollstat 3  where usn = 5;   HWMSIZE    SHRINKS      WRAPS    EXTENDS ———- ———- ———- ———- 260096          1          3          2   SQL> select optsize, extents 2  from v$rollstat 3  where usn=5; OPTSIZE    EXTENTS ———- ———- 2

Note: When optimal is not set, the shrink command reduces the size of the rollback segment to 2.

4.        To demonstrate clearly how the number of wraps increases every time a different extent becomes the current one, repeat the same exercise above but create the rollback segment with three extents to start with.

SQL> alter rollback segment RBS4 offline;Rollback segment altered.   SQL> drop rollback segment RBS4; Rollback segment dropped.   SQL> create rollback segment RBS4 2  storage (initial 10K next 10K minextents 3); Rollback segment created.   SQL> alter rollback segment RBS4 online; Rollback segment altered.   Note:  Put all the other user RBSs offline

 

5.        Create two user sessions and examine the statistics in V$ROLLSTAT.

Session 1  SQL> insert into TAB111 values (1); 1 row created.   Note: This session does not commit.  This means that the first extent cannot be reused.   Session 2   SQL> select hwmsize, shrinks, wraps, extends 2  from v$rollstat 3  where usn = 5; HWMSIZE    SHRINKS      WRAPS    EXTENDS ———- ———- ———- ———- 129024          0          0          0   SQL> insert into TAB112 select * from sys.obj$; 3121 rows created.   SQL> select hwmsize, shrinks, wraps, extends 2  from v$rollstat 3  where usn = 5;   HWMSIZE    SHRINKS      WRAPS    EXTENDS ———- ———- ———- ———- 260096          0          3          1

Note: We need a total of four extents to perform both transactions.  If the rollback segment has 2 extents to start with, there will be a need for an additional 2 (extends = 2).  If minextents is 3, then only one additional extent is necessary (extends = 1).

 

However, the wraps occur when we move from extent 0 to extent 1, from 1 to 2 and from 2 to 3 (wraps = 3).

6.        Re-execute the transaction for session 2, and examine V$ROLLSTAT.

Session 2  SQL> insert into TAB112 select * from sys.obj$; 3121 rows created.   SQL> select hwmsize, shrinks, wraps, extends 2  from v$rollstat 3  where usn = 5;   HWMSIZE    SHRINKS      WRAPS    EXTENDS ———- ———- ———- ———- 456704          0          6          4

Note: Another run of the transaction forces the allocation of three more extents and the number of wraps continues to increase accordingly even though extent 0 has never been reused because the transaction in session 1 is preventing this.

7.        Commit both transactions and re-execute the insert into TAB112.

Session 1  SQL> commit; Commit complete.   Session 2   SQL> commit; Commit complete.   SQL> insert into TAB112 select * from sys.obj$; 3121 rows created.   SQL> select hwmsize, shrinks, wraps, extends 2  from v$rollstat 3  where usn = 5;   HWMSIZE    SHRINKS      WRAPS    EXTENDS ———- ———- ———- ———- 456704          0         10          4

Note: Both transactions have committed now, so there is no need to allocate new extents but as we continue to move from one extent to the next, the number of wraps increases.

 

 

8.        Force RBS4 to shrink and re-examine V$ROLLSTAT.

 

SQL> alter rollback segment rbs4 shrink; Rollback segment altered.   SQL> select hwmsize, shrinks, wraps, extends 2  from v$rollstat 3  where usn = 5;   HWMSIZE    SHRINKS      WRAPS    EXTENDS ———- ———- ———- ———- 456704          1         10          4   SQL> select optsize, extents 2  from v$rollstat 3  where usn=5; OPTSIZE    EXTENTS ———- ———- 2

Note: When optimal is not set, the shrink reduces the size of the rollback segment to 2 not to minextents which in this case was set to 3.

9.    The following exercises illustrate what happens when optimal is set.  With optimal set, we first check whether we need to perform a shrink before crossing the extent boundary.

Create a rollback segment with minextents of 2 and optimal of 20k.  Ensure all other rollback segments are offline.

SQL> alter rollback segment RBS4 offline; Rollback segment altered.   SQL> drop rollback segment RBS4; Rollback segment dropped.   SQL> create rollback segment RBS4 2  storage (initial 10K next 10K minextents 2 optimal 20k); Rollback segment created.   SQL> alter rollback segment RBS4 online; Rollback segment altered.   Note:  Put all the other user RBSs offline

 

 

10.     Create two user sessions and start a transaction in Session 1 by inserting a value.  Do not commit this session.

In Session 2, examine V$ROLLSTAT for extents and wraps.  Issue insert into TAB112 as select * from sys.obj$;   Re-examine V$ROLLSTAT and note the changes.

 

 

Session 1  SQL> insert into TAB111 values (1); 1 row created.   Session 2   SQL> select hwmsize, shrinks, wraps, extends 2  from v$rollstat 3  where usn = 5; HWMSIZE    SHRINKS      WRAPS    EXTENDS ———- ———- ———- ———- 129024          0          0          0   SQL> insert into TAB112 select * from sys.obj$; 3121 rows created.   SQL> select hwmsize, shrinks, wraps, extends 2  from v$rollstat 3  where usn = 5;   HWMSIZE    SHRINKS      WRAPS    EXTENDS ———- ———- ———- ———- 260096          0          3          2     Session 2   SQL> insert into TAB112 select * from sys.obj$; 3121 rows created.   SQL> select hwmsize, shrinks, wraps, extends 2  from v$rollstat 3  where usn = 5;   HWMSIZE    SHRINKS      WRAPS    EXTENDS ———- ———- ———- ———- 456704          0          6          5

Note: Another run of the transaction forces the allocation of three more extents and the number of wraps continues to increase accordingly even though extent 0 has never been reused because the transaction in session 1 is preventing this.

11. Commit both sessions. In Session 2, re-execute the insert from sys.obj$ and examine the shrinks, wraps and extends from V$ROLLSTAT.

Determine the optimal size from V$ROLLSTAT and explain the results.

Session 1   SQL> commit; Commit complete.   Session 2   SQL> commit; Commit complete.   SQL> insert into TAB112 select * from sys.obj$; 3121 rows created.   SQL> select hwmsize, shrinks, wraps, extends 2  from v$rollstat 3  where usn = 5;   HWMSIZE    SHRINKS      WRAPS    EXTENDS ———- ———- ———- ———- 456704          1         10          8   SQL> select optsize, extents 2  from v$rollstat 3  where usn=5;   OPTSIZE    EXTENTS ———- ———- 20480          5

Note: At the time of the shrink there were 7 extents in the rollback segment, the two we started with plus 5 extends.  Optimal was set to 20kb = 2 extents. The current extent (number 7) cannot be deallocated and neither can the initial extent. The shrink brings the rollback segment size down to optimal.  As the transaction runs, it required 3 more extents, hence extends is now 8 and the number of extents is back to 5.