Mega Code Archive
Timing a loop
<![CDATA[
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>
]]>