Re: rollback segment too old

From: Christopher Merry <merryct_at_constructingbits.com>
Date: Fri, 21 Jun 2002 01:43:46 -0700
Message-ID: <uh5pq1o9amp351@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).

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 - 03:43:46 CDT