In article <378b64ec.0_at_news2>,
"S. Anthony Sequeira" <Sequeira_at_telinco.co.uk> wrote:
> markp7832_at_my-deja.com wrote in message <7lvqss$f89
$1_at_nnrp1.deja.com>...
> [large snip]
> >If your process is commiting as you go then you could be overlaying
the
> >data you need yourself. Decreasing the frequency of commits may be
> >beneficial in this case.
> >
> Mark,
>
> I am intrigued by the above. Could you satisfy my curiosity
and
> explain why frequent commits would overlay rollback segment data?
>
> TIA.
> >--
> >Mark D. Powell -- The only advice that counts is the advice that
> > you follow so follow your own advice --
> >
> >
> >Sent via Deja.com http://www.deja.com/
> >Share what you know. Learn what you don't.
>
> Tony
> --
> S. Anthony Sequeira
>
>
This a simplified explanation of what happens:
Rollback segments are used in a circular fashion. Assume that there
are 10 extents allocated to the rollback segment and your transaction
starts in block one and modifies and commits enough data to fill block
10. Now when you change more information Oracle goes back to extent
1. If there is no in-progress (uncommited) transaction in the block
then Oracle will reuse extent 1 overlaying the information stored in
the extent. Now since you are in this case using an index to find the
rows you want to process your application now fetches a row in a block
that you have changed before. The original, rollback, information
about how this block looked was stored in block 8 of extent 1 which was
just overlayed by our transaction. Hence we create our own 'snapshot
too old' error. If we were not commiting then when our transaction
filled extent 10 and made more changes, Oracle would have allocated
another extent to the rbs segment up to max_extents if space was
available in the tablespace. The problem with this is that any other
transaction that attempts to update a row in the target table of our
transaction, is probably going to end up waiting on our transaction
since we will have a lock on every row we have changed. On a heavy hit
table this is unacceptable. Also a large table will consume a large
amount of rollback and that is another management issue. There are
several bulletins from Oracle on rollback segment management.
--
Mark D. Powell -- The only advice that counts is the advice that
you follow so follow your own advice --
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
Received on Wed Jul 14 1999 - 09:44:25 CDT
