Mega Code Archive
Timing loop in another loop
<![CDATA[
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
3 seconds
4 seconds
3 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 1667 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
10 rows selected.
SQL> drop table myTable;
Table dropped.
]]>