Oracle query execution time

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".

Use:

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

Or possibly:

SET TIMING ON;-- do stuffSET 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

select LAST_LOAD_TIME, ELAPSED_TIME, MODULE, SQL_TEXT elapsed from v$sql  order by LAST_LOAD_TIME desc

More complicated example (don't forget to delete or to substitute PATTERN):

select * from (  select LAST_LOAD_TIME, to_char(ELAPSED_TIME/1000, '999,999,999.000') || ' ms' as TIME,         MODULE, SQL_TEXT from SYS."V_\$SQL"    where SQL_TEXT like '%PATTERN%'    order by LAST_LOAD_TIME desc  ) where ROWNUM <= 5;