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
Received on Tue Oct 22 2002 - 09:55:18 CDT
