Re: Rollback OPTIMAL setting

cool! thanks!

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> 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
>
> >>> 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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Deshpande, Kirti
> INET: kirti.deshpande_at_verizon.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).

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