measure time of an sql statement in a procedure in plsql

Question

I must write a procedure which save the execute time of any sql-statement in a table.

The procedure is calling by exec measuresqltime('sql statement as string');

My idea is like this:

  --declarations 
  timestart NUMBER;
  BEGIN 
    dbms_output.enable; 
    timestart:=dbms_utility.get_time(); 
    EXECUTE IMMEDIATE sql
    COMMIT; 
    dbms_output.put_line(dbms_utility.get_time()-timestart); 
    -- save time

But it didn't work for me for a SELECT *... clause. (I think sql need a INTO-order)

Is there a way to execute any sql-atatements in a procedure?

score 16 · Answer 1 · answered Jan 20 '12 at 13:22

If your SQL statement is a SELECT, you need to fetch from the cursor to have a meaningful measure of its execution time.

If you don't fetch from the cursor, you only measure the time spent in "parse" and "execution" phases, whereas much of the work is usually done in the "fetch" phase for SELECT statements.

You won't be able to fetch with EXECUTE IMMEDIATE or OPEN cursor FOR 'string' if you don't know the number of columns the actual statement will have. You will have to use the dynamic SQL package DBMS_SQL if the number/type of columns of the SELECT is unknown.

Here's an example:

SQL> CREATE OR REPLACE PROCEDURE demo(p_sql IN VARCHAR2) AS
  2     l_cursor  INTEGER;
  3     l_dummy   NUMBER;
  4     timestart NUMBER;
  5  BEGIN
  6     dbms_output.enable;
  7     timestart := dbms_utility.get_time();
  8     l_cursor  := dbms_sql.open_cursor;
  9     dbms_sql.parse(l_cursor, p_sql, dbms_sql.native);
 10     l_dummy := dbms_sql.execute(l_cursor);
 11     LOOP
 12        EXIT WHEN dbms_sql.fetch_rows(l_cursor) <= 0;
 13     END LOOP;
 14     dbms_sql.close_cursor(l_cursor);
 15     dbms_output.put_line(dbms_utility.get_time() - timestart);
 16  END;
 17  /

Procedure created.

SQL> exec demo('SELECT * FROM dual CONNECT BY LEVEL <= 1e6');
744

PL/SQL procedure successfully completed.

Note that this will measure the time needed to fetch to the last row of the SELECT.

hey, that looks great :) but it only works fpr select statements isn't it? Is there a option o run delete or insert-statements? — sheepy, Jan 24 '12 at 14:41
@sheepy: You can't fetch a DML cursor (INSERT/UPDATE...) so the line 12 will raise an error in that case. Catch the exception and the procedure will work for both DML and SELECT queries. — Vincent Malgrat, Jan 24 '12 at 15:02
dbms_utility.get_time can't be trusted and you should use systimestamp instead, see https://asktom.oracle.com/pls/asktom/asktom.search?tag=how-true-is-the-dbms-utilityget-time-function — Superdooperhero, May 08 '18 at 11:36
@Superdooperhero That's not what Tom Kyte suggests at all! He suggests replacing `SYSDATE` with `SYSTIMESTAMP` to have greater precision. He still uses `dbms_utility.get_time` [in his suggested code](https://asktom.oracle.com/pls/asktom/asktom.search?tag=how-true-is-the-dbms-utilityget-time-function) :) — Vincent Malgrat, May 08 '18 at 15:38

score 4 · Answer 2 · answered Apr 16 '15 at 14:41

completing devosJava answered... avoid using it at dawn ;P

PROCEDURE MY_PROCEDURE IS
  timeStart  TIMESTAMP;
  timeEnd    TIMESTAMP;
  timeSecond NUMBER
BEGIN
  timeStart  := SYSTIMESTAMP;

  -- YOUR CODE HERE

  timeEnd    := SYSTIMESTAMP;
  timeSecond :=((extract(hour from timeEnd)*3600)+(extract(minute from timeEnd)*60)+extract(second from timeEnd))-((extract(hour from timeStart)*3600)+(extract(minute from timeStart)*60)+extract(second from timeStart));
  dbms_output.put_line('finished: '||timeSecond||' seconds');
END MY_PROC;

`timeSecond := extract(second from timeEnd - timeStart);` – Roland May 22 '15 at 16:47 — Roland, May 22 '15 at 16:47

score 1 · Answer 3 · edited Aug 26 '13 at 07:38

1

To calculate the duration for an execution time

PROCEDURE MY_PROCEDURE IS
  timeStart  TIMESTAMP;
  timeEnd    TIMESTAMP;
BEGIN
  timeStart  := SYSTIMESTAMP;

  -- YOUR CODE HERE

  timeEnd    := SYSTIMESTAMP;

  INSERT INTO PROC_RUNTIMES (PROC_NAME, START_TIME, END_TIME)
    VALUES ('MY_PROCEDURE ', timeStart  , timeEnd    );
END MY_PROC;

edited Aug 26 '13 at 07:38

hol

8,255
5
33
59

answered May 30 '13 at 12:11

devosJava

263
1
3
12

score 0 · Answer 4 · answered Dec 07 '15 at 13:57

INSERT INTO PROC_RUNTIMES (PROC_NAME, START_TIME, END_TIME) VALUES ('PROC_NAME', TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS'),NULL );

Your query here;

INSERT INTO PROC_RUNTIMES (PROC_NAME, START_TIME, END_TIME) VALUES ('PROC_NAME',NULL, TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS'));

measure time of an sql statement in a procedure in plsql

4 Answers4