Re: Rollback OPTIMAL setting

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

>>> Tim_at_SageLogix.com 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: StephenAndert_at_firsthealth.com

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: ListGuru_at_fatcity.com (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).