Get timing on individual queries in Anonymous code block

Question

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;

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

  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 p.name not like '%bob%' and rownum = 1;

  -- get timing of another query here

end;

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

Elapsed: 00:00:00.045

Answer 1

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

Answer 2

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
begin

    select
      s.elapsed_time,
      s.executions
    into
      ela,
      exe
    from
      v$sql      s join
      v$session  t on s.hash_value = t.prev_hash_value and
                      s.address    = t.prev_sql_addr
    where 
      t.audsid=sys_context('userenv','SESSIONID');

end previous_sql_stats;
/

... you can get these figures like so:

declare
  dummy number;

  ela   number;
  exe   number;

begin

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


  previous_sql_stats(ela, exe);

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

end;
/