Re: -1555-ORA-01555: snapshot too old: rollback segment number 7 with name "_SYSSMU7$" too small

spremuta_at_gmail.com wrote:
> xhoster_at_gmail.com ha scritto:
>
> > "Vladimir M. Zakharychev" <vladimir.zakharychev_at_gmail.com> wrote:
> > > Sybrand Bakker wrote:
> > > >
> > > > ALL that lines.
> > > > Better still : get rid of the entire for loop (as it fetches record by
> > > > record) and convert this mess into a proper
> > > > INSERT
> > > > SELECT statement
> > > >
> > > > and forget about it.
> > > >
> > >
> > > Couldn't say better. :)
> >
> > Me neither.
> >
> > > Not only will this get rid of ORA-1555, but you
> > > will find that the whole process completes faster and uses less
> > > resources. My suggestion about changing undo_retention was made in
> > > assumption that you do NOT commit inside the loop over a cursor, which
> > > you *never* should do in Oracle unless you want to make sure you will
> > > get ORA-1555 at some point. Changing undo_retention will not help in
> > > this case.
> >
> > Why won't it help in this case (other than for the reason that once the
> > obvious change is made there would no longer be a need for help)?
> > Committing in a fetch-loop is surely an effective way to stomp on redo
> > which you will later need, but I see no reason to think it is qualitatively
> > different than all other methods of achieving the same inglorious result.
> >
> > In fact, I've seen 1555's disappear merely by changing undo_retention in a
> > situation (unlike this one) where it couldn't easily be rewritten as an
> > insert into...select and where removing the commit from the loop would have
> > been rather inconvenient.
> >
>
>
>
> I don't know if an Insert is more efficient than a Fetch regarding
> tables that contains 80-90 milions of records, i haven't create the
> pl/sql script i posted, but i can change it if surely an insert is
> better...

If you want to know, you should understand what is happening. Tom Kyte explains in his books why commit in a loop is bad, and also talks about it on asktom.oracle.com. Search for ORA-1555 there. He also explains somewhere (I forget where) why using SQL where possible is better than PL loops - partly because it necessitates handling everything in sets so the SQL engine can optimize everything better.

Steve Adams also has an older article about commits (some details on how it works may have changed on newer versions, but still a good article): http://www.ixora.com.au/newsletter/2001_09.htm

jg

-- 
@home.com is bogus.
http://www.archive.org/web/hardware.php