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: Rollback to Undo in a RAC env.

Re: Rollback to Undo in a RAC env.

From: Daniel W. Fink <Daniel.Fink_at_Sun.COM>
Date: Fri, 13 Aug 2004 15:07:14 -0600
Message-id: <411D2D82.6070108@sun.com>


It is not the number of concurrent users, but the number of concurrent transactions. The goal of AUM is to assign each transaction to its own undo segment if there is sufficient space in the undo tablespace. SMON is responsible for mantaining the undo retention table in each undo segment header. This maintainence does not necessarily coincide with the end of a transaction using the particular undo segment. That much we know for certain.

The theory (formulated by several of us outside of Oracle, so this is not official) is that SMON becomes overworked when a large number of undo segments are created by a large number of concurrent transactions. As new transactions look to bind to an undo segment, they are unable to find an 'available' undo segment (as indicated by the segment header), so they create a new segment since there is sufficient space. This only creates more work for an already overworked SMON process, so the problem snowballs. In extreme cases, we have seen the number of undo segments to be 20x the maximum number of concurrent transactions. The final result in some cases is that the database begins to throw 600 and 7445 errors and finally takes a hard crash.

I have also heard the recommendation against AUM in high volume OLTP systems from a very credible Oracle source.

Regards,
Daniel Fink

ryan.gaffuri_at_comcast.net wrote:

>daniel,
>I Have not seen this anywhere. Why avoid undo tablespaces in high transaction oltps? Do you mean just with RAC? My last project had 30,000 users and a 4 node rac cluster.
>This one is up to about 400,000 without RAC and will go to 3,000,000. Why shouldn't we use AUM?
>
>



Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Fri Aug 13 2004 - 16:03:08 CDT

Original text of this message

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