How to Analyze the performance of "for loop" ?

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 Fri Dec 07 2001 - 13:56:49 CST