Oracle query execution time

Question

I would like to get the query execution time in Oracle. I don't want the time Oracle needs to print the results - just the execution time.

In MySQL it is easy to get the execution time from the shell.

How can I do this in SQL*Plus?

Answer 1

One can issue the SQL*Plus command SET TIMING ON to get wall-clock times, but one can't take, for example, fetch time out of that trivially.

The AUTOTRACE setting, when used as SET AUTOTRACE TRACEONLY will suppress output, but still perform all of the work to satisfy the query and send the results back to SQL*Plus, which will suppress it.

Lastly, one can trace the SQL*Plus session, and manually calculate the time spent waiting on events which are client waits, such as "SQL*Net message to client", "SQL*Net message from client".

Answer 2

Use:

set serveroutput on variable n number exec :n := dbms_utility.get_time; select ...... exec dbms_output.put_line( (dbms_utility.get_time-:n)/100) || ' seconds....' ); 

Or possibly:

SET TIMING ON;  -- do stuff  SET TIMING OFF; 

...to get the hundredths of seconds that elapsed.

In either case, time elapsed can be impacted by server load/etc.

Reference:

• ASKTOM - SET TIMING ON/OFF