в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:05:38Z

1 версия

Новая страница

== Timing a loop==



<source lang="sql">
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></source>

== Timing loop in another loop==



<source lang="sql">
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.</source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/PL SQL Statements/Timing a Statement - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия