Yes, your assumption is right. Create new rollback segments without the OPTIMAL clause, bring them online, take the old ones offline, blow the old ones away, document the new ones in your init.ora, remove the old ones from your init.ora. See whether this solves your problem. My guess is that you should be set. Keep us posted.
Cheers, Gaja --- "Jesse, Rich" <[EMAIL PROTECTED]> wrote: > Excellent advice, Gaja! Yes, we do have OPTIMAL > set. From the docs I don't > see a way to remove the OPTIMAL setting once used. > I assume this means new > RBSs? > > > Rich > > Rich Jesse System/Database > Administrator > [EMAIL PROTECTED] Quad/Tech > International, Sussex, WI USA > > > -----Original Message----- > Sent: Friday, April 04, 2003 12:54 PM > To: Multiple recipients of list ORACLE-L > > > Rich, > > The overcommiting is a definite suspect and is worth > looking into. ALso, do you by chance have OPTIMAL > set > on your rollback segments? If so, I'd suggest you > remove the OPTIMAL clause and try again. In my > experience, I have had my share of hassles with > OPTIMAL. Even when it was sized 'reasonably large' > (way above INITIAL * MINEXTENTS) for the > application. > > OPTIMAL does increase the probability of ORA-1555, > as > extents of your rollback segments that have the > "before images" of your transactions, can get > dropped, > while your queries are left "high and dry". Just > something to check. > > The downside of not setting OPTIMAL is more disk > usage > for your rollback segments. In the bigger scheme of > things, it may be much cheaper to eat the disk cost > (which is probably a few dollars) than to deal with > queries failing with ORA-1555s. If you are so > strapped > for space, then you can "hand shrink" the rollback > segments using the ALTER rollback segment xxx SHRINK > command via a job, at a time when no queries are > running. > > If you don't have OPTIMAL set, and if you are using > any READ-ONLY tablespaces that were put in READ-ONLY > mode recently, then try the following: > > 1) Set those tablespaces back to READ-WRITE mode > 2) Write a sql-generating-sql script that performs a > select count(*) on all the objects in the tablespace > 3) Set the tablespaces back to READ-ONLY. > > The above exercise will force a block-cleanout (if > required) on all the objects, which may not have > occured before the tablespace was originally put in > READ-ONLY mode. > > Hope this helps, > > Gaja > > --- "Jesse, Rich" <[EMAIL PROTECTED]> wrote: > > Hey all, > > > > Fighting with a lot of ORA-1555s lately on 8.1.7.4 > > on HP/UX. Most of them > > are now coming from long-running Business Objects > > (B.O.) queries against our > > OLTP DB. I think I need to recreate the RBS > > tablespace (currently 1MB > > extents in LMT), but until I can get time to do > > that, I'd like to approach > > this from the application side, where I think the > > majority of the problem is > > occurring. I've been tracking TPM based on "user > > commits" in V$SYSSTAT and > > we spiked at 20K TPM just before the B.O. query in > > question ORA-1555'd. > > From STATSPACK reports, I think the most likely > > cause for this is a COMMIT > > for every DML in a batch job. From what I've > read, > > including MetaLink > > 40689.1, this over committing is one potential > cause > > of ORA-1555s. > > > > In order to narrow down the problem, I've turned > on > > event 1555 in the > > instance. Is it possible to determine what > > table(s)' DML is causing the > > ORA-1555 based on the trace file? I have the last > > wait state, which happens > > to be "db file sequential read", but I don't know > if > > there's any > > correlation. If there is, I should be able to > > determine which table by the > > file# and block# given in the trace. Is this > > correct? > > > > Also, if the over-committing process is not doing > > any DML on the tables of > > the B.O. query, is it still a possible suspect of > > causing the ORA-1555 > > because of the potential of overwriting another > > process' RBS? > > > > Damn. I was hoping to be at 9i before I had to > deal > > with RBSs... :) > > > > > > Rich > > > > Rich Jesse System/Database > > Administrator > > [EMAIL PROTECTED] Quad/Tech > > International, Sussex, WI USA > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Jesse, Rich > 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). > ===== __________________________________________________ Do you Yahoo!? Yahoo! Tax Center - File online, calculators, forms, and more http://tax.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gaja Krishna Vaidyanatha 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).
