Get timing on individual queries in Anonymous code block

oracleplsql

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

Best Answer

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

Related Question