Timing loop in another loop : Timing a Statement « PL SQL Statements « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Statements
3. Timing a Statement

SQL>
SQL>
SQL> CREATE TABLE myTable (
  2       c1     NUMBER NOT NULL,
  3       c2     VARCHAR2(30) NULL,
  4       c3     DATE NULL
  5       )
  6  ;

Table created.

SQL> CREATE OR REPLACE PROCEDURE test_time IS
  2       maxloops NUMBER := 5000;
  3       loopcount NUMBER(6,0) := 0;
  4       starttime CHAR(5) ;
  5       endtime CHAR(5) ;
  6
  7       runtime NUMBER;
  8       processrate NUMBER(20,10);
  9  BEGIN
 10       starttime := TO_CHAR(SYSDATE,'SSSSS');
 11       LOOP
 12            loopcount := loopcount +1;
 13            INSERT INTO myTable (C1, C2,C3)
 14            VALUES (loopcount, 'TEST ENTRY', SYSDATE);
 15            COMMIT;
 16            IF loopcount >= maxloops THEN
 17                 EXIT;
 18            END IF;
 19       END LOOP;
 20       COMMIT;
 21       endtime := TO_CHAR(SYSDATE,'SSSSS');
 22       runtime := TO_NUMBER(endtime)-TO_NUMBER(starttime);
 23       dbms_output.put_line(runtime || ' seconds' );
 24       processrate := maxloops / runtime;
 25       INSERT INTO myTable (C1, C2, C3) VALUES
 26            (loopcount+1,
 27            TO_CHAR(processrate, '9999999999')||' records per second',
 28            SYSDATE
 29            );
 30  END test_time;
 31  /

Procedure created.

SQL> SET SERVEROUTPUT ON
SQL> BEGIN
  2      FOR trial_count IN 1..10
  3      LOOP
  4          test_time;
  5          COMMIT;
  6      END LOOP;
  7  END;
  8  /
4 seconds
3 seconds
4 seconds
4 seconds
3 seconds
4 seconds
3 seconds
3 seconds
4 seconds
4 seconds

PL/SQL procedure successfully completed.

SQL> SELECT   *
  2  FROM     myTable
  3  WHERE    c1 > 5000
  4  ORDER BY c3;


        C1 C2                             C3
---------- ------------------------------ ---------
      5001        1250 records per second 03-JUN-07
      5001        1667 records per second 03-JUN-07
      5001        1250 records per second 03-JUN-07
      5001        1250 records per second 03-JUN-07
      5001        1667 records per second 03-JUN-07
      5001        1250 records per second 03-JUN-07
      5001        1667 records per second 03-JUN-07
      5001        1667 records per second 03-JUN-07
      5001        1250 records per second 03-JUN-07
      5001        1250 records per second 03-JUN-07

10 rows selected.

SQL> drop table myTable;

Table dropped.