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?

Answer 1

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.

Answer 2

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;