Oracle PL/SQL Tutorial/PL SQL Statements/Timing a Statement

Timing a loop

SQL> CREATE TABLE myTable (
  2       c1     NUMBER NOT NULL,
  3       c2     VARCHAR2(30) NULL,
  4       c3     DATE NULL
  5       );
Table created.
SQL>
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> EXECUTE test_time;
5 seconds
PL/SQL procedure successfully completed.
SQL> SELECT * FROM myTable
  2  WHERE  c1 > 5000;

        C1 C2                             C3
---------- ------------------------------ ---------
      5001        1000 records per second 03-JUN-07
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL>

Timing loop in another loop

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.