Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Why ORA-1555 snapshot too old.

Re: Why ORA-1555 snapshot too old.

From: Daniel Morgan <dmorgan_at_exesolutions.com>
Date: Tue, 22 Oct 2002 16:09:58 GMT
Message-ID: <3DB57845.B7BCD4D5@exesolutions.com>


Chuck wrote:

> I agree with everything you said below but the error is still not making
> sense to me unless Oracle is not treating each execution of the select as a
> different transaction. Remember the one big query was broken up into many
> much smaller queries inside a PL/SQL loop. Each one runs in perhaps 30
> seconds. Does Oracle treat all SQL executed by PL/SQL as part of one
> transaction because it is done under the control of PL/SQL? If so would
> switching the selects to "execute immediate 'select...';" help? I thought I
> remember reading something about a context switch occurring when you do
> "execute immediate" and that the SQL engine rather than the PL/SQL engine
> runs the query then. I could be wrong about this as I do very little
> programming in PL/SQL and it may have even been from an older version of
> Oracle.
> --
> Chuck
>
> "Sybrand Bakker" <gooiditweg_at_sybrandb.demon.nl> wrote in message
> news:17s1ru005ei9gsk70vnl6m9io1iq161d6u_at_4ax.com...
> > On Fri, 18 Oct 2002 22:50:54 GMT, Richard Kuhler <noone_at_nowhere.com>
> > wrote:
> >
> > >I must admit I don't understand your answer. The poster stated that "I
> > >know the RBS is not wrapping back on itself in that short of a time and
> > >it is not shrinking either." However, you seem to be implying that
> > >committing records individually makes this a mute point (i.e. no matter
> > >how big the rollback was you couldn't stop ora-1555 in this
> > >situation?). Can you please expound on your answer a little more?
> > >
> > >Richard Kuhler
> >
> >
> > The issue here is read-consistency. The select will use a
> > read-consistent image to get results. Whatever is currently being
> > changed is going to be retrieved from the rollback segment. The select
> > will try to reconstruct a read-consistent image to the point in time
> > *the select started*.
> > In the mean time, a different process has committed a transaction,
> > which started before the select fired. A commit means that process
> > releases the rollback data, and the rollback data can be overwritten
> > by any other transaction. However the select you are firing still
> > needs it to reconstruct that read-consistent image. Once it discovers
> > it can't find that record(s) anymore because the other transaction has
> > been committed it will result in an ora-1555.
> > There are two solutions to this problem:
> > 1 stop committing every individual record. Most people committing
> > every individual record do this because they want to avoid the
> > rollback segment grow. Now this is silly as they are splitting up
> > their *logical* transaction in smaller *physical* transactions, at the
> > same time increasing the redolog volume (because every transaction has
> > overhead)
> > 2 Increase the rollback segments in size, or stop using optimal, in
> > order to make sure committed data ages out of the rollback segment in
> > a lower tempo.
> >
> > Hth
> >
> >
> > Sybrand Bakker, Senior Oracle DBA
> >
> > To reply remove -verwijderdit from my e-mail address

A transaction starts with the first DML statement and ends with either COMMIT or ROLLBACK. In a loop you can any darn thing you want ... and it is one transaction until you end it.

Which does not mean that the solution is to put COMMIT into your loop. That is almost always a bad idea. Far better to increase the size of your rollback segments.

Daniel Morgan Received on Tue Oct 22 2002 - 11:09:58 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US