索引使用空间异常增长一例

2010/07/21 by Leave a Comment

客户的某套系统上有一个表空间近日使用率异常增长，该表空间用以存储索引段，经过定位发现一个原本只有200M左右的索引使用将近30+G的空间，而且表现为绝大多数是未格式化的数据块。以下为通过 show_space脚本收集的段信息：

Unformatted Blocks = 1772568
Blocks with 00-25% free space = 0
Blocks with 26-50% free space = 2173
Blocks with 51-75% free space = 0
Blocks with 76-100% free space = 0
Full Blocks = 60762

Unformatted Blocks总数1772568,共使用1772568*16k=27G. 一个索引占用如此多的未格式化块似乎不可思议，首先想到的可能是一个威力惊人的Bug。

探索MOS，可以发现以下这个有趣的note:

Applies to:

Oracle Server – Enterprise Edition – Version: 10.2.0.3
Information in this document applies to any platform.

Symptoms

The problem as observed is that the space allocated for a table/index(partition) is far too high and keeps growing.

Using the AVG_ROW_SIZE * NUM_ROWS shows that the space as needed would be far less that the allocated space (in some cases allocated xxGb while xxMb would be expected).

Cause

Bug 5987262 TABLESPACE IS ABNORMALLY INCREASED BY UNFORMATTED BLOCKS.   This bug was closed as duplicate of unpublished Bug 5890312 HANG OBSERVED WHILE CREATING CTXCAT INDEX

The following PL/SQL block will show the space usage in the (partitioned) table

set serveroutput on
exec dbms_output.enable(1000000);

declare

unf number;
unfb number;
fs1 number;
fs1b number;
fs2 number;
fs2b number;
fs3 number;
fs3b number;
fs4 number;
fs4b number;
full number;
fullb number;

own dba_tables.owner%type;
tab dba_tables.table_name%type;

yesno varchar2(3);

type parts is table of dba_tab_partitions%rowtype;
partlist parts;

type cursor_ref is ref cursor;
c_cur cursor_ref;

begin

own:=upper(‘&owner’);
tab:=upper(‘&table_name’);
dbms_output.put_line(‘——————————————————————————–‘);

open c_cur for select partitioned from dba_tables
where owner=own and table_name=tab;
fetch c_cur into yesno;
close c_cur;

dbms_output.put_line(‘Owner:     ‘||own);
dbms_output.put_line(‘Table:     ‘||tab);

dbms_output.put_line(‘————————————————‘);

if yesno=’NO’
then
dbms_space.space_usage(own,tab,’TABLE’,unf,unfb,fs1,fs1b,fs2,fs2b,fs3,fs3b,fs4,fs4b,full,fullb);
dbms_output.put_line(‘unf: ‘||unf||’ fs1: ‘||fs1||’ fs2: ‘||fs2||’ fs3: ‘||fs3||’ fs4: ‘||fs4||’ full: ‘||full);

else
open c_cur for select * from dba_tab_partitions
where table_owner=own and table_name=tab;
fetch c_cur bulk collect into partlist;
close c_cur;

for i in partlist.first .. partlist.last
loop
dbms_space.space_usage(partlist(i).table_owner,partlist(i).table_name,’TABLE PARTITION’,unf,unfb,fs1,fs1b,fs2,fs2b,fs3,fs3b,fs4,fs4b,full,fullb,partlist(i).partition_name);
dbms_output.put_line(‘Partition: ‘||partlist(i).partition_name);
dbms_output.put_line(‘unf: ‘||unf||’ fs1: ‘||fs1||’ fs2: ‘||fs2||’ fs3: ‘||fs3||’ fs4: ‘||fs4||’ full: ‘||full);
end loop;

end if;
dbms_output.put_line(‘——————————————————————————–‘);

end;
/

In case there is a need to check a single table partition:

set serveroutput on
exec dbms_output.enable(1000000);

declare

unf number;
unfb number;
fs1 number;
fs1b number;
fs2 number;
fs2b number;
fs3 number;
fs3b number;
fs4 number;
fs4b number;
full number;
fullb number;

own dba_tables.owner%type;
tab dba_tables.table_name%type;
par dba_tab_partitions.partition_name%type;

begin

own:=upper(‘&owner’);
tab:=upper(‘&table_name’);
par:=upper(‘&partition_name’);

dbms_output.put_line(‘——————————————————————————–‘);

dbms_output.put_line(‘Owner:     ‘||own);
dbms_output.put_line(‘Table:     ‘||tab);
dbms_output.put_line(‘Partition: ‘||par);
dbms_output.put_line(‘————————————————‘);

