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

Vance Wu wrote:
>
> 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.

Look into array processing.

-- 
Ron Reidy
Oracle DBA
Reidy Consulting, L.L.C.