Re: Out of transaction slots

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