Re: How to Analyze the performance of "for loop" ?

Connor,

I agree with you that a straight update can solve the performance problem.
In fact, I have the following update completed in just less than 20 minutes.

UPDATE p_types t

       SET t.p_eff_date =
           (select p_eff_date from p
                   where t.p_id = p_id);

But for some reason the original goal is to commit the change every 10000 update (see the logic in "for" loop).

Regards,
Vance

"Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message news:<9urhek$m6f$1_at_newsg2.svr.pol.co.uk>...
> A straight update would have been the way to go...
>
> update ( SELECT p.p_id, p.p_eff_date new_date, p_types.eff_date old_date
> FROM p, p_types
> WHERE p_types.p_id = p.p_id )
> set old_date = new_date;
>
> hth
> connor
>
>
>
>
> "Vance Wu" <vwu_at_anacomp.com> wrote in message
> news:c3d4638a.0112071156.49bd0085_at_posting.google.com...
> > Hi,
> >
> > I have a segment of script to update table p_types:
> >
> > DECLARE
> > CURSOR c_p IS
> > SELECT p_id, p_eff_date
> > FROM p
> > ORDER BY p_id;
> >
> > rec_p c_p%ROWTYPE;
> > v_count number := 0;
> >
> > BEGIN
> > FOR rec_p IN c_p LOOP
> >
> > UPDATE p_types
> > SET p_eff_date = rec_p.p_eff_date
> > WHERE p_id = rec_p.p_id;
> >
> > v_count := v_count + 1;
> >
> > IF v_count >= 10000 THEN
> > v_count := 0;
> > COMMIT;
> > END IF;
> >
> > END LOOP;
> >
> > COMMIT;
> > END;
> > /
> >
> > The p table has approximately 900 thousand rows and p_types table have
> > 1.7 million rows, the scrip is supposed to update all rows in table
> > p_types, but after 18 hours the script is still running, I have no
> > choice to terminate the execution, then ran a sql to find out how far
> > it has been completed and discovered only 1% is completed.
> >
> > If I execute the same script on other development system (database),
> > it took only 20 minutes, of course, they are different production
> > release, and amount of data can be different too, I mean the script
> > may not be badly written.
> >
> > Since the iteration of the "for loop" has approximately 900 thousand,
> > and the UPDATE statement made use of the cursor's result, can someone
> > tell me how can I analyze the cost of the "for loop" vs UPDATE
> > statement using explain plan, or is there any better way to analyze
> > this "for loop".
> >
> > OS is Sun Solaris 2.6
> > Oracle Enterprise Server is 8.1.6
> > Optimizer is set to Cost-Based
> >
> > Thanks,
> > Vance.
Received on Sat Dec 08 2001 - 00:34:31 CST