Re: rollback segment too old

"Christopher Merry" <merryct_at_constructingbits.com> wrote in message news:uh5pq1o9amp351_at_corp.supernews.com...
> To understand the wrapping issue, lets first focus on how Oracle uses
> rollback segments. More than one transaction may use a particular
rollback
> segment. The only thing Oracle will not do is utilize a single rollback
> segment block for more than one transaction. Further more, Oracle tries
to
> re-use allocated extents within a segment rather than allocating a new
one.
> The only rule here is the head of a transaction, as it fills the extent
and
> moves on, cannot move past the tail (in other words, when wrapping).
>

Actually, a 'wrap' occurs every time we move from one extent to another. It has nothing particularly to do with the segment catching up with itself.

Regards
HJR
> In your case, I believe the reason your wraps have increased is because
your
> transactions have stayed the same size. When the transaction utilizing
the
> initial extent ends, latter transactions will "wrap" around to re-use the
> extent. Consider the following scenario. Transaction 1 has ended
clearing
> the way for transaction 3 to utilize the first extent. Ok, simplistic
> example, but you get the idea.
>
> ______________________________________________________________
> | TRANS 1 | TRANS2 = | =>TRANS2 = |=> TRANS2 | TRANS3
|
> |___(commit)_|____________|____________|___________|______________|
> /\
> |
> /__\
> |
> |________________________________________________|
>
>
> Your real issue is dealing with the snapshot too old. It sounds to me
like
> you may want to add more rollback segments to resolve this issue.
>
> Oh isn't it great working with these lovely rollback segments. Halelujah
> Oracle9i!!!
>
> cm
>
>
> "Pinto" <ucanmailme_at_india.com> wrote in message
> news:aeuebk$11a$1_at_news.mch.sbs.de...
> > hello ,
> > I know this question has been posted in this forum a lot of times and i
> have
> > read most of them from the archives but still can anybody help me out
with
> > this.
> >
> > Most of the activities on our system are short queries and updates and
> there
> > are no batch files doing doing huge updates/selects that are running.
> >
> > initially i had 10 rollback segements with 512K of initial,next and 5M
of
> > optimal. and the statistics that i used to get from v$rollstat was this
> > EXTENTS WRITES GETS WAITS OPTSIZE HWMSIZE
> SHRINKS
> > WRAPS AVESHRINK
>
> ---------- ---------- ---------- ---------- ---------- ---------- --------
> --
> > ---------- ----------
> > 6 10416 301 0 364544
> 0
> > 0 0
> > 11 93479584 303442 0 5242880 5627904
> 0
> > 212 0
> > 11 171553042 358345 20 5242880 93179904
> 18
> > 390 4864000
> > 11 93410072 346803 42 5242880 5627904
> 0
> > 213 0
> > 11 94937792 304325 2 5242880 5627904
> 0
> > 216 0
> > 11 89638294 301663 2 5242880 5627904
> 0
> > 204 0
> > 11 169077244 340748 3 5242880 100859904
> 19
> > 400 5012210
> > 11 90504356 303184 0 5242880 5627904
> 0
> > 206 0
> > 11 91587584 303244 1 5242880 5627904
> 0
> > 209 0
> > 11 118358574 315595 3 5242880 37371904
> 7
> > 276 4534857
> > 11 96496414 303315 1 5242880 5627904
> 0
> > 219 0
> >
> >
> > and i used to get the snapshot too old error
> >
> > after this i changed the rollback segment parameters to intial 1M, next
> 1M,
> > optimal 50M and minextent 20 maxextent 400
> > Isn't this supposed to reduce the wraps? but now i get these statistics
> from
> > v$rollstat
> >
> >
> > EXTENTS WRITES GETS WAITS OPTSIZE HWMSIZE
> SHRINKS
> > WRAPS AVESHRINK
>
> ---------- ---------- ---------- ---------- ---------- ---------- --------
> --
> > ---------- ----------
> > 6 9750 300 0 364544
> 0
> > 0 0
> > 20 223812724 631736 1 52428800 21295104
> 0
> > 534 0
> > 20 223707032 673188 29 52428800 21295104
> 0
> > 534 0
> > 20 224711684 632845 2 52428800 21295104
> 0
> > 537 0
> > 50 310731946 663666 2 52428800 102510592
> 5
> > 637 9859891
> > 50 223743564 631558 0 52428800 53243904
> 0
> > 534 0
> > 20 224872176 632430 2 52428800 21295104
> 0
> > 538 0
> > 50 223026560 629640 1 52428800 53243904
> 0
> > 532 0
> > 20 223153556 628883 1 52428800 21295104
> 0
> > 532 0
> > 50 223274594 654880 17 52428800 53243904
> 0
> > 533 0
> > 50 351965640 690660 3 52428800 121401344
> 7
> > 688 9736777
> >
> >
> > if you can see, the wraps have more than doubled. and as expected i am
> still
> > getting ora-1555
> >
> > what should i do for this.
> >
> > warm regards,
> > Pinto.
> >
> >
>
>
Received on Fri Jun 21 2002 - 06:34:51 CDT