Re: Out of transaction slots

You can monitor the XACTS column in V$ROLLSTAT view to see how many active transactions are in each rollback segment. Alternatively, you can query as follows:

    select xidusn, status, count(*) from v$transaction group by xidusn, status;

...as you keep adding transactions...

Should be fun!

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, October 03, 2002 6:03 PM

>
> Thanks Tim for your response to this. I agree with you. For now, I have
> come up with this theory :
>
> I feel that the "Unable to use system rollback tablespace" errors were a
> result of the "Out of transaction slots" message. Transactions were
> assigned as they came in to different transaction slots in the rollback
> segments. Suppose, we had 21 transaction slots in each of the 20 rollback
> segments. This were utilized one by one by different transactions, and
> never released. So, the transactions never commited or rolled back, and
> they kept coming in. I think, as all the transaction slots in a rollback
> segment were utilized, that rollback segment was marked as not available
> for any more transactions. So, one by one, the rollback segments started
> going unavailable. No errors were reported anywhere in the logs when this
> was happening, because there were transaction slots available in other
> rollback segments. But finally when the last transaction slot in the last
> available rollback segment was utilized, the application log reported the
> "Out of transaction slots" in the error log to the next incoming
> transaction. This would also mark all the rollback segments as not
> available for transaction, whereby Oracle would then try to make use of
the
> system rollback segment. Hence, all subsequent errors were for "Unable to
> use system rollback segment for non system tables".
>
> This answers my questions, why did the "Out of transaction slots" error
> happen just once, whereas the "Unable to use system rollback" errors got
> reported for every subsequent transaction? Also, why was the "Out of
> transaction slots" reported first?
>
> Does it make sense? Anyways, I plan to conduct a test tomorrow where I
keep
> just one rollback segment online, start more than 20 transactions, dont
> commit them, and then check the errors that should hopefully be reported
> after the 21st session. I wonder what the status of the rollback segment
> would be?
>
> Thanks
> Raj
>
>
>
>
>
>
>
> "Tim Gorman"
> <Tim_at_SageLogi To: Multiple recipients of
list ORACLE-L <ORACLE-L_at_fatcity.com>
> x.com> cc:
> Sent by: Subject: Re: Out of
transaction slots
> root_at_fatcity.
> com
>
>
> October 03,
> 2002 07:01 PM
> Please
> respond to
> ORACLE-L
>
>
>
>
>
>
> comments inline...
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Thursday, October 03, 2002 4:34 PM
>
>
> > Hi All,
> >
> > OPS 8.0.6.2 on Sun 2.6 nodes. The other day, the users reported that
they
> > were unable to complete transactions, and before we could take a proper
> > look, the database was shutdown aborted, and started up. I am now trying
> to
> > investigate possible reasons for these errors. Just before the shutdown
> at
> > 00:15 AM, the alert log reported an error saying "ORA-01595: error
> freeing
> > extent (8) of rollback segment (2)) ORA-01554: out of transaction slots
> in
> > transaction tables". Now, we have 20 rollback segments, 10 on each node,
> on
> > a block size of 2k. So that would mean about approximately a total of
> > (21*20) transaction slots.
>
> The total number of transaction slots is not relevant; only the number of
> slots per RBS. A new transaction is first assigned to an RBS; the
> algorithm which chooses is strictly LRU -- the number of available slots
in
> the transaction table doesn't enter into it (though it easily could)...
>
> >
> > Later, we found that that application logs reported the ORA-01554 almost
> 2
> > hours before the alert log entry. Later, the logs had multiple errors
> > saying "ORA???? - Unable to use system rollback segment for non system
> > tables". No one had taken the rollback segments offline. Also, there
> wasnt
> > any large amount of transactions running as is reflected by the redo log
> > switches. Also, application team says 90% of the transactions are
> selects.
>
> The percentage mix of SELECTs vs DML is kind of irrelevant, especially
when
> dealing with small transaction tables in 2k database blocks (i.e. 21
> entries). Even if only 10% of all SQL statements are DML, how long would
> it
> take to generate 420 (i.e. 21 tt slots times 10 RBS) of them? An hour? A
> day? If the transactions are not committing promptly and properly, then
> the
> percentage mix only accelerates or decelerates the rapidity of getting
> ORA-01554; it's going to happen regardless...
>
> ..I would bet that a change has happened to the application recently which
> is somehow preventing prompt commits of transactions from occurring, and
> that they are just piling up. Do you have good change-management on
> application code changes in place?
>
> > So, for whatever reason, the rollback segments were made unavailable.
But
> I
> > am not able to confirm this. Should Oracle not be creating a trace file
> > when a rollback segment goes unavailable? Also, why was the out of
> > transaction slots error reported in the alert log just before the
> shutdown,
> > when in fact, the application reported it much earlier? Have not been to
> > get a definite answer yet on my TAR. Any pointers, or clues to look
for?
>
> I'm not certain, but you may only be assuming that the RBS actually went
> OFFLINE, just because the error (I'm guessing that it was ORA-01552?) is
> commonly associated with unavailable non-SYSTEM rollback segments. Unless
> you actually saw the status OFFLINE somewhere, it may not be useful to
> assume that to be the case...
>
> >
> > Thanks
> > Raj
>
> Wow! Crazy situation! Best of luck -- sorry not to offer any real
help...
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Tim Gorman
> INET: Tim_at_SageLogix.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: Tim Gorman
  INET: Tim_at_SageLogix.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).