CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
destiny
destiny

Reputation: 1862

How execute a commited sql-statement (as parameter) in a procedure and measure the execute time?

I want to write a function in PL/SQL, which has a sql-statement as parameter. The statement can be DDL,DML and DCL

I want to print the time from the executed statement in the console.

I have following code:

 create or replace procedure measureTime (statement  IN varchar2 )AS 
--declarations
neededTime INTEGER;
  BEGIN 
    dbms_output.enable; 
    EXECUTE IMMEDIATE statement
 COMMIT; 
SELECT ELAPSED_TIME AS neededTime FROM V_$SQL WHERE SQL_TEXT=statement
    dbms_output.put_line('executeTime '|| neededTime);
 END measureTime;

But it doesn't work. What's wrong?

Upvotes: 0

Views: 274

Answers (1)

Ben
Ben

Reputation: 52923

There's a simpler way to do it; simply take the start time before you start and take that off the current time when you've finished.

 create or replace procedure measureTime (statement  IN varchar2 )AS 

   l_start_time timestamp default systimestamp;

  BEGIN

    dbms_output.enable;

    EXECUTE IMMEDIATE statement;
    COMMIT; 

    dbms_output.put_line('executeTime '|| systimestamp - l_start_time);

  END measureTime;

Alternatively if you want it in seconds say change systimestamp - l_start_time to trunc((systimestamp - l_start_time) * 24 * 60 * 60)

This does seem like an over-worked way to go about things. execute immediate will be slightly slower as Oracle has to validate the query before it can run each time. Why are you not just executing the query?

The commit will also commit everything that has happened in that session, which may not be what you want.

You should also ensure that you have escaped your statement properly to avoid SQL injection.

In answer to your question:

Your procedure wouldn't have compiled as you needed a semi-colon after your execute immediate. This should not be included in the parameter statement.

Your syntax for your select is also incorrect. As this is PL/SQL and not SQL you need to use the into clause and you were missing a semi-colon at the end of this statement as well.

select elapsed_time 
  into neededTime 
  from v$sql 
 where sql_text = statement;

Even without syntax errors there are several reasons your statement might not work for instance, sql_text in v$sql only has the first 1,000 characters, so if statement is more than 1k characters long there would be no match. If you have a group by clause, according to the documentation, statistics are not stored.

Upvotes: 5

Related Questions