Re: rollback segment with no optimal question

"Anna C. Dent" <anacdent_at_hotmail.com> wrote in message news:<soOvb.14103$kl6.4418_at_fed1read03>...
> anu wrote:
>
> > Hi,
> > I have rollback segment with no optimal set. the rollback tablespace
> > has a lot of space. It is a OLTP system with many but small
> > transactions. So I expect no shrinking. However for one report that
> > runs around 20 minutes we are getting ora-01555 snapshot too old. the
> > intial and next are set to 1M which I think is OK for OLTP transaction
> > size. There are many rollback segments = 40. So how do I go about
> > increasing the size or the number and on what basis? Thank you
>
> What make you conclude the solution is "increasing the size or the
> number" of rollback segments?
>
> Plz post your code.
> What else is running while the report runs.
> A report (which ONLY reads blocks) in and of itself
> can NOT generate an ORA-01555 error!
> Something external to the report is the real culprit.

Misleading. The culprit is that the report requires read consistency from the time it starts. It's not "something" that creates the problem, it is the combination of all transactions that burn through the rollback segments before the report has gotten everything it needs. Posting the code would only do good on the off chance there is something obviously wrong with it that fixing it would shorten the run time enough to remove the problem. But even that is assuming that a 20 minute report is something unlikely to happen, which I think is a bad assumption.

Don't you think that 40 1M rollback segments is going to cycle way too fast on an OLTP system that also has long running transactions like reports? Don't you think there should be enough rollback for 20 minutes worth of transactions?

>
> go visit http://asktom.oracle.com
> search for "ORA-1555" & then read
> and learn about this particular error.

Besides DLB, of course.

jg

--
@home.com is bogus.
http://www.comics.com/comics/franknernest/archive/franknernest-20031125.html