Oracle query execution time

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?

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

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;

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.

set timing on
spool /home/sss/somefile.txt
set termout off
select ...
set termout on
set timing off
spool off

Save it to a script.sql, and do this in terminal:

sqlcl user@host/dbname @/path/to/your/script.sql