ora-1555 and minextents (8i)

Sorry, just another ora-1555 post :(

Im not a DBA just (another) developer having to do as parttime DBA (Guess real DBA's dont come cheap)

SYSTEM: 8.1.7.4, few online users, some batch programs that keeps querying and doing many small transactions. Sometimes larger transactions run. System runs one canned application. No DBA (smallshop). 12G rollback tablespace, LMT with 1M extents, 9 rollback segments minextents 64, optimal 128.

One large job that runs rarely, but runs 3-4 days fails with ora-1555. The job consists of many small transactions that are committed, so after an ora-1555 the customer can restart the job, and eventually it will finish after many restarts. The same job, with somewhat less data, runs fine at other similar installations.

Changing application or upgrading Oracle are not options.

So I read Tom Kyte:
http://asktom.oracle.com/pls/ask/f?p=4950:8:64103058400970425::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1441804355350,

"The ORA-1555 happens when people try to save space typically. They'll have small rollback segments that could grow if they needed (and will shrink using
OPTIMAL). So, they'll start with say 10 or so 1meg rollback segments. These
rollback segments COULD grow to 100meg each if we let them (in this example) however, they will NEVER grow unless you get a big transaction. If your database does lots of little transactions, the RBS will never grow on
their own. They will stay small."

and I decide that I need larger MINEXTENTS, to have rollback segments that start big.
Is this correct?
Is this the right thing to do?

However, when I create new rbs's with larger minextents, DBA_ROLLBACK_SEGS still says minextents is 1. This is because of the LMT, right? Should I ignore this? Can I query the actual MINEXTENTS in some other view? (Yes, storage parameters for LMT's still confuse me)

With very large minextents I also need large optimal size. This might decrease overall performence since the rollback segments will not fit in memory and thus not be cached. Right? I am afraid that if I do, say 9 1Gb rollback segments normal day performence will drop hard.

From oracle manual - managing rollback segments : "OPTIMAL should be smaller for a system that mainly executes short transactions and queries so that the rollback segments remain small enough to be cached in memory, thus improving system performance. "

TIA
Peter Laursen Received on Mon Jun 21 2004 - 17:46:41 CDT