dbms_space.space_usage(own,tab,’TABLE PARTITION’,unf,unfb,fs1,fs1b,fs2,fs2b,fs3,fs3b,fs4,fs4b,full,fullb,par);
dbms_output.put_line(‘unf: ‘||unf||’ fs1: ‘||fs1||’ fs2: ‘||fs2||’ fs3: ‘||fs3||’ fs4: ‘||fs4||’ full: ‘||full);

dbms_output.put_line(‘——————————————————————————–‘);

end;
/

The following PL/SQL block will show the space usage in the (partitioned) index:

set serveroutput on
exec dbms_output.enable(1000000);

declare

unf number;
unfb number;
fs1 number;
fs1b number;
fs2 number;
fs2b number;
fs3 number;
fs3b number;
fs4 number;
fs4b number;
full number;
fullb number;

own dba_indexes.owner%type;
ind dba_indexes.index_name%type;

yesno varchar2(3);

type parts is table of dba_ind_partitions%rowtype;
partlist parts;

type cursor_ref is ref cursor;
c_cur cursor_ref;

begin

own:=upper(‘&owner’);
ind:=upper(‘&index_name’);
dbms_output.put_line(‘——————————————————————————–‘);

open c_cur for select partitioned from dba_indexes
where owner=own and index_name=ind;
fetch c_cur into yesno;
close c_cur;

dbms_output.put_line(‘Owner: ‘||own);
dbms_output.put_line(‘Index: ‘||ind);

dbms_output.put_line(‘————————————————‘);

if yesno=’NO’
then
dbms_space.space_usage(own,ind,’INDEX’,unf,unfb,fs1,fs1b,fs2,fs2b,fs3,fs3b,fs4,fs4b,full,fullb);
dbms_output.put_line(‘unf: ‘||unf||’ fs1: ‘||fs1||’ fs2: ‘||fs2||’ fs3: ‘||fs3||’ fs4: ‘||fs4||’ full: ‘||full);

else
open c_cur for select * from dba_ind_partitions
where index_owner=own and index_name=ind;
fetch c_cur bulk collect into partlist;
close c_cur;

for i in partlist.first .. partlist.last
loop
dbms_space.space_usage(partlist(i).index_owner,partlist(i).index_name,’INDEX PARTITION’,unf,unfb,fs1,fs1b,fs2,fs2b,fs3,fs3b,fs4,fs4b,full,fullb,partlist(i).partition_name);
dbms_output.put_line(‘Partition: ‘||partlist(i).partition_name);
dbms_output.put_line(‘unf: ‘||unf||’ fs1: ‘||fs1||’ fs2: ‘||fs2||’ fs3: ‘||fs3||’ fs4: ‘||fs4||’ full: ‘||full);
end loop;

end if;
dbms_output.put_line(‘——————————————————————————–‘);

end;
/

In case there is a need to check a single index partition:

set serveroutput on
exec dbms_output.enable(1000000);

declare

unf number;
unfb number;
fs1 number;
fs1b number;
fs2 number;
fs2b number;
fs3 number;
fs3b number;
fs4 number;
fs4b number;
full number;
fullb number;

own dba_indexes.owner%type;
ind dba_indexes.index_name%type;
par dba_ind_partitions.partition_name%type;

begin

own:=upper(‘&owner’);
ind:=upper(‘&index_name’);
par:=upper(‘&partition_name’);

dbms_output.put_line(‘——————————————————————————–‘);

dbms_output.put_line(‘Owner: ‘||own);
dbms_output.put_line(‘Index: ‘||ind);
dbms_output.put_line(‘Partition: ‘||par);
dbms_output.put_line(‘————————————————‘);

dbms_space.space_usage(own,ind,’INDEX PARTITION’,unf,unfb,fs1,fs1b,fs2,fs2b,fs3,fs3b,fs4,fs4b,full,fullb,par);
dbms_output.put_line(‘unf: ‘||unf||’ fs1: ‘||fs1||’ fs2: ‘||fs2||’ fs3: ‘||fs3||’ fs4: ‘||fs4||’ full: ‘||full);

dbms_output.put_line(‘——————————————————————————–‘);

end;
/

If the value of UNF makes up the major part of the space as allocated,  unpublished bug 5890312 is hit.
In a specific case a table had a value of 4GB unformatted blocks out of 4.3GB allocated blocks.

Solution

The actions as needed to resolve this issue are:

1) Install the patch for unpublished Bug 5890312 in order to prevent future occurrences

2) Resolve the current space allocation, options:
– shrink the table (partition)
– recreate the table (partition)
– recreate the index (partition)

以上关于表空间异常增长且表(或表分区)和索引(或索引分区)中分配了大量未格式化的块的描述与我们的问题十分相似，但该文档的指出可能发生该问题的版本是10.2.0.3,而我们的版本已经是10.2.0.4了，是否又一次是Oracle的”伪修复”呢?怀着疑虑我们提交了SR，希望MOS帮助我们确认是否”hit Bug 729149.1″。

