Re: Rollback OPTIMAL setting

I prefer to have OPTIMAL set and deal with "long running large transactions" another way - setting "OPTIMAL" on the developers! Granted, this doesn't work for 3rd party products, but I usually deal with in-house applications. Getting this to work requires: (1) having enough rollback segments, (2) large enough rollback segments that extends almost never occur, and (3) NOT having the "one giant rollback segment for large jobs", and, most importantly, (4) all "batch-lookin' thingies" have to be reviewed by the DBA and optimized, by either the DBA or the developer - or both, until they pass muster. Using this, I rarely seen an ORA-01555 or a rollback shrink (over six months on the systems I'm working with now) - and when I do, I know that it (usually) means that somebody broke rule #(4). When that happens, I track it down and work with the developer to fix it. My experience is that the vast majority of developers are receptive - they actually like to have their code run without problems.

Of course, this assumes that the "wasted" disk space for (1) and (2) is not a significant issue. It rarely is, but I work almost exclusively with
(essentially) OLTP systems where space is typically less critical than I/O
throughput capability and reliability. Usually, we have to add disks for performance rather than space. The database datafile drives are typically 36 GB (less often 18 GB) and are rarely "full", so a GB or two more for rollback tablespace is OK. If I had to operate under the space constraints that many seem to have, I might not set OPTIMAL either.

I am looking forward to the day that this "no OPTIMAL" suggestion fades into the "myths and folklore" category. Either because of basic policy changes
(e.g. the extents myth), a better Oracle algorithm (e.g. 10i system managed
undo???), more intelligently designed batch processes in applications, or the trend towards ever-increasing drive size.

Note: All this doesn't mean that I don't understand why so many use the "manual shrink" method. My philosophy differs in that not setting OPTIMAL should be a last resort, not a blanket policy.

Don Granaman
[OraSaurus]

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, June 27, 2002 10:13 AM

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: Don Granaman
  INET: granaman_at_cox.net

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