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

Home -> Community -> Mailing Lists -> Oracle-L -> RE: How are rollback segments assigned?

RE: How are rollback segments assigned?

From: <Jared.Still_at_radisys.com>
Date: Tue, 26 Nov 2002 13:59:03 -0800
Message-ID: <F001.0050CC04.20021126135903@fatcity.com> 





> In v7, the tie-breaker algorithm apparently changed to choose the 
segment


> that is listed earliest in the init.ora file.



What if the rollback segments are created PUBLIC, and are
not listed in init.ora?



Order of sys.undo$.us#?



Jared








"Cary Millsap" <cary.millsap_at_hotsos.com>
Sent by: root_at_fatcity.com


 11/26/2002 11:15 AM


 Please respond to ORACLE-L

 

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        RE: How are rollback segments assigned?






Exactly. A guy might have to poll V$ROLLSTAT 100+ times per second to
see the allocations happening on a system with well-designed
transactions. You just can't do that with SQL.



Raj, you *can* test the behavior by using some artificially *bad*
transactions. On a quiescent system (nothing else running) with n
rollback segments, open n+1 sqlplus sessions. In each session, update a
row but don't commit. After each session's update, look at V$ROLLSTAT. 



To the best of my knowledge, the assignment algorithm has always been to
assign a new txn to the segment with the smallest number of active txns
in it. There are of course lots of ties (e.g., lots of segments might
have 0 active txns in them). The tie-breaker in v6 was to assign in
round-robin order (as the segments are listed in the init.ora file). In
v7, the tie-breaker algorithm apparently changed to choose the segment
that is listed earliest in the init.ora file. I don't know whether it
changed again in 8 or 9. You'll know if you do the test.




Cary Millsap


Hotsos Enterprises, Ltd.


http://www.hotsos.com



Upcoming events:

- Hotsos Clinic, Dec 9-11 Honolulu
- Hotsos Clinic 101, Jan 7-9 Knoxville
- Steve Adams's Miracle Master Class, Jan 13-15 Copenhagen
- 2003 Hotsos Symposium, Feb 9-12 Dallas






-----Original Message-----


Jared.Still_at_radisys.com


Sent: Tuesday, November 26, 2002 12:25 PM
To: Multiple recipients of list ORACLE-L



Raj,



You're assuming that the transactions are lasting long enough
for you to catch them in the rollback segments.



Jared








Rajesh.Rao_at_jpmchase.com


Sent by: root_at_fatcity.com


 11/26/2002 09:42 AM


 Please respond to ORACLE-L

 


        To:     Multiple recipients of list ORACLE-L
<ORACLE-L_at_fatcity.com>

        cc: 
        Subject:        Re: How are rollback segments assigned?







Thanks Jared, thats not what I see. I have 20 rollback segments, and at
no


point in time, there were more than


4 transactions in the rollback segments. In fact, I happened to see it a
couple of minutes ago.



select usn, xacts from v$rollstat showed me 0 in 19 rollback segments,
and


2 in one of them. And I have got alerts in place
to alert me when there are more than 4 transactions in all of the
rollback


segments. And yeah, the rollback segments
are online.



Remains a mystery to me.



Raj






 

                    Jared.Still_at_r 
                    adisys.com           To:     Multiple recipients of 
list ORACLE-L <ORACLE-L_at_fatcity.com> 
                    Sent by:             cc: 
                    root_at_fatcity.        Subject:     Re: How are


rollback 


segments assigned? 

                    com 
 
 
                    November 25, 
                    2002 08:59 PM 
                    Please 
                    respond to 
                    ORACLE-L 
 
 








Here's one scenario:



4 transactions, 2 rollback segs



tx 1 - rbs1


tx 2 - rbs 2


tx 3  - rbs 1


tx 4 - rbs 2



tx1 and tx3 finish.



tx2 and tx4 have not yet committed.



Jared








Rajesh.Rao_at_jpmchase.com


Sent by: root_at_fatcity.com


 11/25/2002 07:39 AM


 Please respond to ORACLE-L




        To:     Multiple recipients of list ORACLE-L 
<ORACLE-L_at_fatcity.com>

        cc:
        Subject:        How are rollback segments assigned?






Hello Friends,



Oracle Parallel Server 8.0.6.2.0 on Solaris 2.6 Nodes



We were having some locking and rollback issues, and I set up some
scripts


to alert me in case there are more than 4 transactions in the rollback
segments, and more than 1 in any of the rollback segments.



Select sum(xacts) from v$rollstat  --- Alert if more than 4, condition
set


to exclude system rollback


where usn != 0



Select count(*) from v$rollstat          --- Alert if more than 0, where
condition to exclude system rollback


where xacts >=2


and    usn != 0



We have 20 rollback segments, and there is very minimal DML activity on
this database. I happened to see a scenario where all rollback segments
had


0 transactions, except for one which had 2 in them. Under what scenario,
can this happen?



I thought the criteria for assigning rollback segments to transactions
was

1. If object in system tablespace, use system rolback segment
2. Use the one with the least number of active transactions
3. If 2 or more rollback segments fit the second criteria, use the LRU


algorithm.



Thanks


Raj


--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: Rajesh.Rao_at_jpmchase.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.com
--
Author:
  INET: Jared.Still_at_radisys.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.com
-- 
Author: 
  INET: Rajesh.Rao_at_jpmchase.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.com
-- 
Author: 
  INET: Jared.Still_at_radisys.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.com
-- 
Author: Cary Millsap
  INET: cary.millsap_at_hotsos.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.com
-- 
Author: 
  INET: Jared.Still_at_radisys.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).


Received on Tue Nov 26 2002 - 15:59:03 CST












Original text of this message













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