来自MOS的回复:

“The bug is fixed in 10.2.0.4.3 as well as 11.1.0.6.
You might as well apply the lastest PSU available for your platform: 10.2.0.4.4 Patch:9352164

I’m afraid there is no one off (interim) patch available for 10.2.0.4 on Solaris SPARC.
It was only provided for 9.2.0.8 and 10.2.0.3.
The one off patch 5890312 has been made available in Recommended Patch Sets (PSU): 10.2.0.4.3 and 10.2.0.4.4.

More information about Oracle Recommended Patches and their availability can be found in:
NOTE.756671.1 Oracle Recommended Patches — Oracle Database

If you have trouble applying any of the recommended PSUs, please let me know and i will log a one off backport request on your behalf  to ask development is it would be possible to provide you with the one off patch on 10.2.0.4.”

看起来我们并不走运，在10.2.0.4的基础上甚至没有one-off补丁，如果要彻底解决只能打PSU。

这个Bug最早出现是在9.2.0.8上，有一个相关的说明:

Hdr: 5987262 9.2.0.8.0 RDBMS 9.2.0.8.0 SPACE PRODID-5 PORTID-197 5890312
Abstract: TABLESPACE IS ABNORMALLY INCREASED  BY UNFORMATTED BLOCKS

*** 04/12/07 12:32 am ***
TAR:
----
6265975.992

PROBLEM:
--------
SQL>  select count(*) from pb.PBTAJUMT;

  COUNT(*)
----------
    897807

SQL> select sum(bytes)/1024/1024 from dba_extents where
segment_name='PBTAJUMT';

SUM(BYTES)/1024/1024
--------------------
               22008

SQL> select count(*) from dba_extents where segment_name='PBTAJUMT';

  COUNT(*)
----------
      5502

SQL>
       UNF
----------
   1404407

SQL>
       FS4
----------
        87

SQL>
       FS3
----------
         8

SQL>
       FS2
----------
         1

SQL>
       FS1
----------
        99

SQL>
       FULL
----------
      2291

AFTER 18 rows inserted >>

SQL> select count(*) from pb.PBTAJUMT;

  COUNT(*)
----------
    897825

SQL> select sum(bytes)/1024/1024 from dba_extents where
segment_name='PBTAJUMT';

SUM(BYTES)/1024/1024
--------------------
               23004

SQL> select count(*) from dba_extents where segment_name='PBTAJUMT';

  COUNT(*)
----------
      5751

SQL>
       UNF
----------
   1468072

SQL>
       FS4
----------
       103

SQL>
       FS3
----------
         7

SQL>
       FS2
----------
         2

SQL>
       FS1
----------
        56

SQL>
      FULL
----------
      2334

After 18 rows inserted, extents increased about 1G btyes.

DIAGNOSTIC ANALYSIS:
--------------------
ALERT LOG : node2 >

Thu Apr 12 09:40:15 2007
ORA-1653: unable to extend table PB.PBTAJUMT by 256 in
tablespace PB
Thu Apr 12 09:42:48 2007
ORA-1653: unable to extend table PB.PBTAJUMT by 256 in
tablespace PB
..
Thu Apr 12 09:44:37 2007
ORA-1653: unable to extend table PB.PBTAJUMT by 256 in
tablespace PB
Thu Apr 12 09:48:06 2007
ORA-1653: unable to extend table PB.PBTAJUMT by 256 in
tablespace PB
Thu Apr 12 09:49:49 2007
alter database datafile '/dev/oravg05/roravg05_04' resize 1000m
Thu Apr 12 09:50:21 2007
Completed: alter database datafile '/dev/oravg05/roravg05_04'
Thu Apr 12 09:50:39 2007
ORA-1653: unable to extend table PB.PBTAJUMT by 256 in
tablespace PB
...
Thu Apr 12 11:43:07 2007
 alter database datafile '/dev/oravg05/roravg05_04' resize 3300m
Completed:  alter database datafile '/dev/oravg05/roravg05_04
Thu Apr 12 11:43:51 2007
ORA-1653: unable to extend table PB.PBTAJUMT by 256 in
tablespace PB
Thu Apr 12 11:44:35 2007
alter database datafile '/dev/oravg05/roravg05_04' resize 3500
Thu Apr 12 11:44:37 2007
ORA-3214 signalled during: alter database datafile
'/dev/oravg05/roravg05_04'...
Thu Apr 12 11:44:54 2007
alter database datafile '/dev/oravg05/roravg05_04' resize 3500m
Thu Apr 12 11:45:05 2007
Completed: alter database datafile '/dev/oravg05/roravg05_04'

