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: Out of transaction slots

Re: Out of transaction slots

From: <Rajesh.Rao_at_jpmchase.com>
Date: Thu, 03 Oct 2002 16:03:18 -0800
Message-ID: <F001.004E0464.20021003160318@fatcity.com>

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...

> 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).
Received on Thu Oct 03 2002 - 19:03:18 CDT

Original text of this message

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