Sometimes the extents should be larger (fewer larger extents, then many small extents).
----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Saturday, April 05, 2003 1:30 AM > 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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Anjo Kolk 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).