kjbdb1_ora_23026.trc :: segment dump >>

After a specific  extents , we can find owner is only inst 2 .
...
 0x05073607  Free: 2 Inst: 2
   0x05073608  Free: 1 Inst: 2
   0x05482905  Free: 2 Inst: 2
   0x05482906  Free: 1 Inst: 1
   0x05482907  Free: 5 Inst: 1
   0x05482908  Free: 5 Inst: 1
   0x11400405  Free: 5 Inst: 2 <======
   0x11400406  Free: 5 Inst: 2
   0x11400407  Free: 5 Inst: 2
   0x11400408  Free: 5 Inst: 2
   0x11400505  Free: 5 Inst: 2
   0x11400506  Free: 5 Inst: 2
   0x11400507  Free: 5 Inst: 2
   0x11400508  Free: 5 Inst: 2
   0x11400605  Free: 5 Inst: 2
   0x11400606  Free: 5 Inst: 2
   0x11400607  Free: 5 Inst: 2
   0x11400608  Free: 5 Inst: 2
   0x11400705  Free: 5 Inst: 2
   0x11400706  Free: 5 Inst: 2
   0x11400707  Free: 5 Inst: 2
   0x11400708  Free: 5 Inst: 2
   0x11400805  Free: 5 Inst: 2
   0x11400806  Free: 5 Inst: 2
   0x11400807  Free: 5 Inst: 2
   0x11400808  Free: 5 Inst: 2
   0x11400905  Free: 5 Inst: 2
   0x11400a05  Free: 5 Inst: 2
   0x11400b05  Free: 5 Inst: 2
   0x11400c05  Free: 5 Inst: 2
   0x11400d05  Free: 5 Inst: 2
   0x11400e05  Free: 5 Inst: 2
   0x11400f05  Free: 5 Inst: 2
   0x11401005  Free: 5 Inst: 2
   0x11401105  Free: 5 Inst: 2
   0x11401205  Free: 5 Inst: 2
   0x11401305  Free: 5 Inst: 2
   0x11401405  Free: 5 Inst: 2
   0x11401505  Free: 5 Inst: 2
   0x11401605  Free: 5 Inst: 2
   0x11401705  Free: 5 Inst: 2
   0x11401805  Free: 5 Inst: 2
   0x11401905  Free: 5 Inst: 2
   0x11401a05  Free: 5 Inst: 2
   0x11401b05  Free: 5 Inst: 2
   0x11401c05  Free: 5 Inst: 2
   0x11401d05  Free: 5 Inst: 2
   0x11401e05  Free: 5 Inst: 2
   0x11401f05  Free: 5 Inst: 2
   0x11402005  Free: 5 Inst: 2
   0x11402105  Free: 5 Inst: 2
   0x11402205  Free: 5 Inst: 2
   0x11402305  Free: 5 Inst: 2
   0x11402405  Free: 5 Inst: 2
   0x11402505  Free: 5 Inst: 2
   0x11402605  Free: 5 Inst: 2
   0x11402705  Free: 5 Inst: 2
   0x11402805  Free: 5 Inst: 2
   0x11402905  Free: 5 Inst: 2
   0x11402a05  Free: 5 Inst: 2
   0x11402b05  Free: 5 Inst: 2
   0x11402c05  Free: 5 Inst: 2
   0x11403605  Free: 5 Inst: 2
   0x11403705  Free: 5 Inst: 2

WORKAROUND:
-----------
none

RELATED BUGS:
-------------
bug.5945288, bug.4887955, bug.2447046
REPRODUCIBILITY:
----------------
No, cannot reproduce intentionally. But happened 4 times on product machine.

TEST CASE:
----------

STACK TRACE:
------------

SUPPORTING INFORMATION:
-----------------------
This system migrated on 19th of Mar. It happened 4 times from 20th of Mar.
24 HOUR CONTACT INFORMATION FOR P1 BUGS:

MOS无法重现该Bug,但可以确认该Bug的存在，具体触发的条件也不得而知。

如果您的环境无法在短期内应用补丁的话，可以通过 shrink/recreate 表(或表分区)和索引(或索引分区)来workaround这个问题，事实上我感觉这个Bug的触发条件并不简单，也就是说出现概率并不高；但如果您觉得自己不是那么幸运，那么还尽快将您的产品数据库升级到10.2.0.4.4以上或者10.2.0.5(我们是激进派)吧！

Filed Under: Oracle Tagged With: , , , , , , ,

Rollback Segment Utilization:Extent, Wrap and Shrink

2005/07/10 by Leave a Comment

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.

  1. 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.

Filed Under: Oracle Tagged With: ,

沪ICP备14014813号-2

沪公网安备 31010802001379号