Mega Code Archive

 

Categories / Oracle PLSQL / System Packages

 

Timing Per Thousand Records Processed (in secs)

SQL> SQL> SQL> DECLARE   2     lv_counter_num        PLS_INTEGER := 0;   3     lv_timer_start_num    NUMBER;   4     lv_timer_previous_num NUMBER;   5     lv_timer_current_num  NUMBER;   6  BEGIN   7     lv_timer_start_num    := DBMS_UTILITY.GET_TIME;   8     lv_timer_previous_num := lv_timer_start_num;   9     LOOP  10        lv_counter_num := lv_counter_num + 1;  11        IF MOD(lv_counter_num, 1000) = 0 THEN  12           lv_timer_current_num := DBMS_UTILITY.GET_TIME;  13           DBMS_OUTPUT.PUT_LINE('Time Elapsed-Total: ' ||  14              (lv_timer_current_num - lv_timer_start_num)/100 ||  15              CHR(9) || ' This Set: ' ||  16              (lv_timer_current_num - lv_timer_previous_num)/100 ||  17              CHR(9) || ' Records Processed: ' ||  18              lv_counter_num);  19           lv_timer_previous_num := lv_timer_current_num;  20         END IF;  21         EXIT WHEN lv_counter_num = 5540;  22     END LOOP;  23     lv_timer_current_num := DBMS_UTILITY.GET_TIME;  24     DBMS_OUTPUT.PUT_LINE('Time Elapsed-Total: ');  25     DBMS_OUTPUT.PUT_LINE(lv_timer_current_num - lv_timer_start_num );  26     DBMS_OUTPUT.PUT_LINE(' This Set: ' );  27     DBMS_OUTPUT.PUT_LINE(lv_timer_current_num - lv_timer_previous_num);  28     DBMS_OUTPUT.PUT_LINE(' Records Processed: ' ||lv_counter_num);  29  END;  30  / Time Elapsed-Total: 0    This Set: 0     Records Processed: 1000 Time Elapsed-Total: 0    This Set: 0     Records Processed: 2000 Time Elapsed-Total: 0    This Set: 0     Records Processed: 3000 Time Elapsed-Total: .01  This Set: .01   Records Processed: 4000 Time Elapsed-Total: .01  This Set: 0     Records Processed: 5000 Time Elapsed-Total: 1  This Set: 0  Records Processed: 5540 PL/SQL procedure successfully completed. SQL>