Re: rbs' maxextents in LMT

Guang,

Which version are you using? I tested this on a 8.1.7.4 system and it works fine. The only difference is, I didn't supply the INITIAL and NEXT; they are unnecessary anyway.

 create rollback segment arup2
 storage(maxextents 4);

select max_extents
from dba_rollback_segs
where segment_name = 'ARUP2'

returns 4, as expected!

Anyway, the other issue is about your decision to limit extension of rollback segments to contain what you term as "runaway transaction". A transaction does not own an rbs, rather an rbs contains several transactions. When a txn changes data, it places the pre-image in the rbs and if there is no space, then the rbs grows. Now, a long transaction may be killed since a rbs space was not found, but it can also happen to a small, legitimate txn that needs to store the pre-image, simply because the long txn has grown the rbs to the maxextents. So, how did it help? It stopped a desired txn.

Another problem is the read consistency. Not just transactions, but even selects also need to read data from RBS. If a rollback segment cannot grow, oracle determines if there is a way it can get the RBS to be used again. If there is no active transaction, then the old space is reused; but if a long running query needs that old data, i.e. pre-image, it doesn't find it and you get the dreaded "ORA-1555 Snapshot too old" error. The likelihood increases if your RBS can't grow.

So, that was my concern for artificially limiting the RBS extension. If you need to hal t abnormlly long transactions, use resource managaers, but not using MAXEXTENTS.

Arup

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, January 07, 2003 2:08 PM

> Hi, Arup:
>
> I created lmt "rbs" this way:
>
> CREATE TABLESPACE RBS
> DATAFILE '/oracle/u02/oradata/YPD/rbs01.dbf' SIZE 2048M
> EXTENT MANAGEMENT LOCAL
> UNIFORM SIZE 2M;
>
> and then
>
> SQL> create rollback segment RBSTest1 storage(initial 2048K next 2048K)
> tablespace rbs;
>
> Rollback segment created.
>
> SQL> create rollback segment RBSTest2 storage(initial 2048K next 2048K
> MAXEXTENTS 300) tablespace rbs;
>
> Rollback segment created.
>
> SQL> select SEGMENT_NAME,INITIAL_EXTENT,NEXT_EXTENT,
> 2 MIN_EXTENTS,MAX_EXTENTS
> 3 from dba_rollback_segs
> 4 where SEGMENT_NAME like '%TEST%';
>
> SEGMENT_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
> ------------------------------ -------------- ----------- -----------
> MAX_EXTENTS
> -----------
> RBSTEST1 4194304 2097152 1
> 32765
>
> RBSTEST2 4194304 2097152 1
> 32765
>
>
> It shows that you can not set MAXEXTENTS of a rollback segment when it is
> created in LMT. What I mean the "run away transaction" is a transaction
> that keep using rollback segment until it uses up all it's extents. In DMT
> case, we can set the MAXEXTENTS of all the rollback segments so that there
> is no transaction that can use the whole tablespace. But in LMT, it seems
a
> "run away" transaction can "eat up" the whole rbs tablespace because there
> is no MAXEXTENTS ( ie, MAXEXTENTS = unlimited). Does anyone know there is
> somewhere in Oracle Doc that I can find the answer of my question?
>
> Thanks.
>
> Guang
>
> ----------------
> Date: Mon, 06 Jan 2003 18:10:08 -0500
>
> Guang,
>
> You should use LMTs with UNFORM extent allocation of some size So create
the
> tablespaces and the rollback segments but not the INITIAL or NEXT.
>
> I am not sure what you meamn by runaway processes. If a transaction needs
> rollback segment space, it will need to extend it. You can still specify
> MAXEXTENTS to limit the number of extents.
>
> HTH
>
> Arup
>
>
> _________________________________________________________________
> Add photos to your e-mail with MSN 8. Get 2 months FREE*.
> http://join.msn.com/?page=features/featuredemail
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Guang Mei
> INET: zlmei_at_hotmail.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Arup Nanda
  INET: arupnanda_at_hotmail.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).