And I should by no means add that you should not commit too often as
commits effectively allow rollback space to be reused. Commit only when
it is logically sound (for example, when you're done with your batch of
inserts and updates). Committing often is not a way to reduce rollback
space usage, it's a way to introduce 1555 errors. Just allocate enough
rollback for your whole transaction (if it's a batch load, then it's sound
to create one big rollback segment that can fit the transaction and
commit;
set transaction use rollback segment my_big_rbs;
run batch; -- and don't commit there!
commit;
alter rollback segment my_big_rbs offline;
If fact, that segment gotta be a bit bigger than big, because other
transactions may well use it either while you run the batch.)
--
Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.
"Knut Talman" <knut.talman_at_mytoys.de> wrote in message news:3D1330A2.7738BED4_at_mytoys.de...
> "Howard J. Rogers" wrote:
> >
> > "Knut Talman" <knut.talman_at_mytoys.de> wrote in message
> > news:3D130468.826B5A8A_at_mytoys.de...
> > > Pinto wrote:
> > >
> > > > if you can see, the wraps have more than doubled. and as expected i am
> > still
> > > > getting ora-1555
> > >
> > > Not only the size of a rollback segment can become a bottleneck also the
> > number
> > > of rbs is important. Every transaction has to use the header of the rbs to
> > enter
> > > its information. If there are no slots left (in the header) you'll get
> > > ORA-01555, even if there are a lot of free rollback segment blocks.
> > >
> >
> > Er, no... you'd end up with horrendous amounts of buffer busy waits, but
> > that's about it. The 1555s you are thinking of is because of the delayed
> > block cleanout mechanism. It's when the transaction slot on a *normal data
> > block* is re-used, thus preventing retrieval of rollback information needed
> > for a consistent read, and hence producing a 1555.
>
> Of course, I mixed it up. What I wanted to say (after rethinking) was:
>
> After a transaction has commited, the rollback block it has used can be
> released, also the transaction slot can be overwritten. If a read request that
> started a long time ago finally reaches the data block after this has happened
> and tries to find the relevant rollback it may fail, because the tranaction
> table entry can not be rebuilt -> ORA-01555. Even if it can be rebuilt but
> points to an already overwritten rollback block -> ORA-01555.
> To reduce the rate at which slots in the segment header transaction tables are
> recycled you have to have plenty of rollback segments. To reduce the rate at
> which rollback information is overwritten, the rollback segments have to be
> large enough.
> Conclusion: The only way to reduce or avoid ORA-01555 is to have enough rollback
> segments, which are large enough. Often it is a trial and error process to find
> the best values.
>
> Regards,
>
> Knut
Received on Fri Jun 21 2002 - 11:06:55 CDT
