Timing Per Thousand Records Processed (in secs) : dbms_utility « System Packages « Oracle PL / SQL






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>

   
    
  








Related examples in the same category

1.This script demonstrates DBMS_UTILITY.NAME_TOKENIZE.
2.Use dbms_utility to time
3.time to commit
4.dbms_utility.get_parameter_value
5.Call dbms_utility.get_time twice to time a statement in PL SQL
6.round dbms_utility.get_time
7.Assign dbms_utility.get_time to integer variable
8.Use dbms_utility.get_time to do performace check
9.dbms_utility.format_error_stack
10.Timing Package function call
11.use DBMS_UTILITY.FORMAT_ERROR_STACK in a SERVERERROR trigger.
12.DBMS_UTILITY.analyze_schema
13.demonstrates the use of DBMS_UTILITY.TABLE_TO_COMMA and DBMS_UTILITY.COMMA_TO_TABLE.
14.Performace difference between simple parameter and collection parameter