Prev: Oracle sql completed with warnings...
Next: Client Connection Error (TNS-12518 TNS-12560 TNS-00530)
From: hpuxrac on 13 Sep 2006 15:26

spremuta(a)gmail.com wrote:
> xhoster(a)gmail.com ha scritto:
>
> > "Vladimir M. Zakharychev" <vladimir.zakharychev(a)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...

Take Vladimir's advice in this thread and fix the code as suggested.

From: joel garry on 13 Sep 2006 16:39

spremuta(a)gmail.com wrote:
> xhoster(a)gmail.com ha scritto:
>
> > "Vladimir M. Zakharychev" <vladimir.zakharychev(a)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

First  |  Prev  | 
Pages: 1 2 3 4
Prev: Oracle sql completed with warnings...
Next: Client Connection Error (TNS-12518 TNS-12560 TNS-00530)