Timing a 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> 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>