Looking for something? Try here..

Monday, July 13, 2015

Query elapsed time

Many times, for performance point of view we would like to know the elapsed time of a query. May be for comparison purpose or for documenting the elapsed time in good and bad scenarios. The following piece of code will get you the elapsed time for any query.
This code uses the dbms_utility.get_time procedure before and after the query is run so that we get the elapsed time. Make sure you run the query as a single block so we don't have an additional time added to the difference.
set serveroutput on
variable n number
exec :n := dbms_utility.get_time
-- Place your actual query here
select * from dual;
-- Query ends here
   ( (round((dbms_utility.get_time - :n)/100,2)) || ' seconds' );

Reference oracle_document and Asktom site

Happy Working!

No comments:

Post a Comment