cool! thanks! ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, June 27, 2002 10:28 PM
> I do the same thing. No optimal setting, manual (automated job) shrinking of > rollback segments when database has no DML activity (mostly just before > midnight). No ORA-1555 in several years now. > About the space for rollback, we are very generous. Tom Kyte explains this > very well in his wonderful book. > I am working on fixing a rollback problem in one other (7.3.4) database I > acquired. RBS monitoring (v$rollstat, v$transaction etc) script were > installed just last night. Within a week (to cover low/high/eom processing) > we will know the sizing and HWM etc. And then we plan to zap 'Optimal' > setting. With current optimal settings ORA-1555 gets reported at least twice > a month, but I know it will be history after redoing the RBS in a few days. > > - Kirti > > -----Original Message----- > Sent: Thursday, June 27, 2002 4:16 PM > To: Multiple recipients of list ORACLE-L > > > Tim, > > I rebuilt the rollback segments without OPTIMAL in one case because the > shrinks were causing ORA-1555's at bad times. I have a procedure that > is scheduled via DBMS_JOBS to manually shrink the rollback segments to a > preset size. This runs at what we have found is a relatively quiet time > and has not yet caused an ORA-1555. > > As far as the space wastage goes, this is kept somewhat in control by > the weekly shrink mentioned above. Also, we simply agreed to have a > bigger RBS tablespace that may be strictly needed and when we showed the > business that the frequency of these errors dropped off the chart, they > agreed and the cost of a couple of extra GB was not an issue. > > While this is not a formula that will be needed (or even work) for > everyone, it worked well for this case. There is a mix of OLTP users, > batch jobs and reporting/DSS. Some tools that are is use (SAS) will not > allow SET ROLLBACK SEGMENT, so the recommendation to have 1 big rollback > segment that you bring online for specific users didn't work. > > I like the ability to control when a rollback segment gets shrunk and > at the same time I don't need to do it. To me this is a workable > solution. > > Stephen > > >>> [EMAIL PROTECTED] 06/27/02 11:03AM >>> > I personally prefer to have it set, but I think the reasoning against > setting it has to do with allowing rollback segments to "find > themselves"... :-) > > In other words, it is based on the idea that space allocation (and > deallocation) for rollback segment extents is unnecessary and harmful. > By not setting OPTIMAL, each rollback segment will grow to a "high-water > mark" (HWM) value and never shrink. Therefore, no more "overhead > processing" from extent allocation/deallocation... > > My argument against this involves space "wastage" and sharply varying > workloads. If a set of extremely large transactions (i.e. application > conversion process) runs, they will cause the RBs to grow large, and > stay large, even though the transaction mix may never approach the HWM > boundary. This is a waste of space and a potential failure condition, > as a lack of space may prevent another RBS from growing as it needs in > future. Without OPTIMAL, they will not shrink automatically -- a DBA > would have to manually intervene and shrink them back. That may be > acceptable for some folks, but if I can get the database to do something > automatically instead of me monitoring it, I'd prefer automation. > > If you prefer to minimize RBS extent allocation/deallocation, set > OPTIMAL quite high -- perhaps quite close to the HWMSIZE readings in > V$ROLLSTAT if you prefer. If you are constrained for space in the > tablespace containing rollback segments, then it is quite important to > set OPTIMAL to a lower value and simply "suffer" through the extent > allocations/deallocations necessary to conserve space. If you want to > take the responsibility for monitoring and shrinking RBSs in this > situation manually, well, then to each their own... > > Now, we get to the discussion of just how "onerous" extent > allocation/deallocations are. First of all, such a determination should > be made empirically by monitoring wait-events, V$ROLLSTAT, and > V$WAITSTAT. If nothing there indicates a problem with the > allocation/deallocation of extents, then why would we worry? Obviously, > there are conditions when using DICTIONARY-managed tablespaces where it > can be a concern, but even they are easy to fix, once and for all. If > you put your rollback segments in a UNIFORM-type LOCAL-managed > tablespace, then any concerns about "overhead" from extent > allocations/deallocations are surely unfounded. > ----- Original Message ----- > From: Fink, Dan > To: Multiple recipients of list ORACLE-L > Sent: Thursday, June 27, 2002 9:13 AM > Subject: Rollback OPTIMAL setting > > > One of the constant comments regarding rollback segments is not to > set optimal. I am wondering why this setting is often discouraged. I > have my own ideas, but I want to gather more opinions and experiences. > Daniel W. Fink > Sr. Oracle DBA > MICROMEDEX > 303.486.6456 > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Stephen Andert > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > 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.com > -- > Author: Deshpande, Kirti > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > 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.com -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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).
