> You are right, for Oracle8, not later. In Oracle 8 and before, the > tablespaces were dictionary managed and all space allocation and > deallocation had to go through the ST (Space Management Enqueue) lock. Since > there was only one ST enqueue, a very frequent space management created > contention and performance degradations. Therefore Oracle advocated against > making any rollback segment unlimited extents. For that matter it is not > advisable to make *any* object in a DMT unlimited extents. > > However, with the advent of LMTs, the problem of the ST enqueue is not there > any more. The RBSs can freely grow and shrink as needed without too much > problem. Please see my note earlier about the potential problems if you > limit the growth - (i) you are going to affect the perfectly normal > transactions, not just runaway ones and (ii) you will increase the > likelyhood of ORA-1555 error. Imagine a situation where a city bus service > decides to limit the number of miles a bus travels a day to limit some > people who ride a lot. But what about a fellow who just hopped on the bus to > go to a stop a mile away but the bus stopped after half a mile since the > quota got over? The last passenger was unnecessarily punished due to a > rather ridiculous reason. > > If you really want to see the offending sessions, use the the v$sesstat view > and see the sessions with most undo. > > You have not specified the version. If it's 9i, you could use the Automated > Segment Space Management to further reduce the contention in extent growth. > > HTH > > Arup Nanda > www.proligence.com > > ----- Original Message ----- > From: "Guang Mei" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Wednesday, January 08, 2003 3:12 PM > Subject: Re: rbs' maxextents in LMT > > > > Hi,Arup: > > > > From your message,it seems that you don't think there is any problem > setting MAXEXTENTS of all rollback segments to unlimited? > > > > I looked at my old (very old) Oracle 8 DBA course materials, it says one > should not set MAXEXTENTS to unlimited because it could cause uncessary > extension of a rollback segment (that is, to avoid "run away" transaction). > > > > So what do you think of setting MAXEXTENTS to unlimited, good or bad? > > > > Thanks. > > > > Guang > > > > > > ------------------- > > Guang, > > > > Which version are you using? I tested this on a 8.1.7.4 system and it > works > > fine. The only difference is, I didn't supply the INITIAL and NEXT; they > are > > unnecessary anyway. > > > > create rollback segment arup2 > > storage(maxextents 4); > > > > select max_extents > > from dba_rollback_segs > > where segment_name = 'ARUP2' > > > > returns 4, as expected! > > > > Anyway, the other issue is about your decision to limit extension of > > rollback segments to contain what you term as "runaway transaction". A > > > > transaction does not own an rbs, rather an rbs contains several > > transactions. When a txn changes data, it places the pre-image in the rbs > > and if there is no space, then the rbs grows. Now, a long transaction may > be > > killed since a rbs space was not found, but it can also happen to a small, > > legitimate txn that needs to store the pre-image, simply because the long > > txn has grown the rbs to the maxextents. So, how did it help? It stopped a > > desired txn. > > > > Another problem is the read consistency. Not just transactions, but even > > selects also need to read data from RBS. If a rollback segment cannot > grow, > > oracle determines if there is a way it can get the RBS to be used again. > If > > there is no active transaction, then the old space is reused; but if a > long > > running query needs that old data, i.e. pre-image, it doesn't find it and > > you get the dreaded "ORA-1555 Snapshot too old" error. The likelihood > > increases if your RBS can't grow. > > > > So, that was my concern for artificially limiting the RBS extension. If > you > > need to hal t abnormlly long transactions, use resource managaers, but not > > using MAXEXTENTS. > > > > Arup > > > > > > > > > > > > > > > > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
