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).
