Prev: Oracle sql completed with warnings...
Next: Client Connection Error (TNS-12518 TNS-12560 TNS-00530)
From: Vladimir M. Zakharychev on 12 Sep 2006 16:01

Sybrand Bakker wrote:
> On 12 Sep 2006 07:56:22 -0700, spremuta(a)gmail.com wrote:
>
> >do i have to change script in the lines:
> >IF CONTATORE_COMMIT = FREQUENZA_COMMIT
> > > THEN CONTATORE_COMMIT := 1;
> > > COMMIT;
> > > END IF;
> >?
> >deleting only commit line or all that lines in your opinion?
>
> 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. :) 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. Rewriting this PL/SQL code into single SQL INSERT statement
will.

Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

From: xhoster on 12 Sep 2006 16:35
"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.

Xho

--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service $9.95/Month 30GB
From: spremuta on 13 Sep 2006 04:45

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...

From: Vladimir M. Zakharychev on 13 Sep 2006 07:35

xhoster(a)gmail.com wrote:
> "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.
>
Well, I didn't mean it won't make 1555 go away (though that's not
guaranteed anyway,) but associated price is too high while fixing the
code in this particular case is cheap and efficient. Besides,
committing in a fetch-loop forces you to code for incomplete
processing. That is, you need to think about, and code for, situations
when the loop is broken halfway for any reason, from an unexpected
error to deliberate session kill, and you now have part of your data
processed (and committed!) and part not - how do you restart the
process so that it picks up where it left? This means more work for you
implementing and for Oracle executing.

Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

From: Sybrand Bakker on 13 Sep 2006 14:18
On 13 Sep 2006 01:45:54 -0700, spremuta(a)gmail.com wrote:

>
>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...

Look at it this way:
the FETCH approach gets 80-90 million records in buffer cache, and
transmitted to your pga.
The INSERT SELECT approach transmits 0 records to your PGA.
Which one is faster?

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