Oracle-L: Re: Rollback OPTIMAL setting

From: Tim Gorman <Tim_at_SageLogix.com>
Date: Thu, 27 Jun 2002 10:03:37 -0800
Message-ID: <F001.0048AA19.20020627100337@fatcity.com>

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: Tim Gorman
INET: Tim_at_SageLogix.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). Received on Thu Jun 27 2002 - 13:03:37 CDT