oracle query execution time

Question

Hi! I would like to get my guery execution time from Oracle DB. I dont want the time when Oracle finish to print results. In MySQL it is easy to get execution time from shell, but in SQL Plus there are no such thinks.

Thanks for any help!

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

Answer 3

I'd recommend looking at consistent gets/logical reads as a better proxy for 'work' than run time. The run time can be skewed by what else is happening on the database server, how much stuff is in the cache etc.

But if you REALLY want SQL executing time, the V$SQL view has both CPU_TIME and ELAPSED_TIME.