Re: rollback segment problem

In the case of a long-running query, increasing commits - that is, doing interim commits - will be counter-productive. You need to increase the number and amount of rollback segments to accomodate long queries, especially during periods when other transactions are making changes to the database. Also, make sure that all rollback segments are the same size.

We get confused on that point, thinking that a huge rollback segment is needed to accomodate a long query. The undo that occurs to give you read consistency on a query comes from the already-generated undo residing in the rollback segment containing the committed or uncommited transaction whose work you need to roll back. ORA-01555 is caused by either missing undo blocks or an overwritten transaction entry (in the undo header, or "transaction table"). Consequently, a balance is needed between undo data blocks and undo headers. Keeping all rollback segments the same allows Oracle to reasonably balance transaction load and better preserver transaction history for accomodating long-running queries.

Roger Snowden

Luiz Alberto Ferreira Gomes wrote:
>
> Hi,
> You should do the follows things:
>
> 1) Increase de rollback size;
> 2) Increase commits during transactions;
> 3) Reduce number of rows during the process;
> 4) Add rollback segments;
> 5) Avoid fetch across commits;
>
> luizgoms_at_sucesumg.org.br
>
> daud11_at_hotmail.com wrote:
>
> > Hi All
> >
> > I have a query which always fail with the following msg: Oracle Error 1555:
> > ORA 01555: Snapshot too old: Rollback segment .... too small.
> >
> > What should I do? I have tried to increase the optimal size to 15M from 10M
> > for all the rollback segments but still does not solve the problem.
> >
> > Any advice?
> >
> > Daud
> >
> > -----------== Posted via Deja News, The Discussion Network ==----------
> > http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Sat May 22 1999 - 09:49:45 CDT