Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a set of queries that I need to run in succession, carrying the result from the previous query into the next. My only need is the execution time on the last query in the list, but SET TIMING ON gives me the execution time for the whole block. Any idea how to get the time of JUST the last query? The beef is that I don't want the previous queries to interfere with the timing it gives me. Example below:

set echo on;
set feedback on;
set timing on;
set serveroutput on;

  pid varchar(20);
  aid varchar(20);
  bob char(1);

  select st.some_id, st.person_id into aid, pid
    from some_table st
      join person p on st.person_id = p.person_id
    where not like '%bob%' and rownum = 1;

  -- get timing of another query here


When I run this it gives me the total execution time of the code block:

Elapsed: 00:00:00.045
share|improve this question
up vote 4 down vote accepted

Save sysdate or systimestamp into a pl/sql variable just before the query then use dbms_output.put_line to output the difference afterwards.

share|improve this answer
this seems like it will do the job. thanks! – Andy Aug 30 '11 at 18:16

I'd probably do what you need to do the way that Jack Douglas suggested. But if you really want to go fancy, you coud query elapsed_time and divide it by the number of executions of the query, so you get the average execution time for a query.

With the following function...

create or replace 
procedure previous_sql_stats (
            ela in out number,
            exe in out number
) as

      v$sql      s join
      v$session  t on s.hash_value = t.prev_hash_value and
                      s.address    = t.prev_sql_addr

end previous_sql_stats;

... you can get these figures like so:

  dummy number;

  ela   number;
  exe   number;


    avg(sysdate - a.created) into dummy
    dba_objects a cross join
    dba_objects b
    rownum < 1e6;

  previous_sql_stats(ela, exe);

      'total elapsed time: '         || round(ela/1e6,2) || 
      ' seconds, total exectuions: ' || exe || 
      ', per execution: '            || round(ela/exe/1e6,2) || 
      ' seconds');

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.