CODEWITHSUNDEEP
sqloracle-databaseplsqloracle12c
Ekos IV
Ekos IV

Reputation: 367

Start and end time of a query

As part of the requirements for a stored procedure, I need to provide the start and end time of a particular sub-query. For example, let's say that my query is:

SELECT  price
  FROM  vehicles
  WHERE wheels < 3

The output of my stored procedure should be a refcursor that points to a table like:

price  start_time          end_time
12000  2018-08-08 08:42:31 2018-08-08 08:42:52
130000 2018-08-08 08:42:31 2018-08-08 08:42:52
6000   2018-08-08 08:42:31 2018-08-08 08:42:52
3500   2018-08-08 08:42:31 2018-08-08 08:42:52

where start_time is the time I started the select statement, and end_time is the time it completed.

I've tried the following:

WITH start_time AS
( SELECT sysdate AS start_time
    FROM dual
), main_query AS
( SELECT  price
    FROM  vehicles
    WHERE wheels < 3
), end_time AS
( SELECT sysdate AS end_time
    FROM dual
)
SELECT       main_query.*
     ,       TO_CHAR(start_time, 'YYYY-MM-DD HH24:MI:SS') AS start_time
     ,       TO_CHAR(end_time, 'YYYY-MM-DD HH24:MI:SS') AS end_time
  FROM       main_query
  CROSS JOIN start_time
  CROSS JOIN end_time
;

But both start_time and end_time say the same time.

I know this is an unconventional (and clunky) requirement, but needs must...

Upvotes: 3

Views: 1504

Answers (2)

diziaq
diziaq

Reputation: 7815

Results of functions systimestamp and sysdate are being cached when you use them in SQL, so they always return a moment when the query has started.

Create custom function my_timestamp without caching, so it would be called once per fetching each row. And consider that sysdate has second precision, so there's a chance to miss a time gap on blazing fast queries. That's why systimestamp is more useful for benchmarking.

Depending on how it's going to be used, sometimes it's important to apply /*+ materialize */ hint to ensure that the query executed the only time, otherwise on small result sets optimizer could decide to run the subquery several times making time results inconsistent. 

with
  function my_timestamp return timestamp is begin return systimestamp; end;

  main_query AS (      
    SELECT /*+ materialize */
      my_timestamp AS custom_ts, 
      systimestamp AS standard_ts,
      T.* 
    FROM vehicles T  
  )  
select (select max(custom_ts) from main_query) as end_time,
       (select min(custom_ts) from main_query) as begin_time,
       T.*
from main_query T;

PS. Also, take a look at dbms_utility.get_time function for measuring time of code execution.

Upvotes: 2

JohnHC
JohnHC

Reputation: 11205

If part of a procedure, let it handle in two parts. First we get the data, then we return it.

We create an object to store the data temporarily:

create type r_price_index as object (
    price  number(10,2));
create type t_price_index is table of r_price_index;

Then in your proc:

declare
v_price_index t_price_index;
v_t_stamp1    date;
v_t_stamp2    date;
v_cur         sys_refcursor;
v_out         sys_refcursor;

begin

v_t_stamp := sysdate; -- The start time

open v_cur for
  SELECT  price
  FROM  vehicles
  WHERE wheels < 3;
fetch v_cure bulk collect into v_price_index; -- Store the data in the object
close v_cur;
v_t_stamp2 := sysdate; -- The end time

open v_out for -- Output the stored data
  select price, v_t_stamp1 as s_time, v_t_stampt2 as e_time
  from table(v_price_index);
end;

You'll want to put in some exception handling (no_data_found, etc), but this should give you exactly what you're looking for.

Upvotes: 4

Related Questions