RE: Out of transaction slots

Raj,

I've seen this problem when dblinks are used extensively. A transaction slot is allocated when a dblink is used, even if only selecting over the dblink. It remains allocated until the session commits or ends. However, if the session doesn't make any changes, it won't commit, and the user could stay connected all day, leading to problems.

I've had developers put in a commit after selecting over a dblink in some such applications, and it solved the problem.

Gary

Gary Kirsh
Next Extent Consulting

-----Original Message-----
Sent: Thursday, October 03, 2002 9:38 PM To: Multiple recipients of list ORACLE-L

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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Kirsh, Gary
  INET: gary.kirsh_at_gs.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).