Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Estimating rollback segment size

Re: Estimating rollback segment size

From: Steve Howard <stevedhoward_at_gmail.com>
Date: 24 Aug 2006 11:00:01 -0700
Message-ID: <1156442401.253562.88410@m73g2000cwd.googlegroups.com>


BS wrote:

>

> I have only one transaction as thisis in a test system. What will be
> the relation between the # of blocks and MB's being used?

They are hte number of database blocks in use by the transaction. If you multiply that number by the size of your database block, that will provide you with how much undo your transaction is using. See below for a simple test case...

SQL> create table t0824(c varchar2(4000));

Table created.

SQL> begin
  2 for i in 1..1000 loop
  3 insert into t0824 values(rpad('*',4000,'*'));   4 end loop;
  5 end;
  6 /

PL/SQL procedure successfully completed.

SQL> insert into t0824 select * from t0824;

1000 rows created.

SQL> insert into t0824 select * from t0824;

2000 rows created.

SQL> commit;

Commit complete.

SQL> exec show_space('T0824');

Free Blocks.............................3
Total Blocks............................4096
Total Bytes.............................33554432
Total MBytes............................32
Unused Blocks...........................93
Unused Bytes............................761856
Last Used Ext FileId....................1
Last Used Ext BlockId...................98057
Last Used Block.........................35

PL/SQL procedure successfully completed.

SQL> update t0824 set c = c;

4000 rows updated.

SQL> select used_ublk
  2 from v$transaction
  3 where addr = (select taddr

  4                    from v$session
  5                    where sid = (select distinct sid
  6                                   from v$mystat)
  7                 );

 USED_UBLK


      4001

SQL> select 4001 * 8192 from dual;

 4001*8192



  32776192

SQL> select 32 * 1024 * 1024 from dual;

32*1024*1024


    33554432

SQL> select 33554432 - 32776192 from dual;

33554432-32776192


           778240

SQL> select 778240 / 8192 from dual;

778240/8192


         95

SQL> Two blocks are left over (95 - 93) that are not in use, as shown in the show_space procedure earlier.

Regards,

Steve Received on Thu Aug 24 2006 - 13:00:01 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US