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

> 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.
> But for some reason the original goal is to commit the change every
> 10000 update (see the logic in "for" loop).

• Did you question the validity and reason for this goal ?

> > > p_types, but after 18 hours the script is still running, I have no
> > > choice to terminate the execution,

--That's exactly what's bad and wrong with "commits b4 *the* transaction is complete" approach.
Now your db is in an out-of-sync state -- "half-cooked" as I say. Plus you're just begging for ORA-01555.
Transactions should be as long as they need to be, just give it a big rollback space.
If you want to speed it up further, then you should look into partitioning that table.
Given these facts I don't see what there is to analyze/compare as you want to do.
But to compare amount of redo generate, look to dv$mystat/v$statname. or you can knock yourself out with sql_trace, timed_statistics, tkprof...

Robert Chin

"Vance Wu" <vwu_at_anacomp.com> wrote in message news:c3d4638a.0112072234.4df84c9a_at_posting.google.com...
> 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);
>
>
> Regards,
> Vance
>
>
> "Connor McDonald"
> > 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 - 02:44:48 CST